From: Kevin Grittner <kgrittn@ymail.com>
To: Robert Voinea <rvoinea@gmail.com>
To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: Occasional spike in query response time (jumps from 200ms to 45 seconds)
Date: Thu, 7 Nov 2013 06:40:00 -0800 (PST)
Message-ID: <1383835200.12074.YahooMailNeo@web162905.mail.bf1.yahoo.com> (raw)
In-Reply-To: <16690513.cUljA3p6su@shu>
References: <16690513.cUljA3p6su@shu>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
Robert Voinea <rvoinea@gmail.com> wrote:
> From time to time (1-2 months) one of the production systems I
> manage starts acting crazy... and this is starting to become a
> problem.
>
> Every query I send to the server has a very long running time
> (sometimes it reaches 45+ seconds). Even the simples queries like
> "SELECT NOW()" run in 4-5 seconds... or more.
>
> This goes on for a few minutes. After that, everything is back to
> normal.
A likely cause for this is transparent huge page defrag. If you
run `vmstat 1` during an episode and see a lot of system CPU time,
that tends to confirm this. If you run `perf top` during an
episode and see the kernel spending a lot of time in spinlock
functions, that pretty much nails it. You might want to turn off
transparent huge page support.
> I'm guessing that, somehow, there is heavy I/O usage at that time
Which might be another possibility if the `vmstat 1` shows a lot of
I/O wait time.
> I've seen that postgres 9.1.8 Changelog contains some bug fixes
> related to performance of autovaccum:
>
> // BEGIN QUOTE
> Fix performance problems with autovacuum truncation in busy
> workloads (Jan Wieck)
>
> Truncation of empty pages at the end of a table requires
> exclusive lock, but autovacuum was coded to fail (and release the
> table lock) when there are conflicting lock requests. Under load,
> it is easily possible that truncation would never occur,
> resulting in table bloat. Fix by performing a partial truncation,
> releasing the lock, then attempting to re-acquire the lock and
> continue. This fix also greatly reduces the average time before
> autovacuum releases the lock after a conflicting request arrives.
>
> Fix error in vacuum_freeze_table_age implementation (Andres
> Freund)
>
> In installations that have existed for more than
> vacuum_freeze_min_age transactions, this mistake prevented
> autovacuum from using partial-table scans, so that a full-table
> scan would always happen instead.
> // END QUOTE
If there is high I/O and you see autovacuum running wraparound
prevention vacuums or vacuuming tables which have recently had a
large number of rows deleted, you might solve the problem by
updating. I would strongly recommend updating anyway, because
otherwise you are running with known bugs and security
vulnerabilities, including one very serious one.
Why knowingly put yourself through dealing with bugs that others
have already encountered, reported, diagnosed, and fixed?
http://www.postgresql.org/support/versioning/http://www.postgresql.org/support/security/faq/2013-04-04/
Note that in PostgreSQL, a minor release does not contain any new
features or behavior changes except those deemed to be clear bugs.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: kgrittn@ymail.com, rvoinea@gmail.com
Subject: Re: Occasional spike in query response time (jumps from 200ms to 45 seconds)
In-Reply-To: <1383835200.12074.YahooMailNeo@web162905.mail.bf1.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox