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: