From: Steve Lane <slane@soliantconsulting.com>
To: Steve Lane <slane@soliantconsulting.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-admin@postgresql.org
Cc: Jamie Thomas <jthomas@soliantconsulting.com>
Subject: Re: Odd query behavior [urgent, but long]
Date: Wed, 25 Aug 2004 21:59:25 -0500
Message-ID: <BD52BC3D.1A71B%slane@soliantconsulting.com> (raw)
In-Reply-To: <BD52522C.1A680%slane@soliantconsulting.com>
OK, well I may have some more useful information. The queries that blow up
seem, by and large, to be updates to the session table. Again, some of these
updates are big: I measured one at 50K today.
I suspected that insufficient vacuuming might be involved. I did a full
vacuum and got this for the session table:
NOTICE: --Relation nsse_session--
NOTICE: Pages 3544: Changed 11, reaped 696, Empty 0, New 0; Tup 26121: Vac
37327, Keep/VTL 0/0, Crash 0, UnUsed 107, MinLen 132, MaxLen 2032; Re-using:
Free/Avail. Space 5345328/5336916; EndEmpty/Avail. Pages 0/684. CPU
0.08s/0.03u sec.
NOTICE: Index nsse_session_pkey: Pages 1435; Tuples 26121: Deleted 36871.
CPU 0.07s/0.13u sec.
NOTICE: Rel nsse_session: Pages: 3544 --> 2873; Tuple(s) moved: 13. CPU
0.02s/0.06u sec.
NOTICE: Index nsse_session_pkey: Pages 1435; Tuples 26121: Deleted 13. CPU
0.00s/0.01u sec.
NOTICE: --Relation pg_toast_3521195--
NOTICE: Pages 83872: Changed 6, reaped 81999, Empty 0, New 0; Tup 8100: Vac
327763, Keep/VTL 0/0, Crash 0, UnUsed 88, MinLen 45, MaxLen 2034; Re-using:
Free/Avail. Space 668306776/668306512; EndEmpty/Avail. Pages 0/81954. CPU
3.22s/0.30u sec.
NOTICE: Index pg_toast_3521195_idx: Pages 1668; Tuples 8100: Deleted
327763. CPU 0.33s/1.07u sec.
NOTICE: Rel pg_toast_3521195: Pages: 83872 --> 1933; Tuple(s) moved: 15.
CPU 10.16s/4.87u sec.
NOTICE: Index pg_toast_3521195_idx: Pages 1668; Tuples 8100: Deleted 15.
CPU 0.05s/0.00u sec.
Now granted, this was after I had written scripts to perform that 50K update
about 10-15K times -- I did this just to check the degradation in insert
performance on the unvacuumed table, and as expected I saw a slow, steady
degradation in insert time, but nothing catastrophic.
Clearly the toast table for nsse_session is big, and the table is very
frequently accessed. This seems like a recipe for page faults of some kind,
but it seems this could lead to two opposite conclusions:
1) lower shared buffers in case shared buffers are starving the OS disk
caching buffers
2) raise shared buffers so as to get the whole session/toast table in memory
??
-- sgl
> From: Steve Lane <slane@soliantconsulting.com>
> Date: Wed, 25 Aug 2004 14:26:52 -0500
> To: Tom Lane <tgl@sss.pgh.pa.us>
> Cc: <pgsql-admin@postgresql.org>, Jamie Thomas <jthomas@soliantconsulting.com>
> Subject: Re: [ADMIN] Odd query behavior [urgent, but long]
>
>
>
>> From: Tom Lane <tgl@sss.pgh.pa.us>
>> Date: Wed, 25 Aug 2004 15:16:32 -0400
>> To: Steve Lane <slane@soliantconsulting.com>
>> Cc: pgsql-admin@postgresql.org, Jamie Thomas <jthomas@soliantconsulting.com>
>> Subject: Re: [ADMIN] Odd query behavior [urgent, but long]
>>
>> Steve Lane <slane@soliantconsulting.com> writes:
>>> I have enabled pretty extensive query logging on the 7.1.3 instance. What I
>>> see is that certain queries take hideously long.
>>
>> Tell us about foreign keys associated with the table being updated?
>> An UPDATE would fire triggers for both referencing and referenced keys ...
>>
>> I'm suspecting an unindexed or datatype-incompatible foreign key column.
>
> Hi Tom:
>
> Thanks. There are, unfortunately, no foreign keys or triggers anywhere in
> the db that I know of. And this query is not always slow, just occasionally.
>
> -- sgl
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: slane@soliantconsulting.com, tgl@sss.pgh.pa.us, jthomas@soliantconsulting.com
Subject: Re: Odd query behavior [urgent, but long]
In-Reply-To: <BD52BC3D.1A71B%slane@soliantconsulting.com>
References: <BD52522C.1A680%slane@soliantconsulting.com> <BD52BC3D.1A71B%slane@soliantconsulting.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