Re: transaction timeout - Mailing list pgsql-general

From Dr NoName
Subject Re: transaction timeout
Date
Msg-id 20050726182545.3849.qmail@web31510.mail.mud.yahoo.com
Whole thread Raw
In response to Re: transaction timeout  (Scott Marlowe <smarlowe@g2switchworks.com>)
Responses Re: transaction timeout
List pgsql-general
> You misunderstood his point.  In PostgreSQL
> parlance, a "cluster" is a
> single postmaster running on a single machine, with
> 1 or more
> databases.  So, what he wanted to know was, if your
> application is
> hitting a database called fred, and you have a spare
> database named
> wilma, would "psql wilma" work when the database is
> "locked up?"


ok, I see. That's another thing to try next time.


> Can you elaborate on what you mean by a database
> that is "locked up?"


Here is the ps output from before the offending
process was killed. That one "idle in transaction"
process caused everything to lock.

2005 07 25 6:07:34  s17 79  > ps -efwww | grep
postgres
postgres 23281     1  0 Jul18 ?        00:00:29
/usr/bin/postmaster -p 5432
postgres 23285 23281  0 Jul18 ?        00:20:20
postgres: stats buffer process
postgres 23287 23285  0 Jul18 ?        00:18:08
postgres: stats collector process
postgres 12466 23281  0 Jul18 ?        00:00:00
postgres: siam siam_production 172.16.11.95 idle
postgres 12474 23281  0 Jul18 ?        00:00:00
postgres: siam siam_production 172.16.11.95 idle
postgres 26947 23281  0 Jul19 ?        00:00:00
postgres: siam siam_production 172.16.11.95 idle
postgres  3514 23281  0 Jul19 ?        00:00:00
postgres: siam siam_production 172.16.11.50 idle
postgres  6881 23281  0 Jul19 ?        00:00:01
postgres: siam siam_production 172.16.11.71 idle
postgres 17750 23281  0 Jul20 ?        00:00:00
postgres: siam siam_production 172.16.10.159 idle in
transaction
postgres 26504 23281  0 Jul20 ?        00:00:00
postgres: siam siam_production 172.16.11.50 idle
postgres 12284 23281  0 Jul20 ?        00:00:00
postgres: siam siam_production 172.16.10.125 idle
postgres 16026 23281  0 Jul20 ?        00:00:00
postgres: siam siam_production 172.16.10.125 idle
postgres 25709 23281  0 Jul21 ?        00:01:00
postgres: siam siam_production 172.16.1.17 idle
postgres 27980 23281  0 Jul21 ?        00:04:08
postgres: siam siam_production 172.16.1.17 idle
postgres 14854 23281  0 Jul21 ?        00:00:03
postgres: siam siam_production 172.16.11.95 idle
postgres 19531 23281  0 Jul21 ?        00:00:02
postgres: siam siam_production 172.16.11.95 idle
postgres 17590 23281  0 Jul22 ?        00:00:00
postgres: siam siam_production 172.16.11.95 idle
postgres 26917 23281  0 Jul22 ?        00:00:00
postgres: siam siam_production 172.16.1.17 idle
postgres 26933 23281  0 Jul22 ?        00:06:57
postgres: siam siam_production 172.16.1.17 idle
postgres 26934 23281  0 Jul22 ?        00:00:56
postgres: siam siam_production 172.16.1.17 idle
postgres 26939 23281  0 Jul22 ?        00:01:09
postgres: siam siam_production 172.16.1.17 idle
postgres 27362 23281  0 Jul22 ?        00:01:56
postgres: siam siam_production 172.16.1.17 idle
postgres 27365 23281  0 Jul22 ?        00:01:03
postgres: siam siam_production 172.16.1.17 idle
postgres 27398 23281  0 Jul22 ?        00:00:26
postgres: siam siam_production 172.16.1.17 idle
postgres 27856 23281  0 Jul22 ?        00:01:00
postgres: siam siam_production 172.16.1.17 idle
postgres 27858 23281  0 Jul22 ?        00:05:26
postgres: siam siam_production 172.16.1.17 idle
postgres 27863 23281  0 Jul22 ?        00:00:58
postgres: siam siam_production 172.16.1.17 idle
postgres 27865 23281  0 Jul22 ?        00:01:28
postgres: siam siam_production 172.16.1.17 idle
postgres 27869 23281  0 Jul22 ?        00:00:29
postgres: siam siam_production 172.16.1.17 idle
postgres 28295 23281  0 Jul22 ?        00:00:23
postgres: siam siam_production 172.16.1.17 idle
postgres 28313 23281  0 Jul22 ?        00:00:45
postgres: siam siam_production 172.16.1.17 idle
postgres 28315 23281  0 Jul22 ?        00:01:06
postgres: siam siam_production 172.16.1.17 idle
postgres 28725 23281  0 Jul22 ?        00:05:07
postgres: siam siam_production 172.16.1.17 idle
postgres 13559 23281  0 Jul22 ?        00:00:24
postgres: siam siam_production 172.16.1.17 idle
postgres 13595 23281  0 Jul22 ?        00:00:36
postgres: siam siam_production 172.16.1.17 idle
postgres 14017 23281  0 Jul22 ?        00:00:52
postgres: siam siam_production 172.16.1.17 idle
postgres 25206 23281  0 Jul22 ?        00:00:00
postgres: siam siam_production 172.16.11.146 idle
postgres  3742 23281  0 Jul22 ?        00:00:00
postgres: siam siam_production 172.16.10.142 idle
postgres 12016 23281  0 Jul22 ?        00:00:00
postgres: siam siam_production 172.16.10.106 idle
postgres 13782 23281  0 Jul22 ?        00:00:13
postgres: siam siam_production 172.16.10.106 idle
postgres 13853 23281  0 Jul22 ?        00:00:02
postgres: siam siam_production 172.16.10.106 idle
postgres 14381 23281  0 Jul22 ?        00:00:00
postgres: siam siam_production 172.16.10.106 idle
postgres 14923 23281  0 Jul22 ?        00:00:00
postgres: siam siam_production 172.16.10.106 idle
postgres 17181 23281  0 Jul22 ?        00:00:00
postgres: siam siam_production 172.16.11.59 idle
postgres  6212 23281  0 Jul24 ?        00:00:00
postgres: siam siam_production [local] VACUUM waiting
postgres  5952 23281  0 Jul24 ?        00:00:00
postgres: siam siam_production 172.16.1.17 SELECT
waiting
postgres 24644 23281  0 Jul24 ?        00:00:00
postgres: siam siam_production 172.16.10.100 SELECT
waiting
postgres 26271 23281  0 Jul24 ?        00:00:00
postgres: siam siam_production 172.16.10.114 SELECT
waiting
postgres 26720 23281  0 Jul24 ?        00:00:00
postgres: siam siam_production 172.16.10.114 SELECT
waiting
postgres 26721 23281  0 Jul24 ?        00:00:00
postgres: siam siam_production 172.16.10.114 SELECT
waiting
postgres 27161 23281  0 Jul24 ?        00:00:00
postgres: siam siam_production 172.16.10.114 SELECT
waiting
postgres 27162 23281  0 Jul24 ?        00:00:00
postgres: siam siam_production 172.16.10.114 SELECT
waiting
postgres 28005 23281  0 Jul24 ?        00:00:00
postgres: siam siam_production 172.16.10.114 SELECT
waiting
postgres 28450 23281  0 Jul24 ?        00:00:00
postgres: siam siam_production 172.16.10.114 SELECT
waiting
postgres 28451 23281  0 Jul24 ?        00:00:00
postgres: siam siam_production 172.16.10.114 SELECT
waiting
postgres  3049 23281  0 Jul24 ?        00:00:00
postgres: siam siam_production 172.16.11.134 SELECT
waiting
postgres  3875 23281  0 Jul24 ?        00:00:00
postgres: siam siam_production 172.16.11.134 SELECT
waiting
postgres  4286 23281  0 Jul24 ?        00:00:00
postgres: siam siam_production 172.16.11.130 SELECT
waiting
postgres  4700 23281  0 Jul24 ?        00:00:00
postgres: siam siam_production 172.16.11.185 SELECT
waiting
postgres 13850 23281  0 Jul24 ?        00:00:00
postgres: siam siam_production 172.16.10.105 SELECT
waiting
postgres 13851 23281  0 Jul24 ?        00:00:00
postgres: siam siam_production 172.16.10.105 SELECT
waiting
postgres 13852 23281  0 Jul24 ?        00:00:00
postgres: siam siam_production 172.16.10.105 SELECT
waiting
postgres 13854 23281  0 Jul24 ?        00:00:00
postgres: siam siam_production 172.16.10.105 SELECT
waiting
postgres 13855 23281  0 Jul24 ?        00:00:00
postgres: siam siam_production 172.16.10.105 SELECT
waiting
postgres 13856 23281  0 Jul24 ?        00:00:00
postgres: siam siam_production 172.16.10.145 SELECT
waiting
postgres 14268 23281  0 Jul24 ?        00:00:00
postgres: siam siam_production 172.16.10.105 SELECT
waiting
postgres 14269 23281  0 Jul24 ?        00:00:00
postgres: siam siam_production 172.16.10.105 SELECT
waiting
postgres 14270 23281  0 Jul24 ?        00:00:00
postgres: siam siam_production 172.16.10.138 SELECT
waiting
postgres 14685 23281  0 Jul24 ?        00:00:00
postgres: siam siam_production 172.16.10.105 SELECT
waiting
postgres 14686 23281  0 Jul24 ?        00:00:00
postgres: siam siam_production 172.16.11.24 SELECT
waiting
postgres 15100 23281  0 Jul24 ?        00:00:00
postgres: siam siam_production 172.16.10.105 SELECT
waiting
postgres 15951 23281  0 Jul24 ?        00:00:00
postgres: siam siam_production 172.16.10.125 SELECT
waiting
postgres 16367 23281  0 Jul24 ?        00:00:00
postgres: siam siam_production 172.16.11.109 SELECT
waiting
postgres 25054 23281  0 Jul24 ?        00:00:00
postgres: siam siam_production 172.16.10.112 SELECT
waiting
postgres 25920 23281  0 Jul24 ?        00:00:00
postgres: siam siam_production 172.16.10.112 SELECT
waiting
postgres 25921 23281  0 Jul24 ?        00:00:00
postgres: siam siam_production 172.16.11.157 SELECT
waiting
postgres 25922 23281  0 Jul24 ?        00:00:00
postgres: siam siam_production 172.16.10.112 SELECT
waiting
postgres 26337 23281  0 Jul24 ?        00:00:00
postgres: siam siam_production 172.16.10.112 SELECT
waiting
postgres 26338 23281  0 Jul24 ?        00:00:00
postgres: siam siam_production 172.16.10.112 SELECT
waiting
postgres 10948 23281  0 Jul24 ?        00:00:00
postgres: siam siam_production 172.16.11.105 SELECT
waiting
postgres 12195 23281  0 Jul24 ?        00:00:00
postgres: siam siam_production 172.16.11.187 SELECT
waiting
postgres 12196 23281  0 Jul24 ?        00:00:00
postgres: siam siam_production 172.16.11.105 SELECT
waiting
postgres 22691 23281  0 Jul24 ?        00:00:00
postgres: siam siam_production 172.16.1.17 SELECT
waiting
postgres 23059 23281  0 Jul24 ?        00:00:00
postgres: siam siam_production 172.16.10.121 SELECT
waiting
postgres 15209 23281  0 04:00 ?        00:00:00
postgres: siam siam_production [local] SELECT waiting


