From: Rob Nagler <nagler@bivio.biz>
To: pgsql-performance@postgresql.org
Subject: reindex/vacuum locking/performance?
Date: Fri, 3 Oct 2003 14:24:42 -0600
Message-ID: <16253.56074.737161.470428@jump.bivio.com> (raw)
I've read some posts that says vacuum doesn't lock, but my experience
today indicates the opposite. It seemed that "vacuum full analyze"
was locked waiting and so were other postmaster processes. It
appeared to be deadlock, because all were in "WAITING" state according
to ps. I let this go for about a 1/2 hour, and then killed the vacuum
at which point all other processes completed normally.
The same thing seemed to be happening with reindex on a table. It
seems that the reindex locks the table and some other resource which
then causes deadlock with other active processes.
Another issue seems to be performance. A reindex on some indexes is
taking 12 minutes or so. Vacuum seems to be slow, too. Way longer
than the time it takes to reimport the entire database (30 mins).
In summary, I suspect that it is better from a UI perspective to bring
down the app on Sat at 3 a.m and reimport with a fixed time period
than to live through reindexing/vacuuming which may deadlock. Am I
missing something?
Thanks,
Rob
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: nagler@bivio.biz
Subject: Re: reindex/vacuum locking/performance?
In-Reply-To: <16253.56074.737161.470428@jump.bivio.com>
References: <16253.56074.737161.470428@jump.bivio.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