deadlocks in postgresql 7.2.1
| От | Philipp Reisner |
|---|---|
| Тема | deadlocks in postgresql 7.2.1 |
| Дата | |
| Msg-id | 200307281104.19631.philipp.reisner@linbit.com обсуждение исходный текст |
| Ответы |
Re: deadlocks in postgresql 7.2.1
(Peter Eisentraut <peter_e@gmx.net>)
Re: deadlocks in postgresql 7.2.1 (Tom Lane <tgl@sss.pgh.pa.us>) Deadlock in PostgreSQL 7.3.4 (Philipp Reisner <philipp.reisner@linbit.com>) |
| Список | 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 :
В списке pgsql-bugs по дате отправления: