Skip site navigation (1) Skip section navigation (2)

Re: 12 hour table vacuums

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Ron St-Pierre <ron(dot)pgsql(at)shaw(dot)ca>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: 12 hour table vacuums
Date: 2007-10-23 16:11:51
Message-ID: 19338.1193155911@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
Ron St-Pierre <ron(dot)pgsql(at)shaw(dot)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

In response to

Responses

pgsql-performance by date

Next:From: Alvaro HerreraDate: 2007-10-23 16:12:04
Subject: Re: 12 hour table vacuums
Previous:From: Bill MoranDate: 2007-10-23 16:07:50
Subject: Re: 12 hour table vacuums

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group