Re: Huge performance problem between 7.4.1 and 8.0.3 - CS issue?

On Tue, 19 Jul 2005 12:54:22 -0400
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Robert Creager <Robert_Creager(at)StorageTek(dot)com> 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