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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-performance by date

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