DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=XeF9pK9Itsn+i4qK6eho3B3rB7Ouea7lenjukDRTZTQwQdx4Wgme1ibZlzbx/p07VN
	U5usiTazPjP1PjiSFyN3GRmF+yddZ7RAwtlw3ZpdM8czek/Lv4BdsunXDM2OEd95LJq7
	7V3iQbDw0r2msNHulzuIHhkY+aFB+L4CUGauk=
MIME-Version: 1.0
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>
Date: Mon, 9 Nov 2009 08:33:12 -0700
Message-ID: <dcc563d10911090733u416e5c06mf1f960ae4268b9de@mail.gmail.com>
Subject: Re: CREATE TABLE slowing down significantly over time
From: Scott Marlowe <scott.marlowe@gmail.com>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Aris Samad-Yahaya <aris@quickschools.com>, Tom Lane <tgl@sss.pgh.pa.us>,
	pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

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 t=
o
>> schedule this weekly moving forward).
>>
>> Interesting pointer about system catalog bloat. I tried to vacuum full t=
he
>> system catalog tables (pg_*), and the performance for creating a single
>> table manually improved dramatically (back to what it used to be), but a=
s
>> 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 en=
tire
>> database.
>
> You should really enable autovacuum. =A0You'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. =A0If you're
> running some ancient Pg version where autovacuum is not enabled by
> default, you should also consider upgrading. =A0There 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.