[pgpool-general: 2137] pgpool latency, hesitation under load
Andre Felipe Machado
andremachado at techforce.com.br
Sun Sep 15 01:29:49 JST 2013
Hello,
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
happens.
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?
Regards.
Andre Felipe Machado
http://www.techforce.com.br
More information about the pgpool-general
mailing list