From: Rural Hunter <ruralhunter@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Gin index insert performance issue
Date: Tue, 13 Mar 2012 13:43:03 +0800
Message-ID: <4F5EDE67.3040908@gmail.com> (raw)
I'm using gin index for my full text search engine in production. These
days the gin index size grows to 20-30G and the system started to suffer
with periodical insert hanging. This is same as described in the 2 posts:
http://postgresql.1045698.n5.nabble.com/Random-penalties-on-GIN-index-updates-td2073848.htmlhttp://postgresql.1045698.n5.nabble.com/Periodically-slow-inserts-td3230434.html
The gin index is on a dedicated raid 10 SAS disk and the performance
should be enough for normal db operation. But I always see almost 100%
disk utiliztion on the disk when the inserts hang. The utiliztion for
other data(such as the full text table data) on another disk(same setup
as the gin index disk: SAS raid 10) is quite low comparing with the gin
index disk. From my observation, looks too much data is written to the
disk when the pending list of gin index is flushed to the disk. Below is
the outupt of 'iostat -xm 3' on the disk when inserts hang:
Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s
avgrq-sz avgqu-sz await svctm %util
sde 0.00 0.00 0.67 2614.00 0.08 22.94
18.03 32.94 12.61 0.38 100.00
sde 0.00 0.00 1.67 2377.33 0.17 20.43
17.73 32.00 13.44 0.42 100.00
sde 0.00 0.00 15.67 2320.33 0.23 20.13
17.85 31.99 13.73 0.43 100.00
sde 0.00 0.00 7.33 1525.00 0.12 14.02
18.90 32.00 20.83 0.65 100.00
sde 0.00 0.00 14.33 1664.67 0.12 15.54
19.10 32.00 19.06 0.60 100.00
sde 0.00 0.00 5.33 1654.33 0.04 12.07
14.94 32.00 19.22 0.60 100.00
I tried to increase work_mem but the inserts hang more time each time
with less frequency. So it makes almost no difference for the total
hanging time. Frequent vacuum is not a choice since the hang happens
very 3-5 mins. is there any improvement I can make with pg for such data
volumn(still increasing) or it's time to turn to other full text search
solution such as lucene etc?
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: ruralhunter@gmail.com
Subject: Re: Gin index insert performance issue
In-Reply-To: <4F5EDE67.3040908@gmail.com>
* 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