Skip site navigation (1) Skip section navigation (2)

Random penalties on GIN index updates?

From: jesper(at)krogh(dot)cc
To: pgsql-performance(at)postgresql(dot)org
Subject: Random penalties on GIN index updates?
Date: 2009-10-21 15:03:09
Message-ID: b67fc372766d66a4eb391b4d69861c3c.squirrel@shrek.krogh.cc (view raw or flat)
Thread:
Lists: pgsql-performance
Hi  (running PG8.4.1)

As far as I have gotten in my test of PG Full Text Search.. I have got
over 6m documents indexed so far and the index has grown to 37GB. The
systems didnt do any autovacuums in the process but I manually vacuumed a
few times and that stopped growth for a short period of time.

 table_name |   index_name    | times_used | table_size | index_size |
num_writes |                              definition
------------+-----------------+------------+------------+------------+------------+----------------------------------------------------------------------
 ftstest    | body_tfs_idx |        171 | 5071 MB    | 37 GB      |   
6122086 | CREATE INDEX ftstest_tfs_idx ON ftstest USING gin
(ftstest_body_fts)
(1 row)

This is sort of what I'd expect this is not more scary than the Xapian
index it is comparing with. Search speed seems excellent. But I feel I'm
getting a significant drop-off in indexing speed as time goes by, I dont
have numbers to confirm this.

If i understand the technicalities correct then INSERT/UPDATES to the
index will be accumulated in the "maintainance_work_mem" and the "user"
being unlucky to fill it up will pay the penalty of merging all the
changes into the index?

I currently have "maintainance_work_mem" set to 128MB and according to
"pg_stat_activity" i currently have a insert sitting for over 1 hour. If I
strace the postgres process-id it is reading and writing a lot on the
filesystem and imposing an IO-wait load of 1 cpu.

Can I do something to prevent this from happening? Is it "by design"?

-- 
Jesper


Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2009-10-21 15:13:57
Subject: Re: Random penalties on GIN index updates?
Previous:From: Shaul DarDate: 2009-10-21 11:52:44
Subject: Re: Finding rows in table T1 that DO NOT MATCH any row in table T2

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group