Обсуждение: Odd new symptom - database locking up on a query
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 TOP 22:30:25 up 14 days, 5:59, 1 user, load average: 12.02, 10.67, 9.59 89 processes: 77 sleeping, 12 running, 0 zombie, 0 stopped CPU states: 99.1% user, 0.5% system, 0.4% nice, -0.0% idle Mem: 8123456K total, 2839996K used, 5283460K free, 133144K buffers Swap: 8000208K total, 0K used, 8000208K free, 2453568K cached PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND 19405 postgres 18 0 42244 41M 40956 S 21.2 0.5 1:15 postmaster 19421 postgres 20 0 39928 38M 38612 R 21.2 0.4 1:15 postmaster 19398 postgres 18 0 42796 41M 41480 R 20.6 0.5 1:14 postmaster 19417 postgres 20 0 40448 39M 39168 R 20.6 0.4 1:15 postmaster 19426 postgres 20 0 36436 35M 35104 R 20.4 0.4 1:10 postmaster 19427 postgres 19 0 33928 33M 32592 S 20.4 0.4 1:11 postmaster 19416 postgres 19 0 37852 36M 36500 S 20.2 0.4 1:10 postmaster 19402 postgres 17 0 51508 50M 50268 S 20.0 0.6 1:27 postmaster 19411 postgres 18 0 40884 39M 39620 R 20.0 0.5 1:21 postmaster 19393 postgres 19 0 47952 46M 46700 S 19.6 0.5 1:34 postmaster 19400 postgres 18 0 54308 52M 53068 S 19.6 0.6 1:31 postmaster 19423 postgres 20 0 37648 36M 36356 R 19.6 0.4 1:13 postmaster 19403 postgres 19 0 49708 48M 48456 S 19.4 0.6 1:26 postmaster 19406 postgres 16 0 45292 44M 44040 R 19.4 0.5 1:24 postmaster 19431 postgres 18 0 35856 34M 34524 S 19.4 0.4 1:11 postmaster 19410 postgres 17 0 43508 42M 42248 R 19.2 0.5 1:21 postmaster 19394 postgres 18 0 58308 56M 57096 S 19.0 0.7 1:43 postmaster 19414 postgres 17 0 39452 38M 38168 R 19.0 0.4 1:15 postmaster 19399 postgres 15 0 50424 49M 49184 S 18.4 0.6 1:37 postmaster 19391 postgres 15 0 66668 65M 65464 S 17.7 0.8 1:45 postmaster PS (IP addresses removed) postgres 19376 0.0 0.0 276036 4520 pts/0 S 22:22 0:00 /usr/lib/postgresql/bin/postmaster postgres 19378 0.0 0.0 277028 4500 pts/0 S 22:22 0:00 postgres: stats buffer process postgres 19379 0.0 0.0 276128 4596 pts/0 S 22:22 0:00 postgres: stats collector process postgres 19386 0.0 0.0 277164 6716 pts/0 S 22:22 0:00 postgres: bknowlton bfkdev A.B.C.20 idle postgres 19387 0.0 0.0 277164 6540 pts/0 S 22:22 0:00 postgres: bknowlton bfkdev A.B.C.20 idle postgres 19388 0.0 0.0 277164 6548 pts/0 S 22:22 0:00 postgres: bknowlton bfkdev A.B.C.20 idle postgres 19389 0.0 0.0 277164 6544 pts/0 S 22:22 0:00 postgres: bknowlton bfkdev A.B.C.20 SELECT postgres 19390 0.0 0.0 277164 6548 pts/0 S 22:22 0:00 postgres: bknowlton bfkdev A.B.C.20 idle postgres 19391 22.2 0.8 277808 72828 pts/0 S 22:22 1:59 postgres: tomcat pexicast_lg A.B.C.100 DELETE postgres 19392 0.3 0.1 277816 15248 pts/0 S 22:22 0:01 postgres: tomcat pexicast_lg A.B.C.100 DELETE waiting postgres 19393 20.3 0.6 277816 52536 pts/0 S 22:22 1:48 postgres: tomcat pexicast_lg A.B.C.100 DELETE postgres 19394 21.9 0.7 277812 63936 pts/0 S 22:22 1:57 postgres: tomcat pexicast_lg A.B.C.100 DELETE postgres 19395 0.3 0.1 277812 15600 pts/0 S 22:22 0:01 postgres: tomcat pexicast_lg A.B.C.100 DELETE waiting postgres 19396 0.2 0.1 277812 15608 pts/0 S 22:22 0:01 postgres: tomcat pexicast_lg A.B.C.100 DELETE waiting postgres 19397 0.4 0.1 277812 16128 pts/0 S 22:22 0:02 postgres: tomcat pexicast_lg A.B.C.100 DELETE waiting postgres 19398 16.5 0.5 277956 47424 pts/0 R 22:22 1:28 postgres: tomcat pexicast_lg A.B.C.100 DELETE postgres 19399 20.8 0.6 277816 55272 pts/0 S 22:22 1:51 postgres: tomcat pexicast_lg A.B.C.100 DELETE postgres 19400 19.7 0.7 277816 59996 pts/0 S 22:22 1:45 postgres: tomcat pexicast_lg A.B.C.100 DELETE postgres 19401 0.8 0.2 277816 16764 pts/0 S 22:22 0:04 postgres: tomcat pexicast_lg A.B.C.100 DELETE waiting postgres 19402 19.1 0.7 277812 57124 pts/0 S 22:22 1:42 postgres: tomcat pexicast_lg A.B.C.100 DELETE postgres 19403 18.8 0.6 277816 54764 pts/0 S 22:22 1:40 postgres: tomcat pexicast_lg A.B.C.100 DELETE postgres 19404 0.4 0.1 277956 16068 pts/0 S 22:22 0:02 postgres: tomcat pexicast_lg A.B.C.100 DELETE waiting postgres 19405 16.8 0.5 277952 46812 pts/0 S 22:22 1:29 postgres: tomcat pexicast_lg A.B.C.100 DELETE postgres 19406 18.5 0.6 277948 49896 pts/0 R 22:22 1:38 postgres: tomcat pexicast_lg A.B.C.100 DELETE postgres 19407 0.3 0.1 277952 15892 pts/0 S 22:22 0:01 postgres: tomcat pexicast_lg A.B.C.100 DELETE waiting postgres 19408 0.3 0.1 277948 16060 pts/0 S 22:22 0:01 postgres: tomcat pexicast_lg A.B.C.100 DELETE waiting postgres 19409 0.5 0.2 278216 17524 pts/0 S 22:22 0:02 postgres: tomcat pexicast_lg A.B.C.100 DELETE waiting postgres 19410 18.1 0.5 277952 48120 pts/0 S 22:22 1:35 postgres: tomcat pexicast_lg A.B.C.100 DELETE postgres 19411 18.0 0.5 277952 44904 pts/0 S 22:22 1:35 postgres: tomcat pexicast_lg A.B.C.100 DELETE postgres 19412 0.3 0.1 277952 16156 pts/0 S 22:22 0:01 postgres: tomcat pexicast_lg A.B.C.100 DELETE waiting postgres 19413 0.7 0.2 277952 17396 pts/0 S 22:22 0:03 postgres: tomcat pexicast_lg A.B.C.100 DELETE waiting postgres 19414 17.0 0.5 278212 43368 pts/0 S 22:22 1:30 postgres: tomcat pexicast_lg A.B.C.100 DELETE postgres 19415 0.4 0.2 278216 17080 pts/0 S 22:22 0:02 postgres: tomcat pexicast_lg A.B.C.100 DELETE waiting postgres 19416 16.1 0.5 278220 41980 pts/0 S 22:22 1:24 postgres: tomcat pexicast_lg A.B.C.100 DELETE postgres 19417 17.1 0.5 278208 44652 pts/0 S 22:22 1:30 postgres: tomcat pexicast_lg A.B.C.100 DELETE postgres 19418 0.5 0.2 278212 17440 pts/0 S 22:22 0:02 postgres: tomcat pexicast_lg A.B.C.100 DELETE waiting postgres 19419 0.2 0.2 278216 17692 pts/0 S 22:22 0:01 postgres: tomcat pexicast_lg A.B.C.100 DELETE waiting postgres 19421 17.2 0.5 278216 43920 pts/0 S 22:22 1:30 postgres: tomcat pexicast_lg A.B.C.100 DELETE postgres 19422 0.3 0.0 277452 6548 pts/0 S 22:22 0:02 postgres: tomcat pexicast_lg A.B.C.100 idle postgres 19423 16.8 0.5 278208 41528 pts/0 S 22:22 1:28 postgres: tomcat pexicast_lg A.B.C.100 DELETE postgres 19424 0.3 0.2 278216 16508 pts/0 S 22:22 0:01 postgres: tomcat pexicast_lg A.B.C.100 DELETE waiting postgres 19425 0.7 0.2 278216 18040 pts/0 S 22:22 0:03 postgres: tomcat pexicast_lg A.B.C.100 DELETE waiting postgres 19426 16.3 0.4 278216 40352 pts/0 R 22:22 1:25 postgres: tomcat pexicast_lg A.B.C.100 DELETE postgres 19427 16.4 0.4 278216 37476 pts/0 S 22:22 1:26 postgres: tomcat pexicast_lg A.B.C.100 DELETE postgres 19428 0.4 0.2 278216 16844 pts/0 S 22:22 0:02 postgres: tomcat pexicast_lg A.B.C.100 DELETE waiting postgres 19429 0.3 0.0 277192 6532 pts/0 S 22:22 0:01 postgres: tomcat pexicast_lg A.B.C.100 idle postgres 19430 0.3 0.0 277188 6596 pts/0 S 22:22 0:01 postgres: tomcat pexicast_lg A.B.C.100 idle postgres 19431 16.4 0.4 278216 39696 pts/0 S 22:22 1:25 postgres: tomcat pexicast_lg A.B.C.100 DELETE postgres 19432 0.7 0.2 278212 18164 pts/0 S 22:22 0:03 postgres: tomcat pexicast_lg A.B.C.100 DELETE waiting postgres 19433 0.5 0.0 277184 6552 pts/0 S 22:22 0:02 postgres: tomcat pexicast_lg A.B.C.100 idle postgres 19434 0.5 0.0 277200 6724 pts/0 S 22:22 0:02 postgres: tomcat pexicast_lg A.B.C.100 idle root 19452 0.0 0.0 1344 436 pts/0 S 22:31 0:00 fgrep post
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:
(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
Even more additional information: If I let the database sit for tens of minutes, it will come back to life, slowly. Then, a few minutes later, the cycle will repeat again. Any ideas? Thanks, Doug At 11:20 PM 7/6/2002, Doug Fields wrote: >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
Doug Fields <dfields-pg-general@pexicom.com> writes: > At a certain point, a query hangs, then another, then another, until all my > connections are blocked. My application completely comes to a halt. Are they actually hung, or just taking an unreasonable amount of time? Can you attach to some of the busy backends with gdb and get stack traces? regards, tom lane
At 12:12 AM 7/7/2002, Tom Lane wrote: >Doug Fields <dfields-pg-general@pexicom.com> writes: > > At a certain point, a query hangs, then another, then another, until > all my > > connections are blocked. My application completely comes to a halt. > >Are they actually hung, or just taking an unreasonable amount of time? > >Can you attach to some of the busy backends with gdb and get stack >traces? After increasing my "human patience interval" to a suitable amount (>3,600,000 milliseconds), it seems that they're actually "just taking an unreasonable amount of time." Of course, "unreasonable" in this case means to me "more than a fraction of a second." They're "hanging" with full CPU usage during this time. I will do as you suggest. If Debian doesn't provide symbols in the executable, I'll recompile it, I guess. Thanks, Doug
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:
(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
TOP
22:30:25 up 14 days, 5:59, 1 user, load average: 12.02, 10.67, 9.59
89 processes: 77 sleeping, 12 running, 0 zombie, 0 stopped
CPU states: 99.1% user, 0.5% system, 0.4% nice, -0.0% idle
Mem: 8123456K total, 2839996K used, 5283460K free, 133144K buffers
Swap: 8000208K total, 0K used, 8000208K free, 2453568K cached
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
19405 postgres 18 0 42244 41M 40956 S 21.2 0.5 1:15 postmaster
19421 postgres 20 0 39928 38M 38612 R 21.2 0.4 1:15 postmaster
19398 postgres 18 0 42796 41M 41480 R 20.6 0.5 1:14 postmaster
19417 postgres 20 0 40448 39M 39168 R 20.6 0.4 1:15 postmaster
19426 postgres 20 0 36436 35M 35104 R 20.4 0.4 1:10 postmaster
19427 postgres 19 0 33928 33M 32592 S 20.4 0.4 1:11 postmaster
19416 postgres 19 0 37852 36M 36500 S 20.2 0.4 1:10 postmaster
19402 postgres 17 0 51508 50M 50268 S 20.0 0.6 1:27 postmaster
19411 postgres 18 0 40884 39M 39620 R 20.0 0.5 1:21 postmaster
19393 postgres 19 0 47952 46M 46700 S 19.6 0.5 1:34 postmaster
19400 postgres 18 0 54308 52M 53068 S 19.6 0.6 1:31 postmaster
19423 postgres 20 0 37648 36M 36356 R 19.6 0.4 1:13 postmaster
19403 postgres 19 0 49708 48M 48456 S 19.4 0.6 1:26 postmaster
19406 postgres 16 0 45292 44M 44040 R 19.4 0.5 1:24 postmaster
19431 postgres 18 0 35856 34M 34524 S 19.4 0.4 1:11 postmaster
19410 postgres 17 0 43508 42M 42248 R 19.2 0.5 1:21 postmaster
19394 postgres 18 0 58308 56M 57096 S 19.0 0.7 1:43 postmaster
19414 postgres 17 0 39452 38M 38168 R 19.0 0.4 1:15 postmaster
19399 postgres 15 0 50424 49M 49184 S 18.4 0.6 1:37 postmaster
19391 postgres 15 0 66668 65M 65464 S 17.7 0.8 1:45 postmaster
PS (IP addresses removed)
postgres 19376 0.0 0.0 276036 4520 pts/0 S 22:22 0:00 /usr/lib/postgresql/bin/postmaster
postgres 19378 0.0 0.0 277028 4500 pts/0 S 22:22 0:00 postgres: stats buffer process
postgres 19379 0.0 0.0 276128 4596 pts/0 S 22:22 0:00 postgres: stats collector process
postgres 19386 0.0 0.0 277164 6716 pts/0 S 22:22 0:00 postgres: bknowlton bfkdev A.B.C.20 idle
postgres 19387 0.0 0.0 277164 6540 pts/0 S 22:22 0:00 postgres: bknowlton bfkdev A.B.C.20 idle
postgres 19388 0.0 0.0 277164 6548 pts/0 S 22:22 0:00 postgres: bknowlton bfkdev A.B.C.20 idle
postgres 19389 0.0 0.0 277164 6544 pts/0 S 22:22 0:00 postgres: bknowlton bfkdev A.B.C.20 SELECT
postgres 19390 0.0 0.0 277164 6548 pts/0 S 22:22 0:00 postgres: bknowlton bfkdev A.B.C.20 idle
postgres 19391 22.2 0.8 277808 72828 pts/0 S 22:22 1:59 postgres: tomcat pexicast_lg A.B.C.100 DELETE
postgres 19392 0.3 0.1 277816 15248 pts/0 S 22:22 0:01 postgres: tomcat pexicast_lg A.B.C.100 DELETE waiting
postgres 19393 20.3 0.6 277816 52536 pts/0 S 22:22 1:48 postgres: tomcat pexicast_lg A.B.C.100 DELETE
postgres 19394 21.9 0.7 277812 63936 pts/0 S 22:22 1:57 postgres: tomcat pexicast_lg A.B.C.100 DELETE
postgres 19395 0.3 0.1 277812 15600 pts/0 S 22:22 0:01 postgres: tomcat pexicast_lg A.B.C.100 DELETE waiting
postgres 19396 0.2 0.1 277812 15608 pts/0 S 22:22 0:01 postgres: tomcat pexicast_lg A.B.C.100 DELETE waiting
postgres 19397 0.4 0.1 277812 16128 pts/0 S 22:22 0:02 postgres: tomcat pexicast_lg A.B.C.100 DELETE waiting
postgres 19398 16.5 0.5 277956 47424 pts/0 R 22:22 1:28 postgres: tomcat pexicast_lg A.B.C.100 DELETE
postgres 19399 20.8 0.6 277816 55272 pts/0 S 22:22 1:51 postgres: tomcat pexicast_lg A.B.C.100 DELETE
postgres 19400 19.7 0.7 277816 59996 pts/0 S 22:22 1:45 postgres: tomcat pexicast_lg A.B.C.100 DELETE
postgres 19401 0.8 0.2 277816 16764 pts/0 S 22:22 0:04 postgres: tomcat pexicast_lg A.B.C.100 DELETE waiting
postgres 19402 19.1 0.7 277812 57124 pts/0 S 22:22 1:42 postgres: tomcat pexicast_lg A.B.C.100 DELETE
postgres 19403 18.8 0.6 277816 54764 pts/0 S 22:22 1:40 postgres: tomcat pexicast_lg A.B.C.100 DELETE
postgres 19404 0.4 0.1 277956 16068 pts/0 S 22:22 0:02 postgres: tomcat pexicast_lg A.B.C.100 DELETE waiting
postgres 19405 16.8 0.5 277952 46812 pts/0 S 22:22 1:29 postgres: tomcat pexicast_lg A.B.C.100 DELETE
postgres 19406 18.5 0.6 277948 49896 pts/0 R 22:22 1:38 postgres: tomcat pexicast_lg A.B.C.100 DELETE
postgres 19407 0.3 0.1 277952 15892 pts/0 S 22:22 0:01 postgres: tomcat pexicast_lg A.B.C.100 DELETE waiting
postgres 19408 0.3 0.1 277948 16060 pts/0 S 22:22 0:01 postgres: tomcat pexicast_lg A.B.C.100 DELETE waiting
postgres 19409 0.5 0.2 278216 17524 pts/0 S 22:22 0:02 postgres: tomcat pexicast_lg A.B.C.100 DELETE waiting
postgres 19410 18.1 0.5 277952 48120 pts/0 S 22:22 1:35 postgres: tomcat pexicast_lg A.B.C.100 DELETE
postgres 19411 18.0 0.5 277952 44904 pts/0 S 22:22 1:35 postgres: tomcat pexicast_lg A.B.C.100 DELETE
postgres 19412 0.3 0.1 277952 16156 pts/0 S 22:22 0:01 postgres: tomcat pexicast_lg A.B.C.100 DELETE waiting
postgres 19413 0.7 0.2 277952 17396 pts/0 S 22:22 0:03 postgres: tomcat pexicast_lg A.B.C.100 DELETE waiting
postgres 19414 17.0 0.5 278212 43368 pts/0 S 22:22 1:30 postgres: tomcat pexicast_lg A.B.C.100 DELETE
postgres 19415 0.4 0.2 278216 17080 pts/0 S 22:22 0:02 postgres: tomcat pexicast_lg A.B.C.100 DELETE waiting
postgres 19416 16.1 0.5 278220 41980 pts/0 S 22:22 1:24 postgres: tomcat pexicast_lg A.B.C.100 DELETE
postgres 19417 17.1 0.5 278208 44652 pts/0 S 22:22 1:30 postgres: tomcat pexicast_lg A.B.C.100 DELETE
postgres 19418 0.5 0.2 278212 17440 pts/0 S 22:22 0:02 postgres: tomcat pexicast_lg A.B.C.100 DELETE waiting
postgres 19419 0.2 0.2 278216 17692 pts/0 S 22:22 0:01 postgres: tomcat pexicast_lg A.B.C.100 DELETE waiting
postgres 19421 17.2 0.5 278216 43920 pts/0 S 22:22 1:30 postgres: tomcat pexicast_lg A.B.C.100 DELETE
postgres 19422 0.3 0.0 277452 6548 pts/0 S 22:22 0:02 postgres: tomcat pexicast_lg A.B.C.100 idle
postgres 19423 16.8 0.5 278208 41528 pts/0 S 22:22 1:28 postgres: tomcat pexicast_lg A.B.C.100 DELETE
postgres 19424 0.3 0.2 278216 16508 pts/0 S 22:22 0:01 postgres: tomcat pexicast_lg A.B.C.100 DELETE waiting
postgres 19425 0.7 0.2 278216 18040 pts/0 S 22:22 0:03 postgres: tomcat pexicast_lg A.B.C.100 DELETE waiting
postgres 19426 16.3 0.4 278216 40352 pts/0 R 22:22 1:25 postgres: tomcat pexicast_lg A.B.C.100 DELETE
postgres 19427 16.4 0.4 278216 37476 pts/0 S 22:22 1:26 postgres: tomcat pexicast_lg A.B.C.100 DELETE
postgres 19428 0.4 0.2 278216 16844 pts/0 S 22:22 0:02 postgres: tomcat pexicast_lg A.B.C.100 DELETE waiting
postgres 19429 0.3 0.0 277192 6532 pts/0 S 22:22 0:01 postgres: tomcat pexicast_lg A.B.C.100 idle
postgres 19430 0.3 0.0 277188 6596 pts/0 S 22:22 0:01 postgres: tomcat pexicast_lg A.B.C.100 idle
postgres 19431 16.4 0.4 278216 39696 pts/0 S 22:22 1:25 postgres: tomcat pexicast_lg A.B.C.100 DELETE
postgres 19432 0.7 0.2 278212 18164 pts/0 S 22:22 0:03 postgres: tomcat pexicast_lg A.B.C.100 DELETE waiting
postgres 19433 0.5 0.0 277184 6552 pts/0 S 22:22 0:02 postgres: tomcat pexicast_lg A.B.C.100 idle
postgres 19434 0.5 0.0 277200 6724 pts/0 S 22:22 0:02 postgres: tomcat pexicast_lg A.B.C.100 idle
root 19452 0.0 0.0 1344 436 pts/0 S 22:31 0:00 fgrep post
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Doug Fields <dfields@pexicom.com> writes: > CREATE INDEX idx ON table (a,b); > And the query is of the form: (with hundreds in the static IN) > explain DELETE FROM table WHERE b=44 AND a IN > (1071164,1071176,1071188,1071200,1071212,1071224,1071236,1071248,1071260,1071272,1071284,1071296); > However, if the index is manually rebuilt accidentally as: > CREATE INDEX idx ON table (b,a); > (Note the a,b is reversed) THEN IT WILL NOT BE USED. Yup. This has to do with the planner's search algorithm for potentially useful indexscan qualifications. Given an indexable OR clause it's relatively cheap to see if we can extend it to additional index columns, but the other way around would require unconstrained search through all possible pairs of WHERE clauses, which looks like a bad idea to me. regards, tom lane
On Mon, 8 Jul 2002, Tom Lane wrote: > Doug Fields <dfields@pexicom.com> writes: > > CREATE INDEX idx ON table (a,b); > > > And the query is of the form: (with hundreds in the static IN) > > > explain DELETE FROM table WHERE b=44 AND a IN > > (1071164,1071176,1071188,1071200,1071212,1071224,1071236,1071248,1071260,1071272,1071284,1071296); > > > However, if the index is manually rebuilt accidentally as: > > > CREATE INDEX idx ON table (b,a); > > > (Note the a,b is reversed) THEN IT WILL NOT BE USED. > > Yup. This has to do with the planner's search algorithm for potentially > useful indexscan qualifications. Given an indexable OR clause it's > relatively cheap to see if we can extend it to additional index columns, > but the other way around would require unconstrained search through all > possible pairs of WHERE clauses, which looks like a bad idea to me. Tom, Are you sure about this? I read it as Doug is saying normally the index is built with the order a, b which wouldn't be surprising [to me] if the index wasn't used in a query using a test like b = 4 and a IN (lots). Whereas if the index is built with the order reversed to b, a then the index really isn't used but one would have thought it a good candidate for use. I'm sure a while ago it was you who told me that the order in a multi column index was significant and the first item was the 'major' selector...but then may be I didn't read it properly and my mind just filled in what I would have expected to be the case. I'll see if I can find the email but in the meantime could you restate whether an index built with the order b,a would be a good candidate for use in a query using a where clause of b = x AND a IN (long list) please? Thanks, -- Nigel J. Andrews Director --- Logictree Systems Limited Computer Consultants
"Nigel J. Andrews" <nandrews@investsystems.co.uk> writes: > could you restate whether an index built with the order b,a would be a good > candidate for use in a query using a where clause of b = x AND a IN (long > list) please? Such an index would only be matched to the b = x clause. If you can think of a way to improve that without materially increasing the search time in indxpath.c, I'm all ears... regards, tom lane