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]> <![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'> </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
copy link · copy postgr.es
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