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

Re: SAN performance mystery

From: Markus Schaber <schabi(at)logix-tt(dot)com>
To: Tim Allen <tim(at)proximity(dot)com(dot)au>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: SAN performance mystery
Date: 2006-06-23 11:56:16
Message-ID: 449BD6E0.10305@logix-tt.com (view raw or flat)
Thread:
Lists: pgsql-performance
Hi, Tim,

Tim Allen wrote:
> One thing that has been
> apparent is that autovacuum has not been able to keep the database
> sufficiently tamed. A pg_dump/pg_restore cycle reduced the total
> database size from 81G to 36G.

Two first shots:

- Increase your free_space_map settings, until (auto)vacuum does not
warn about a too small FSM setting any more

- Tune autovacuum to run more often, possibly with a higher delay
setting to lower the load.

If you still have the original database around,

> Performing the restore took about 23 hours.

Try to put the WAL on another spindle, and increase the WAL size /
checkpoint segments.

When most of the restore time was spent in index creation, increase the
sort mem / maintainance work mem settings.


HTH,
Markus

-- 
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org

In response to

Responses

pgsql-performance by date

Next:From: Markus SchaberDate: 2006-06-23 12:02:27
Subject: Re: SAN performance mystery
Previous:From: luchotDate: 2006-06-23 10:12:03
Subject: Occupation bloc in pages of table

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