Re: Odd new symptom - database locking up on a query - Mailing 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