Re: postgresql.conf recommendations

On Tue, Feb 5, 2013 at 2:02 PM, Johnny Tan <johnnydtan(at)gmail(dot)com> wrote:

> checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0 - 1.0

I always set this to 0.9. I don't know why the default is 0.5.

> But periodically, there are spikes in our app's db response time. Normally,
> the app's db response time hovers in the 100ms range for most of the day.
> During the spike times, it can go up to 1000ms or 1500ms, and the number of
> pg connections goes to 140 (maxed out to pgbouncer's limit, where normally
> it's only about 20-40 connections).

What if you lower the pgbouncer limit to 40?

It is hard to know if the latency spikes cause the connection build
up, or if the connection build up cause the latency spikes, or if they
reinforce each other in a vicious circle. But making the connections
wait in pgbouncer's queue rather than in the server should do no harm,
and very well might help.

> Also, during these times, which usually
> last less than 2 minutes, we will see several thousand queries in the pg log
> (this is with log_min_duration_statement = 500), compared to maybe one or
> two dozen 500ms+ queries in non-spike times.

Is the nature of the queries the same, just the duration that changes?
Or are the queries of a different nature?

Cheers,

Jeff