Postgres locking up?
От | Brian Hurt |
---|---|
Тема | Postgres locking up? |
Дата | |
Msg-id | 451D72DE.4080205@janestcapital.com обсуждение исходный текст |
Ответы |
Re: Postgres locking up?
|
Список | pgsql-novice |
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$
В списке pgsql-novice по дате отправления: