From: Gael Le Mignot <gael@pilotsystems.net>
To: Leonardo Francalanci <m_lists@yahoo.it>
Cc: Gael Le Mignot <gael@pilotsystems.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Periodically slow inserts
Date: Thu, 21 Oct 2010 17:07:13 +0200
Message-ID: <plop87tykfr3dq.fsf@aoskar.kilobug.org> (raw)
In-Reply-To: <857928.20870.qm@web29002.mail.ird.yahoo.com>
References: <plop8762wvspfb.fsf@aoskar.kilobug.org>
<857928.20870.qm@web29002.mail.ird.yahoo.com>
Hello Leonardo!
Thu, 21 Oct 2010 14:15:40 +0100 (BST), you wrote:
>> We are using PostgreSQL for storing data and full-text search indexes
>> for the webiste of a daily newspaper. We are very happy overall with the
>> results, but we have one "weird" behaviour that we would like to solve.
> I think there's a lot of missing info worth knowing:
> 1) checkpoints logs? Enable them, maybe the "slowness" happens
> at checkpoints:
> log_checkpoints=true
Yes, it's the checkpoints. The delay is the delay of the "sync" part of
the checkpoints :
2010-10-21 16:39:15 CEST LOG: checkpoint complete: wrote 365 buffers
(11.9%); 0 transaction log file(s) added, 0 removed, 3 recycled;
write=0.403 s, sync=21.312 s, total=21.829 s
Maybe there is something I misunderstood, but aren't the checkpoints
supposed to run smoothly over the checkpoint_completion_target interval ?
Is there any way to smooth it over time ?
> 2) How many rows does each table contain?
The problems occur on the "big" table with around 570 000 rows. Sorry I
forgot that information.
> 3) HW: how many discs you have, and which controller you're using (and:
> does it use a BBU?)
2 SAS 15K disks in RAID1 (Linux software RAID). The controller is LSI
SAS1068E PCI-Express Fusion-MPT SAS, and we did enable the write cache
(sdparm says :
WCE 1 [cha: y]
).
Not sure if it has a BBU, but we have redundant power supply, and when
we'll go live, we'll have a warm standby on different hardware through
WAL log shipping (it's not in place right now), and we can afford a few
minutes of dataloss in case of exceptional failure.
> The more you tell the list, the better help you'll get...
Of course, thanks for your feedback.
As for the othe questions of the Wiki page :
- I don't think explain/explain analyze will provide any information for
inserts with no subqueries/...
- We have (Debian) default config for autovacuum, and I tried a "vacuum
analyze;" just before running a bench, it didn't change anything.
- I tried moving the WAL to another pair of similar RAID1 SAS disks, but
it didn't have any significant effect.
And I also forgot to give the PostgreSQL version, it's 8.4.4 from Debian
backports.
Regards,
--
Gaël Le Mignot - gael@pilotsystems.net
Pilot Systems - 9, rue Desargues - 75011 Paris
Tel : +33 1 44 53 05 55 - www.pilotsystems.net
Gérez vos contacts et vos newsletters : www.cockpit-mailing.com
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: gael@pilotsystems.net, m_lists@yahoo.it
Subject: Re: Periodically slow inserts
In-Reply-To: <plop87tykfr3dq.fsf@aoskar.kilobug.org>
* 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