On 11/07/2012 12:58 PM, Scott Marlowe wrote:
> My experience is that if autovac is causing problems with stalled
> queries etc you're either A: running ancient pg versions (pre 8.3), B:
> Running WAY too aggressive settings in autovac (100 threads, no nap
> time, cost limit of 100000 etc.) or C: Your IO subsystem is absolute
> crap. On any modern server, default autovac settings from 8.3 and on
> should only have the possible problem of not being tuned aggressively
> enough.
A) We are running PG 9.1.
B) We used the default settings in the RPMs provided by
yum.postgresql.org. At the bottom of this message is information about
the RPMs we currently are using.
C) I/O subsystem for PG is twin SATA III SSDs in a RAID 1 configuration,
capable of tens of thousands of IO operations per second. Servers are
recent, SATA III, 16-core Xeons with 128 GB ECC RAM in 1U rackmount cases.
As stated previously, we make extensive use of temp tables,
transactions, and dblink, but had no trouble with catalog table bloat in
8.x; this is a new phenomenon for us.
# rpm -qi postgresql91-9.1.5-3PGDG.rhel6.x86_64
Name : postgresql91 Relocations: (not relocatable)
Version : 9.1.5 Vendor: (none)
Release : 3PGDG.rhel6 Build Date: Sun 02 Sep 2012
12:13:18 PM UTC
Install Date: Wed 12 Sep 2012 03:04:24 AM UTC Build Host:
koji-sl6-x86-64-pg91
Group : Applications/Databases Source RPM:
postgresql91-9.1.5-3PGDG.rhel6.src.rpm
Size : 5193673 License: PostgreSQL
Signature : DSA/SHA1, Sun 02 Sep 2012 12:13:20 PM UTC, Key ID
1f16d2e1442df0f8
URL : http://www.postgresql.org/
Summary : PostgreSQL client programs and libraries
Description :
PostgreSQL is an advanced Object-Relational database management system
(DBMS) that supports almost all SQL constructs (including
transactions, subselects and user-defined types and functions). The
postgresql package includes the client programs and libraries that
you'll need to access a PostgreSQL DBMS server. These PostgreSQL
client programs are programs that directly manipulate the internal
structure of PostgreSQL databases on a PostgreSQL server. These client
programs can be located on the same machine with the PostgreSQL
server, or may be on a remote machine which accesses a PostgreSQL
server over a network connection. This package contains the command-line
utilities for managing PostgreSQL databases on a PostgreSQL server.
If you want to manipulate a PostgreSQL database on a local or remote
PostgreSQL
server, you need this package. You also need to install this package
if you're installing the postgresql91-server package.