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: (view raw, whole thread or download thread mbox)
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
(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"?



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-2017 The PostgreSQL Global Development Group