Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-admin-owner+M41852=pgsql+2Dadmin=arkaria.postgresql.org@postgresql.org>)
 id 1VeoDv-0007sw-CS
 for pgsql-admin@arkaria.postgresql.org; Fri, 08 Nov 2013 15:43:51 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.80) (envelope-from
 <pgsql-admin-owner+M41852=pgsql+2Dadmin=arkaria.postgresql.org@postgresql.org>)
 id 1VeoDu-0000TM-Rr
 for pgsql-admin@arkaria.postgresql.org; Fri, 08 Nov 2013 15:43:50 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
 by malur.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <rvoinea@gmail.com>) id 1VeoDt-0000TG-Ox
 for pgsql-admin@postgresql.org; Fri, 08 Nov 2013 15:43:50 +0000
Received: from mail-ea0-x232.google.com ([2a00:1450:4013:c01::232])
 by magus.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <rvoinea@gmail.com>) id 1VeoDW-0003ip-8x
 for pgsql-admin@postgresql.org; Fri, 08 Nov 2013 15:43:32 +0000
Received: by mail-ea0-f178.google.com with SMTP id a10so997886eae.9
 for <pgsql-admin@postgresql.org>; Fri, 08 Nov 2013 07:43:25 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113;
 h=from:to:cc:subject:date:message-id:user-agent:in-reply-to
 :references:mime-version:content-transfer-encoding:content-type;
 bh=xnvSVPT60nqLz3rkLiZIuKLZpCdP4holKQ+uv5SWeAI=;
 b=k/i6VWAOE5385aPoEK4M3B8A6QNcsIlWXKW6FHQW+atDi4YXmrBL1e/r6ggRE4JoD3
 Ashn4q8wAGm/IJAXMOouDVIZnxJjydKCzW8eDuAveRtynmJJ22iLM8VHKYdH1/+/9FhD
 npY7tZfsUFp3t6PbHET4ym8sOyHLzN6iyxyHlDdM9MDaPaivXI99CUs2IyWp5+toEP1G
 G/vheWGEsczPTs9hczFF4b7/wJ0tPNQa/6Hs9cIc0hSs3R3GhIwiZY5/eNB167lDuUSv
 59IoolbNsSrpjRgikfAFJ7HmlGbhdljEBijFORF56ItoxGCqTh/qCh+3bK4FISsT+fqG
 z3+Q==
X-Received: by 10.15.91.3 with SMTP id r3mr17584227eez.4.1383925405355;
 Fri, 08 Nov 2013 07:43:25 -0800 (PST)
Received: from shu.localnet ([89.38.174.207])
 by mx.google.com with ESMTPSA id z9sm12425een.6.2013.11.08.07.43.23
 for <multiple recipients>
 (version=TLSv1.1 cipher=ECDHE-RSA-RC4-SHA bits=128/128);
 Fri, 08 Nov 2013 07:43:24 -0800 (PST)
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>
User-Agent: KMail/4.11.1 (Linux/3.10.12-031012-generic; KDE/4.11.1; i686; ; )
In-Reply-To: <1383835200.12074.YahooMailNeo@web162905.mail.bf1.yahoo.com>
References: <16690513.cUljA3p6su@shu>
 <1383835200.12074.YahooMailNeo@web162905.mail.bf1.yahoo.com>
MIME-Version: 1.0
Content-Transfer-Encoding: 7Bit
Content-Type: text/plain; charset="utf-8"
X-Pg-Spam-Score: -2.0 (--)
List-Archive: <http://archives.postgresql.org/pgsql-admin>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-admin.postgresql.org>
List-Owner: <mailto:pgsql-admin-owner@postgresql.org>
List-Post: <mailto:pgsql-admin@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-admin>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
X-Mailing-List: pgsql-admin
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

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