Re: Queries seldomly take >4s while normally take <1ms?

On Tue, 9 Apr 2013 07:25:16 -0700 (PDT)
Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:

> Christian Hammers <ch(at)lathspell(dot)de> wrote:
>
> > 9.2.3
>
> You really need to think about 9.2.4 Real Soon Now; there's a
> security fix that you probably should not wait on.

Is scheduled (no access from outside to that network segment at least)

> > max_connections = 1000                  # (change requires restart)
> > shared_buffers = 20GB                  # min 128kB
>
> Those are both potential causes.  For max_connections, see this:
>
> http://wiki.postgresql.org/wiki/Number_Of_Database_Connections
>
> Maybe you happened to have enough users hit the enter key at the
> same moment to cause a process holding a lock to be starved of
> cycles or something similar.

The application connects permanently with a fixed number of only 20
connections.

> One problem with a large shared_buffers setting is that PostgreSQL
> can accumulate a very large number of dirty pages and flush them to
> the OS all at once.  This can overwhelm the storage system and
> cause exactly the kind of symptoms you're seeing.

I have pretty big changes during early night hours on the master and
then almost only read-only accesses during the day. As checkpoint_timeout
is at 5min, there should not be any significant amount of dirty pages
during daytime, right?
Where would I verity this, with pg_stats_bgwriter.buffers_checkpoint and the
Linux I/O graphs?

bye,

-christian-