Lastly, I’ve been blogging about positive performance related feedback on mysqlnd. Whenever I did so, I got some positive feedback – for obvious reasons: I was posting good marketing news. On the one hand I like to get that feedback, on the other hand I do not feed comfortable with it. As much as I’d love to, I can’t stand behind a simple statement like “mysqlnd makes your applications faster”. You must not assume that each and every application will profit from mysqlnd. For example, my WordPress blog did not run any faster with mysqlnd. After looking at WordPress in more detail, this is no surprise to me.
Proper load simulation using JMeter
Recently, when I was visiting old fellows from Mayflower (AKA thinkPHP) in Munich, Johann told me to get familiar with JMeter. JMeter is a nice load and performance testing tool. You can use it to simulate users, search engines and other clients browsing a web site. The simulated clients can post data and fill out forms, accept or reject cookies, wait for random think times, validate responses and much more. All in all, you can emulate a work load that comes pretty close to real clients browsing a web site.
The type of load that JMeter can generate comes much closer to the reality than any load based on ab, httperf or http_load (see also Performance test tools). Simple tools like ab, which comes with the Apache, create a load similar to n lunatic users which open one URL in their browsers and press “Reload” as fast as they can for say 100 times. Most web sites will have few such users… Neither is the scenario of reloading a page over and over again very realistic nor will you be able to see concurrency effects when you hit just one or two pages.
Therefore I decided to use JMeter to test mysqlnd with my blog site. As my WordPress blog is virtually a read-only website, I did not need the full power of JMeter. For example, I did not need to simulate users posting comments or logging in to get personalized content. Therefore, all I did was load one week of Apache web log into JMeter and tell it to use the URLs recorded in the log to simulate 10 concurrent clients and 10.000 requests.
It is amazing to see how many bogus URLs are recorded in the log and how many spiders, crawlers and search engine generate a notable “background noise”. Sometimes, people forget about all this “background noise”, all this web server load when they use ab. In my eyes this is a mistake. The PHP scripts of a web site make only one part of the web server load, although not the smallest one… Sometimes people even dare to extrapolate performance figures or make assumptions on scaling based on simple testing with ab.
To make a long story short, however, my WordPress blog was not any faster with mysqlnd than with libmysql. First I was very disappointed. What a wonderful marketing news this would have been…
Xdebug tells you why
A few days later I got back to the topic. I installed Xdebug and APC on my test system, enabled Xdebug profiling, loaded the index.php page a few times in my browser and opened one of the cachegrind profiles generated by Xdebug into KCachegrind. KCachegrind can draw the call graph as a picture in which the size of a box is set in relation to the overall runtime.
I was not able to see any mysql_*-function in the call graphs. The reason is that the mysql_*-functions do not contribute to the overall runtime of this application in a major way. The dark orange box with “mysql2…” in it is a function of WordPress. It is not a function of the ext/mysql extension. This explains why there was little to no performance difference between libmysql and mysqlnd.
| % of the overall runtime | # calls | function |
|---|---|---|
| 2.65 | 30 | mysql2date |
| 2.15 | 21 | mysql_query() |
| 0.37 | 1 | mysql_connect() |
| 0.10 | 122 | mysql_fetch_field() |
| 0.07 | 196 | mysql_fetch_object() |
Learn: the impact of the API can be very small
The lesson to learn is that mysqlnd can’t do wonders with your web site.It depends very much on your application and web site traffic if you will profit from mysqlnd in notable way or not. In this post I give an example of a setup where PHP scripts make only one part of the web server load. The PHP scripts need to be loaded and executed. Thus PHP script execution time makes only a part of the entire time that it takes to run a PHP script. By the help of Xdebug I was able to find out that mysql_*-function calls take less than 3% of the runtime of a particular script. More than 2% out of these 3% are spend for mysql_query(). This might indicate that a good amount of time out of these 2% is spend waiting for the MySQL server and reading data from the network. Both are things that you can’t make that much faster when you rewrite the driver.
Let the contribution of the PHP scripts to the entire web server load be 80%, let mysqlnd be 10% faster for a certain operation than libmysql, ignore the time it takes to invoke PHP before script execution starts. Given this, mysql_* functions take as little as 2.4% of the overall runtime (3% out of 80%). Now, take 10% of these 2.4%. That is as little as 0.24% .
I have no reason not to trust Miha and his statement on mysqlnd performance. But I have a good reason to warn anybody who desperately wants simple marketing statements raising wrong expectations.