From: | Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: gin index creation performance problems |
Date: | 2008-11-04 01:44:58 |
Message-ID: | 20081104024458.1c693b98@dawn.webthatworks.it |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Mon, 03 Nov 2008 11:04:45 -0500
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it> writes:
> > maintenance_work_mem is still untouched. What would be a good
> > value to start from?
> GIN index build time is *very* sensitive to maintenance_work_mem.
> Try cranking it up to a couple hundred megabytes and see if that
> helps.
200MB did improve the situation. I was waiting a clue about a
reasonable value from where to start.
But the performance is far from being stable.
It can take few minutes to more than 20min even with this setting.
I can't understand if it is CPU bound or RAM bound.
CPU load is always near 100% while postgresql is using 15% of RAM.
Still I've 52K of swap used...
But anyway the performance is very erratic.
BTW Is maintenance_work_mem set per connection?
While gin index looks appreciably faster (actually it is
lightening fast) for searches I'm considering to revert to gist since
even with 200MB maintenance_work_mem it still look a pain to build
up the index, especially considering the random time required to
build it up.
What puzzle me is that while before increasing maintenance_work
mem it was always terribly slow now there is a huge variation in
rebuilt time with 200MB.
Even
vacuum full;
can be pretty slow (>5min) and still 100% CPU use.
The index is dropped at the beginning and rebuilt at the end inside a
quite busy transaction.
Could it be related?
> Also, if you're on 8.2, I think 8.3 might be faster.
8.3 etch backport.
thanks
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
From | Date | Subject | |
---|---|---|---|
Next Message | Craig Ringer | 2008-11-04 02:31:45 | Re: Are there plans to add data compression feature to postgresql? |
Previous Message | Raymond O'Donnell | 2008-11-04 01:24:29 | Re: INSERT with RETURNING clause inside SQL function |