pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgres locking up?
4+ messages / 4 participants
[nested] [flat]
* Postgres locking up?
@ 2006-09-29 19:24 Brian Hurt <bhurt@janestcapital.com>
2006-09-29 19:30 ` Re: Postgres locking up? Andrew Sullivan <ajs@crankycanuck.ca>
2006-09-29 20:58 ` Re: Postgres locking up? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 4+ messages in thread
From: Brian Hurt @ 2006-09-29 19:24 UTC (permalink / raw)
To: pgsql-performance; pgsql-novice@postgresql.org
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$
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Postgres locking up?
2006-09-29 19:24 Postgres locking up? Brian Hurt <bhurt@janestcapital.com>
@ 2006-09-29 19:30 ` Andrew Sullivan <ajs@crankycanuck.ca>
1 sibling, 0 replies; 4+ messages in thread
From: Andrew Sullivan @ 2006-09-29 19:30 UTC (permalink / raw)
To: pgsql-performance
On Fri, Sep 29, 2006 at 03:24:14PM -0400, Brian Hurt wrote:
> I'm experiencing a problem with our postgres database. Queries that
> normally take seconds suddenly start taking hours, if they complete at
> all.
The first thing I'd do is EXPLAIN and EXPLAIN ANALYSE on the queries
in question.
The next thing I'd look for is OS-level performance problems.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
I remember when computers were frustrating because they *did* exactly what
you told them to. That actually seems sort of quaint now.
--J.D. Baldwin
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Postgres locking up?
2006-09-29 19:24 Postgres locking up? Brian Hurt <bhurt@janestcapital.com>
@ 2006-09-29 20:58 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 4+ messages in thread
From: Tom Lane @ 2006-09-29 20:58 UTC (permalink / raw)
To: Brian Hurt <bhurt@janestcapital.com>; +Cc: pgsql-performance; pgsql-novice@postgresql.org
Brian Hurt <bhurt@janestcapital.com> writes:
> I'm experiencing a problem with our postgres database. Queries that
> normally take seconds suddenly start taking hours, if they complete at
> all.
Are they waiting? Consuming CPU? Consuming I/O? top and vmstat will
help you find out.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Postgres locking up?
@ 2006-09-30 00:53 Robert Becker Cope <robert@gonkgonk.com>
0 siblings, 0 replies; 4+ messages in thread
From: Robert Becker Cope @ 2006-09-30 00:53 UTC (permalink / raw)
To: bhurt@janestcapital.com; pgsql-performance; pgsql-novice@postgresql.org
Brian Hurt <bhurt@janestcapital.com> wrote:
> I'm experiencing a problem with our postgres database. Queries that
> normally take seconds suddenly start taking hours, if they complete at
> all.
Do you have any long running transactions? I have noticed that with Postgres
8.1.x, a long running transaction combined with other transactions over a long
enough time period can very predictably lead to this type of behavior
One simple way to see if you have any long running transactions is to look for
PIDs that are "idle in transaction" for long periods of time.
robert
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2006-09-30 00:53 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-09-29 19:24 Postgres locking up? Brian Hurt <bhurt@janestcapital.com>
2006-09-29 19:30 ` Andrew Sullivan <ajs@crankycanuck.ca>
2006-09-29 20:58 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-09-30 00:53 Re: Postgres locking up? Robert Becker Cope <robert@gonkgonk.com>
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