Skip site navigation (1) Skip section navigation (2)

Postgres locking up?

From: Brian Hurt <bhurt(at)janestcapital(dot)com>
To: pgsql-performance(at)postgresql(dot)org, pgsql-novice(at)postgresql(dot)org
Subject: Postgres locking up?
Date: 2006-09-29 19:24:14
Message-ID: 451D72DE.4080205@janestcapital.com (view raw or flat)
Thread:
Lists: pgsql-novicepgsql-performance
I'm experiencing a problem with our postgres database.  Queries that 
normally take seconds suddenly start taking hours, if they complete at 
all. 

This isn't a vacuuming or analyzing problem- I've been on this list long 
enough that they were my first response, and besides it doesn't happen 
with just a single query.    Turning off autovaccum (and switching to a 
weekend vaccuum) seems to have reduced the frequency of the problem, but 
did not eliminate it.  Besides, I've seen this problem with copy 
statements, which shouldn't be that susceptable to problems with these.

Nor is it a problem with normal database locking- when it happens, I've 
been poking around in pg_locks, and nothing seems wrong (all locks have 
been granted, no obvious deadlocks). 

Restarting the database seems to help occassionally, but not always.

This is happening both in production, where the database is held on an 
iscsi partition on an EMC, and in development, where the database is 
held on a single 7200 RPM SATA drive.  Both are Opteron-based HP 145 
servers running Centos (aka Redhat) in 64-bit mode.

What I'm looking for is pointers as to what to do next- what can I do to 
track the problem down.  Any help would be appreciated.  Thank you.

The output of pg_config:
-bash-3.00$ /usr/local/pgsql/bin/pg_config
BINDIR = /usr/local/pgsql/bin
DOCDIR = /usr/local/pgsql/doc
INCLUDEDIR = /usr/local/pgsql/include
PKGINCLUDEDIR = /usr/local/pgsql/include
INCLUDEDIR-SERVER = /usr/local/pgsql/include/server
LIBDIR = /usr/local/pgsql/lib
PKGLIBDIR = /usr/local/pgsql/lib
LOCALEDIR =
MANDIR = /usr/local/pgsql/man
SHAREDIR = /usr/local/pgsql/share
SYSCONFDIR = /usr/local/pgsql/etc
PGXS = /usr/local/pgsql/lib/pgxs/src/makefiles/pgxs.mk
CONFIGURE = '--with-perl' '--with-python' '--with-openssl' '--with-pam' 
'--enable-thread-safety'
CC = gcc
CPPFLAGS = -D_GNU_SOURCE
CFLAGS = -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline 
-Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing
CFLAGS_SL = -fpic
LDFLAGS = -Wl,-rpath,/usr/local/pgsql/lib
LDFLAGS_SL =
LIBS = -lpgport -lpam -lssl -lcrypto -lz -lreadline -ltermcap -lcrypt 
-lresolv -lnsl -ldl -lm -lbsd
VERSION = PostgreSQL 8.1.4
-bash-3.00$


Responses

pgsql-novice by date

Next:From: Andrew SullivanDate: 2006-09-29 19:30:06
Subject: Re: Postgres locking up?
Previous:From: Brandon AikenDate: 2006-09-29 18:44:59
Subject: Re: [NOVICE] Do non-sequential primary keys slow performance significantly??

pgsql-performance by date

Next:From: Andrew SullivanDate: 2006-09-29 19:30:06
Subject: Re: Postgres locking up?
Previous:From: Tom LaneDate: 2006-09-29 18:07:32
Subject: Re: how to optimize postgres 8.1

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group