Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id ABDA1DA629A for ; Wed, 1 Aug 2012 11:18:31 -0300 (ADT) Received: from szxga02-in.huawei.com ([119.145.14.65]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1SwZkr-0000pM-4n for pgsql-hackers@postgresql.org; Wed, 01 Aug 2012 14:18:31 +0000 Received: from 172.24.2.119 (EHLO szxeml210-edg.china.huawei.com) ([172.24.2.119]) by szxrg02-dlp.huawei.com (MOS 4.3.4-GA FastPath queued) with ESMTP id AMU34704; Wed, 01 Aug 2012 22:18:04 +0800 (CST) Received: from SZXEML418-HUB.china.huawei.com (10.82.67.157) by szxeml210-edg.china.huawei.com (172.24.2.183) with Microsoft SMTP Server (TLS) id 14.1.323.3; Wed, 1 Aug 2012 22:14:52 +0800 Received: from blrprnc09ns (10.18.96.98) by szxeml418-hub.china.huawei.com (10.82.67.157) with Microsoft SMTP Server id 14.1.323.3; Wed, 1 Aug 2012 22:14:46 +0800 From: Amit Kapila To: "'Peter Geoghegan'" , "'PG Hackers'" References: In-Reply-To: Subject: Re: Help me develop new commit_delay advice Date: Wed, 1 Aug 2012 19:44:45 +0530 Message-ID: <000701cd6ff0$013a6210$03af2630$@kapila@huawei.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----=_NextPart_000_0008_01CD701E.1AF29E10" X-Mailer: Microsoft Office Outlook 12.0 Thread-Index: Ac1vQ2h7yUp7G+WoQbWQ2AeBeT4UpAAqtj4A Content-Language: en-us X-Originating-IP: [10.18.96.98] X-CFilter-Loop: Reflected X-Pg-Spam-Score: -0.9 (/) X-Archive-Number: 201208/7 X-Sequence-Number: 211829 ------=_NextPart_000_0008_01CD701E.1AF29E10 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit > 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. ------=_NextPart_000_0008_01CD701E.1AF29E10 Content-Type: text/html; name="pgbench_results.htm" Content-Transfer-Encoding: quoted-printable Content-Disposition: attachment; filename="pgbench_results.htm"
Machine details
CPU 8 core RAM 24GB
OS suse linu= x 10.3
Ser= ver Configuration
sync commit on shared buffers 1GB
Pgb= ench tcp_b benchmark suite
threads 8 clients 16
scale 75 fill fact= or 100
pgbench  delay =3D 0 delay =3D 3000 delay =3D 4000 delay =3D 5000
TPS Total ops= TPS Total ops= TPS Total ops= TPS Total ops=
Run1 968 583060 973 584130 935 561516 916 550043
Run2 971 583035 963 578560 971 585143 921 556471
Run3 995 597368 978 590085 998 599371 872 523890
Avg 978 587821 971.33333 584258.3 968 582010 903 543468
= ------=_NextPart_000_0008_01CD701E.1AF29E10--