pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Thomas F. O'Connell <tfo@sitening.com>
To: pgsql-admin@postgresql.org
Subject: vacuumdb vs. max_connections: SELECT waiting
Date: Fri, 12 May 2006 20:13:14 -0500
Message-ID: <BC924EDD-8D0E-4A3D-AE9A-B43AF7770BC1@sitening.com> (raw)

Tonight as part of a scheduled maintenance operation, I was going to  
perform a VACUUM FULL ANALYZE on a postgres 8.1.3 instance that had  
just had it's FSM settings increased to account for about 2 years'  
worth of growth (particularly in number of relations).

Shortly after I kicked it off, I watched the number of connections  
trend upward as a result of the aggressive locking of FULL. I didn't  
want to let this continue without notifying the developers about a  
potential downtime for their application, so I killed the vacuumdb  
process I had started, figuring that the existing connections would  
go right through.

Unfortunately, my expectations were not met, and I wound up with a  
bunch of connections in a waiting state. It seems as though the  
connections that were waiting for the VACUUM locks were still waiting  
even once the VACUUM was killed. Is that expected behavior?  
Eventually, a timeout threshold must've been hit because everything  
went back to normal, but it was not immediate. statement_timeout is  
set to 0, so it wasn't that.

This postmaster does have autovacuum enabled. Could that have  
interfered in any way?

On the one hand I'm curious about the behavior of postgres in this  
scenario. On the other hand, though, I'm wondering if this operation  
is necessary. The postmaster has been restarted such that the new FSM  
settings are in effect. Can autovacuum recover for the months where  
the FSM settings were not sufficient to cover the number of relations  
in this cluster? Is a vacuumdb/reindexdb cycle necessary to reclaim  
disk space?

--
Thomas F. O'Connell
Database Architecture and Programming
Sitening, LLC

http://www.sitening.com/
3004 B Poston Avenue
Nashville, TN 37203-1314
615-260-0005 (cell)
615-469-5150 (office)
615-469-5151 (fax)




view thread (4+ messages)  latest in thread

Message-ID: <BC924EDD-8D0E-4A3D-AE9A-B43AF7770BC1@sitening.com>
Permalink:  ../BC924EDD-8D0E-4A3D-AE9A-B43AF7770BC1@sitening.com/
Also on:    postgresql.org/message-id/BC924EDD-8D0E-4A3D-AE9A-B43AF7770BC1@sitening.com

 · 

reply

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-admin@postgresql.org
  Cc: tfo@sitening.com
  Subject: Re: vacuumdb vs. max_connections: SELECT waiting
  In-Reply-To: <BC924EDD-8D0E-4A3D-AE9A-B43AF7770BC1@sitening.com>
  References: <BC924EDD-8D0E-4A3D-AE9A-B43AF7770BC1@sitening.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