Re: The shared buffers challenge - Mailing list pgsql-performance

Merlin Moncure wrote:
> So, the challenge is this: I'd like to see repeatable test cases that
> demonstrate regular performance gains > 20%.  Double bonus points for
> cases that show gains > 50%.

Do I run around challenging your suggestions and giving you homework?
You have no idea how much eye rolling this whole message provoked from me.

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 Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books