Re: Page Checksums + Double Writes

On Wed, Jan 4, 2012 at 4:02 PM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
>> 2. The CLOG code isn't designed to manage a large number of
>> buffers, so adding more might cause a performance regression on
>> small systems.
>>
>> On Nate Boley's 32-core system, running pgbench at scale factor
>> 100, the optimal number of buffers seems to be around 32.  I'd
>> like to get some test results from smaller systems - any chance
>> you (or anyone) have, say, an 8-core box you could test on?
>
> Hmm.  I can think of a lot of 4-core servers I could test on.  (We
> have a few poised to go into production where it would be relatively
> easy to do benchmarking without distorting factors right now.)
> After that we jump to 16 cores, unless I'm forgetting something.
> These are currently all in production, but some of them are
> redundant machines which could be pulled for a few hours here and
> there for benchmarks.  If either of those seem worthwhile, please
> spec the useful tests so I can capture the right information.

Yes, both of those seem useful. To compile, I do this:

./configure --prefix=$HOME/install/$BRANCHNAME --enable-depend
--enable-debug ${EXTRA_OPTIONS}
make
make -C contrib/pgbench
make check
make install
make -C contrib/pgbench install

In this case, the relevant builds would probably be (1) master, (2)
master with NUM_CLOG_BUFFERS = 16, (3) master with NUM_CLOG_BUFFERS =
32, and (4) master with NUM_CLOG_BUFFERS = 48. (You could also try
intermediate numbers if it seems warranted.)

Basic test setup:

rm -rf $PGDATA
~/install/master/bin/initdb
cat >> $PGDATA/postgresql.conf <<EOM;
shared_buffers = 8GB
maintenance_work_mem = 1GB
synchronous_commit = off
checkpoint_segments = 300
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9
wal_writer_delay = 20ms
EOM

I'm attaching a driver script you can modify to taste.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company