From: Craig Ringer <ringerc@ringerc.id.au>
To: Yan Chunlu <springrider@gmail.com>
Cc: Albe Laurenz <laurenz.albe@wien.gv.at>
Cc: pgsql-performance@postgresql.org
Subject: Re: how could select id=xx so slow?
Date: Tue, 10 Jul 2012 10:46:25 +0800
Message-ID: <4FFB9781.60601@ringerc.id.au> (raw)
In-Reply-To: <CAOA66tEe4m8OQQ+26AKxrreEF458R-983UVMARZ6zeqEkZxwCQ@mail.gmail.com>
References: <CAOA66tEJLDOt4JpJ0RwOEsgizfQPWkCHd+SgT7kbiHix_io18A@mail.gmail.com>
<D960CB61B694CF459DCFB4B0128514C2081BF021@exadv11.host.magwien.gv.at>
<CAOA66tF8dAzAS9wG381bhp+AiC2LrzcF7MDmOxvgW0p07rMNtw@mail.gmail.com>
<4FFB848D.7080900@ringerc.id.au>
<CAOA66tEe4m8OQQ+26AKxrreEF458R-983UVMARZ6zeqEkZxwCQ@mail.gmail.com>
On 07/10/2012 10:25 AM, Yan Chunlu wrote:
> I didn't set log_min_duration_statement in the postgresql.conf, but
> execute
> /dbapi_con.cursor().execute("SET log_min_duration_statement to 30")/
> /for every connection./
OK, same effect: You're only logging slow statements.
It's not at all surprising that BEGIN doesn't appear when a
log_min_duration_statement is set. It's an incredibly fast operation.
What's amazing is that it appears even once - that means your database
must be in serious performance trouble, as BEGIN should take tenths of a
millisecond on an unloaded system. For example my quick test here:
LOG: statement: BEGIN;
LOG: duration: 0.193 ms
... which is actually a lot slower than I expected, but hardly slow
statement material.
The frequent appearance of slow (multi-second) COMMIT statements in your
slow statement logs suggests there's enough load on your database that
there's real contention for disk, and/or that checkpoints are stalling
transactions.
First, you need to set log_min_messages = 'info' to allow Pg to complain
about things like checkpoint frequency.
Now temporarily set log_checkpoints = on to record when checkpoints
happen and how long they take. Most likely you'll find you need to tune
checkpoint behaviour. Some information, albeit old, on that is here:
http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm
<http://www.westnet.com/%7Egsmith/content/postgresql/chkp-bgw-83.htm;
Basically you might want to try increasing your
checkpoint_completion_target and making the bgwriter more aggressive -
assuming that your performance issues are in fact checkpoint related.
It's also possible that they're just overall load, especially if you
have lots and lots (hundreds) of connections to the database all trying
to do work at once without any kind of admission control or
pooling/queuing. In that case, introducing a connection pool like
PgBouncer may help.
--
Craig Ringer
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-performance@postgresql.org
Cc: ringerc@ringerc.id.au, springrider@gmail.com, laurenz.albe@wien.gv.at
Subject: Re: how could select id=xx so slow?
In-Reply-To: <4FFB9781.60601@ringerc.id.au>
* 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