PHP 5 with mysqlnd some tests, some numbers

[Jürgen Krieger]

lately i ran some tests.
And i have to say that the mysqlnd is awesome.

Statistics retrieved by:
* phpinfo
* xdebug
* system enviroment of www's

1) measured by loss of connection time when connection is established.
in dev we normally have a connection time of ~900ms --- initialize db-class now takes 100-150ms
2) better times in xdebug when connecting
dropped from the above ~900ms to 320ms (evaluated by 10.000 connects) ... still wonder why initial connection also speeds up
3) better query time in xdebug
not too much of boost though ... time decreases by 50ms per query to 300ms ( in dev enviroment normally takes 350ms)
4) couple of buffered hits (phpinfo)
5) one less query ... mysqlnd defaults to charset utf8 by connecting to our DB
so our $this->set_charset('UTF8'); is cut out
we use UTF8 as default for our site and for database ... the set_charset('UTF8') always costed us 150ms - 250ms each connection (so every page impression)
6) as we have many querys it instanly showed that we now have more memory left after our querys
we can't confirm 50% less memory load ... though about 30% is realistic and shown in our server statistics
7) as we have now buffered reads in use we decided to store less results in apc (our Zend_Session Handler) / memcache (global caching mechanism throughout www-cluster)
resulting in more memory left too

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.

Test Enviroment:
local: Linux 2.6.17-11-server #2 SMP Tue Mar 13 23:33:44 UTC 2007 i686 based Ubuntu ( 4 * Intel(R) Xeon(TM) CPU 2.66GHz, 2GB RAM)
Apache: Apache/2.0.55 (Ubuntu) DAV/2 PHP/5.2.4 SVN/1.4.4
php: PHP 5.2.4: mysqlnd: mysqlnd 5.0.2-dev - 070928 - $Revision: 1075 $
spread: $Id: php_spread.c,v 1.17 2006/05/08 14:01:55 mike Exp
Zend Engine v2.2.0 - APC 3.0.13 - MMAP Support
Xdebug v2.0.0RC3

foreign: (tcp conn over internet pptp)
Mysql: Server version: 5.1.20-beta

The advantage will drop on the real enviroment though as there we currently have an average time of 800ms.
But i think we still will get a boost of 10-15% in whole, that would be essential for us as we have 15.000 ppl online nearly 24/7 and heavily increasing.

(
I hope i get more information and some benchmarks soon too ...

brought to the point, the following points are the features we praise:
* default charset utf8 (by setting db default to utf8)
* persistent db-connection
* buffered reads
* zval-structure with *ptr
)

(Always keep in mind that my numbers where on our dev enviroment ... slow servers on slow internet connection to the database ...
so every connection and query (all that goes to the database) is damn slow ... mysqlnd basically speeds things up by keeping the connection established and so most communication and tcp-establishing is cut)

but the 10-15% in real enviroment are realistic based on the data i have.

many thanks to
* Adnrey Hristov (refering to adnrey-mysqlnd.pdf and email-support of him, plus new sources and install routines)
* Ulf Wendel (http://blog.ulf-wendel.de/) ... basic information on mysqlnd in his articles