From: Heikki Linnakangas <heikki@enterprisedb.com>
To: Karl Wright <kwright@metacarta.com>
Cc: Francisco Reyes <lists@stringsutils.com>
Cc: Alvaro Herrera <alvherre@commandprompt.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance query about large tables, lots of concurrent access
Date: Wed, 20 Jun 2007 18:40:37 +0100
Message-ID: <46796695.3080403@enterprisedb.com> (raw)
In-Reply-To: <467963A8.2060703@metacarta.com>
References: <4676DD57.7070304@metacarta.com>
<14291.1182221811@sss.pgh.pa.us>
<4677B831.8060704@metacarta.com>
<4677D298.2010109@metacarta.com>
<20070619132643.GC4265@alvh.no-ip.org>
<4677DC06.3000300@metacarta.com>
<20070619134637.GG4265@alvh.no-ip.org>
<cone.1182267949.154012.90929.5001@35st.simplicato.com>
<467963A8.2060703@metacarta.com>
Karl Wright wrote:
> So, I guess this means that there's no way I can keep the database
> adequately vacuumed with my anticipated load and hardware. One thing or
> the other will have to change.
Have you checked your maintenance_work_mem setting? If it's not large
enough, vacuum will need to scan through all indexes multiple times
instead of just once. With 16 GB of RAM you should set it to something
like 2GB I think, or even more.
> Is the VACUUM in 8.2 significantly faster than the one in 8.1?
Yes, in particular if you have a lot of indexes. Scanning the indexes
was done in index page order, which in worst case means random I/O, and
we used to do an extra scan of all index pages to collect empty ones.
Now it's all done as a single sequential pass.
> Or, is
> the database less sensitive performance-wise to delayed VACUUM commands?
No.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: heikki@enterprisedb.com, kwright@metacarta.com, lists@stringsutils.com, alvherre@commandprompt.com, tgl@sss.pgh.pa.us
Subject: Re: Performance query about large tables, lots of concurrent access
In-Reply-To: <46796695.3080403@enterprisedb.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox