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@xxxxxxxxxxxxxxxx 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