Here is the ps output immediately after the hung
client was killed. As you can see, a whole shitload of
SELECTs suddenly woke up and finished. Unfortunately,
I don't have pg_stat_activity output.


2005 07 25 6:14:41  s17 78 > ps -efwww | grep postgres
postgres 23281     1  0 Jul18 ?        00:00:29
/usr/bin/postmaster -p 5432
postgres 23285 23281  0 Jul18 ?        00:20:21
postgres: stats buffer process
postgres 23287 23285  0 Jul18 ?        00:18:08
postgres: stats collector process
postgres 12466 23281  0 Jul18 ?        00:00:00
postgres: siam siam_production 172.16.11.95 idle
postgres 12474 23281  0 Jul18 ?        00:00:00
postgres: siam siam_production 172.16.11.95 idle
postgres 26947 23281  0 Jul19 ?        00:00:00
postgres: siam siam_production 172.16.11.95 idle
postgres  3514 23281  0 Jul19 ?        00:00:00
postgres: siam siam_production 172.16.11.50 idle
postgres  6881 23281  0 Jul19 ?        00:00:01
postgres: siam siam_production 172.16.11.71 idle
postgres 26504 23281  0 Jul20 ?        00:00:00
postgres: siam siam_production 172.16.11.50 idle
postgres 12284 23281  0 Jul20 ?        00:00:00
postgres: siam siam_production 172.16.10.125 idle
postgres 16026 23281  0 Jul20 ?        00:00:00
postgres: siam siam_production 172.16.10.125 idle
postgres 25709 23281  0 Jul21 ?        00:01:00
postgres: siam siam_production 172.16.1.17 idle
postgres 27980 23281  0 Jul21 ?        00:04:08
postgres: siam siam_production 172.16.1.17 idle
postgres 14854 23281  0 Jul21 ?        00:00:03
postgres: siam siam_production 172.16.11.95 idle
postgres 19531 23281  0 Jul21 ?        00:00:02
postgres: siam siam_production 172.16.11.95 idle
postgres 17590 23281  0 Jul22 ?        00:00:00
postgres: siam siam_production 172.16.11.95 idle
postgres 26917 23281  0 Jul22 ?        00:00:00
postgres: siam siam_production 172.16.1.17 idle
postgres 26933 23281  0 Jul22 ?        00:06:57
postgres: siam siam_production 172.16.1.17 idle
postgres 26934 23281  0 Jul22 ?        00:00:56
postgres: siam siam_production 172.16.1.17 idle
postgres 26939 23281  0 Jul22 ?        00:01:09
postgres: siam siam_production 172.16.1.17 idle
postgres 27362 23281  0 Jul22 ?        00:01:56
postgres: siam siam_production 172.16.1.17 idle
postgres 27365 23281  0 Jul22 ?        00:01:03
postgres: siam siam_production 172.16.1.17 idle
postgres 27398 23281  0 Jul22 ?        00:00:26
postgres: siam siam_production 172.16.1.17 idle
postgres 27856 23281  0 Jul22 ?        00:01:00
postgres: siam siam_production 172.16.1.17 idle
postgres 27858 23281  0 Jul22 ?        00:05:26
postgres: siam siam_production 172.16.1.17 idle
postgres 27863 23281  0 Jul22 ?        00:00:58
postgres: siam siam_production 172.16.1.17 idle
postgres 27865 23281  0 Jul22 ?        00:01:28
postgres: siam siam_production 172.16.1.17 idle
postgres 27869 23281  0 Jul22 ?        00:00:29
postgres: siam siam_production 172.16.1.17 idle
postgres 28295 23281  0 Jul22 ?        00:00:23
postgres: siam siam_production 172.16.1.17 idle
postgres 28313 23281  0 Jul22 ?        00:00:45
postgres: siam siam_production 172.16.1.17 idle
postgres 28315 23281  0 Jul22 ?        00:01:06
postgres: siam siam_production 172.16.1.17 idle
postgres 28725 23281  0 Jul22 ?        00:05:07
postgres: siam siam_production 172.16.1.17 idle
postgres 13559 23281  0 Jul22 ?        00:00:24
postgres: siam siam_production 172.16.1.17 idle
postgres 13595 23281  0 Jul22 ?        00:00:36
postgres: siam siam_production 172.16.1.17 idle
postgres 14017 23281  0 Jul22 ?        00:00:52
postgres: siam siam_production 172.16.1.17 idle
postgres 25206 23281  0 Jul22 ?        00:00:00
postgres: siam siam_production 172.16.11.146 idle
postgres  3742 23281  0 Jul22 ?        00:00:00
postgres: siam siam_production 172.16.10.142 idle
postgres 12016 23281  0 Jul22 ?        00:00:00
postgres: siam siam_production 172.16.10.106 idle
postgres 13782 23281  0 Jul22 ?        00:00:13
postgres: siam siam_production 172.16.10.106 idle
postgres 13853 23281  0 Jul22 ?        00:00:02
postgres: siam siam_production 172.16.10.106 idle
postgres 14381 23281  0 Jul22 ?        00:00:00
postgres: siam siam_production 172.16.10.106 idle
postgres 14923 23281  0 Jul22 ?        00:00:00
postgres: siam siam_production 172.16.10.106 idle
postgres 17181 23281  0 Jul22 ?        00:00:00
postgres: siam siam_production 172.16.11.59 idle
postgres  6212 23281  0 Jul24 ?        00:00:00
postgres: siam siam_production [local] VACUUM waiting
postgres  5952 23281  0 Jul24 ?        00:00:00
postgres: siam siam_production 172.16.1.17 idle
postgres 24644 23281  0 Jul24 ?        00:00:00
postgres: siam siam_production 172.16.10.100 idle
postgres 26721 23281  0 Jul24 ?        00:00:00
postgres: siam siam_production 172.16.10.114 idle
postgres 27161 23281  0 Jul24 ?        00:00:00
postgres: siam siam_production 172.16.10.114 idle
postgres 22691 23281  0 Jul24 ?        00:00:00
postgres: siam siam_production 172.16.1.17 idle
postgres 15209 23281  0 03:59 ?        00:00:10
postgres: siam siam_production [local] COPY
postgres 26975 23281  0 06:14 ?        00:00:00
postgres: siam siam_production 172.16.1.17 idle
postgres 26976 23281  0 06:14 ?        00:00:00
postgres: siam siam_production 172.16.1.17 idle
postgres 26977 23281  0 06:14 ?        00:00:00
postgres: siam siam_production 172.16.1.17 idle
postgres 26978 23281  0 06:14 ?        00:00:00
postgres: siam siam_production 172.16.1.17 idle
postgres 26979 23281  0 06:14 ?        00:00:00
postgres: siam siam_production 172.16.1.17 idle
postgres 26980 23281  0 06:14 ?        00:00:00
postgres: siam siam_production 172.16.1.17 idle
postgres 26981 23281  0 06:14 ?        00:00:00
postgres: siam siam_production 172.16.1.17 idle
postgres 26982 23281  0 06:14 ?        00:00:00
postgres: siam siam_production 172.16.1.17 idle
postgres 26983 23281  0 06:14 ?        00:00:00
postgres: siam siam_production 172.16.1.17 idle
postgres 26984 23281  0 06:14 ?        00:00:00
postgres: siam siam_production 172.16.1.17 idle
postgres 26985 23281  0 06:14 ?        00:00:00
postgres: siam siam_production 172.16.1.17 idle
postgres 26986 23281  0 06:14 ?        00:00:00
postgres: siam siam_production 172.16.1.17 idle
postgres 26987 23281  0 06:14 ?        00:00:00
postgres: siam siam_production 172.16.1.17 idle
postgres 26988 23281  0 06:14 ?        00:00:00
postgres: siam siam_production 172.16.1.17 idle
postgres 26989 23281  0 06:14 ?        00:00:00
postgres: siam siam_production 172.16.1.17 idle
postgres 26990 23281  0 06:14 ?        00:00:00
postgres: siam siam_production 172.16.1.17 SELECT
waiting
postgres 27041 23281  0 06:14 ?        00:00:00
postgres: siam siam_production 172.16.11.130 SELECT
waiting
costa    27091 26473  0 06:14 pts/0    00:00:00 grep
postgres

> No, VAcuum full shouldn't cause this kind of issue.
> Now, if the
> database is just running real slow, instead of
> actually locking up,
> that's possible with vacuum full.

no, there was *zero* load on the server.

thanks,

Eugene

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: error when using SELECT
Next
From: Tom Lane
Date:
Subject: Re: transaction timeout