27 Jun 2009

PHP's pg_connect via pgpool-II vs pgBouncer vs native

In the last few weeks one of our customer had some performance issues with our web application. We discovered a lot of opportunities in our infrastructure that would improve the performance. I’m currently working on an article series about those experiences.

Whatever, if you’re running a high-traffic PHP application with a PostgreSQL database as backend you might be running into problems with slow repsonse times of your pages caused by pg_connect. This was one of the things happens to our application.

To solve this issue you can use a connection pooling software like pgpool-II or pgBouncer. I’m not going to explain what this makes or show how this works in theory. I just give you some statistics fresh from one of our servers to think about it. The webserver is running on Apache 2 + fcgid + PHP (CGI). This explains why the first benchmark results are low and increasing by and by.

Overview

1) PHP file without a database connection

/etc/init.d/apache2 restart ; \ 
  for c in {1..10}; \
  do ab -n 1000 -c 100 http://localhost/phpinfo.php 2>&1 | \
    grep "Requests per second"; \
  done
Restarting web server: apache2 ... waiting ..
Requests per second:     55.04 [#/sec] (mean)
Requests per second:    117.04 [#/sec] (mean)
Requests per second:    108.67 [#/sec] (mean)
Requests per second:     99.30 [#/sec] (mean)
Requests per second:    114.79 [#/sec] (mean)
Requests per second:    155.76 [#/sec] (mean)
Requests per second:    152.06 [#/sec] (mean)
Requests per second:    142.65 [#/sec] (mean)
Requests per second:    154.77 [#/sec] (mean)
Requests per second:    149.60 [#/sec] (mean)

2) PHP file with pg_connect to PostgreSQL

/etc/init.d/apache2 restart ; \
  for c in {1..10}; \
  do ab -n 1000 -c 100 http://localhost/phpinfo2.php 2>&1 | \
    grep "Requests per second"; \
  done
Restarting web server: apache2 ... waiting ..
Requests per second:     8.50 [#/sec] (mean)
Requests per second:    12.47 [#/sec] (mean)
Requests per second:    12.14 [#/sec] (mean)
Requests per second:    12.50 [#/sec] (mean)
Requests per second:    12.50 [#/sec] (mean)
Requests per second:    12.39 [#/sec] (mean)
Requests per second:    12.38 [#/sec] (mean)
Requests per second:    12.34 [#/sec] (mean)
Requests per second:    12.01 [#/sec] (mean)
Requests per second:    11.91 [#/sec] (mean)

3) PHP file with pg_connect via pgpool-II

/etc/init.d/apache2 restart ; \
  for c in {1..10}; \
  do ab -n 1000 -c 100 http://localhost/phpinfo3.php 2>&1 | \
    grep "Requests per second"; \
  done
Restarting web server: apache2 ... waiting ...
Requests per second:     41.43 [#/sec] (mean)
Requests per second:     94.64 [#/sec] (mean)
Requests per second:     80.83 [#/sec] (mean)
Requests per second:     85.47 [#/sec] (mean)
Requests per second:     73.03 [#/sec] (mean)
Requests per second:     87.65 [#/sec] (mean)
Requests per second:     84.18 [#/sec] (mean)
Requests per second:    103.66 [#/sec] (mean)
Requests per second:    105.99 [#/sec] (mean)
Requests per second:    104.72 [#/sec] (mean)

4) PHP file with pg_connect via pgBouncer

/etc/init.d/apache2 restart ; \
  for c in {1..10}; \
  do ab -n 1000 -c 100 http://localhost/phpinfo4.php 2>&1 | \
    grep "Requests per second"; \
  done
Restarting web server: apache2 ... waiting ...
Requests per second:     47.53 [#/sec] (mean)
Requests per second:    106.67 [#/sec] (mean)
Requests per second:     90.49 [#/sec] (mean)
Requests per second:     66.29 [#/sec] (mean)
Requests per second:    102.98 [#/sec] (mean)
Requests per second:    129.06 [#/sec] (mean)
Requests per second:    115.56 [#/sec] (mean)
Requests per second:    132.95 [#/sec] (mean)
Requests per second:    127.06 [#/sec] (mean)
Requests per second:    126.64 [#/sec] (mean)

What are your experiences and which one do you prefer? Why do you prefer pgpool-II over pgBouncer or pgBouncer over pgpool-II?