Pinba: profile PHP-FPM bottlenecks on Nginx / MariaDB

2016 note: this uses an older version of MariaDB. Make sure you update the relevant version numbers.

Pinba is a tool developers can use to profile their code under production conditions. Don't think of it as a debugger, more of a way to see bottlenecks and monitor (hopefully good) changes once the bugs causing those bottlenecks are fixed. I use it to nut out a Wordpress performance which generally sucks needlessly at CPU, depending on the plugins installed.

The Pinba Engine acts as a pluggable storage engine for  MySQL or MariaDB. Clients such as the PHP Pinba Extension and the Nginx Pinba Module feed data in. The engine itself provides access to reports and raw data. There is a GUI available called "Pinboard".

The documentation for installing it is fairly light on and in addition to this, distro packages are fairly out of date (Trusty has 1.0 packages, the current version is 1.1 released Feb 2015)   meaning we have to build the plugin and extensions from source. In addition to that, Pinboard 1.5 required Pinba 1.1 to be available.

Many developers have moved to MariaDB and I thought it would be helpful to share how Pinba was built for MariaDB 10.0. When you update MariaDB you'll have to build Pinba against the new version's sources to avoid inconsistencies.

Packages installed from distro and provider repos


First off, this assumes you have MariaDB 10.0 installed and configured, along with Nginx and PHP-FPM up and running. There are plenty of tutorials out there to help you with this, I'll assume if you are reading this then you have your stack up and running ready to profile.

Lastly: compile on a system that mirrors production, then install the built files on production.

Pinba dependencies


Building the Pinba Engine requires the following packages to be installed on an Ubuntu 14.04 LTS server:
$ sudo apt-get install libprotobuf-dev libprotobuf-lite8 libprotobuf8 protobuf-compiler libjudydebian1 libjudy-dev libevent-dev libevent-2.0-5 libevent-core-2.0-5 libevent-extra-2.0-5 libevent-openssl-2.0-5 libevent-pthreads-2.0-5 libncurses5-dev libtinfo-dev

To configure MariaDB from sources, you'll need cmake:
$ sudo apt-get install cmake

Create temp directories and clone sources


On the server you are building Pinba Engine:
$ mkdir ~/tmp/pinba
$ cd ~/tmp/pinba
$ git clone https://github.com/tony2001/pinba_engine.git
$ git clone https://github.com/tony2001/pinba_extension.git
$ git clone https://github.com/tony2001/ngx_http_pinba_module.git

First, we make a directory for building, switch to it then clone the engine and both the PHP and Nginx extensions.

Get MariaDB server source


We need the MariaDB server source matching the installed system package in order to build the Pinba Engine plugin:
$ apt-get source mariadb-server
$ tar -xzvf <source.tar.gz>

wait a while.... then when done, switch to the directory containing the source. E.g:
$ cd mariadb-10.0-10.0.16+maria

then run cmake to configure the source and generate the required include/my_config.h
$ cmake .

After this  is done the file 'my_config.h' should be in 'include'. i.e mariadb-10.0-10.0.16+maria/include/my_config.h

Build Pinba Engine


Next up, switch to the Pinba Engine source you cloned from Github, run ./buildconf.sh to create the configure file, then configure it. Note that you not longer need the --with-protobuf option.
$ cd ~/tmp/pinba/pinba_engine
$ ./buildconf.sh
$ ./configure \
--with-mysql=/your/home/tmp/mariadb-10.0-10.0.16+maria \
--with-judy=/usr \
--with-event=/usr \
--libdir=/usr/lib/mysql/plugin.

Install the plugin once configured:
$ make
$ sudo make install

This will create the plugin in /usr/lib/mysql/plugin and create a default_tables.sql in /usr/local/share/pinba_engine/

Log into mysql using root and on the mariadb console install the plugin and import the default tables:

$ mysql -u root -p
INSTALL PLUGIN pinba SONAME 'libpinba_engine.so';
CREATE DATABASE pinba;
\u pinba
\. /usr/local/share/pinba_engine/default_tables.sql
\q

Restart MariaDB and check that the process is running.

PHP Extension


To build the PHP extension, switch over to the sources cloned from Github  and create it:
$ cd ~/tmp/pinba_extension
$ phpize
$ ./configure --enable-pinba
$ sudo make install

Ensure pinba_extension is enabled in PHP:
$ cd /etc/php5/mods-available
$ vim pinba.ini

add extension=pinba.so and :wq. You'll need to symlink this file to both the fpm/conf.d and cli/conf.d directories to get data from both types of PHP requests.
For CLI, add another ini file in cli/conf.d that enables Pinba (e.g a pinba.enable=1 and pinba.server= 127.0.0.1:30002).

Test PHP:

$ sudo php5-fpm -t -y /etc/php5/fpm/php-fpm.conf

Reload PHP if the configuration change is OK (sudo service php5-fpm reload). To then enable Pinba for relevant FPM pools, open the relevant pool file and add the configuration:
$ vim /etc/php5/fpm/pool.d/some.pool.conf
php_admin_value[pinba.enabled] = 1
php_admin_value[pinba.server] = 127.0.0.1:30002
php_admin_value[pinba.auto_flush] = 1

Reload PHP-FPM again.
To test PHP data collection, make a few requests on the relevant host and query the MariaDB table pinba.requests (e.g SELECT * FROM requests LIMIT 5 for instance)

NGINX extension


The easiest way to do this is to build a .deb package using the same method as shown in my ngx_pagespeed howto:
$ cd ~/tmp/nginx_source
$ vim ./nginx-1.6.2/debian/rules

Add "--add-module=/path/to/pinba/ngx_http_pinba_module \" then build the package and install it:
$ sudo dpkg-buildpackage -b
$ sudo dpkg -i nginx_1.6.2-1~trusty_amd64.deb

Run nginx -t to test configuration, nginx -V to check that the module is installed. You can then go ahead and configure Pinba in an enabled server{} within Nginx:
pinba_enable on;
pinba_server 127.0.0.1:30002;

BONUS: With the Nginx module enabled, you can add request tags to log into Pinba. For example:
# GET, POST or something else?
pinba_tag request_method $request_method;

# Pinboard logs script_name by default which is fairly useless when you use a front controller, tag the request with the request_uri instead:
pinba_tag request_uri $request_uri;

# who doesn't want to know the request Gzip ratio?
pinba_tag gzip_ratio $gzip_ratio;

# log stats about Nginx at request time
pinba_tag connections_active $connections_active;


Once you are happy with the config, reload Nginx.

Everyone likes graphs, so now go ahead and install Intaro Pinboard to get a decent view on the reports being generated by Pinba!

Post your comment

Comments

No one has commented on this page yet.

RSS feed for comments on this page | RSS feed for all comments