From: Gael Le Mignot <gael@pilotsystems.net>
To: pgsql-performance@postgresql.org
Subject: Re: Periodically slow inserts
Date: Fri, 22 Oct 2010 17:10:52 +0200
Message-ID: <plop874oce460z.fsf@aoskar.kilobug.org> (raw)
In-Reply-To: <plop8762wvspfb.fsf@aoskar.kilobug.org>
References: <plop8762wvspfb.fsf@aoskar.kilobug.org>
Hello,
Thanks to everyone who gave me hints and feedbacks. I managed to solve
the problem.
My understanding of what was happening is the following :
- The gin index (as explained on [1]), stores temporary list, and when
they grow big enough, those are dispatched into the real index. Vacuum
also does this index flush, in background.
- This index flush, on a table with 500k rows, means making changes to a
lot of disk pages, filling the WAL in one big burst, forcing an
immediate checkpoint, and blocking the INSERT that triggered it.
I managed to solve the problem by adjusting two set of parameters :
- The work_mem variable, which sepcify the maximal size of the temporary
list before the gin index is "flushed".
- The autovacuum parameters.
The main idea was to increase the size of temporary lists (through
work_mem) and increase the frequency of autovacuums, to ensure that
under real life load (even heavy real life load), the "index flush" is
always done by the autovacuum, and never by the "list is full" trigger.
With this setup, I managed to handle indexing 10 000 objects in 2 hours
without any stall, which is much more than we'll have to handle under
real life load.
Regards,
[1] http://www.postgresql.org/docs/8.4/static/gin-implementation.html
--
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
Subject: Re: Periodically slow inserts
In-Reply-To: <plop874oce460z.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