pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedHelp me develop new commit_delay advice
9+ messages / 4 participants
[nested] [flat]
* Help me develop new commit_delay advice
@ 2012-07-29 15:39 Peter Geoghegan <peter@2ndquadrant.com>
2012-08-01 14:14 ` Re: Help me develop new commit_delay advice Amit Kapila <amit.kapila@huawei.com>
2012-08-01 15:19 ` Re: Help me develop new commit_delay advice Peter Geoghegan <peter@2ndquadrant.com>
2012-08-02 18:02 ` [repost] Help me develop new commit_delay advice Peter Geoghegan <peter@2ndquadrant.com>
2012-08-02 18:04 ` Re: Help me develop new commit_delay advice Peter Geoghegan <peter@2ndquadrant.com>
0 siblings, 4 replies; 9+ messages in thread
From: Peter Geoghegan @ 2012-07-29 15:39 UTC (permalink / raw)
To: PG Hackers <pgsql-hackers@postgresql.org>
Many of you will be aware that the behaviour of commit_delay was
recently changed. Now, the delay only occurs within the group commit
leader backend, and not within each and every backend committing a
transaction:
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=f11e8be3e812cdbbc139c1b4e49141378b118d...
For those of you that didn't follow this development, I should point
out that I wrote a blogpost that described the idea, which will serve
as a useful summary:
http://pgeoghegan.blogspot.com/2012/06/towards-14000-write-transactions-on-my.html
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.
The devel docs still say of commit_delay and commit siblings: "Good
values for these parameters are not yet clear; experimentation is
encouraged". This has been the case since Postgres 7.1 (i.e. it has
never been clear what good values were - the folk wisdom was actually
that commit_delay should always be set to 0). I hope to be able to
formulate some folk wisdom about setting commit_delay from 9.3 on,
that may go on to be accepted as an official recommendation within the
docs.
I am rather curious as to what experimentation shows optimal values
for commit_delay to be for a representative cross-section of hardware.
In particular, I'd like to see if setting commit_delay to half of raw
sync time appears to be optimal for both insert.sql and tpc-b.sql
workloads across different types of hardware with different sync
times. Now, it may be sort of questionable to take those workloads as
general proxies for performance, not least since they will literally
give Postgres as many *completely uniform* transactions as it can
handle. However, it is hard to think of another, better general proxy
for performance that is likely to be accepted as such, and will allows
us to reason about setting commit_delay.
While I am not completely confident that we can formulate a widely
useful, simple piece of advice, I am encouraged by the fact that a
commit_delay of 4,000 worked very well for both tpc-b.sql and
insert.sql workloads on my laptop, beating out settings of 3,000 and
5,000 on each benchmark. I am also encouraged by the fact that in some
cases, including both the insert.sql and tpc-b.sql cases that I've
already described elsewhere, there is actually no downside to setting
commit_delay - transaction throughput naturally improves, but
transaction latency is actually improved a bit too (or at least the
average and worst-cases). This is presumably due to the amelioration
of resource contention (from greater commit batching) more than
compensating for the obvious downside of adding a delay.
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 ***
So if I can perform 114.035 8KiB sync operations per second, that's an
average of about 1 per 8.77 milliseconds, or 8770 microseconds to put
it in the units that commit_delay speaks. It is my hope that we will
find that when this number is halved, we will arrive at a figure that
is worth recommending as a general useful setting for commit_delay for
the system. I guess I could gain some additional insight by simply
changing my wal_sync_method, but I'd find it more interesting to look
at organic setups with faster (not slower) sync times than my system's
fdatasync. For those who are able to help me here, I'd like to see
pgbench-tools workloads for both tpc-b.sql and insert.sql with
incrementing values of commit_delay (increments of, say, 1000
microseconds, perhaps with less granularity where it isn't needed),
from 0 to $(1.5 times raw sync speed) microseconds.
Thanks
--
Peter Geoghegan http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training and Services
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Help me develop new commit_delay advice
2012-07-29 15:39 Help me develop new commit_delay advice Peter Geoghegan <peter@2ndquadrant.com>
@ 2012-08-01 14:14 ` Amit Kapila <amit.kapila@huawei.com>
2012-08-02 17:48 ` Re: Help me develop new commit_delay advice Josh Berkus <josh@agliodbs.com>
3 siblings, 1 reply; 9+ messages in thread
From: Amit Kapila @ 2012-08-01 14:14 UTC (permalink / raw)
To: 'Peter Geoghegan' <peter@2ndquadrant.com>; 'PG Hackers' <pgsql-hackers@postgresql.org>
> 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>=
* Re: Help me develop new commit_delay advice
2012-07-29 15:39 Help me develop new commit_delay advice Peter Geoghegan <peter@2ndquadrant.com>
2012-08-01 14:14 ` Re: Help me develop new commit_delay advice Amit Kapila <amit.kapila@huawei.com>
@ 2012-08-02 17:48 ` Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 9+ messages in thread
From: Josh Berkus @ 2012-08-02 17:48 UTC (permalink / raw)
To: pgsql-hackers@postgresql.org
Peter,
For some reason I didn't receive the beginning of this thread. Can you
resend it to me, or (better) post it to the pgsql-performance mailing list?
I have a linux system where I can test both on regular disk and on SSD.
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Help me develop new commit_delay advice
2012-07-29 15:39 Help me develop new commit_delay advice Peter Geoghegan <peter@2ndquadrant.com>
@ 2012-08-01 15:19 ` Peter Geoghegan <peter@2ndquadrant.com>
2012-08-02 11:15 ` Re: Help me develop new commit_delay advice Amit Kapila <amit.kapila@huawei.com>
3 siblings, 1 reply; 9+ messages in thread
From: Peter Geoghegan @ 2012-08-01 15:19 UTC (permalink / raw)
To: Amit Kapila <amit.kapila@huawei.com>; +Cc: PG Hackers <pgsql-hackers@postgresql.org>
On 1 August 2012 15:14, Amit Kapila <amit.kapila@huawei.com> wrote:
> 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.
Well, I was specifically interested in testing if half of raw sync
time was a widely useful setting, across a variety of different,
though representative I/O subsystems. Unfortunately, without some
context about raw sync speed to go along with your numbers, I cannot
advance or disprove that idea.
It would also have been nice to see a baseline number of 0 too, to get
an idea of how effective commit_delay may now be. However, that's
secondary.
--
Peter Geoghegan http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training and Services
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Help me develop new commit_delay advice
2012-07-29 15:39 Help me develop new commit_delay advice Peter Geoghegan <peter@2ndquadrant.com>
2012-08-01 15:19 ` Re: Help me develop new commit_delay advice Peter Geoghegan <peter@2ndquadrant.com>
@ 2012-08-02 11:15 ` Amit Kapila <amit.kapila@huawei.com>
0 siblings, 0 replies; 9+ messages in thread
From: Amit Kapila @ 2012-08-02 11:15 UTC (permalink / raw)
To: 'Peter Geoghegan' <peter@2ndquadrant.com>; +Cc: 'PG Hackers' <pgsql-hackers@postgresql.org>
> From: Peter Geoghegan [mailto:peter@2ndquadrant.com]
> Sent: Wednesday, August 01, 2012 8:49 PM
On 1 August 2012 15:14, Amit Kapila <amit.kapila@huawei.com> wrote:
>> 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.
> Well, I was specifically interested in testing if half of raw sync
> time was a widely useful setting, across a variety of different,
> though representative I/O subsystems. Unfortunately, without some
> context about raw sync speed to go along with your numbers, I cannot
> advance or disprove that idea.
Raw sync speed data
--------------------------
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 n/a
fdatasync 165.506 ops/sec
fsync 166.647 ops/sec
fsync_writethrough n/a
open_sync 164.654 ops/sec
165.506 * 8KB operations can perform in one sec.
so 1 * 8KB operation takes 6.042 msec.
> It would also have been nice to see a baseline number of 0 too, to get
> an idea of how effective commit_delay may now be. However, that's
> secondary.
In the data sent yesterday commit_delay=0 was there.
With Regards,
Amit Kapila.
^ permalink raw reply [nested|flat] 9+ messages in thread
* [repost] Help me develop new commit_delay advice
2012-07-29 15:39 Help me develop new commit_delay advice Peter Geoghegan <peter@2ndquadrant.com>
@ 2012-08-02 18:02 ` Peter Geoghegan <peter@2ndquadrant.com>
2012-09-06 03:20 ` Re: [repost] Help me develop new commit_delay advice Greg Smith <greg@2ndQuadrant.com>
3 siblings, 1 reply; 9+ messages in thread
From: Peter Geoghegan @ 2012-08-02 18:02 UTC (permalink / raw)
To: pgsql-performance; +Cc: Josh Berkus <josh@agliodbs.com>
This has been reposted to this list from the pgsql-hackers list, at
the request of Josh Berkus. Hopefully there will be more interest
here.
---------- Forwarded message ----------
From: Peter Geoghegan <peter@2ndquadrant.com>
Date: 29 July 2012 16:39
Subject: Help me develop new commit_delay advice
To: PG Hackers <pgsql-hackers@postgresql.org>
Many of you will be aware that the behaviour of commit_delay was
recently changed. Now, the delay only occurs within the group commit
leader backend, and not within each and every backend committing a
transaction:
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=f11e8be3e812cdbbc139c1b4e49141378b118d...
For those of you that didn't follow this development, I should point
out that I wrote a blogpost that described the idea, which will serve
as a useful summary:
http://pgeoghegan.blogspot.com/2012/06/towards-14000-write-transactions-on-my.html
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.
The devel docs still say of commit_delay and commit siblings: "Good
values for these parameters are not yet clear; experimentation is
encouraged". This has been the case since Postgres 7.1 (i.e. it has
never been clear what good values were - the folk wisdom was actually
that commit_delay should always be set to 0). I hope to be able to
formulate some folk wisdom about setting commit_delay from 9.3 on,
that may go on to be accepted as an official recommendation within the
docs.
I am rather curious as to what experimentation shows optimal values
for commit_delay to be for a representative cross-section of hardware.
In particular, I'd like to see if setting commit_delay to half of raw
sync time appears to be optimal for both insert.sql and tpc-b.sql
workloads across different types of hardware with different sync
times. Now, it may be sort of questionable to take those workloads as
general proxies for performance, not least since they will literally
give Postgres as many *completely uniform* transactions as it can
handle. However, it is hard to think of another, better general proxy
for performance that is likely to be accepted as such, and will allows
us to reason about setting commit_delay.
While I am not completely confident that we can formulate a widely
useful, simple piece of advice, I am encouraged by the fact that a
commit_delay of 4,000 worked very well for both tpc-b.sql and
insert.sql workloads on my laptop, beating out settings of 3,000 and
5,000 on each benchmark. I am also encouraged by the fact that in some
cases, including both the insert.sql and tpc-b.sql cases that I've
already described elsewhere, there is actually no downside to setting
commit_delay - transaction throughput naturally improves, but
transaction latency is actually improved a bit too (or at least the
average and worst-cases). This is presumably due to the amelioration
of resource contention (from greater commit batching) more than
compensating for the obvious downside of adding a delay.
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 ***
So if I can perform 114.035 8KiB sync operations per second, that's an
average of about 1 per 8.77 milliseconds, or 8770 microseconds to put
it in the units that commit_delay speaks. It is my hope that we will
find that when this number is halved, we will arrive at a figure that
is worth recommending as a general useful setting for commit_delay for
the system. I guess I could gain some additional insight by simply
changing my wal_sync_method, but I'd find it more interesting to look
at organic setups with faster (not slower) sync times than my system's
fdatasync. For those who are able to help me here, I'd like to see
pgbench-tools workloads for both tpc-b.sql and insert.sql with
incrementing values of commit_delay (increments of, say, 1000
microseconds, perhaps with less granularity where it isn't needed),
from 0 to $(1.5 times raw sync speed) microseconds.
Thanks
--
Peter Geoghegan http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training and Services
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [repost] Help me develop new commit_delay advice
2012-07-29 15:39 Help me develop new commit_delay advice Peter Geoghegan <peter@2ndquadrant.com>
2012-08-02 18:02 ` [repost] Help me develop new commit_delay advice Peter Geoghegan <peter@2ndquadrant.com>
@ 2012-09-06 03:20 ` Greg Smith <greg@2ndQuadrant.com>
2012-10-08 12:38 ` Re: [repost] Help me develop new commit_delay advice Peter Geoghegan <peter@2ndquadrant.com>
0 siblings, 1 reply; 9+ messages in thread
From: Greg Smith @ 2012-09-06 03:20 UTC (permalink / raw)
To: pgsql-performance
On 08/02/2012 02:02 PM, Peter Geoghegan wrote:
> 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 dug up what I wrote when trying to provide better advice for this
circa V8.3. That never really gelled into something worth publishing at
the time. But I see some similar patterns what what you're reporting,
so maybe this will be useful input to you now. That included a 7200RPM
drive and a system with a BBWC.
In the BBWC case, the only useful tuning I found was to add a very small
amount of commit_delay, possibly increasing the siblings too. I was
using http://benjiyork.com/blog/2007/04/sleep-considered-harmful.html to
figure out the minimum sleep resolution on the server (3us at the time)
and setting commit_delay to that; then increasing commit_siblings to 10
or 20. Jignesh Shah came back with something in the same sort of range
then at
http://jkshah.blogspot.com/2007/07/specjappserver2004-and-postgresql_09.html
, setting commit_delay=10.
On the 7200RPM drive ~= 115 TPS, 1/2 of the drive's rotation was
consistently what worked best for me across multiple tests too. I also
found lowering commit_siblings all the way to 1 could significantly
improve the 2 client case when you did that. Here's my notes from then:
commit_delay=4500, commit_siblings=1: By waiting 1/2 a revolution if
there's another active transaction, I get a small improvement at the
low-end (around an extra 20 TPS between 2 and 6 clients), while not
doing much damage to the higher client loads. This might
be a useful tuning if your expected number of active clients are low,
you don't have a good caching controller, but you'd like a little more
oomph out of things. The results for 7000 usec were almost as good.
But in general, if you're stuck choosing between two commit_delay values
you should use the smaller one as it will be less likely to have a bad
impact on low client loads.
I also found considering a high delay only when a lot of clients were
usually involved worked a bit better than a 1/2 rotation:
commit_delay=10000, commit_siblings=20: At higher client loads, there's
almost invariably another commit coming right behind yours if you wait a
bit. Just plan to wait a bit more than an entire rotation between
commits. This buys me about an extra 30 TPS on the high client loads,
which is a small fraction of an improvement (<5%) but might be worthwhile.
The fact that it seemed the optimal delay needed to vary a bit based on
the number of the siblings was one of the challenges I kept butting into
then. Making the GUC settings even more complicated for this doesn't
seem a productive step forward for the average user.
--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [repost] Help me develop new commit_delay advice
2012-07-29 15:39 Help me develop new commit_delay advice Peter Geoghegan <peter@2ndquadrant.com>
2012-08-02 18:02 ` [repost] Help me develop new commit_delay advice Peter Geoghegan <peter@2ndquadrant.com>
2012-09-06 03:20 ` Re: [repost] Help me develop new commit_delay advice Greg Smith <greg@2ndQuadrant.com>
@ 2012-10-08 12:38 ` Peter Geoghegan <peter@2ndquadrant.com>
0 siblings, 0 replies; 9+ messages in thread
From: Peter Geoghegan @ 2012-10-08 12:38 UTC (permalink / raw)
To: Greg Smith <greg@2ndquadrant.com>; Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-performance
On 6 September 2012 04:20, Greg Smith <greg@2ndquadrant.com> wrote:
> On 08/02/2012 02:02 PM, Peter Geoghegan wrote:
> I dug up what I wrote when trying to provide better advice for this circa
> V8.3. That never really gelled into something worth publishing at the time.
> But I see some similar patterns what what you're reporting, so maybe this
> will be useful input to you now. That included a 7200RPM drive and a system
> with a BBWC.
So, did either Josh or Greg ever get as far as producing numbers for
drives with faster fsyncs than the ~8,000 us fsync speed of my
laptop's disk?
I'd really like to be able to make a firm recommendation as to how
commit_delay should be set, and have been having a hard time beating
the half raw-sync time recommendation, even with a relatively narrow
benchmark (that is, the alernative pgbench-tools scripts). My
observation is that it is generally better to ameliorate the risk of
increased latency through a higher commit_siblings setting rather than
through a lower commit_delay (though it would be easy to overdo it -
commit_delay can now be thought of as a way to bring the benefits of
group commit to workloads that could in principle benefit, but would
otherwise not benefit much from it, such as workloads with lots of
small writes but not too many clients).
One idea I had, which is really more -hackers material, was to test if
backends with a transaction are inCommit (that's a PGXACT field),
rather than just having a transaction, within MinimumActiveBackends().
The idea is that commit_siblings would represent the number of
backends imminently committing needed to delay, rather than the number
of backends in a transaction. It is far from clear that that's a good
idea, but that's perhaps just because the pgbench infrastructure is a
poor proxy for real workloads, with variable sized transactions.
Pretty much all pgbench transactions commit imminently anyway.
Another idea which I have lost faith in - because it has been hard to
prove that client count is really relevant - was the notion that
commit_delay should be a dynamically adapting function of the client
(with transactions) count. Setting commit_delay to 1/2 raw sync time
appears optimal at any client count that is > 1. The effect at 2
clients is quite noticeable.
I have a rather busy schedule right now, and cannot spend too many
more cycles on this. I'd like to reach a consensus on this soon. Just
giving the 1/2 raw sync time the official blessing of being included
in the docs should be the least we do, though. It is okay if the
wording is a bit equivocal - that has to be better than the current
advice, which is (to paraphrase) "we don't really have a clue; you
tell us".
--
Peter Geoghegan http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training and Services
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Help me develop new commit_delay advice
2012-07-29 15:39 Help me develop new commit_delay advice Peter Geoghegan <peter@2ndquadrant.com>
@ 2012-08-02 18:04 ` Peter Geoghegan <peter@2ndquadrant.com>
3 siblings, 0 replies; 9+ messages in thread
From: Peter Geoghegan @ 2012-08-02 18:04 UTC (permalink / raw)
To: PG Hackers <pgsql-hackers@postgresql.org>
On 29 July 2012 16:39, Peter Geoghegan <peter@2ndquadrant.com> wrote:
> Many of you will be aware that the behaviour of commit_delay was
> recently changed. Now, the delay only occurs within the group commit
> leader backend, and not within each and every backend committing a
> transaction:
I've moved this to the pgsql-performance list. Please continue the
discussion there.
--
Peter Geoghegan http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training and Services
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2012-10-08 12:38 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-07-29 15:39 Help me develop new commit_delay advice Peter Geoghegan <peter@2ndquadrant.com>
2012-08-01 14:14 ` Amit Kapila <amit.kapila@huawei.com>
2012-08-02 17:48 ` Josh Berkus <josh@agliodbs.com>
2012-08-01 15:19 ` Peter Geoghegan <peter@2ndquadrant.com>
2012-08-02 11:15 ` Amit Kapila <amit.kapila@huawei.com>
2012-08-02 18:02 ` Peter Geoghegan <peter@2ndquadrant.com>
2012-09-06 03:20 ` Greg Smith <greg@2ndQuadrant.com>
2012-10-08 12:38 ` Peter Geoghegan <peter@2ndquadrant.com>
2012-08-02 18:04 ` Peter Geoghegan <peter@2ndquadrant.com>
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