Ulf Wendel

Dear MySQL and PHP users,

we have two good news for you. First, we are proud to announce that one year after the announcement to develop a new MySQL native driver for PHP (mysqlnd), the new driver has been committed into the 5.3 branch of the PHP project. PHP 5.3 is expected to be the next major release of PHP.

According to a poll on the PHP Internals development list the new driver is among the five most desired features for PHP 5.3. Thank you for your trust in mysqlnd! We will try to continue to listen to your whishes and are looking forward to hear back from you.

The second news is that we will strengthen our commitment into PHP by improving our support for PHP Data Objects (PDO). We will work on a PDO/mysqlnd driver, both for PHP 5.3 and PHP 6.0. Development starts in these days and will include QA, testing and documentation as we go forward.

Continue Reading →

Things start rolling! mysqlnd is among the five most desired features for PHP 5.3 according to a PHP 5.3 Feature Summary email from Ilia, who – at the time of writing – was doing the release management of PHP.

Thanks everybody for your trust in mysqlnd and your support! We shall try not to disappoint you in the future and continue with the development. Of course, as the core feature mature more and more, we will try to spend time on implementing new, useful tricks and try to provide you with additional documentation, if time permits.

Meanwhile we are still looking for your feedback. Tell us about your positive and negative experiences with mysqlnd. Jürgen Krieger did so in his forums posting and concludes And i have to say that the mysqlnd is awesome. .

He used Xdebug and system statistics to test PHP 5 with mysqlnd. Some of his findings:

• Connection time has gone down: from ~900ms down to ~320ms
• Better query time: from ~350ms down to ~300ms
• Less memory consumption: ~30% according to server statistics

All in all he describes the mysqlnd test run with:

as result (in dev enviroment … have to state this out as our dev is REAL slow)
we gain a average boost of 1000ms retrieving a page which originally took us 3.4 seconds.
That’s quite awesome.

Source: http://forums.mysql.com/read.php?52,175718,175718#msg-175718.

Ok, but what did I not mention so far? Well, the usual. Jürgen needed some compile/installation procedure information. If you want to compile mysqlnd, read this old posting with instructions how to install mysqlnd from the MySQL internal SVN. SVN/MySQL internal repository times should be gone soon – forever. Hopefully they will be gone once we have all code in the php.net CVS and we use the CVS as the one and only development place. By that time, we’ll update installation instructions as well. Stay tuned for further news on this.

Also, I did not mention some other quirks he found. You want to hear them? I promise to tell them with the next user report. Will you be the next user to give feedback? Positive or negative feedback, we build on your support! Don’t hesitate to nag us, if we miss your message on any of the usual places.

59 – that is the number of statistics collected by mysqlnd. When I started writing this blog post, I hoped it would be easy going describing them. But it wasn’t. Some ten days ago, we’ve had 51 statistics and near zero tests for them. Now we have 59 statistics. And, in a certain way, every figure is a tuning screw in the hand of one who knows what they do.

I must confess, I grossly underestimated the time it takes to write only a few words about some of them. Here is a first batch of comments on some 20 figures. Already those some 20 figures should give you an idea how the mysqlnd statistics might help you with bottleneck analysis, monitoring. Or you use the statistics to identify scripts that select more rows than they consume, open more connections than needed, …

How to access client statistics with mysqlnd

Statistics are only available with mysqlnd. Statistics can be accessed using:

• Per process: phpinfo()
• Per process: mysqli_get_client_stats()
• Per connection: mysqli_get_connection_stats(link mysqli)

Continue Reading →

Andrey has implemented a little mysqlnd only addition to mysqli_debug(): ‘m’ – dump memory information. It works like the other options described in PHP: Debugging ext/mysqli and mysqlnd with the only difference that it is only available with mysqlnd.

Without option ‘m’

This is a short example of mysqlnd freeing the meta data of a result set.

[...]
| | | >mysqlnd_res_meta::free
| | | | info : persistent=0
| | | | info : Freeing fields metadata
| | | | info : Freeing zend_hash_keys
| | | | info : Freeing metadata structure
| | | >mysqlnd_res_meta::free
[...]

Additional mysqlnd memory allocation debug information with option ‘m’

Same trace excerpt but with memory debugging turned on. The control string used for this trace was ‘t:O,/tmp/mysqlnd.trace:m’.

[...]
| | | >mysqlnd_res_meta::free
| | | | info : persistent=0
| | | | info : Freeing fields metadata
| | | | >_mysqlnd_pefree
| | | | | info : file=mysqlnd_result_meta.c line=  37
| | | | | info : ptr=0xb7b5d2e8 persistent=0
| | | | | info : before: 322788
| | | | | info : after : 322788
| | | | <_mysqlnd_pefree
| | | | >_mysqlnd_pefree
| | | | | info : file=mysqlnd_result_meta.c line=  37
| | | | | info : ptr=0xb7b608f8 persistent=0
| | | | | info : before: 322788
| | | | | info : after : 322788
| | | | <_mysqlnd_pefree
| | | | >_mysqlnd_pefree
| | | | | info : file=mysqlnd_result_meta.c line= 272
| | | | | info : ptr=0xb7b52bc0 persistent=0
| | | | | info : before: 322788
| | | | | info : after : 322452
| | | | <_mysqlnd_pefree
| | | | info : Freeing zend_hash_keys
| | | | >_mysqlnd_pefree
| | | | | info : file=mysqlnd_result_meta.c line= 287
| | | | | info : ptr=0xb7b607ac persistent=0
| | | | | info : before: 322452
| | | | | info : after : 322452
| | | | <_mysqlnd_pefree
| | | | info : Freeing metadata structure
| | | | >_mysqlnd_pefree
| | | | | info : file=mysqlnd_result_meta.c line= 291
| | | | | info : ptr=0xb7b5d490 persistent=0
| | | | | info : before: 322452
| | | | | info : after : 322452
| | | | <_mysqlnd_pefree
| | | <mysqlnd_res_meta::free
[...]

