From: Gael Le Mignot <gael@pilotsystems.net>
To: pgsql-performance@postgresql.org
Subject: Re: Periodically slow inserts
Organization: Pilot Systems
References: <plop8762wvspfb.fsf@aoskar.kilobug.org>
Mail-Copies-To: nobody
Date: Fri, 22 Oct 2010 17:10:52 +0200
In-Reply-To: <plop8762wvspfb.fsf@aoskar.kilobug.org> (Gael Le Mignot's message
	of "Thu, 21 Oct 2010 14:25:44 +0200")
Message-ID: <plop874oce460z.fsf@aoskar.kilobug.org>
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/23.2 (gnu/linux)
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

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