Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id DD3229FB2F6; Fri, 29 Sep 2006 16:24:28 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 57191-08; Fri, 29 Sep 2006 19:24:16 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- X-Greylist: from auto-whitelisted by SQLgrey- Received: from janestcapital.com (unknown [66.155.124.107]) by postgresql.org (Postfix) with ESMTP id EF4469F95CB; Fri, 29 Sep 2006 16:24:15 -0300 (ADT) Received: from [192.168.250.117] [209.213.205.130] by janestcapital.com with ESMTP (SMTPD32-8.13) id A2E262300B0; Fri, 29 Sep 2006 15:24:18 -0400 Message-ID: <451D72DE.4080205@janestcapital.com> Date: Fri, 29 Sep 2006 15:24:14 -0400 From: Brian Hurt User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.7.2) Gecko/20040804 Netscape/7.2 (ax) X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-performance@postgresql.org, pgsql-novice@postgresql.org Subject: Postgres locking up? Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200609/217 X-Sequence-Number: 17759 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$