When it comes to performance developers are limited with a choice of a suitable tool for measuring it. For PHP programmers there is a good one - Pinba, and since there is no much articles about it in English I decided to write one.
Pinba is a MySQL pluggable storage engine that acts as a realtime monitoring/statistics server for PHP using MySQL as a read-only interface. It collects data sent by PHP extension which generates a data packet on request shutdown and sends it over UDP. Using UDP protocol means there is no need to establish connection and allows not to affect performance of PHP scripts on production servers.
Pinba accumulates data in a set of read-only MySQL tables which are of two types:
- raw data tables; using these tables makes it possible to generate custom reports, but keep in mind that accessing the raw data is relatively slow for there may be a great number of records with no indexes except primary keys;
- reports; already aggregated data, updates on-the-fly as new request data arrives.
And the most valuable in my opinion part of Pinba is the ability to measure particular parts of code using timers with arbitrary tags. More about them later. The Pinba working stack consists of two parts: Pinba engine and PHP extension. I forgot to mention I’m working on Mac OS X 10.8.2.
Pinba engine requires at least
5.1 version of MySQL (both sources and installation) since it’s the first version to support pluggable storage engine. Among other requirements Google Protocol Buffers and libevent (chances are it is already installed in your system). You can use Hoard memory allocator as an option to reduce memory consumption, but I won’t cover this option in this tutorial.
So, let’s start. Get and install
Google Protocol Buffers.
You’ll need MySQL sources in order to build Pinba engine. Notice that I had MySQL 5.5.28 installed on my machine and I got errors during engine installation. Finally I just upgraded to 5.6.10. More about it here. Pay attention to
-DBUILD_CONFIG=mysql_release option (read here).
For building Pinba engine you’ll also need
judy. I used brew to install them.
Finally, get and install Pinba engine.
After that make sure you’ve got
/usr/local/mysql/lib/plugin. Now that you have the plugin you need to enable it in MySQL.
Now let’s build and install PHP extension. It requires previously installed
Google Protocol Buffers and PHP development package.
Now enable the extension in
php.ini by adding
pinba.so and setting option
Now as you have everything installed you may want to configure Pinba engine or PHP extension. I’m just fine with default options but
port option. By default Pinba engine and PHP extension have
30002 port number in their configs, but I failed to gather any statistics with this configuration may be due to some permission problems. So I changed the default port to
If you are interested in more options here are official descriptions for engine and extension.
Running any PHP script, no matter CLI or via web server, causes PHP extension to send gathered statistics to Pinba engine. It accumulates in a set of MySQL tables which you can use to build any kind of reports. By the way, developers already made some work for us and created basic report tables. These tables have names starting with
report_. Here I will not focus on detailed description of each table and the data it stores for it is rather straitforward. For any explanation you’d better follow official documentation.
The real power is hidden in timers.
This mechanism allows to measure particular parts of code by integrating timers in you code by means of a set of functions provided by PHP extension. Moreover you can tag timers with special marks which are called, as you may guessed, tags. They are used to group timers information in tag reports.
Each timer consists of:
- float value - time between timer start and stop
- int hit_count - number of timer starts
- array of tags
To use timers you’ll basically need only two functions
pinba_timer_stop. Let’s look at the example:
After running this script you’ll have some data in tags tables:
tag table stores all tags used,
timertag stores tag values.
Having these tags you can count how often each operations runs on each server. To be honest this is quite useless example but I hope you got the idea.
- UPD doesn’t affect application performance
- raw data access
- basic reports
- grouping by tags
Here I described the process of installation of the PHP extension but Pinba engine API allows using any other languages and there already clients for Python, Ruby and Node.js.