From: Scott Marlowe <scott.marlowe@gmail.com>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Aris Samad-Yahaya <aris@quickschools.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: CREATE TABLE slowing down significantly over time
Date: Mon, 9 Nov 2009 08:33:12 -0700
Message-ID: <dcc563d10911090733u416e5c06mf1f960ae4268b9de@mail.gmail.com> (raw)
In-Reply-To: <603c8f070911090422t52412697hf19b222a033f729@mail.gmail.com>
References: <008801ca6021$c1cc7630$45656290$@com>
<18869.1257650960@sss.pgh.pa.us>
<009601ca6030$0e731440$2b593cc0$@com>
<603c8f070911090422t52412697hf19b222a033f729@mail.gmail.com>
On Mon, Nov 9, 2009 at 5:22 AM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Sat, Nov 7, 2009 at 11:58 PM, Aris Samad-Yahaya
> <aris@quickschools.com> wrote:
>> We vacuum analyze nightly, and vacuum normally ad-hoc (but we're going to
>> schedule this weekly moving forward).
>>
>> Interesting pointer about system catalog bloat. I tried to vacuum full the
>> system catalog tables (pg_*), and the performance for creating a single
>> table manually improved dramatically (back to what it used to be), but as
>> soon as I created the next schema, the performance went back down to the
>> same level.
>>
>> So there's a clue there somewhere. Next I will try to vacuum full the entire
>> database.
>
> You should really enable autovacuum. You'll probably have to VACUUM
> FULL and REINDEX to clean everything up, but after that autovacuum
> should be MUCH more effective than a nightly vacuum run. If you're
> running some ancient Pg version where autovacuum is not enabled by
> default, you should also consider upgrading. There are a lot of
> goodies (including performance enhancements) in newer versions.
Also note that the argument that autovacuum chews up too much IO is
moot now that you can set cost delay to 10 to 20 milliseconds. Unless
you're running on the hairy edge of maximum IO at all times, autovac
should be pretty much unnoticed.
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: scott.marlowe@gmail.com, robertmhaas@gmail.com, aris@quickschools.com, tgl@sss.pgh.pa.us
Subject: Re: CREATE TABLE slowing down significantly over time
In-Reply-To: <dcc563d10911090733u416e5c06mf1f960ae4268b9de@mail.gmail.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