Re: Help me develop new commit_delay advice

> From: pgsql-hackers-owner@postgresql.org
[mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Peter Geoghegan
> Sent: Sunday, July 29, 2012 9:09 PM


> I made what may turn out to be a useful observation during the
> development of the patch, which was that for both the tpc-b.sql and
> insert.sql pgbench-tools scripts, a commit_delay of half of my
> wal_sync_method's reported raw sync speed looked optimal. I use Linux,
> so my wal_sync_method happened to have been fdatasync. I measured this
> using pg_test_fsync.

I have done some basic test for commit_delay parameter
OS version: suse linux 10.3 
postgresql version: 9.3 dev on x86-64, compiled by gcc (GCC) 4.1.2 20070115 
Machine details: 8 core cpu, 24GB RAM. 
Testcase: pgbench tcp_b test. 

Before running the benchmark suite, the buffers are loaded by using
pg_prewarm utility. 

Test Results are attached with this mail.
Run1,Run2,Run3 means the same test has ran 3 times.


> It would be useful, for a start, if I had numbers for a battery-backed
> write cache. I don't have access to one right now though, nor do I
> have access to any more interesting hardware, which is one reason why
> I'm asking for help with this.

> I like to run "sync" prior to running pg_test_fsync, just in case.

> [peter@peterlaptop pg_test_fsync]$ sync

>I then interpret the following output:

> [peter@peterlaptop pg_test_fsync]$ pg_test_fsync
> 2 seconds per test
> O_DIRECT supported on this platform for open_datasync and open_sync.

> Compare file sync methods using one 8kB write:
> (in wal_sync_method preference order, except fdatasync
> is Linux's default)
>         open_datasync                 112.940 ops/sec
>         fdatasync                         114.035 ops/sec
>         fsync                                 21.291 ops/sec
> *** SNIP ***

I shall look into this aspect also(setting commit_delay based on raw sync).
You also suggest if you want to run the test with different configuration.

With Regards,
Amit Kapila.