From: Steve Crawford <scrawford@pinpointresearch.com>
To: Reuven M. Lerner <reuven@lerner.co.il>
Cc: pgsql-performance@postgresql.org
Subject: Re: Very long deletion time on a 200 GB database
Date: Fri, 24 Feb 2012 09:20:04 -0800
Message-ID: <4F47C6C4.6030102@pinpointresearch.com> (raw)
In-Reply-To: <4F45FB55.1020600@lerner.co.il>
References: <4F45FB55.1020600@lerner.co.il>
On 02/23/2012 12:39 AM, Reuven M. Lerner wrote:
> Hi, everyone. ...
> ...at one installation, we're quickly running out of disk space. The
> database is already taking up about 200 GB of space, and is growing by
> 1 GB or so a day.
I've been following the discussion of approaches and tuning for bulk
deletes and suddenly wondered if you have checked a couple other basics.
Do you know the source of the increases in DB size? Is it due strictly
to inserted data or are there lots of updates as well?
Is autovacuum running properly?
Could you, due to bulk deletes and other issues, be suffering from
table- or index-bloat? Heavily bloated tables/indexes will exacerbate
both your disk-usage and performance problems.
If possible you might try clustering your tables and see what happens to
disk usage and bulk-delete performance. Clusters are generally
reasonably fast - way faster than VACUUM FULL, though they could still
take a while on your very large tables.
As a bonus, cluster gives you shiny, new non-bloated indexes. They do
require an exclusive lock and they do require sufficient disk-space to
build the new, albeit smaller, table/indexes so it may not be an option
if you are short on disk-space. You may be able to start by clustering
your smaller tables and move toward the larger ones as you free
disk-space. Be sure to run ANALYZE on any table that you have CLUSTERed.
You might find it useful to make CLUSTER part of your regular maintenance.
Cheers,
Steve
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: scrawford@pinpointresearch.com, reuven@lerner.co.il
Subject: Re: Very long deletion time on a 200 GB database
In-Reply-To: <4F47C6C4.6030102@pinpointresearch.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