pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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)
> 




view thread (4+ messages)

Message-ID: <BD52BC3D.1A71B%slane@soliantconsulting.com>
Permalink:  ../BD52BC3D.1A71B%25slane@soliantconsulting.com/
Also on:    postgresql.org/message-id/BD52BC3D.1A71B%slane@soliantconsulting.com

 · 

reply

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