From: Robert Voinea <rvoinea@gmail.com>
To: Kevin Grittner <kgrittn@ymail.com>
Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: Occasional spike in query response time (jumps from 200ms to 45 seconds)
Date: Fri, 08 Nov 2013 17:43:22 +0200
Message-ID: <13209807.nWtyJFh2Fe@shu> (raw)
In-Reply-To: <1383835200.12074.YahooMailNeo@web162905.mail.bf1.yahoo.com>
References: <16690513.cUljA3p6su@shu>
<1383835200.12074.YahooMailNeo@web162905.mail.bf1.yahoo.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
Hi
On Thursday 07 November 2013 06:40:00 Kevin Grittner wrote:
> 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 need to read about this first.
I forgot to mention that this is a 32 bit system... and due to the known
limitations we are forced to use PAE on the kernel...
Unfortunately we cannot use a 64 bit operating system.
> > 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.
Another colleague of mine managed to somehow reproduce the issue.
It seems that the disk + network interface could be the bottle neck (iostat &
iotop shows average wait times very high) and the peak throughput of the disk
at 70MB/s.
This is not the actual production system, but a similar one (as hardware).
We'll try to re-do the tests with the clients and the server connected via a
Gigabit network and see if this helps.
> > I've seen that postgres 9.1.8 Changelog contains some bug fixes
> > related to performance of autovaccum:
> 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?
I agree with you on this, but the update requires downtime and has to be
scheduled in advance.
Thank you for replying.
I will investigate this even further and see what I may come up with.
I'll keep you informed with what I find.
--
Robert Voinea
Software Engineer
+4 0740 467 262
Don't take life too seriously. You'll never get out of it alive.
(Elbert Hubbard)
--
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: rvoinea@gmail.com, kgrittn@ymail.com
Subject: Re: Occasional spike in query response time (jumps from 200ms to 45 seconds)
In-Reply-To: <13209807.nWtyJFh2Fe@shu>
* 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