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
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.
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, 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.
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
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!