On Tue, 19 Jul 2005 12:54:22 -0400 Tom Lane <[EMAIL PROTECTED]> wrote:
> Robert Creager <[EMAIL PROTECTED]> writes: > > Hmm, I hadn't thought about the possible impact of multiple concurrent > vacuums. Is the problem caused by that, or has performance already gone > into the tank by the time the cron-driven vacuums are taking long enough > to overlap? All statements over 5 seconds are logged. Vacuums are running on the 5 minute mark. Log file shows the first query starts going bad a 9:32:15 (7 seconds), although the second query start before the first . The first vacuum statement logged shows 1148 seconds completing at 9:54:09, so starting at 9:35. Looks like the vacuum is an innocent bystander of the problem. The first problem queries are below. Additionally, I've attached 5 minutes (bzipped) of logs starting at the first event below. Jul 19 09:32:15 annette postgres[17029]: [2-1] LOG: duration: 7146.168 ms statement: Jul 19 09:32:15 annette postgres[17029]: [2-2] ^I SELECT location_id, location_type.name AS type, library, rail Jul 19 09:32:15 annette postgres[17029]: [2-3] ^I FROM location_lock JOIN location USING( location_id ) Jul 19 09:32:15 annette postgres[17029]: [2-4] ^I JOIN location_type USING( location_type_id ) Jul 19 09:32:15 annette postgres[17029]: [2-5] ^I WHERE test_session_id = '5264' Jul 19 09:32:20 annette postgres[17092]: [2-1] LOG: duration: 13389.730 ms statement: Jul 19 09:32:20 annette postgres[17092]: [2-2] ^I SELECT location_type.name AS location_type_name, Jul 19 09:32:20 annette postgres[17092]: [2-3] ^I library, rail, col, side, row, location_id, Jul 19 09:32:20 annette postgres[17092]: [2-4] ^I hli_lsm, hli_panel, hli_row, hli_col Jul 19 09:32:20 annette postgres[17092]: [2-5] ^I FROM location JOIN location_type USING( location_type_id ) Jul 19 09:32:20 annette postgres[17092]: [2-6] ^I JOIN complex USING( library_id ) Jul 19 09:32:20 annette postgres[17092]: [2-7] ^I LEFT OUTER JOIN hli_location USING( location_id ) Jul 19 09:32:20 annette postgres[17092]: [2-8] ^I LEFT OUTER JOIN application USING( application_id ) Jul 19 09:32:20 annette postgres[17092]: [2-9] ^I WHERE complex.complex_id = '13' Jul 19 09:32:20 annette postgres[17092]: [2-10] ^I AND location_id NOT IN Jul 19 09:32:20 annette postgres[17092]: [2-11] ^I (SELECT location_id Jul 19 09:32:20 annette postgres[17092]: [2-12] ^I FROM location_lock) Jul 19 09:32:20 annette postgres[17092]: [2-13] ^I AND location_id NOT IN Jul 19 09:32:20 annette postgres[17092]: [2-14] ^I (SELECT location_id Jul 19 09:32:20 annette postgres[17092]: [2-15] ^I FROM cartridge) Jul 19 09:32:20 annette postgres[17092]: [2-16] ^IAND (location_type.name ~ 'cell' AND application.name ~ 'hli' AND hli_lsm = 1 AND col BETWEEN -2 AND 2) Jul 19 09:32:20 annette postgres[17092]: [2-17] ^I Jul 19 09:32:20 annette postgres[17092]: [2-18] ^I ORDER BY location.usage_count, location.rand LIMIT 1 Jul 19 09:32:20 annette postgres[17092]: [2-19] ^I FOR UPDATE OF location Cheers, Rob
pg.log.bz2
Description: BZip2 compressed data
---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
