Re: Periodically slow inserts

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(at)pilotsystems(dot)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