Re: Odd query behavior [urgent, but long]

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(at)soliantconsulting(dot)com>
> Date: Wed, 25 Aug 2004 14:26:52 -0500
> To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> Cc: <pgsql-admin(at)postgresql(dot)org>, Jamie Thomas <jthomas(at)soliantconsulting(dot)com>
> Subject: Re: [ADMIN] Odd query behavior [urgent, but long]
>
>
>
>> From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
>> Date: Wed, 25 Aug 2004 15:16:32 -0400
>> To: Steve Lane <slane(at)soliantconsulting(dot)com>
>> Cc: pgsql-admin(at)postgresql(dot)org, Jamie Thomas <jthomas(at)soliantconsulting(dot)com>
>> Subject: Re: [ADMIN] Odd query behavior [urgent, but long]
>>
>> Steve Lane <slane(at)soliantconsulting(dot)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(at)postgresql(dot)org)
>