Imagine you’re approaching the planned release date. It is less than one week to go and you have just stopped doing micro-benchmarks only and started with more realistic benchmarks. As an example of a realistic benchmark, we have followed the decision of the german computer magazine c’t and choosen the Dell DVD Store. Last year a MySQL team had won (german press release) a database performance contest of the magazine and outperformed its competitors by far with an Apache, MySQL and PHP based contest submission. The benchmark is an OLTP-style application that simulates users browsing an online DVD store and purchasing DVDs.

However, I installed the benchmark on a computer and the first result I got with the contest submission setup was -40%. Minus 40%.

It seemed that our new development was 40% slower than your old, established solution! All previous benchmarks, which we had started to run long ago in parallel to the regular development, had shown that we are in the same league than before. Sometimes about 10% slower, sometimes faster. But the in same league. No need to worry, no need to shift development focus, no such alarming results.

On January, 26th the first figures we got from the Dell DVD Store benchmark have been about 5.500 operations per minute (opm) for ext/mysqli using mysqlnd (100%), roughly 7.800 operations per minute for ext/mysqli using libmysql, the MySQL Client Libarary, (~140%) and approximately 8.000 opm for ext/mysql using libmysql (~145).

It has been only five days to the release and we have simply not been there with our product. How could we fail so miserably? Luckily, this story has a good ending. One day before the launch, on January 31st, we repeated the benchmark and got:

Strike.

Working with senior developers and managers

On Friday, 26th I was pretty frustrated. I repeated the benchmark on another computer and I got the same result: 35% behind. On Saturday, Georg called me around lunch time: “I found the bottleneck”. As you know from above, he and Andrey fixed it.

Later on we realized that on both boxes that we used for benchmarking we had other processes running in the background. We have grossly underestimated their influence, because we did run the benchmark on a computer with several CPUs and 8GB of memory which seemed to be idle all the time (load < 0.2 )). Later in the evening, when people had stopped working on the computer, Georg could not confirm the first result but saw us more like 10% behind. This is similar to the impressions we had got before in our micro-benchmarks.

The lesson for you is not to make the same mistake! If you run benchmarks, run on a dedicated machine. No other people should be working on the computer, no KDE or any other background processing, nothing that could give you faulty results. The above figures from the 31st are from the same box but with all background processes stopped and several minutes of runtime to ensure hot caches.

Is a PHP mysqlnd applcation faster than a PHP libmysql application?

Unfortunately, there is no simple answer to the question if mysqlnd is faster than libmysql or not. For the Dell DVD Store mysqlnd is about as fast as libmysql. The figures for ext/mysqli using mysqlnd, ext/mysqli using libmysql and ext/mysql are all within 5%. This is not enough to promise that all of your PHP MySQL applications will become suddenly faster.

However, mysqlnd has a great potential and has become faster than libmysql in many cases. Here is one example from our micro-benchmarks. It comes from an AMD64 3000+ computer.

I’m sorry to say, there is a “but” here again. The first problem is that no real-life application calls affected_rows() 10.000 times in a loop. The second problem is that although mysqlnd is more than twice as fast as libmysql, you save only (5.96595 – 2.71338) / 10000 = 0.000325257 seconds execution time per mysqli_affected_rows() call on the computer that has performed this benchmark.

Of course, the 0.0003 seconds sum up with every script execution and every web server process/thread. But you have to understand that this significant improvement in the performance of mysqli_affected_rows() makes only a fraction of the total execution time for a web application. If wou want to make your web application really fast, avoid any dynamic content at all. Here are some figures from a quick test (german text on caching) of the lighttpd web server in 2003:

What sense does it make to get from 100 requests/s and 0.58% to 110 requests/s and per second 0.63%, for example by making mysqli_affected_rows() more than 50% faster? Keep this in mind. We could have made the function even 90% faster and execute at 1/10 or the original time, it does not make your applications suddenly faster. That said, let’s get excited about mysqlnd. Here comes another example of fetching 10 rows with a total size of 1500k each row. Maybe something for those who store images or documents in the database:

What has become faster?

Instead of asking if all applications suddenly will execute faster, you should study the performance properties of mysqlnd. The question is what has become faster. And of course, how much. Here we have two good news for you: we have included a set of scripts in the mysqlnd download archive that you can use for micro-benchmarking and we show example results.

The following table shows a list of micro-benchmarks. You will learn in a minute how to run these benchmarks on your computer. But first, let us explain how to interpret the table figures.

For every micro-benchmark the table shows one row. The first rows is about the micro-benchmark ‘georg_bench1.php’. The name of the benchmark is followed by “2 100%”. This tells you that 2 times have been recorded when running the PHP script.

