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

From Robert Creager
Subject Re: Huge performance problem between 7.4.1 and 8.0.3 - CS issue?
Date
Msg-id 20050719133417.00001473@C118181.stortek.com
Whole thread Raw
In response to Re: Huge performance problem between 7.4.1 and 8.0.3 - CS issue?  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-performance
On Tue, 19 Jul 2005 12:54:22 -0400
Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Robert Creager <Robert_Creager@StorageTek.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
vacuumis 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
eventbelow. 

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


Attachment

pgsql-performance by date:

Previous
From: Christopher Petrilli
Date:
Subject: Re: Impact of checkpoint_segments under continual load conditions
Next
From: "Steinar H. Gunderson"
Date:
Subject: Re: Looking for tips