Re: CPU spikes and transactions - Mailing list pgsql-performance

On 15.10.2013 01:00, Tony Kay wrote:
> Hi,
>
> I'm running 9.1.6 w/22GB shared buffers, and 32GB overall RAM on a
> 16 Opteron 6276 CPU box. We limit connections to roughly 120, but
> our webapp is configured to allocate a thread-local connection, so
> those connections are rarely doing anything more than half the time.

Lower your shared buffers to about 20% of your RAM, unless you've tested
it's actually helping in your particular case. It's unlikely you'll get
better performance by using more than that, especially on older
versions, so it's wiser to leave the rest for page cache.

It might even be one of the causes of the performance issue you're
seeing, as shared buffers are not exactly overhead-free.

See this for more details on tuning:

   http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

You're on a rather old 9.1.x version, BTW. The last version in this
branch is 9.1.10 and there are some important security fixes (e.g. in
9.1.9). Not sure if there are any fixes relevant to the performance
issue, though.

A few initial questions:

* What OS are we dealing with?

* So how many active connections are there on average (see
  pg_stat_activity for connections running queries)?

* How much data are we talking about? In total and in the imports?

> We have been running smoothly for over a year on this configuration,
> and recently started having huge CPU spikes that bring the system to
> its knees. Given that it is a multiuser system, it has been quite
> hard to pinpoint the exact cause, but I think we've narrowed it down
> to two data import jobs that were running in semi-long transactions
> (clusters of row inserts).
>
> The tables affected by these inserts are used in common queries.
>
> The imports will bring in a row count of perhaps 10k on average
> covering 4 tables.
>
> The insert transactions are at isolation level read committed (the
> default for the JDBC driver).
>
> When the import would run (again, theory...we have not been able to
> reproduce), we would end up maxed out on CPU, with a load average of
> 50 for 16 CPUs (our normal busy usage is a load average of 5 out of
> 16 CPUs).
>
> When looking at the active queries, most of them are against the
> tables that are affected by these imports.

Which processes consume most CPU time? Are those backends executing the
queries, or some background processes (checkpointer, autovacuum, ...)?

Can you post a "top -c" output collected at the time of the CPU peak?

Also, try to collect a few snapshots of pg_stat_bgwriter catalog before
and during the loads. Don't forget to include the timestamp:

   select now(), * from pg_stat_bgwriter;

and when you're at it, pg_stat_database snapshots might be handy too
(not sure if you're running a single database or multiple ones), so use
either

  select now(), * from pg_stat_database;

or

  select now(), * from pg_stat_database where datname = '..dbname..';

That should give us at least some insight into what's happening.

> Our workaround (that is holding at present) was to drop the
> transactions on those imports (which is not optimal, but fortunately
> is acceptable for this particular data). This workaround has
> prevented any further incidents, but is of course inconclusive.
>
> Does this sound familiar to anyone, and if so, please advise.

I'm wondering how this could be related to the transactions, and IIRC
the stats (e.g. # of inserted rows) are sent at commit time. That might
trigger the autovacuum. But without the transactions the autovacuum
would be triggered sooner ...

regards
Tomas