What is new in PHP 5.3 – part 3: mysqlnd

Date November 17, 2007

In the first two parts of this series I wrote about namespaces and late static binding. In this part I will cover mysqlnd (MySQL native driver for PHP), which is also a really cool feature of PHP 5.3. This a replacement library (not an extension) for libmysql (MySQL Client Library) offering a lot of advantage over it. While libmysql was developed primarily for C and C++, mysqlnd was designed and optimized for PHP, it takes advantages of the internal structure of Zend Engine. By the time of this writing mysql and mysqli are supported, and the integration to PDO is in working progress.

The overview of the advantages of mysqlnd over libmysql: high integration with the Zend Engine, faster execution, lower memory footprint, performance statistics for bottle-neck analysis, easier compilation, own development life cycle, usage of PHP stream API, client side query cache and a lot of possibility for future features.

Over the technical advantages theres is a licensing one. MySQL is rely on the GPL license which is not compatible with the PHP license, using libmysql with PHP needs the FLOSS exception, but mysqlnd is licensed under the PHP license so this is no more a problem (check the mysqlnd FAQ).

The installation process will be easier, you won’t need to have libmysql on the server. Libmysql is shipped with the MySQL server, updating libmysql means that you have to update the MySQL server too. With myslqnd this bounding no more exists, it has its own release and development life cycle. Mysqlnd has a common code base for PHP5 and PHP6, so the maintenance of the code is easier, a bug should be fixed only at one place.

High integration with the Zend Engine

Mysqlnd is highly integrated with the Zend engine, so it can leverage all the internal PHP infrastructure for communication and memory management, it can do a lot of tricky thing to improve performance. If you are curious about the details you should check Andrey Hristov’s presentation from mysqlnd (I get some illustration from it). You can find here a little benchmark about the performance of mysqlnd.

Comply with the memory_limit settings

Memory limit support
Myslqnd uses the Zend Engine’s memory management so it comply (in contrast to libmysql) with the memory limit setting. It allocates bigger chunk of memory (allocating memory is relatively expensive task), it tries to reuse zvals using zval cache to save CPU cycle.

Memory savings

Read only variables
Mysqlnd introduces an interesting concept: read only variables with which it can save a huge amount of memory. You can read about it in the mentioned presentation or in Ulf Wendel’s blog entry. To completely understand this you should be familiar with the internal representation of variables in PHP (I’ll write about it soon), the short version is the following. When you run a buffered query libmysql reads first the data into its own buffer, and then when you run mysql_fetch_*() function the data is get copied from this buffer into another memory area which represents the PHP variables. Mysqlnd uses a read buffer too, but when you run the mysql_fetch_*() function it isn’t copy the data but links the PHP variables to this memory area. This way it can consume only half as much memory as libmysql.

Usage of the PHP stream API

Mysqlnd uses the PHP stream API
Mysqlnd uses the PHP stream API, which holds a lot of possibility because it makes possible to hook into the communication with MySQL. Exposing the streams to users is not yet implemented, if it is done one can easily write a custom proxy using stream filters.

Persistent connections

You may know that persistent connections was disabled in mysqli extension, but with mysqlnd you will have again the possibility to use persistent connection (mysqli_connect('p:localhost', ...);). This is an interesting post about this topic.

Statistical data collection

Mysqlnd collects a lot of statistics which you can use for tuning your application. This statistical information will simplify the process of finding bottlenecks in your system, you can easily monitor your application and you can even build automatic problem detection into it. You can access these data via phpinfo(), mysqli_get_client_stats() (per process statistics), mysqli_get_connection_stats() (per connections statistics) functions. Currently 59 statistical information are collected by mysqlnd, you can read more about this at Ulf’s blog. It collects information about its internal zval cache, this you can get with the mysqli_get_cache_stat(), which could bu useful to tweak you zval cahe related php.ini settings, or to find an incidental bad habits in your code (for example you needlessly modify the fetched data and hence you lose the memory saving option).

Client-side query cache

Mysqlnd supports client-side query cache which could be very useful in some situation because it eliminates communication overhead (network latency), memory allocation overhead. By client side query cache it could be interesting the invalidation of the cache, mysqlnd currently supports only TTL (time-to-live) invalidation which you can control via php.ini settings. This part of the library is experimental, will change a lot in the future.

