Re: Periodically slow inserts - Mailing list pgsql-performance

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