pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Gael Le Mignot <gael@pilotsystems.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Gael Le Mignot <gael@pilotsystems.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Periodically slow inserts
Date: Thu, 21 Oct 2010 18:07:47 +0200
Message-ID: <plop87mxq7r0ks.fsf@aoskar.kilobug.org> (raw)
In-Reply-To: <23677.1287672948@sss.pgh.pa.us>
References: <plop8762wvspfb.fsf@aoskar.kilobug.org>
	<23677.1287672948@sss.pgh.pa.us>

Hello Tom!

Thu, 21 Oct 2010 10:55:48 -0400, you wrote: 

 > Gael Le Mignot <gael@pilotsystems.net> writes:
 >> The problem is  when we index objects into the  full-text search part of
 >> the database (which a DELETE and  then an INSERT into a specific table),
 >> the INSERT  sometimes take a long time  (from 10s to 20s),  but the same
 >> insert (and many other similar ones) are fast (below 0.2s).

 >> This  slowness  comes  regularly,   about  every  200  objects  indexed,
 >> regardless  of the frequency  of the  inserts.

 > Hm.  You didn't say which PG version you're using, but if it's >= 8.4,
 > I think this may be caused by GIN's habit of queuing index insertions
 > until it's accumulated a reasonable-size batch:
 > http://www.postgresql.org/docs/9.0/static/gin-implementation.html#GIN-FAST-UPDATE

 > While you can turn that off, I think that doing so will reduce the
 > index's search efficiency over time.  It might be better to schedule
 > regular vacuums on the table so that the work is done by vacuum rather
 > than foreground queries.

Thanks for your feedback.

It seems to  be related, at least, if I  increase the work_mem variable,
the slowness because  bigger (up to 1 minute for a  work_mem of 8mb) but
much less frequent (around 0.05% instead of 0.5% of the requests for 8mb
instead of 1mb).

So a big work_mem and a regular  vacuum would do the tick, I think. Does
auto_vacuum triggers the  gin index vacuuming too, or  does it require a
manual vacuum ?

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



view thread (14+ messages)  latest in thread

Message-ID: <plop87mxq7r0ks.fsf@aoskar.kilobug.org>
Permalink:  ../plop87mxq7r0ks.fsf@aoskar.kilobug.org/
Also on:    postgresql.org/message-id/plop87mxq7r0ks.fsf@aoskar.kilobug.org

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: gael@pilotsystems.net, tgl@sss.pgh.pa.us
  Subject: Re: Periodically slow inserts
  In-Reply-To: <plop87mxq7r0ks.fsf@aoskar.kilobug.org>
  References: <plop8762wvspfb.fsf@aoskar.kilobug.org> <23677.1287672948@sss.pgh.pa.us> <plop87mxq7r0ks.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