Message-ID: <4DE03413.5050001@catalyst.net.nz>
Date: Sat, 28 May 2011 11:30:27 +1200
From: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.2.17) Gecko/20110424 Thunderbird/3.1.10
MIME-Version: 1.0
To: Greg Smith <greg@2ndquadrant.com>
CC: Merlin Moncure <mmoncure@gmail.com>,
	postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: The shared buffers challenge
References: <BANLkTimPC-K_o8XNn1hK4ZgBrTGO_Z6RDw@mail.gmail.com>
	<4DDEDDDB.6000607@2ndquadrant.com>
In-Reply-To: <4DDEDDDB.6000607@2ndquadrant.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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