First steps with 8.3 and autovacuum launcher

From: "Guillaume Smet" <guillaume(dot)smet(at)gmail(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: First steps with 8.3 and autovacuum launcher
Date: 2007-09-18 16:51:04
Message-ID: 1d4e0c10709180951j2413cf5cta20cbc315b907c75@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi all,

As we will soon enter beta, I decided to give a try to 8.3devel. The
first step is of course to load a dump from an existing database.

The dump is a text dump of 1.6 GB (database is approximately 4 GB).

The restore is far slower than with 8.2. From time to time ALTER TABLE
queries creating primary keys are waiting for a long time. After a
while, I discovered that that I had three autovacuum processes which
were running to analyze the created tables while the dump was trying
to create primary keys on these very tables.

While I understand that it's important to have a fully analyzed
database, I usually do it at the end of the restore and the fact that
three tables are analyzed concurrently while primary keys are created
is far from optimal IMHO as primary keys creation often (*really*
often in my case) waits for autovacuum to finish its job.

I don't have any magical solution for this problem but I'm pretty sure
we will have feedback from users about it. Perhaps the dump should
disable autovacuum on the database while restoring it?

The good news is that the database size is smaller (3.1 GB with
8.3devel instead of 3.6 with 8.2.4).

My next steps are to compare the performances of 8.1.10, 8.2.5 and
8.3devel on a set of real life read queries (e.g. real queries
executed on this database). I'll let you know if I have interesting
results.

Regards,

--
Guillaume

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2007-09-18 17:13:34 Re: Open issues for HOT patch
Previous Message Pavan Deolasee 2007-09-18 16:32:39 Re: Open issues for HOT patch