One good news, one bad news – that’s the frank way we blog about mysqlnd. The last posting on significant memory savings (40%) and new tuning options was good news. The bad news: mysqlnd might have bugs. How to report and debug these bugs – using mysqli_debug() – is subject of this posting.

Where to send mysqlnd problem reports, how to contact

Due to a low feedback rate – which is disappointing in a certain way – we are in the comfortable situation that you may report issues or ask questions on pretty much every channel: on the mailing list php@lists.mysql.com, on http://bugs.mysql.com/, on http://bugs.php.net/, by private mail (georg/andrey/uwendel at mysql dot com) or even using a blog comment. Of course, if you are a MySQL customer, you may also use the MySQL Support System to get in touch with us. Whatever way you choose, we should be get your message.

The hard part is to write a good bug report. Ideally, a good report comes at least with a debug trace of ext/mysqli. If you are an expert user, skip the introduction part and read below how to create a client trace with ext/mysqli.

Continue Reading →

mysqlnd saves memory. It consumes half as much memory as libmysql. This is what we have been convinced of. This is what we taught you. Then I tried to test it and made Andrey get nervous for a few hours… Meanwhile he is fine again and we can announce: mysqlnd saves memory, not only in theory, we tested it – we can proof it, can we?

The read-only variable trick

In theory it is so simple. libmysql is not part of PHP. If libmysql fetches any data from the MySQL Server, it puts the data into its own buffers. Then the data gets copied from the libmysql buffers into the ext/mysql resp. ext/mysqli data structures. Those data structures are “zvals“. “zval” is the name of the data structure that PHP uses internally – on the C level – to implement user variables. So, you have the data twice in memory: 1x inside the libmysql buffers and 1x inside mysqlnd. With mysqlnd you might have the data only once in memory. mysqlnd also uses buffers but links the user variables directly to the read buffers, whenever possible. The zvals that represent the user variables do not contain copies of the buffer, they try to work with pointers. Therefore mysqlnd sometimes consumes only half as much memory as libmysql. In the worst case it consumes up to the same amount, but often it is less. How much memory you save depends on the size of your (buffered) result set. If it is only 1kB, you will hardly be able to measure any difference. If you are fetching BLOBs or large result sets it can become more obvious.

Continue Reading →

Have you ever been annoyed about the input and output binding that is part of Prepared Statements? Prepared Statements have their room in PHP and MySQL[i] for some good reasons. Though, I recall that I didn’t like the output binding when I tried them for the first time. I wanted the good old mysqli_fetch_assoc() to be available. Last year in November someone, I think it was Lukas (but don’t blame me, if I’m wrong), suggested to implement mysqli_stmt_get_results() with mysqlnd.

By help of the new function, you can create a mysqli_result object from a statement that returns data (SELECT and other – version dependent!). Then you can use the mysqli_result object to process the returned data: fetch results, access meta data – all you can also do using a mysqli_result object returned by mysqli_query().

Continue Reading →

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.

Continue Reading →

Do you happen to have functions that read all rows of a database result into one array, without processing the rows, and pass the array to other functions? For example, do you fetch results in your database class and pass them to a template engine displaying? If so, here is excellent news for you. mysqli_fetch_all(), which comes with mysqlnd, does the task of fetching the data sometimes twice as fast as mysqli_fetch_array(). Reason being: it saves a loop with function calls…

mixed mysqli_fetch_all( mysqli_result $result [, int $resulttype] )

The new API call, which is only available if you build ext/mysqli with mysqlnd support, does fetch all rows of a result set into an array with just one function call:

$all_rows = mysqli_fetch_all($res);

The one mysqli_fetch_all() call does exactly the same as the following loop:

$all_rows = array(); 
while ($all_rows[] = mysqli_fetch_array($res)) 
  ;

In my testing I found result sets for which mysqli_fetch_all() took only 60% of the execution time of the mysqli_fetch_array() variant. At the first glance this sounds great, but you need to be aware of what it means to get all results in one array.

Continue Reading →

Miha Nedok from http://www.izklop.com wrote us a few days ago about their mysqlnd test run:

I wish I had another production box to test. But what we and our users see now, actually the users notified our team that the site is suddenly “always loading fast”.. The only change… you guessed it mysqlnd installed. It wasn’t slow before, but people noticed performance degradation at times that the site is most visited when libmysql was used.

Hick-ups solved

After a little hick-up during the first days the server is now running stable with ext/mysql @ mysqlnd. Immediately after the installation of mysqlnd, Miha noticed that persistent connections got closed because of a low wait_timeout MySQL Server setting. After changing the setting, the problems were gone and all sites (izklop.com, meanduck.com, keintower.de) that are hosted by same hardware setup are running fine.

Based on Miha’s feedback, we started to test persistent connections in more detail (killing server threads, provoking timeouts, checking limits, etc.)…. I must confess that we found and fixed some ten issues around persistent connections which his team had not noticed so far. Meanwhile, however, the persistent connection code seems to have matured very well and does not cause any issues any more neither on izklop.com nor in our testing.

Miha’s statement on the performance did really surprise me. I did not expect to read any such positive feedback. In the past I had many argues with my co-workers on performance. Quite often Andrey told me that in theory mysqlnd should be way faster than libmysql for this or that test. Most of the time I was unable to come up with figures that confirmed our expectations. Therefore, we tried to communicate “give it a try”. So, give it a try…