From: Robert Creager <Robert_Creager@StorageTek.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Robert Creager <Robert_Creager@StorageTek.com>
Cc: pgsql-performance@postgresql.org, Andy Hewitt III <ralph_hewitt_iii@StorageTek.com>
Subject: Re: Huge performance problem between 7.4.1 and 8.0.3 - CS issue?
Date: Tue, 19 Jul 2005 13:34:17 -0600
Message-ID: <20050719133417.00001473@C118181.stortek.com> (raw)
In-Reply-To: <7322.1121792062@sss.pgh.pa.us>
References: <20050718114952.00004ac5@C118181.stortek.com>
<15472.1121709173@sss.pgh.pa.us>
<20050719105014.00007635@C118181.stortek.com>
<7322.1121792062@sss.pgh.pa.us>
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 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
Attachments:
pg.log.bz2 (application/x-bzip2, 7.9K) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: Robert_Creager@StorageTek.com, tgl@sss.pgh.pa.us, ralph_hewitt_iii@StorageTek.com
Subject: Re: Huge performance problem between 7.4.1 and 8.0.3 - CS issue?
In-Reply-To: <20050719133417.00001473@C118181.stortek.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox