[pgpool-general: 2137] pgpool latency, hesitation under load

Andre Felipe Machado andremachado at techforce.com.br
Sun Sep 15 01:29:49 JST 2013

Using pgpool 3.3.0 as connection pooling (1 backend) with shared memory query 
cache, and connection pooling only, we are trying to spot a misconfiguration 
that is causing a hesitation, latency, staggering under load.
During these hesitation periods, sgbd transactions per second drops.
If we connect web server directly to sgbd backend (too many processes open) 
obviously %sys cpu is too high, but there are not staggering latencies.
Sometimes a delay of up o 2.5 seconds to get a new connection for the web app.
Also, we had to use 2 level pooling (one level at each web server, other in 
front of sgbd) because when connections exceeded about 10% of num_init_children, 
it hesitated for about 30 seconds. 
num_init_children = 160
max_pool = 1
When leaving child_max_connections at 0, the problem is reduced, but still 
connection_life_time = 360
client_idle_limit = 360
The backend is executing about 2000 transactions per second, around 300 
connections per second.
Most of queries are below 0,5 seconds.
Query cache hit at same machine is 0.55. We had to blacklist some tables with 
complex queries, because postgresql used a non literal execution and query cache 
caused errors.
The pgpool at each web frontend does not have query cache enabled.
The postgresql 9.2.4 backend on debian linux 7.1, kernel 3.2, 40 cores 80 
threads, 512 GB ram, raid controller 1GB NVRAM,  is running db on RAM and always 
answer psql requests under 100 ms, about 12% cpu load. Zabbix monitoring.
max_connections = 256

How could we track our misconfiguration that cause pgpool latency staggering?

Andre Felipe Machado

More information about the pgpool-general mailing list