Musings of a West Texas Business Venture

PgPool Two

We are within a few weeks of being done with the entire conversion, and we have found a weak link in our application stack; the actual load balancer, pgpool.

Testing pgpool began in March 2006, and we found it to be of an adequate speed running inside of our Virtual test environment (using the excellent and free VMware Server) for most of our database queries. But when it was placed onto a live-almost-production server, we quickly discovered that using pgpool 3.1.1 is our limiting factor.

Our aging Informix server (almost 5 years old), and Informix 7.3 database (almost 10 years old!), logging into one of the information portals takes about 15 seconds. This normally would be unacceptable for a "simple" login, but this login process actually runs about 2,000 different queries, and the timeframe is within reason for the customer. Using the new PostgreSQL / enterpriseDB configuration, with pgpool 3.1.1 as the load balancer, across three very fast servers (less than 4 months old, and significantly more powerful than the 5 year old machine), the same login procedure takes almost 75 seconds! Something is wrong.

After spending a full day checking indexes and query paths, we found out that each of the 2,000 queries was taking about 2.88ms. Using Google's calculator, we know that 1 second = 1000 ms. So, 2000 queries taking 2.88 ms, we can do (2000 * 2.88) / 1000 = seconds. Almost 3x as fast as the old Informix system, and 13x faster than the pgpool was behaving.

We decided to pull out pgpool and query the database directly: six seconds.

Unfortunately, a load balancer is part of our implementation plan, and pgpool 3.1.1 is unacceptable for our performance requirements. Luckily, the pgpool developers have put out pgpool II, an almost complete rewrite of the pgpool engine. We have been having some difficulties making it compile with enterpriseDB, on Fedora Core 5, 64-bit, as it relies heavily on PostgreSQL's native pq library.

In the meantime, we are hoping to bring up the application within the next two weeks using replication at the database layer, but having to "cold swap" at the application layer....

posted by Anthony Presley @ 6:08 AM