PHP: the 38 characters of the mysqlnd profiler
Recently I was forced to benchmark a mysqlnd plugin on Windows. X-Debug was no help. It worked flawless on Windows but it can't tell you what goes on inside a PHP user space API call, for example, you will not know how much time mysqli_query() spends reading a result set. Very Sleepy is nice and the latest Microsoft Visual Studio profiler are probably wonderful but any of those external profiling tools did give me too fine-grained information. Also, they are external profiler which means you have to install extra software.
The mysqlnd statistics didn't help me either. I didn't need any aggregated values, I was curios if a certain function was the bottleneck. The solution:
- extra statistics for critical sections in the plugin using the mysqlnd statistics framework (C coding)
- dump profiling information into mysqlnd debug log (thanks Andrey)
- make mysqlnd debug log available on Windows (when using VC9 or newer, thanks Andrey)
Since today mysqlnd adds profiling information to its debug log. Note the "(total = n own= n in_calls=n)" behind the closing tag of a function call. The times are given in milliseconds. For example, "_mysqlnd_pecalloc (total=401 own=401 in_calls=0)" tells you that mysqlnd has spend 401 milliseconds to allocate some memory. Why is that so slow? Simple: I'm using a VM.
Here is how you create such a debug log with profiling information:
- Get PHP 5.3.4-dev, the fresh meat not the stinky one
- Compile PHP with mysqlnd support and enable debugging, for example, ./configure --with-mysqli=mysqlnd --enable-debug on Linux or configure --with-mysqli --with-mysqlnd --enable-debug on Windows.
- Set the PHP configuration (php.ini) setting: mysqlnd.debug, for example, mysqlnd.debug="d:t:O,/tmp/mysqlnd.log" (38 characters) on Linux or mysqlnd.debug="d:t:O,mysqlnd.log" (34).
- Run your PHP MySQL script
Pitfall: the mysqlnd debug option parser does not support file names that contain ":" You cannot use something like "C:\tmp\mysqlnd.log". Its about debug and profiling. We will probably not lift that limitation without a strong need. But, of course, mysqlnd is Open Source. If you need it urgently, feel freea