Re: Odd new symptom - database locking up on a query - Mailing list pgsql-general

From Doug Fields
Subject Re: Odd new symptom - database locking up on a query
Date
Msg-id 5.1.0.14.2.20020706232035.02f76c78@pop.pexicom.com
Whole thread Raw
In response to Odd new symptom - database locking up on a query  (Doug Fields <dfields-pg-general@pexicom.com>)
Responses Re2: Odd new symptom - database locking up on a query
List pgsql-general
Additional information from the STATS being turned on:

(The static IN clause has between 250 and 350 IDs in it, each time. I don't know which DELETE hangs first, but they all cascade into being hung, as this shows.)

Thoughts?

Thanks,

Doug

  datid  |   datname   | procpid | usesysid |  usename  |                                                                                                                          current_query                                                                                                                         
---------+-------------+---------+----------+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 1576030 | bfkdev      |     274 |      102 | bknowlton | <IDLE>
 3926366 | pexicast_lg |     275 |      100 | tomcat    | DELETE FROM table_entries WHERE job_id=44 AND list_entry_id IN (242101,242121,242141,242161,242181,242201,242221,242241,242261,242281,242301,242321,242341,242361,242381,242401,242421,242441,242461,242481,242501,242521,242541,242561,242581,242601,2
 3926366 | pexicast_lg |     276 |      100 | tomcat    | DELETE FROM table_entries WHERE job_id=44 AND list_entry_id IN (237123,237143,237163,237183,237203,237223,237243,237263,237283,237303,237323,237343,237363,237383,237403,237423,237443,237463,237483,237503,237523,237543,237563,237583,237603,237623,2
 3926366 | pexicast_lg |     277 |      100 | tomcat    | DELETE FROM table_entries WHERE job_id=44 AND list_entry_id IN (239084,239104,239124,239144,239164,239184,239204,239224,239244,239264,239284,239304,239324,239344,239364,239384,239404,239424,239444,239464,239484,239504,239524,239544,239564,239584,2
 3926366 | pexicast_lg |     278 |      100 | tomcat    | DELETE FROM table_entries WHERE job_id=44 AND list_entry_id IN (238525,238545,238565,238585,238605,238625,238645,238665,238685,238705,238725,238745,238765,238785,238805,238825,238845,238865,238885,238905,238925,238945,238965,238985,239005,239025,2
 3926366 | pexicast_lg |     279 |      100 | tomcat    | DELETE FROM table_entries WHERE job_id=44 AND list_entry_id IN (246006,246026,246046,246066,246086,246106,246126,246146,246166,246186,246206,246226,246246,246266,246286,246306,246326,246346,246366,246386,246406,246426,246446,246466,246486,246506,2
 3926366 | pexicast_lg |     280 |      100 | tomcat    | DELETE FROM table_entries WHERE job_id=44 AND list_entry_id IN (234787,234807,234827,234847,234867,234887,234907,234927,234947,234967,234987,235007,235027,235047,235067,235087,235107,235127,235147,235167,235187,235207,235227,235247,235267,235287,2
 3926366 | pexicast_lg |     281 |      100 | tomcat    | DELETE FROM table_entries WHERE job_id=44 AND list_entry_id IN (230268,230288,230308,230328,230348,230368,230388,230408,230428,230448,230468,230488,230508,230528,230548,230568,230588,230608,230628,230648,230668,230688,230708,230728,230748,230768,2
 3926366 | pexicast_lg |     282 |      100 | tomcat    | DELETE FROM table_entries WHERE job_id=44 AND list_entry_id IN (230249,230269,230289,230309,230329,230349,230369,230389,230409,230429,230449,230469,230489,230509,230529,230549,230569,230589,230609,230629,230649,230669,230689,230709,230729,230749,2
 3926366 | pexicast_lg |     283 |      100 | tomcat    | DELETE FROM table_entries WHERE job_id=44 AND list_entry_id IN (224030,224050,224070,224090,224110,224130,224150,224170,224190,224210,224230,224250,224270,224290,224310,224330,224350,224370,224390,224410,224430,224450,224470,224490,224510,224530,2
 3926366 | pexicast_lg |     284 |      100 | tomcat    | DELETE FROM table_entries WHERE job_id=44 AND list_entry_id IN (237971,237991,238011,238031,238051,238071,238091,238111,238131,238151,238171,238191,238211,238231,238251,238271,238291,238311,238331,238351,238371,238391,238411,238431,238451,238471,2
 3926366 | pexicast_lg |     285 |      100 | tomcat    | DELETE FROM table_entries WHERE job_id=44 AND list_entry_id IN (231832,231852,231872,231892,231912,231932,231952,231972,231992,232012,232032,232052,232072,232092,232112,232132,232152,232172,232192,232212,232232,232252,232272,232292,232312,232332,2
 3926366 | pexicast_lg |     286 |      100 | tomcat    | DELETE FROM table_entries WHERE job_id=44 AND list_entry_id IN (231953,231973,231993,232013,232033,232053,232073,232093,232113,232133,232153,232173,232193,232213,232233,232253,232273,232293,232313,232333,232353,232373,232393,232413,232433,232453,2
 3926366 | pexicast_lg |     287 |      100 | tomcat    | DELETE FROM table_entries WHERE job_id=44 AND list_entry_id IN (225634,225654,225674,225694,225714,225734,225754,225774,225794,225814,225834,225854,225874,225894,225914,225934,225954,225974,225994,226014,226034,226054,226074,226094,226114,226134,2
 3926366 | pexicast_lg |     288 |      100 | tomcat    | DELETE FROM table_entries WHERE job_id=44 AND list_entry_id IN (232755,232775,232795,232815,232835,232855,232875,232895,232915,232935,232955,232975,232995,233015,233035,233055,233075,233095,233115,233135,233155,233175,233195,233215,233235,233255,2
 3926366 | pexicast_lg |     289 |      100 | tomcat    | DELETE FROM table_entries WHERE job_id=44 AND list_entry_id IN (220436,220456,220476,220496,220516,220536,220556,220576,220596,220616,220636,220656,220676,220696,220716,220736,220756,220776,220796,220816,220836,220856,220876,220896,220916,220936,2
 3926366 | pexicast_lg |     290 |      100 | tomcat    | DELETE FROM table_entries WHERE job_id=44 AND list_entry_id IN (233837,233857,233877,233897,233917,233937,233957,233977,233997,234017,234037,234057,234077,234097,234117,234137,234157,234177,234197,234217,234237,234257,234277,234297,234317,234337,2
 3926366 | pexicast_lg |     291 |      100 | tomcat    | DELETE FROM table_entries WHERE job_id=44 AND list_entry_id IN (234178,234198,234218,234238,234258,234278,234298,234318,234338,234358,234378,234398,234418,234438,234458,234478,234498,234518,234538,234558,234578,234598,234618,234638,234658,234678,2
 1576030 | bfkdev      |     292 |      102 | bknowlton | <IDLE>
 1576030 | bfkdev      |     293 |      102 | bknowlton | <IDLE>
 1576030 | bfkdev      |     294 |      102 | bknowlton | <IDLE>
 3926366 | pexicast_lg |     295 |      100 | tomcat    | DELETE FROM table_entries WHERE job_id=44 AND list_entry_id IN (220719,220739,220759,220779,220799,220819,220839,220859,220879,220899,220919,220939,220959,220979,220999,221019,221039,221059,221079,221099,221119,221139,221159,221179,221199,221219,2
 3926366 | pexicast_lg |     300 |      100 | tomcat    | DELETE FROM table_entries WHERE job_id=44 AND list_entry_id IN (225020,225040,225060,225080,225100,225120,225140,225160,225180,225200,225220,225240,225260,225280,225300,225320,225340,225360,225380,225400,225420,225440,225460,225480,225500,225520,2
 3926366 | pexicast_lg |     301 |      100 | tomcat    | DELETE FROM table_entries WHERE job_id=44 AND list_entry_id IN (242101,242121,242141,242161,242181,242201,242221,242241,242261,242281,242301,242321,242341,242361,242381,242401,242421,242441,242461,242481,242501,242521,242541,242561,242581,242601,2
 3926366 | pexicast_lg |     302 |      100 | tomcat    | DELETE FROM table_entries WHERE job_id=44 AND list_entry_id IN (220822,220842,220862,220882,220902,220922,220942,220962,220982,221002,221022,221042,221062,221082,221102,221122,221142,221162,221182,221202,221222,221242,221262,221282,221302,221322,2
 3926366 | pexicast_lg |     303 |      100 | tomcat    | DELETE FROM table_entries WHERE job_id=44 AND list_entry_id IN (220719,220739,220759,220779,220799,220819,220839,220859,220879,220899,220919,220939,220959,220979,220999,221019,221039,221059,221079,221099,221119,221139,221159,221179,221199,221219,2
 3926366 | pexicast_lg |     304 |      100 | tomcat    | DELETE FROM table_entries WHERE job_id=44 AND list_entry_id IN (220436,220456,220476,220496,220516,220536,220556,220576,220596,220616,220636,220656,220676,220696,220716,220736,220756,220776,220796,220816,220836,220856,220876,220896,220916,220936,2
 3926366 | pexicast_lg |     305 |      100 | tomcat    | DELETE FROM table_entries WHERE job_id=44 AND list_entry_id IN (237123,237143,237163,237183,237203,237223,237243,237263,237283,237303,237323,237343,237363,237383,237403,237423,237443,237463,237483,237503,237523,237543,237563,237583,237603,237623,2
 3926366 | pexicast_lg |     306 |      100 | tomcat    | DELETE FROM table_entries WHERE job_id=44 AND list_entry_id IN (233837,233857,233877,233897,233917,233937,233957,233977,233997,234017,234037,234057,234077,234097,234117,234137,234157,234177,234197,234217,234237,234257,234277,234297,234317,234337,2
 3926366 | pexicast_lg |     307 |      100 | tomcat    | DELETE FROM table_entries WHERE job_id=44 AND list_entry_id IN (232755,232775,232795,232815,232835,232855,232875,232895,232915,232935,232955,232975,232995,233015,233035,233055,233075,233095,233115,233135,233155,233175,233195,233215,233235,233255,2
 3926366 | pexicast_lg |     308 |      100 | tomcat    | DELETE FROM table_entries WHERE job_id=44 AND list_entry_id IN (231953,231973,231993,232013,232033,232053,232073,232093,232113,232133,232153,232173,232193,232213,232233,232253,232273,232293,232313,232333,232353,232373,232393,232413,232433,232453,2
 3926366 | pexicast_lg |     309 |      100 | tomcat    | DELETE FROM table_entries WHERE job_id=44 AND list_entry_id IN (231832,231852,231872,231892,231912,231932,231952,231972,231992,232012,232032,232052,232072,232092,232112,232132,232152,232172,232192,232212,232232,232252,232272,232292,232312,232332,2
 3926366 | pexicast_lg |     310 |      100 | tomcat    | DELETE FROM table_entries WHERE job_id=44 AND list_entry_id IN (224030,224050,224070,224090,224110,224130,224150,224170,224190,224210,224230,224250,224270,224290,224310,224330,224350,224370,224390,224410,224430,224450,224470,224490,224510,224530,2
 3926366 | pexicast_lg |     311 |      100 | tomcat    | DELETE FROM table_entries WHERE job_id=44 AND list_entry_id IN (230249,230269,230289,230309,230329,230349,230369,230389,230409,230429,230449,230469,230489,230509,230529,230549,230569,230589,230609,230629,230649,230669,230689,230709,230729,230749,2
 3926366 | pexicast_lg |     312 |      100 | tomcat    | DELETE FROM table_entries WHERE job_id=44 AND list_entry_id IN (230268,230288,230308,230328,230348,230368,230388,230408,230428,230448,230468,230488,230508,230528,230548,230568,230588,230608,230628,230648,230668,230688,230708,230728,230748,230768,2
 3926366 | pexicast_lg |     313 |      100 | tomcat    | DELETE FROM table_entries WHERE job_id=44 AND list_entry_id IN (234787,234807,234827,234847,234867,234887,234907,234927,234947,234967,234987,235007,235027,235047,235067,235087,235107,235127,235147,235167,235187,235207,235227,235247,235267,235287,2
 3926366 | pexicast_lg |     314 |      100 | tomcat    | DELETE FROM table_entries WHERE job_id=44 AND list_entry_id IN (246006,246026,246046,246066,246086,246106,246126,246146,246166,246186,246206,246226,246246,246266,246286,246306,246326,246346,246366,246386,246406,246426,246446,246466,246486,246506,2
 3926366 | pexicast_lg |     315 |      100 | tomcat    | DELETE FROM table_entries WHERE job_id=44 AND list_entry_id IN (238525,238545,238565,238585,238605,238625,238645,238665,238685,238705,238725,238745,238765,238785,238805,238825,238845,238865,238885,238905,238925,238945,238965,238985,239005,239025,2
 3926366 | pexicast_lg |     316 |      100 | tomcat    | DELETE FROM table_entries WHERE job_id=44 AND list_entry_id IN (239084,239104,239124,239144,239164,239184,239204,239224,239244,239264,239284,239304,239324,239344,239364,239384,239404,239424,239444,239464,239484,239504,239524,239544,239564,239584,2
 3926366 | pexicast_lg |     317 |      100 | tomcat    | DELETE FROM table_entries WHERE job_id=44 AND list_entry_id IN (225634,225654,225674,225694,225714,225734,225754,225774,225794,225814,225834,225854,225874,225894,225914,225934,225954,225974,225994,226014,226034,226054,226074,226094,226114,226134,2
 3926366 | pexicast_lg |     318 |      100 | tomcat    | DELETE FROM table_entries WHERE job_id=44 AND list_entry_id IN (234178,234198,234218,234238,234258,234278,234298,234318,234338,234358,234378,234398,234418,234438,234458,234478,234498,234518,234538,234558,234578,234598,234618,234638,234658,234678,2
 3926366 | pexicast_lg |     319 |      100 | tomcat    | DELETE FROM table_entries WHERE job_id=44 AND list_entry_id IN (237971,237991,238011,238031,238051,238071,238091,238111,238131,238151,238171,238191,238211,238231,238251,238271,238291,238311,238331,238351,238371,238391,238411,238431,238451,238471,2
 3926366 | pexicast_lg |     320 |      100 | tomcat    | <IDLE>
 3926366 | pexicast_lg |     321 |      100 | tomcat    | <IDLE>
 3926366 | pexicast_lg |     327 |      101 | dfields   | <IDLE>
(45 rows)




At 10:34 PM 7/6/2002, Doug Fields wrote:
Hello,

I've encountered an odd new symptom which has me absolutely flabbergasted.

I'm running about 20-25 parallel connections to my Debian/Woody PostgreSQL 7.2.1 server (Dual P4X 2.4, RAID-1 log partition, RAID-5 data partition, 8gb RAM, 64meg sort space, 256meg shared memory segment).

At a certain point, a query hangs, then another, then another, until all my connections are blocked. My application completely comes to a halt.

I've never seen this before, and don't know where to begin trying to solve it. Certainly nothing should cause these problems: the three queries being heavily used are simple:

1) A single one-to-one Joined SELECT
2) A simple DELETE with a static IN clause to delete a whole bunch of records
3) A simple UPDATE with a similar static IN clause to update one field (to now()) of a whole bunch of primary keys

Nothing appears in the logs. In fact, nothing seems amiss anywhere.

So, I'm stumped. I'm going to turn on some of the statistics collection stuff (new in 7.2) but really don't know what to do.

I welcome ideas. Some "top" and "ps" stuff is below.

Cheers,

Doug

pgsql-general by date:

Previous
From: Doug Fields
Date:
Subject: Re: database backup
Next
From: Lamar Owen
Date:
Subject: Re: database backup