pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Amit Kapila <amit.kapila@huawei.com>
To: 'Peter Geoghegan' <peter@2ndquadrant.com>
To: 'PG Hackers' <pgsql-hackers@postgresql.org>
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> (raw)
In-Reply-To: <CAEYLb_WtD+Ew3k6RZuL6ydsowoYbMKJWa6w+cNH-kzngAzN16g@mail.gmail.com>
References: <CAEYLb_WtD+Ew3k6RZuL6ydsowoYbMKJWa6w+cNH-kzngAzN16g@mail.gmail.com>

> 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.

Attachments:

pgbench_results.htm (text/html, 16.4K) download <html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"> <head> <meta http-equiv=Content-Type content="text/html; charset=gb2312"> <meta name=ProgId content=Excel.Sheet> <meta name=Generator content="Microsoft Excel 12"> <link rel=File-List href="pgbench_results_files/filelist.xml"> <style id="pgbench_5654_Styles"> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} .xl155654 {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:windowtext; font-size:10.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Arial, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; mso-background-source:auto; mso-pattern:auto; white-space:nowrap;} .xl635654 {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:windowtext; font-size:10.0pt; font-weight:700; font-style:normal; text-decoration:none; font-family:Arial, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:.5pt solid windowtext; mso-background-source:auto; mso-pattern:auto; white-space:nowrap;} .xl645654 {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:windowtext; font-size:10.0pt; font-weight:700; font-style:normal; text-decoration:none; font-family:Arial, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:center; vertical-align:bottom; border:.5pt solid windowtext; mso-background-source:auto; mso-pattern:auto; white-space:nowrap;} .xl655654 {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:windowtext; font-size:10.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Arial, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:.5pt solid windowtext; mso-background-source:auto; mso-pattern:auto; white-space:nowrap;} .xl665654 {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:windowtext; font-size:10.0pt; font-weight:700; font-style:normal; text-decoration:none; font-family:Arial, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; mso-background-source:auto; mso-pattern:auto; white-space:nowrap;} .xl675654 {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:windowtext; font-size:10.0pt; font-weight:700; font-style:normal; text-decoration:none; font-family:Arial, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:center; vertical-align:middle; border-top:.5pt solid windowtext; border-right:.5pt solid windowtext; border-bottom:none; border-left:.5pt solid windowtext; mso-background-source:auto; mso-pattern:auto; white-space:nowrap;} .xl685654 {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:windowtext; font-size:10.0pt; font-weight:700; font-style:normal; text-decoration:none; font-family:Arial, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:center; vertical-align:middle; border-top:none; border-right:.5pt solid windowtext; border-bottom:.5pt solid windowtext; border-left:.5pt solid windowtext; mso-background-source:auto; mso-pattern:auto; white-space:nowrap;} .xl695654 {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:windowtext; font-size:10.0pt; font-weight:700; font-style:normal; text-decoration:none; font-family:Arial, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:center; vertical-align:bottom; border-top:.5pt solid windowtext; border-right:none; border-bottom:.5pt solid windowtext; border-left:.5pt solid windowtext; mso-background-source:auto; mso-pattern:auto; white-space:nowrap;} .xl705654 {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:windowtext; font-size:10.0pt; font-weight:700; font-style:normal; text-decoration:none; font-family:Arial, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:center; vertical-align:bottom; border-top:.5pt solid windowtext; border-right:.5pt solid windowtext; border-bottom:.5pt solid windowtext; border-left:none; mso-background-source:auto; mso-pattern:auto; white-space:nowrap;} .xl715654 {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:windowtext; font-size:10.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Arial, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:right; vertical-align:bottom; border:.5pt solid windowtext; mso-background-source:auto; mso-pattern:auto; white-space:nowrap;} .xl725654 {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:windowtext; font-size:10.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Arial, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:right; vertical-align:bottom; mso-background-source:auto; mso-pattern:auto; white-space:nowrap;} --> </style> </head> <body> <!--[if !excel]>&nbsp;&nbsp;<![endif]--> <!--The following information was generated by Microsoft Office Excel's Publish as Web Page wizard.--> <!--If the same item is republished from Excel, all information between the DIV tags will be replaced.--> <!-----------------------------> <!--START OF OUTPUT FROM EXCEL PUBLISH AS WEB PAGE WIZARD --> <!-----------------------------> <div id="pgbench_5654" align=center x:publishsource="Excel"> <table border=0 cellpadding=0 cellspacing=0 width=689 style='border-collapse: collapse;table-layout:fixed;width:517pt'> <col width=111 style='mso-width-source:userset;mso-width-alt:4059;width:83pt'> <col width=96 style='mso-width-source:userset;mso-width-alt:3510;width:72pt'> <col width=93 style='mso-width-source:userset;mso-width-alt:3401;width:70pt'> <col width=69 style='mso-width-source:userset;mso-width-alt:2523;width:52pt'> <col width=64 span=5 style='width:48pt'> <tr height=17 style='height:12.75pt'> <td height=17 class=xl665654 width=111 style='height:12.75pt;width:83pt'>Machine details</td> <td class=xl155654 width=96 style='width:72pt'></td> <td class=xl155654 width=93 style='width:70pt'></td> <td class=xl155654 width=69 style='width:52pt'></td> <td class=xl155654 width=64 style='width:48pt'></td> <td class=xl155654 width=64 style='width:48pt'></td> <td class=xl155654 width=64 style='width:48pt'></td> <td class=xl155654 width=64 style='width:48pt'></td> <td class=xl155654 width=64 style='width:48pt'></td> </tr> <tr height=17 style='height:12.75pt'> <td height=17 class=xl635654 style='height:12.75pt'>CPU</td> <td class=xl715654 style='border-left:none'>8 core</td> <td class=xl635654 style='border-left:none'>RAM</td> <td class=xl655654 style='border-left:none'>24GB</td> <td class=xl155654></td> <td class=xl155654></td> <td class=xl155654></td> <td class=xl155654></td> <td class=xl155654></td> </tr> <tr height=17 style='height:12.75pt'> <td height=17 class=xl635654 style='height:12.75pt;border-top:none'>OS</td> <td class=xl655654 style='border-top:none;border-left:none'>suse linux 10.3</td> <td class=xl155654></td> <td class=xl155654></td> <td class=xl155654></td> <td class=xl155654></td> <td class=xl155654></td> <td class=xl155654></td> <td class=xl155654></td> </tr> <tr height=17 style='height:12.75pt'> <td height=17 class=xl155654 style='height:12.75pt'></td> <td class=xl155654></td> <td class=xl155654></td> <td class=xl155654></td> <td class=xl155654></td> <td class=xl155654></td> <td class=xl155654></td> <td class=xl155654></td> <td class=xl155654></td> </tr> <tr height=17 style='height:12.75pt'> <td height=17 class=xl155654 style='height:12.75pt'></td> <td class=xl155654></td> <td class=xl155654></td> <td class=xl155654></td> <td class=xl155654></td> <td class=xl155654></td> <td class=xl155654></td> <td class=xl155654></td> <td class=xl155654></td> </tr> <tr height=17 style='height:12.75pt'> <td height=17 class=xl665654 colspan=2 style='height:12.75pt'>Server Configuration</td> <td class=xl155654></td> <td class=xl155654></td> <td class=xl155654></td> <td class=xl155654></td> <td class=xl155654></td> <td class=xl155654></td> <td class=xl155654></td> </tr> <tr height=17 style='height:12.75pt'> <td height=17 class=xl635654 style='height:12.75pt'>sync commit</td> <td class=xl715654 style='border-left:none'>on</td> <td class=xl635654 style='border-left:none'>shared buffers</td> <td class=xl715654 style='border-left:none'>1GB</td> <td class=xl155654></td> <td class=xl155654></td> <td class=xl155654></td> <td class=xl155654></td> <td class=xl155654></td> </tr> <tr height=17 style='height:12.75pt'> <td height=17 class=xl155654 style='height:12.75pt'></td> <td class=xl155654></td> <td class=xl155654></td> <td class=xl155654></td> <td class=xl155654></td> <td class=xl155654></td> <td class=xl155654></td> <td class=xl155654></td> <td class=xl155654></td> </tr> <tr height=17 style='height:12.75pt'> <td height=17 class=xl665654 style='height:12.75pt'></td> <td class=xl725654></td> <td class=xl665654></td> <td class=xl725654></td> <td class=xl155654></td> <td class=xl155654></td> <td class=xl155654></td> <td class=xl155654></td> <td class=xl155654></td> </tr> <tr height=17 style='height:12.75pt'> <td height=17 class=xl665654 colspan=3 style='height:12.75pt'>Pgbench tcp_b benchmark suite</td> <td class=xl155654></td> <td class=xl155654></td> <td class=xl155654></td> <td class=xl155654></td> <td class=xl155654></td> <td class=xl155654></td> </tr> <tr height=17 style='height:12.75pt'> <td height=17 class=xl635654 style='height:12.75pt'>threads</td> <td class=xl655654 align=right style='border-left:none'>8</td> <td class=xl635654 style='border-left:none'>clients</td> <td class=xl655654 align=right style='border-left:none'>16</td> <td class=xl155654></td> <td class=xl155654></td> <td class=xl155654></td> <td class=xl155654></td> <td class=xl155654></td> </tr> <tr height=17 style='height:12.75pt'> <td height=17 class=xl635654 style='height:12.75pt;border-top:none'>scale</td> <td class=xl655654 align=right style='border-top:none;border-left:none'>75</td> <td class=xl635654 style='border-top:none;border-left:none'>fill factor</td> <td class=xl655654 align=right style='border-top:none;border-left:none'>100</td> <td class=xl155654></td> <td class=xl155654></td> <td class=xl155654></td> <td class=xl155654></td> <td class=xl155654></td> </tr> <tr height=17 style='height:12.75pt'> <td height=17 class=xl665654 style='height:12.75pt'></td> <td class=xl155654></td> <td class=xl665654></td> <td class=xl155654></td> <td class=xl155654></td> <td class=xl155654></td> <td class=xl155654></td> <td class=xl155654></td> <td class=xl155654></td> </tr> <tr height=17 style='height:12.75pt'> <td height=17 class=xl155654 style='height:12.75pt'></td> <td class=xl155654></td> <td class=xl155654></td> <td class=xl155654></td> <td class=xl155654></td> <td class=xl155654></td> <td class=xl155654></td> <td class=xl155654></td> <td class=xl155654></td> </tr> <tr height=17 style='height:12.75pt'> <td rowspan=2 height=34 class=xl675654 style='border-bottom:.5pt solid black; height:25.5pt'>pgbench<span style='mso-spacerun:yes'>&nbsp;</span></td> <td colspan=2 class=xl695654 style='border-right:.5pt solid black;border-left: none'>delay = 0</td> <td colspan=2 class=xl695654 style='border-right:.5pt solid black;border-left: none'>delay = 3000</td> <td colspan=2 class=xl695654 style='border-right:.5pt solid black;border-left: none'>delay = 4000</td> <td colspan=2 class=xl695654 style='border-right:.5pt solid black;border-left: none'>delay = 5000</td> </tr> <tr height=17 style='height:12.75pt'> <td height=17 class=xl645654 style='height:12.75pt;border-top:none; border-left:none'>TPS</td> <td class=xl645654 style='border-top:none;border-left:none'>Total ops</td> <td class=xl645654 style='border-top:none;border-left:none'>TPS</td> <td class=xl645654 style='border-top:none;border-left:none'>Total ops</td> <td class=xl645654 style='border-top:none;border-left:none'>TPS</td> <td class=xl645654 style='border-top:none;border-left:none'>Total ops</td> <td class=xl645654 style='border-top:none;border-left:none'>TPS</td> <td class=xl645654 style='border-top:none;border-left:none'>Total ops</td> </tr> <tr height=17 style='height:12.75pt'> <td height=17 class=xl635654 style='height:12.75pt;border-top:none'>Run1</td> <td class=xl655654 align=right style='border-top:none;border-left:none'>968</td> <td class=xl655654 align=right style='border-top:none;border-left:none'>583060</td> <td class=xl655654 align=right style='border-top:none;border-left:none'>973</td> <td class=xl655654 align=right style='border-top:none;border-left:none'>584130</td> <td class=xl655654 align=right style='border-top:none;border-left:none'>935</td> <td class=xl655654 align=right style='border-top:none;border-left:none'>561516</td> <td class=xl655654 align=right style='border-top:none;border-left:none'>916</td> <td class=xl655654 align=right style='border-top:none;border-left:none'>550043</td> </tr> <tr height=17 style='height:12.75pt'> <td height=17 class=xl635654 style='height:12.75pt;border-top:none'>Run2</td> <td class=xl655654 align=right style='border-top:none;border-left:none'>971</td> <td class=xl655654 align=right style='border-top:none;border-left:none'>583035</td> <td class=xl655654 align=right style='border-top:none;border-left:none'>963</td> <td class=xl655654 align=right style='border-top:none;border-left:none'>578560</td> <td class=xl655654 align=right style='border-top:none;border-left:none'>971</td> <td class=xl655654 align=right style='border-top:none;border-left:none'>585143</td> <td class=xl655654 align=right style='border-top:none;border-left:none'>921</td> <td class=xl655654 align=right style='border-top:none;border-left:none'>556471</td> </tr> <tr height=17 style='height:12.75pt'> <td height=17 class=xl635654 style='height:12.75pt;border-top:none'>Run3</td> <td class=xl655654 align=right style='border-top:none;border-left:none'>995</td> <td class=xl655654 align=right style='border-top:none;border-left:none'>597368</td> <td class=xl655654 align=right style='border-top:none;border-left:none'>978</td> <td class=xl655654 align=right style='border-top:none;border-left:none'>590085</td> <td class=xl655654 align=right style='border-top:none;border-left:none'>998</td> <td class=xl655654 align=right style='border-top:none;border-left:none'>599371</td> <td class=xl655654 align=right style='border-top:none;border-left:none'>872</td> <td class=xl655654 align=right style='border-top:none;border-left:none'>523890</td> </tr> <tr height=17 style='height:12.75pt'> <td height=17 class=xl635654 style='height:12.75pt;border-top:none'>Avg</td> <td class=xl655654 align=right style='border-top:none;border-left:none'>978</td> <td class=xl655654 align=right style='border-top:none;border-left:none'>587821</td> <td class=xl655654 align=right style='border-top:none;border-left:none'>971.33333</td> <td class=xl655654 align=right style='border-top:none;border-left:none'>584258.3</td> <td class=xl655654 align=right style='border-top:none;border-left:none'>968</td> <td class=xl655654 align=right style='border-top:none;border-left:none'>582010</td> <td class=xl655654 align=right style='border-top:none;border-left:none'>903</td> <td class=xl655654 align=right style='border-top:none;border-left:none'>543468</td> </tr> <![if supportMisalignedColumns]> <tr height=0 style='display:none'> <td width=111 style='width:83pt'></td> <td width=96 style='width:72pt'></td> <td width=93 style='width:70pt'></td> <td width=69 style='width:52pt'></td> <td width=64 style='width:48pt'></td> <td width=64 style='width:48pt'></td> <td width=64 style='width:48pt'></td> <td width=64 style='width:48pt'></td> <td width=64 style='width:48pt'></td> </tr> <![endif]> </table> </div> <!-----------------------------> <!--END OF OUTPUT FROM EXCEL PUBLISH AS WEB PAGE WIZARD--> <!-----------------------------> </body> </html>=

view thread (9+ messages)  latest in thread

Message-ID: <000701cd6ff0$013a6210$03af2630$@kapila@huawei.com>
Permalink:  ../000701cd6ff0$013a6210$03af2630$@kapila@huawei.com/
Also on:    postgresql.org/message-id/000701cd6ff0$013a6210$03af2630$@kapila@huawei.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: amit.kapila@huawei.com, peter@2ndquadrant.com, pgsql-hackers@postgresql.org
  Subject: Re: Help me develop new commit_delay advice
  In-Reply-To: <000701cd6ff0$013a6210$03af2630$@kapila@huawei.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox