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

Re: Displaying accumulated autovacuum cost

From: Greg Smith <greg(at)2ndQuadrant(dot)com>
To: Robert Haas <robertmhaas(at)gmail(dot)com>
Cc: Fujii Masao <masao(dot)fujii(at)gmail(dot)com>, Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Displaying accumulated autovacuum cost
Date: 2012-02-22 04:55:06
Message-ID: 4F44752A.3090305@2ndQuadrant.com (view raw or flat)
Thread:
Lists: pgsql-hackers
I just took this for spin.  Everything I tried worked, docs built and 
read fine.  The description of how "dirty" differs from "written" is a 
bit cryptic, but I don't see an easy way to do better without a whole 
new section on that topic.  Once the extension upgrade questions are 
sorted out, I'd say this is ready to commit.  Example I have at the 
bottom here shows a case where this is a big improvement over the 
existing tracking.  I think this is a must-have improvement if we're 
going to advocate using pg_stat_statements for more things.

This works as expected in all of the EXPLAIN forms, I tried all of the 
supported formats.  Sample of the text one:

$ psql -d pgbench -c "EXPLAIN (ANALYZE,BUFFERS,FORMAT text) UPDATE 
pgbench_accounts SET aid=aid+0 WHERE aid<1000"
QUERY PLAN
----------
  Update on pgbench_accounts  (cost=0.00..86.09 rows=860 width=103) 
(actual time=8.587..8.587 rows=0 loops=1)
    Buffers: shared hit=8315 read=70 dirtied=16
    ->  Index Scan using pgbench_accounts_pkey on pgbench_accounts 
(cost=0.00..86.09 rows=860 width=103) (actual time=0.017..2.086 rows=999
  loops=1)
          Index Cond: (aid < 1000)
          Buffers: shared hit=1828 read=28
  Total runtime: 8.654 ms

Also ran just the UPDATE statement alone, then retrieved the counts from 
pg_stat_statements:

$ psql -x -c "select * from pg_stat_statements"
-[ RECORD 1 
]-------+-------------------------------------------------------------------------------------------
userid              | 10
dbid                | 16385
query               | UPDATE pgbench_accounts SET aid=aid+0 WHERE aid<1000
calls               | 1
total_time          | 0.007475
rows                | 999
shared_blks_hit     | 8370
shared_blks_read    | 15
shared_blks_dirtied | 15
shared_blks_written | 0
...

Note that there are no blocks shown as written there.  That is also 
demonstrated by the results after some pgbench "-M prepared" stress 
testing against a small database.  The pgbench tables are structured 
such that the number of branches < tellers << accounts.  On a small 
scale database (I used 10 here), there might only be a single page of 
branch data.  That shows up clearly in the different amount of dirtied 
blocks in each update:

$ psql -x -c "select 
query,shared_blks_hit,shared_blks_read,shared_blks_dirtied,shared_blks_written 
from pg_stat_statements order by calls desc limit 7"

...
query               | UPDATE pgbench_branches SET bbalance = bbalance + 
$1 WHERE bid = $2;
shared_blks_hit     | 32929
shared_blks_read    | 0
shared_blks_dirtied | 1
shared_blks_written | 0

query               | UPDATE pgbench_tellers SET tbalance = tbalance + 
$1 WHERE tid = $2;
shared_blks_hit     | 19074
shared_blks_read    | 0
shared_blks_dirtied | 7
shared_blks_written | 0

query               | UPDATE pgbench_accounts SET abalance = abalance + 
$1 WHERE aid = $2;
shared_blks_hit     | 35563
shared_blks_read    | 9982
shared_blks_dirtied | 4945
shared_blks_written | 2812

Note how in the branches and tellers case, the existing "written" 
counter shows 0.  Those hot pages stay in cache the whole time with a 
high usage count, backends never get to write them out; only the 
checkpointer does.  Only this new "dirtied" one reflects a useful write 
count for frequently used pages like that, and it does show that more 
pages are being touched by pgbench_tellers than pgbench_branches.

I'd never ran into this before because I normally test against larger 
databases.  But once I tried to find an example of this form, it was 
easy to do so.  Systems where much of the database fits into 
shared_buffers in particular are likely to see a deceptively small write 
count.

-- 
Greg Smith   2ndQuadrant US    greg(at)2ndQuadrant(dot)com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com

In response to

Responses

pgsql-hackers by date

Next:From: Noah MischDate: 2012-02-22 05:27:47
Subject: Re: Measuring relation free space
Previous:From: Greg SmithDate: 2012-02-22 04:27:58
Subject: Re: Displaying accumulated autovacuum cost

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