[pgpool-general: 2145] Re: pgpool latency, hesitation under load

[Tatsuo Ishii]

I am a little bit confused.

You said:
> Using pgpool 3.3.0 as connection pooling (1 backend) with shared memory query 
and:
> The pgpool at each web frontend does not have query cache enabled.

So you are "cascading" pgpool-II?

Also I am confused by this:
> Most of queries are below 0,5 seconds.
and this:
> answer psql requests under 100 ms, about 12% cpu load. Zabbix monitoring.

In the mean time I'm interested in this:
> complex queries, because postgresql used a non literal execution and query cache 
> caused errors.

What kind of errors exactly do you have?
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> 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