Skip site navigation (1) Skip section navigation (2)

Re: I/O on select count(*)

From: Robert Lor <Robert(dot)Lor(at)Sun(dot)COM>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Jan de Visser <jdevisser(at)digitalfairway(dot)com>, Greg Smith <gsmith(at)gregsmith(dot)com>, Alvaro Herrera <alvherre(at)commandprompt(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: I/O on select count(*)
Date: 2008-05-15 21:23:10
Message-ID: 482CA9BE.8060204@sun.com (view raw or flat)
Thread:
Lists: pgsql-performance
Tom Lane wrote:
> Hmm, the problem would be trying to figure out what percentage of writes
> could be blamed solely on hint-bit updates and not any other change to
> the page.  I don't think that the bufmgr currently keeps enough state to
> know that, but you could probably modify it easily enough, since callers
> distinguish MarkBufferDirty from SetBufferCommitInfoNeedsSave.  Define
> another flag bit that's set only by the first, and test it during
> write-out.
>   
Ok, I made a few changes to bufmgr per my understanding of your 
description above and with my limited understanding of the code. Patch 
is attached.

Assuming the patch is correct, the effect of writes due to hint bits is 
quite significant. I collected the data below by runing pgbench in one 
terminal and psql on another to run the query.

Is the data plausible?

-Robert

--------------


Backend PID    : 16189
SQL Statement  : select count(*) from accounts;
Execution time : 17.33 sec

============ Buffer Read Counts ============
Tablespace   Database      Table      Count
      1663      16384       2600          1
      1663      16384       2601          1
      1663      16384       2615          1
      1663      16384       1255          2
      1663      16384       2602          2
      1663      16384       2603          2
      1663      16384       2616          2
      1663      16384       2650          2
      1663      16384       2678          2
      1663      16384       1247          3
      1663      16384       1249          3
      1663      16384       2610          3
      1663      16384       2655          3
      1663      16384       2679          3
      1663      16384       2684          3
      1663      16384       2687          3
      1663      16384       2690          3
      1663      16384       2691          3
      1663      16384       2703          4
      1663      16384       1259          5
      1663      16384       2653          5
      1663      16384       2662          5
      1663      16384       2663          5
      1663      16384       2659          7
      1663      16384      16397       8390

======== Dirty Buffer Write Counts =========
Tablespace   Database      Table      Count
      1663      16384      16402          2
      1663      16384      16394         11
      1663      16384      16397       4771

========== Hint Bits Write Counts ==========
Tablespace   Database      Table      Count
      1663      16384      16397       4508

Total buffer cache hits      :       732
Total buffer cache misses    :      7731
Average read time from cache :      9136 (ns)
Average read time from disk  :    384201 (ns)
Average write time to disk   :    210709 (ns)


Backend PID    : 16189
SQL Statement  : select count(*) from accounts;
Execution time : 12.72 sec

============ Buffer Read Counts ============
Tablespace   Database      Table      Count
      1663      16384      16397       8392

======== Dirty Buffer Write Counts =========
Tablespace   Database      Table      Count
      1663      16384      16394          6
      1663      16384      16402          7
      1663      16384      16397       2870

========== Hint Bits Write Counts ==========
Tablespace   Database      Table      Count
      1663      16384      16402          2
      1663      16384      16397       2010

Total buffer cache hits      :       606
Total buffer cache misses    :      7786
Average read time from cache :      6949 (ns)
Average read time from disk  :    706288 (ns)
Average write time to disk   :     90426 (ns)


Attachment: hint-bits-test.patch
Description: text/x-patch (4.6 KB)

In response to

pgsql-performance by date

Next:From: James MansionDate: 2008-05-15 22:11:52
Subject: Re: I/O on select count(*)
Previous:From: Subbiah Stalin-XCGF84Date: 2008-05-15 19:27:48
Subject: Re: Update performance degrades over time

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group