pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Odd double queries continues
3+ messages / 2 participants
[nested] [flat]

* Odd double queries continues
@ 2004-08-26 12:17  Steve Lane <slane@soliantconsulting.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Steve Lane @ 2004-08-26 12:17 UTC (permalink / raw)
  To: Steve Lane <slane@soliantconsulting.com>; +Cc: pgsql-admin; Jamie Thomas <jthomas@soliantconsulting.com>

My odd "double" queries continue. On the theory that I had some kind of
page-faulting issue tied into large, frequent updates of a table, I vacuumed
the whole database and began to watch it closely this morning. Already,
after very little activity, I get this in the log:

2004-08-26 07:01:26 [22056]  DEBUG:  query: INSERT INTO nsse_session
(id_user, id_session, expiration, value) VALUES (<values>)
2004-08-26 07:01:26 [22056]  ERROR:  Cannot insert a duplicate key into
unique index nsse_session_pkey
2004-08-26 07:01:26 [22056]  DEBUG:  query: UPDATE nsse_session SET <values>
WHERE id_session = '32845b87350846bf51176079c19f2fb4'
EXECUTOR STATISTICS
! system usage stats:
!       0.000183 elapsed 0.000000 user 0.000000 system sec
!       [0.000000 user 0.030000 sys total]
!       0/0 [0/0] filesystem blocks in/out
!       2/4 [414/257] page faults/reclaims, 0 [0] swaps
!       0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
!       0/0 [0/0] voluntary/involuntary context switches
! postgres usage stats:
!       Shared blocks:          0 read,          0 written, buffer hit rate
= 100.00%
!       Local  blocks:          0 read,          0 written, buffer hit rate
= 0.00%
!       Direct blocks:          0 read,          0 written
EXECUTOR STATISTICS
! system usage stats:
!       10.626398 elapsed 10.490000 user 0.010000 system sec
!       [10.510000 user 0.010000 sys total]
!       0/0 [0/0] filesystem blocks in/out
!       261/10 [691/281] page faults/reclaims, 0 [0] swaps
!       0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
!       0/0 [0/0] voluntary/involuntary context switches
! postgres usage stats:
!       Shared blocks:          0 read,          0 written, buffer hit rate
= 100.00%
!       Local  blocks:          0 read,          0 written, buffer hit rate
= 0.00%
!       Direct blocks:          0 read,          0 written

Same pattern as before -- the same query seems to get run twice, the second
time ridiculously long, with a high number of page faults. (Again, this is a
straightforward query involving a single index scan, and the planner says as
much).

I realize no one may have any insight in the whole problem, but can anyone
at least tell me the significance of each of these numbers from the stats:

261/10 [691/281] page faults/reclaims, 0 [0] swaps

That might give me an idea of where to begin.

-- sgl




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Odd double queries continues
@ 2004-08-26 14:51  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Steve Lane <slane@soliantconsulting.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Tom Lane @ 2004-08-26 14:51 UTC (permalink / raw)
  To: Steve Lane <slane@soliantconsulting.com>; +Cc: pgsql-admin@postgresql.org, Jamie Thomas <jthomas@soliantconsulting.com>

Steve Lane <slane@soliantconsulting.com> writes:
> I realize no one may have any insight in the whole problem, but can anyone
> at least tell me the significance of each of these numbers from the stats:
> 261/10 [691/281] page faults/reclaims, 0 [0] swaps

It's just repeating what getrusage() told it.  See the docs for your
operating system.

It looks like the numbers inside the square brackets are totals since
process start, while the ones before are the delta since query start.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Odd double queries continues
@ 2004-08-26 19:33  Steve Lane <slane@soliantconsulting.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 3+ messages in thread

From: Steve Lane @ 2004-08-26 19:33 UTC (permalink / raw)
  To: pgsql-admin; +Cc: Jamie Thomas <jthomas@soliantconsulting.com>

Okay, that's helpful. I found the relevant code in /backend/tcop/postgres.c
and it's as you say, process start vs query start ...

OK, so these numbers are OS-level page faults. Can you help me understand
what a page fault on an update would mean? Is it that the disk page
containing some or all of the row to be updated is not found in the OS
cache?

Lastly, any idea why I would see executor stats twice (or in one case this
morning) three times in a row? Is it actually re-running the query?

Here's one last oddness: the vast bulk of these problem queries occur when
we're updating a session table, where our logic is:

1) Send the query as an INSERT
2) See if it fails
3) If so, resend as an UPDATE

The initial INSERTs never show this kind of problem, only the subsequent
UPDATES. Is there something in our pattern that somehow increases the
possibility of a cache miss? The example of sequential reads on a
non-interleaved hard drive comes to mind, but I can't imagine that that's
useful except in some vague metaphorical way ...

-- sgl



> From: Tom Lane <tgl@sss.pgh.pa.us>
> Date: Thu, 26 Aug 2004 10:51:21 -0400
> To: Steve Lane <slane@soliantconsulting.com>
> Cc: pgsql-admin@postgresql.org, Jamie Thomas <jthomas@soliantconsulting.com>
> Subject: Re: [ADMIN] Odd double queries continues
> 
> Steve Lane <slane@soliantconsulting.com> writes:
>> I realize no one may have any insight in the whole problem, but can anyone
>> at least tell me the significance of each of these numbers from the stats:
>> 261/10 [691/281] page faults/reclaims, 0 [0] swaps
> 
> It's just repeating what getrusage() told it.  See the docs for your
> operating system.
> 
> It looks like the numbers inside the square brackets are totals since
> process start, while the ones before are the delta since query start.
> 
> regards, tom lane
> 




^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2004-08-26 19:33 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-08-26 12:17 Odd double queries continues Steve Lane <slane@soliantconsulting.com>
2004-08-26 14:51 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-08-26 19:33   ` Steve Lane <slane@soliantconsulting.com>

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