On 27/05/11 11:10, Greg Smith wrote:
OK, so the key thing to do is create a table such that shared_buffers
is smaller than the primary key index on a table, then UPDATE that
table furiously. This will page constantly out of the buffer cache to
the OS one, doing work that could be avoided. Increase shared_buffers
to where it fits instead, and all the index writes are buffered to
write only once per checkpoint. Server settings to exaggerate the
effect:
shared_buffers = 32MB
checkpoint_segments = 256
log_checkpoints = on
autovacuum = off
Test case:
createdb pgbench
pgbench -i -s 20 pgbench
psql -d pgbench -c "select
pg_size_pretty(pg_relation_size('public.pgbench_accounts_pkey'))"
psql -c "select pg_stat_reset_shared('bgwriter')"
pgbench -T 120 -c 4 -n pgbench
psql -x -c "SELECT * FROM pg_stat_bgwriter"
This gives the following size for the primary key and results:
pg_size_pretty
----------------
34 MB
transaction type: TPC-B (sort of)
scaling factor: 20
query mode: simple
number of clients: 4
number of threads: 1
duration: 120 s
number of transactions actually processed: 13236
tps = 109.524954 (including connections establishing)
tps = 109.548498 (excluding connections establishing)
-[ RECORD 1 ]---------+------------------------------
checkpoints_timed | 0
checkpoints_req | 0
buffers_checkpoint | 0
buffers_clean | 16156
maxwritten_clean | 131
buffers_backend | 5701
buffers_backend_fsync | 0
buffers_alloc | 25276
stats_reset | 2011-05-26 18:39:57.292777-04
Now, change so the whole index fits instead:
shared_buffers = 512MB
...which follows the good old "25% of RAM" guidelines given this
system has 2GB of RAM. Restart the server, repeat the test case. New
results:
transaction type: TPC-B (sort of)
scaling factor: 20
query mode: simple
number of clients: 4
number of threads: 1
duration: 120 s
number of transactions actually processed: 103440
tps = 861.834090 (including connections establishing)
tps = 862.041716 (excluding connections establishing)
gsmith@meddle:~/personal/scripts$ psql -x -c "SELECT * FROM
pg_stat_bgwriter"
-[ RECORD 1 ]---------+------------------------------
checkpoints_timed | 0
checkpoints_req | 0
buffers_checkpoint | 0
buffers_clean | 0
maxwritten_clean | 0
buffers_backend | 1160
buffers_backend_fsync | 0
buffers_alloc | 34071
stats_reset | 2011-05-26 18:43:40.887229-04
Rather than writing 16156+5701=21857 buffers out during the test to
support all the index churn, instead only 1160 buffers go out,
consisting mostly of the data blocks for pgbench_accounts that are
being updated irregularly. With less than 1 / 18th as I/O to do, the
system executes nearly 8X as many UPDATE statements during the test run.
As for figuring out how this impacts more complicated cases, I hear
somebody wrote a book or something that went into pages and pages of
detail about all this. You might want to check it out.
Greg, having an example with some discussion like this in the docs would
probably be helpful. If you want to add it that would be great, however
that sounds dangerously like giving you homework :-) I'm happy to put
something together for the docs if you'd prefer that I do my own
assignments.
Cheers
Mark
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