Possible future improvements, features

There is a lot of possibility in this area, just to name a few: improving the client side query cache, exposing PHP streams to users, prepared statement cache, automatic load balancing, built-in profiling. More foretastes for gourmands: the wishlist of Lukas, and Peter Zaitsev (MySQL Performance Blog).

Enjoy your meal! :)

13 Responses to “What is new in PHP 5.3 – part 3: mysqlnd”

  1. Chris Utz said:

    Great article! I cannot wait for PHP 5.3!

  2. developercast.com » Gergely Hodicska’s Blog: What is new in PHP 5.3 - part 3: mysqlnd said:

    [...] Hodicska continues his series on what’s coming up in the next major PHP release with a new post covering the new mysql [...]

  3. Andrey said:

    mysqlnd doesn’t have client side query caching by default. It’s in a branch which is not anymore updated. Some time in the future we will work again on it, but can’t say when.

  4. open source said:

    PHP 5.3 have got many features :) great!

  5. Ulf Wendel said:

    Thanks for choosing mysqlnd as a valuable addition to PHP.

    As Andrey has pointed out already, client side caching is not available yet. We have some ideas on it but currently no time and resources to implement it in a proper way. If PHP 5_3 comes out in Q1 2008, I doubt we’ll have it ready for a release. As client side caching is a killer feature you can be sure that we won’t forget about it. However the next milestone is a proper version for the PHP 5_3 release. Once that has happened we can pick up the idea again and check if we have resources to implement it.

    We are not sure about the Stream API, its a raw bin idea nothing more. No code, no concrete plans. Personally, I’d like to see an experimental branch featuring it and play with it for some days. But do we really need support for streams now that MySQL Proxy is there? Adding MySQL Proxy to your setup is a matter of minutes and Jan is making great progress on performance. Proxy comes with all what you need for load balancing, query rewriting, auto explain and stuff.

    Also, do you really want to parse the binary data send over the wire to MySQL using PHP. To be honest, nobody who has ever implemented the MySQL Client Server protocol has fallen in love with it. Its an efficient protocol but Joe Doe PHP user will hate it.

    There are few things that might be easier to do within your PHP application using a stream API than using the external MySQL Proxy. For example, I could imagine that if you application uses some sort of DB abstraction layer its much easier to turn on an “auto_explain” for only selected parts of your app but it would be with Proxy.

    However, does that really justify adding a stream API? And what do you think will happen to mysqlnd if a power user changes states using the stream API… Again, I’d love to play with it for a minute but I’m afraid that there’s no enough benefit in a stream API to justify the efforts.

    Try out MySQL Proxy. Its simple, its fast (enough), its how.

  6. Felho said:

    @Ulf: thx your comment, I really appreciate it. I’ve already played with MySQL Proxy, and I really liked it, there is a lot potential in it.

  7. PHP::Impact ( [str blog] ) » Blog Archive » PHP 5.3: Just keeps getting better and better said:

    [...] 1: Namespaces Part 2: Late Static Binding Part 3: mysqlnd Part 4: OpenID support, callStatic, user.ini, XSLT profiling and more. Posted by phpimpact [...]

  8. PHP 5.3: Just keeps getting better and better « PHP::Impact ( [str blog] ) said:

    [...] 1: Namespaces Part 2: Late Static Binding Part 3: mysqlnd Part 4: OpenID support, callStatic, user.ini, XSLT profiling and more. Posted by phpimpact [...]

  9. What’s new in PHP 5.3 « we’re only gonna die from our arrogance said:

    [...] What is new in PHP 5.3 – part 3: mysqlnd [...]

  10. PHP 5.3 Improvements » EricByers.com said:

    [...] and Gergely Hodicska has a truly excellent four part series/overview on what’s new (1, 2, 3, 4). PHP 5.3 is expected to be release mid-October, so get a learning (and putting pressure on [...]

  11. Buy acai berry said:

    Your blog is very interresting for me, i will come back here..

  12. DÅ©ng » Blog Archive » PHP 5.3 is Coming: Are You Ready? said:

    [...] Hodicska’s blog entry on the subject (also, part 2, part 3 and part [...]

  13. fleadie said:

    hi,

    Can we configure the socket_read_timeout in this version or not??

    bye.

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>