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
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 |