Each column that follows the “2 100%” shows the binaries that have been used to execute the ‘georg_bench1.php’ micro-benchmark. Here, two binaries are compared. One has been given the label ‘libmysql’ and one has been given the label ‘mysqlnd’. The binary ‘mysqlnd’ has been faster than all other binaries for 2 (= 100%) of the times that have been recorded. Another example is ‘mysqli_fetch_all_vs_fetch_array.php’. For this benchmark mysqlnd has been faster for 212 (= 88%) of the 240 times recorded.

Here is the entire result from a run on my computer. Your computer, might give you different figures and relations. We are curious to hear from you, please tell us on php@lists.mysql.com (see also http://lists.mysql.com/php/.

Note also that this table says nothing about how much mysqlnd was faster or slower in my benchmark.

Running the hacker’s micro-benchmarks

One way to learn more about the performance about mysqlnd is to do benchmarking on your own. We have included the micro-benchmarks from above in the source distribution of the alpha relase. To run the micro-benchmarks you need to get a copy of PHP6. For example, you can check out a CVS copy. Create two copies to be able to compile two PHP binaries with a different code base. One using the original code and one using a patched mysqlnd version.

nixnutz@linux-eu6p:~/blog> cvs -d :pserver:cvsread@cvs.php.net:/repository checkout php6
cvs checkout: Updating php6
[...]
U TSRM/tsrm_win32.h
nixnutz@linux-eu6p:~/blog> cp -R php6 php6_libmysql
nixnutz@linux-eu6p:~/blog> cp -R php6 php6_mysqlnd
nixnutz@linux-eu6p:~/blog> ls
php6  php6_libmysql  php6_mysqlnd

Get a copy of mysqlnd. You can download it from http://dev.mysql.com/downloads/connector/php-mysqlnd/ or check out the public (read-only) SVN repository as shown below. Then, remove the ext/mysqli directory from the directory php6_mysqlnd and copy the ext/mysqli directory from the mysqlnd distribution in it.

nixnutz@linux-eu6p:~/blog> svn co http://svn.mysql.com/svnpublic/php-mysqlnd/
A    php-mysqlnd/trunk
[...]
Ausgecheckt, Revision 19.
ixnutz@linux-eu6p:~/blog> rm -rf php6_mysqlnd/ext/mysqli
nixnutz@linux-eu6p:~/blog> cp -R php-mysqlnd/tags/release-5.0.0-alpha/ext/mysqli php6_mysqlnd/ext/mysqli
nixnutz@linux-eu6p:~/blog> ls php6_mysqlnd/ext/mysqli
config.m4   INSTALL  mysqli_api.c  mysqli_driver.c  mysqli_embedded.c   mysqli_fe.c      
 mysqli_nonapi.c  mysqli_repl.c    mysqli_report.h   mysqlnd       README
config.w32  LICENSE  mysqli.c      mysqli.dsp       mysqli_exception.c  
mysqli_mysqlnd.h  mysqli_prop.c    mysqli_report.c  mysqli_warning.c  php_mysqli.h  tests

At this point you are ready to compile PHP6 with ext/mysqli using mysqlnd. Go to the PHP6 source directory php6_mysqlnd/ and do the usual compile steps. The configure parameter for mysqlnd are: –with-mysqli –enable-mysqlnd. Unlike normally, you do not specify the path to the tool mysql_config.

nixnutz@linux-eu6p:~/blog> cd php6_mysqlnd
nixnutz@linux-eu6p:~/blog/php6_mysqlnd> ./buildconf --force
nixnutz@linux-eu6p:~/blog/php6_mysqlnd> ./configure --with-mysqli --enable-mysqlnd
nixnutz@linux-eu6p:~/blog/php6_mysqlnd> make
nixnutz@linux-eu6p:~/blog/php6_mysqlnd> ls -la sapi/cli/php
-rwxr-xr-x 1 nixnutz users 13281952 2007-02-02 14:13 sapi/cli/php
nixnutz@linux-eu6p:~/blog/php6_mysqlnd> sapi/cli/php -i | grep mysqlnd
Configure Command =>  './configure' '--with-mysqli' '--enable-mysqlnd'
Client API library version => mysqlnd 5.0.1-alpha - 50100 - $Revision: 18 $
PWD => /home/nixnutz/blog/php6_mysqlnd
_SERVER["PWD"] => /home/nixnutz/blog/php6_mysqlnd
_ENV["PWD"] => /home/nixnutz/blog/php6_mysqlnd

Once you managed to build a first binary that can be used for micro-benchmarking, you need to compile a second one. This time PHP6 with a classical ext/mysqli using libmysql. You can either use the ext/mysqli tree from the PHP CVS or the ext/mysqli tree from the mysqlnd tree. Both trees can be used. I am lazy here and I use the original ext/mysqli tree from the PHP CVS. The important point is to make sure that you get a second binary and to make sure that you do not mix up your setup. I personally like to have two build directories as shown here. So, build a second PHP with the classical configure parameter: –with-mysqli=/path/to/mysql_config .

nixnutz@linux-eu6p:~/blog/php6_mysqlnd> cd ../php6_libmysql/
nixnutz@linux-eu6p:~/blog/php6_libmysql> ./buildconf --force 
nixnutz@linux-eu6p:~/blog/php6_libmysql> ./configure --with-mysqli=/usr/local/mysql/bin/mysql_config       
nixnutz@linux-eu6p:~/blog/php6_libmysql> ls -la sapi/cli/php
-rwxr-xr-x 1 nixnutz users 15178082 2007-02-02 15:55 sapi/cli/php
nixnutz@linux-eu6p:~/blog/php6_libmysql> sapi/cli/php -i | grep mysqli | head -n 2
Configure Command =>  './configure' '--with-mysqli=/usr/local/mysql/bin/mysql_config'
mysqli

Configuring the micro-benchmarks

Now you have two binaries that you can use for benchmarking. The micro-benchmarks used here are very simple. All they do is use the binaries to run a script and let the script record the runtimes in a MySQL database. Then, a complicated script, creates some text and HTML output. How this is done, is explained below. But usually you want to see performance figures and not read long stories about the how.

Go back to the php6_mysqlnd directory into which you had copied the ext/mysqli tree from the mysqlnd distribution and check the tests directory. Inside ext/mysqli/tests/bench you find the framwork that runs the micro-benchmarks. The directory framework/ holds the files of the framework and micro_benches/ holds the scripts that will be benchmarked.

nixnutz@linux-eu6p:~/blog/php6_mysqlnd/ext/mysqli/tests/bench> ls -la
insgesamt 11
drwxr-xr-x 5 nixnutz users  160 2007-02-02 14:03 .
drwxr-xr-x 4 nixnutz users 6264 2007-02-02 14:03 ..
drwxr-xr-x 3 nixnutz users  312 2007-02-02 14:03 framework
drwxr-xr-x 3 nixnutz users 1064 2007-02-02 14:03 micro_benches
-rw-r--r-- 1 nixnutz users 3699 2007-02-02 14:03 README
drwxr-xr-x 7 nixnutz users  328 2007-02-02 14:03 .svn

In the directory framework/, you will find a file ‘config.php’. Adapt it to your needs. Inline comments in the file explain what to do.

nixnutz@linux-eu6p:~/blog/php6_mysqlnd/ext/mysqli/tests/bench/framework> ls -la
insgesamt 56
[...]
-rw-r--r-- 1 nixnutz users  2393 2007-02-02 14:03 config.php
[...]

In particular make sure that you:

• change the database connection parameter
• make sure the MySQL database configured with RB_DB_DB exists
• update the list of binaries in the hash $rb_binaries

Finally, you can try to run one of the micro-benchmarks. Here is my attempt to start it. It ends in an error, because I have not started the configures MySQL Server to store the runtimes yet. However, this failure is very good for educational purpose, because it shows you what the script does and how to debug. The script tries to execute a copy of the micro-benchmark (mysqli_connect_php_run_normal.php). You can run and debug this file like any other standalone PHP script. You can see the exact call what the framework does to run this script. When you found the cause of the failure, you must manually remove it before you rerun the framework.

nixnutz@linux-eu6p:~/blog/php6_mysqlnd/ext/mysqli/tests/bench/framework> ~/blog/php6_mysqlnd/sapi/cli/php main.php -v ../micro_benches/mysqli_connect.php

[2007-02-02 16:16:08] Starting run for binary 'libmysql' and runner 'rb_testrunner_normal'...
  ... running file '/home/nixnutz/blog/php6_mysqlnd/ext/mysqli/tests/bench/micro_benches/mysqli_connect.php'
  ...'/home/nixnutz/blog/php6_libmysql/sapi/cli/php -f 
/home/nixnutz/blog/php6_mysqlnd/ext/mysqli/tests/bench/micro_benches/mysqli_connect_php_run_normal.php'
Errors during bench run: 'Connect TCP/IP success' failure [original code: yes]
'Connect Socket success' failure [original code: yes]


Syntax:
  program [options] dir_or_file [dir_or_file [dir_or_file ...]]

Options:
  -v                - Verbose
  -h                - Help
  -q                - Quiet, suppress output
  -p                - Profile with oprofile (see config.php)

I started the configured MySQL Server, removed the temporary file and rerun the main.php

ixnutz@linux-eu6p:~/blog/php6_mysqlnd/ext/mysqli/tests/bench/framework> rm ../micro_benches/*run_normal*
nixnutz@linux-eu6p:~/blog/php6_mysqlnd/ext/mysqli/tests/bench/framework> ~/blog/php6_mysqlnd/sapi/cli/php main.php -v ../micro_benches/mysqli_connect.php

[2007-02-02 16:23:43] Starting run for binary 'libmysql' and runner 'rb_testrunner_normal'...
  ... running file '/home/nixnutz/blog/php6_mysqlnd/ext/mysqli/tests/bench/micro_benches/mysqli_connect.php'
  ...'/home/nixnutz/blog/php6_libmysql/sapi/cli/php -f 
/home/nixnutz/blog/php6_mysqlnd/ext/mysqli/tests/bench/micro_benches/mysqli_connect_php_run_normal.php'
[...] 
                                         libmysql          mysqlnd
---------------------------------------------------------------------------
Connect TCP/IP success                   2.3604s ( 100%)  2.6366s ( 111%)
Connect Socket success                   2.2580s ( 100%)  3.0613s ( 135%)
Connect Socket failure                   1.9615s ( 100%)  2.0672s ( 105%)
Connect TCP/IP failure                   1.9193s ( 100%)  1.8259s (  95%)
===========================================================================
Total                                    8.4991s ( 100%)  9.5910s ( 112%)
---------------------------------------------------------------------------

No error, mysqlnd is about 10% slower for mysqli_connect(). However, this is the first alpha release and for the
Dell DVD Store we already beat libmysql, although we are slower here!

nixnutz@linux-eu6p:~/blog/php6_mysqlnd/ext/mysqli/tests/bench/framework> ls -la web/
insgesamt 284
drwxr-xr-x 2 nixnutz users   200 2007-02-02 16:24 .
drwxr-xr-x 4 nixnutz users   336 2007-02-02 16:24 ..
-rw-r--r-- 1 nixnutz users 62022 2007-02-02 16:24 index.html
-rw-r--r-- 1 nixnutz users 63109 2007-02-02 16:24 index_wiki.txt
-rw-r--r-- 1 nixnutz users 77022 2007-02-02 16:24 mysqli_connect_php.html
-rw-r--r-- 1 nixnutz users 78143 2007-02-02 16:24 mysqli_connect_php_wiki.txt

How does the micro-benchmarking work?

The basic idea of these micro-benchmarks is to have a several PHP binaries executing the same script on the command line. The script is plain-vanilla PHP. It provides two hashes $errors and $times which the caller of the script can use to generate some human-readable output. Times are recorded using microtime(true).There is no web server involved and as few overhead as possible to make low-level debugging with oprofile, valgrind, callgrind, ddd, strace and whatever else development tool as simple as possible.

The scripts in the directory micro_benches look like this. It should be easy for everybody to add their own micro-benchmarks:

$times = $errors = array();
$runs = 20000;

do {
  $times['overall'] = microtime(true);

  if (!$mysqli = new mysqli($host, $user, $passwd, $db, $port, $socket)) {
    $errors[] = sprintf("Cannot connect: [%d] %s\n", 
       mysqli_connect_errno(), mysqli_connect_error());
    break;
  }
 [...]
}

The framework loops over the configured PHP binaries and does for every PHP binary it finds: php -f micro_benchmark_php_run_normal.php . With micro_benchmark_php_run_normal.php beeing:

$db = ;
[...]
include('micro_benchmark.php');
[...]
$fp = fopen('data_exchange_file_for_caller', 'w');
fwrite($fp, serialize(array('errors' => $errors, 'times' => $times));
fclose($fp)

The framwork opens the file ‘data_exchange_file_for_called’ reads the hashes $errors and $times, decides what to do and might eventually present you the results in a human-readable way. Currently the framework is over-modularized. However, the idea is to have one fine day more “runners” that do “strace -tt php -f micro_bench.php” and pre-process the output for system call bottleneck-analysis or use oprofile for CPU time analysis.

What do the figures mean?

The meaning of the labels or figures like “Connect TCP/IP success” of a micro-benchmark depends on the PHP script used. You do not know what it means and tries to measure, before you look it up in the source of the micro-benchmark. In this case the answer is in “mysqli_connect.php”. Note that labels can be totally misleading. You need to look carefully at the script. This is why the entire source code of the script is part of the generated HTML output.

It is a hacker’s tool!

Benchmarking and bottle-neck analysis is difficult. You need to be a bit of a hacker. And the first figures we present about mysqlnd are figures for hackers. At this point the message to you is: we believe to have made already the alpha faster than libmysql in many cases, but check yourself. And help us to further improve performance and stability. php@lists.mysql.com is the preferred way to contact us.