Re: Periodically slow inserts

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(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