From: Tom Lane <tgl@sss.pgh.pa.us>
To: Ron St-Pierre <ron.pgsql@shaw.ca>
Cc: pgsql-performance@postgresql.org
Subject: Re: 12 hour table vacuums
Date: Tue, 23 Oct 2007 12:11:51 -0400
Message-ID: <19338.1193155911@sss.pgh.pa.us> (raw)
In-Reply-To: <471E18ED.3090704@shaw.ca>
References: <471E18ED.3090704@shaw.ca>
Ron St-Pierre <ron.pgsql@shaw.ca> writes:
> The table has 140,000 rows, 130 columns (mostly NUMERIC), 60 indexes. It
> is probably our 'key' table in the database and gets called by almost
> every query (usually joined to others). The table gets updated only
> about 10 times a day. We were running autovacuum but it interfered with
> the updates to we shut it off. We vacuum this table nightly, and it
> currently takes about 12 hours to vacuum it. Not much else is running
> during this period, nothing that should affect the table.
Here is your problem:
> vacuum_cost_delay = 200
If you are only vacuuming when nothing else is happening, you shouldn't
be using vacuum_cost_delay at all: set it to 0. In any case this value
is probably much too high. I would imagine that if you watch the
machine while the vacuum is running you'll find both CPU and I/O load
near zero ... which is nice, unless you would like the vacuum to finish
sooner.
In unrelated comments:
> maintenance_work_mem = 786432
That seems awfully high, too.
> max_fsm_pages = 70000
And this possibly too low --- are you sure you are not leaking disk
space?
> stats_start_collector = off
> stats_command_string = on
> stats_block_level = on
> stats_row_level = on
These are not self-consistent.
regards, tom lane
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: tgl@sss.pgh.pa.us, ron.pgsql@shaw.ca
Subject: Re: 12 hour table vacuums
In-Reply-To: <19338.1193155911@sss.pgh.pa.us>
* 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