From: | Philipp Reisner <philipp(dot)reisner(at)linbit(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | deadlocks in postgresql 7.2.1 |
Date: | 2003-07-28 09:04:19 |
Message-ID: | 200307281104.19631.philipp.reisner@linbit.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
Hi,
We use postgresql 7.2.1 (actually the Debian binary packet version
7.2.1-2woody2)
Our database load has more QUERIES than INSERT/DELETES. But we have a
sever problem with the wirting operations.
Once in a while (about 3 times a day) one or more INSERTS/DELETES simply
go into the "waiting" state, and block the whole database. The only way
out is to terminate the client connection (i.e. to abort the blocked
INSERT/DELETE query)
Further investigation with ps -e -o wchan... showed that the backed
process was simply sleeping in "semop".
Output of ps:
762 ? S 0:00 /usr/lib/postgresql/bin/postmaster
764 ? S 0:00 postgres: stats buffer process
765 ? S 0:00 postgres: stats collector process
24872 ? S 0:00 postgres: sd sd 10.2.2.6 idle in transaction
24873 ? R 68:01 postgres: sd sd 10.2.2.6 SELECT
24932 ? S 3:09 postgres: sd sd 10.2.2.6 idle in transaction
24943 ? R 3:02 postgres: sd sd 10.2.2.6 SELECT
25004 ? S 0:01 postgres: sd sd 10.2.1.5 idle in transaction
21226 ? S 0:00 postgres: sd sd 10.2.1.5 idle in transaction
21228 ? S 0:00 postgres: sd sd 10.2.1.5 idle in transaction
21229 ? S 0:00 postgres: sd sd 10.2.1.5 idle in transaction
21230 ? S 0:00 postgres: sd sd 10.2.1.5 idle in transaction
21231 ? S 0:01 postgres: sd sd 10.2.1.5 idle in transaction
21232 ? S 0:00 postgres: sd sd 10.2.1.5 idle in transaction
21227 ? S 0:01 postgres: sd sd 10.2.1.5 INSERT waiting
21391 pts/1 S 0:00 grep postg
762 ? S 0:00 /usr/lib/postgresql/bin/postmaster
764 ? S 0:00 postgres: stats buffer process
765 ? S 0:00 postgres: stats collector process
24872 ? S 0:00 postgres: sd sd 10.2.2.6 idle in transaction
24873 ? S 68:02 postgres: sd sd 10.2.2.6 idle in transaction
24932 ? S 3:09 postgres: sd sd 10.2.2.6 idle in transaction
24943 ? S 3:04 postgres: sd sd 10.2.2.6 idle in transaction
25004 ? S 0:01 postgres: sd sd 10.2.1.5 idle in transaction
21226 ? S 0:00 postgres: sd sd 10.2.1.5 idle in transaction
21228 ? S 0:00 postgres: sd sd 10.2.1.5 idle in transaction
21229 ? S 0:00 postgres: sd sd 10.2.1.5 idle in transaction
21230 ? S 0:00 postgres: sd sd 10.2.1.5 idle in transaction
21231 ? S 0:01 postgres: sd sd 10.2.1.5 idle in transaction
21232 ? S 0:00 postgres: sd sd 10.2.1.5 idle in transaction
21227 ? S 0:01 postgres: sd sd 10.2.1.5 INSERT waiting
762 ? S 0:00 /usr/lib/postgresql/bin/postmaster
764 ? S 0:00 postgres: stats buffer process
765 ? S 0:00 postgres: stats collector process
10291 ? S 0:05 postgres: sd sd 10.2.1.5 idle in transaction
4303 ? S 0:00 postgres: sd sd 10.2.2.6 idle in transaction
4304 ? S 40:43 postgres: sd sd 10.2.2.6 idle in transaction
4370 ? S 2:54 postgres: sd sd 10.2.2.6 idle in transaction
4384 ? S 3:12 postgres: sd sd 10.2.2.6 idle in transaction
19961 ? S 4:32 postgres: sd sd 10.2.1.5 idle in transaction
19962 ? S 4:40 postgres: sd sd 10.2.1.5 idle in transaction
19963 ? S 4:24 postgres: sd sd 10.2.1.5 DELETE waiting
19964 ? S 4:58 postgres: sd sd 10.2.1.5 idle in transaction
19965 ? S 4:18 postgres: sd sd 10.2.1.5 idle in transaction
19966 ? S 3:49 postgres: sd sd 10.2.1.5 idle in transaction
5574 ? S 2:50 postgres: sd sd 10.2.1.5 idle in transaction
5575 ? S 2:54 postgres: sd sd 10.2.1.5 idle in transaction
8744 pts/0 S 0:00 /usr/lib/postgresql/bin/psql -d sd
8751 ? S 0:00 postgres: sd sd [local] idle
16606 ? S 0:03 postgres: sd sd 10.2.1.5 idle in transaction
To get rid of the problem we tried to upgrade to 7.3.3. But see next
mail for our experiences with 7.3.3.
-Philipp
--
: Dipl-Ing Philipp Reisner Tel +43-1-8178292-50 :
: LINBIT Information Technologies GmbH Fax +43-1-8178292-82 :
: Schönbrunnerstr 244, 1120 Vienna, Austria http://www.linbit.com :
From | Date | Subject | |
---|---|---|---|
Next Message | Philipp Reisner | 2003-07-28 09:05:13 | Postgresql 7.3.3 crashing on query |
Previous Message | Andreas Pflug | 2003-07-28 08:43:07 | Re: problem with libpq/encrypted |