Perf Benchmarking and regression.

Hi,

Thanks for benchmarking!

On 2016-05-06 19:43:52 +0530, Mithun Cy wrote:

1. # first bad commit: [ac1d7945f866b1928c2554c0f80fd52d7f977772] Make idle
backends exit if the postmaster dies.
this made performance to drop from

15947.21546 (15K +) to 13409.758510 (arround 13K+).

Let's debug this one first, it's a lot more local. I'm rather surprised
that you're seing a big effect with that "few" TPS/socket operations;
and even more that our efforts to address that problem haven't been
fruitful (given we've verified the fix on a number of machines).

Can you verify that removing
AddWaitEventToSet(FeBeWaitSet, WL_POSTMASTER_DEATH, -1, NULL, NULL);
in src/backend/libpq/pqcomm.c : pq_init() restores performance?

I think it'd be best to test the back/forth on master with
bgwriter_flush_after = 0
checkpointer_flush_after = 0
backend_flush_after = 0
to isolate the issue.

Also, do you see read-only workloads to be affected too?

2. # first bad commit: [428b1d6b29ca599c5700d4bc4f4ce4c5880369bf] Allow to
trigger kernel writeback after a configurable number of writes.

FWIW, it'd be very interesting to test again with a bigger
backend_flush_after setting.

Greetings,

Andres Freund

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Fri, May 6, 2016 at 8:35 PM, Andres Freund <andres@anarazel.de> wrote:

Also, do you see read-only workloads to be affected too?

Thanks, I have not tested with above specific commitid which reported
performance issue but
At HEAD commit 72a98a639574d2e25ed94652848555900c81a799
Author: Andres Freund <andres@anarazel.de>
Date: Tue Apr 26 20:32:51 2016 -0700

READ-Only (prepared) tests (both when data fits to shared buffers or it
exceeds shared buffer=8GB) performance of master has improved over 9.5

*Sessions* *PostgreSQL-9.5 scale 300* *PostgreSQL-9.6 scale 300* *%diff*
*1* 5287.561594 5213.723197 -1.396454598
*8* 84265.389083 84871.305689 0.719057507
*16* 148330.4155 158661.128315 6.9646624936
*24* 207062.803697 219958.12974 6.2277366155
*32* 265145.089888 290190.501443 9.4459269699
*40* 311688.752973 340000.551772 9.0833559212
*48* 327169.9673 372408.073033 13.8270960829
*56* 274426.530496 390629.24948 42.3438356248
*64* 261777.692042 384613.9666 46.9238893505
*72* 210747.55937 376390.162022 78.5976374517
*80* 220192.818648 398128.779329 80.8091570713
*88* 185176.91888 423906.711882 128.9198429512
*96* 161579.719039 421541.656474 160.8877271115
*104* 146935.568434 450672.740567 206.7145316618
*112* 136605.466232 432047.309248 216.2738074582
*120* 127687.175016 455458.086889 256.6983816753
*128* 120413.936453 428127.879242 255.5467845776

*Sessions* *PostgreSQL-9.5 scale 1000* *PostgreSQL-9.6 scale 1000* %diff
*1* 5103.812202 5155.434808 1.01145191
*8* 47741.9041 53117.805096 11.2603405694
*16* 89722.57031 86965.10079 -3.0733287182
*24* 130914.537373 153849.634245 17.5191367836
*32* 197125.725706 212454.474264 7.7761279017
*40* 248489.551052 270304.093767 8.7788571482
*48* 291884.652232 317257.836746 8.6928806705
*56* 304526.216047 359676.785476 18.1102862489
*64* 301440.463174 388324.710185 28.8230206709
*72* 194239.941979 393676.628802 102.6754254511
*80* 144879.527847 383365.678053 164.6099719885
*88* 122894.325326 372905.436117 203.4358463076
*96* 109836.31148 362208.867756 229.7715144249
*104* 103791.981583 352330.402278 239.4582094921
*112* 105189.206682 345722.499429 228.6672752217
*120* 108095.811432 342597.969088 216.939171416
*128* 113242.59492 333821.98763 194.7848270925

Even for READ-WRITE when data fits into shared buffer (scale_factor=300 and
shared_buffers=8GB) performance has improved.
Only case is when data exceeds shared_buffer(scale_factor=1000 and
shared_buffers=8GB) I see some regression.

I will try to run the tests as you have suggested and will report the same.

Thanks and Regards
Mithun C Y
EnterpriseDB: http://www.enterprisedb.com

Hi,

On 2016-05-06 21:21:11 +0530, Mithun Cy wrote:

I will try to run the tests as you have suggested and will report the same.

Any news on that front?

Regards,

Andres

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Hi Andres,

I am extremely sorry for the delayed response. As suggested by you, I have
taken the performance readings at 128 client counts after making the
following two changes:

*1).* Removed AddWaitEventToSet(FeBeWaitSet, WL_POSTMASTER_DEATH, -1, NULL,
NULL); from pq_init(). Below is the git diff for the same.

diff --git a/src/backend/libpq/pqcomm.c b/src/backend/libpq/pqcomm.c
index 8d6eb0b..399d54b 100644
--- a/src/backend/libpq/pqcomm.c
+++ b/src/backend/libpq/pqcomm.c
@@ -206,7 +206,9 @@ pq_init(void)
        AddWaitEventToSet(FeBeWaitSet, WL_SOCKET_WRITEABLE,
MyProcPort->sock,
                                          NULL, NULL);
        AddWaitEventToSet(FeBeWaitSet, WL_LATCH_SET, -1, MyLatch, NULL);
+#if 0
        AddWaitEventToSet(FeBeWaitSet, WL_POSTMASTER_DEATH, -1, NULL, NULL);
+#endif

*2).* Disabled the guc vars "bgwriter_flush_after",
"checkpointer_flush_after" and "backend_flush_after" by setting them to
zero.

After doing the above two changes below are the readings i got for 128
client counts:

*CASE :* Read-Write Tests when data exceeds shared buffers.

Non Default settings and test
./postgres -c shared_buffers=8GB -N 200 -c min_wal_size=15GB -c
max_wal_size=20GB -c checkpoint_timeout=900 -c maintenance_work_mem=1GB -c
checkpoint_completion_target=0.9 &

./pgbench -i -s 1000 postgres

./pgbench -c 128 -j 128 -T 1800 -M prepared postgres

*Run1 :* tps = 9690.678225
*Run2 :* tps = 9904.320645
*Run3 :* tps = 9943.547176

Please let me know if i need to take readings with other client counts as
well.

*Note:* I have taken these readings on postgres master head at,

commit 91fd1df4aad2141859310564b498a3e28055ee28
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date: Sun May 8 16:53:55 2016 -0400

With Regards,
Ashutosh Sharma
EnterpriseDB: *http://www.enterprisedb.com <http://www.enterprisedb.com&gt;*

On Wed, May 11, 2016 at 3:53 AM, Andres Freund <andres@anarazel.de> wrote:

On Wed, May 11, 2016 at 12:51 AM, Ashutosh Sharma <ashu.coek88@gmail.com> wrote:

I am extremely sorry for the delayed response. As suggested by you, I have
taken the performance readings at 128 client counts after making the
following two changes:

1). Removed AddWaitEventToSet(FeBeWaitSet, WL_POSTMASTER_DEATH, -1, NULL,
NULL); from pq_init(). Below is the git diff for the same.

diff --git a/src/backend/libpq/pqcomm.c b/src/backend/libpq/pqcomm.c
index 8d6eb0b..399d54b 100644
--- a/src/backend/libpq/pqcomm.c
+++ b/src/backend/libpq/pqcomm.c
@@ -206,7 +206,9 @@ pq_init(void)
AddWaitEventToSet(FeBeWaitSet, WL_SOCKET_WRITEABLE,
MyProcPort->sock,
NULL, NULL);
AddWaitEventToSet(FeBeWaitSet, WL_LATCH_SET, -1, MyLatch, NULL);
+#if 0
AddWaitEventToSet(FeBeWaitSet, WL_POSTMASTER_DEATH, -1, NULL, NULL);
+#endif

2). Disabled the guc vars "bgwriter_flush_after", "checkpointer_flush_after"
and "backend_flush_after" by setting them to zero.

After doing the above two changes below are the readings i got for 128
client counts:

CASE : Read-Write Tests when data exceeds shared buffers.

Non Default settings and test
./postgres -c shared_buffers=8GB -N 200 -c min_wal_size=15GB -c
max_wal_size=20GB -c checkpoint_timeout=900 -c maintenance_work_mem=1GB -c
checkpoint_completion_target=0.9 &

./pgbench -i -s 1000 postgres

./pgbench -c 128 -j 128 -T 1800 -M prepared postgres

Run1 : tps = 9690.678225
Run2 : tps = 9904.320645
Run3 : tps = 9943.547176

Please let me know if i need to take readings with other client counts as
well.

Can you please take four new sets of readings, like this:

- Unpatched master, default *_flush_after
- Unpatched master, *_flush_after=0
- That line removed with #if 0, default *_flush_after
- That line removed with #if 0, *_flush_after=0

128 clients is fine. But I want to see four sets of numbers that were
all taken by the same person at the same time using the same script.

Thanks,

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Hi,

Please find the test results for the following set of combinations taken at
128 client counts:

*1)* *Unpatched master, default *_flush_after :* TPS = 10925.882396

*2) Unpatched master, *_flush_after=0 :* TPS = 18613.343529

*3)* *That line removed with #if 0, default *_flush_after :* TPS =
9856.809278

*4)* *That line removed with #if 0, *_flush_after=0 :* TPS = 18158.648023

Here, *That line* points to "*AddWaitEventToSet(FeBeWaitSet,
WL_POSTMASTER_DEATH, -1, NULL, NULL);* in pq_init()."

Please note that earlier i had taken readings with data directory and
pg_xlog directory at the same location in HDD. But this time i have changed
the location of pg_xlog to ssd and taken the readings. With pg_xlog and
data directory at the same location in HDD i was seeing much lesser
performance like for "*That line removed with #if 0, *_flush_after=0 :*"
case i was getting 7367.709378 tps.

Also, the commit-id on which i have taken above readings along with pgbench
commands used are mentioned below:

commit 8a13d5e6d1bb9ff9460c72992657077e57e30c32
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date: Wed May 11 17:06:53 2016 -0400

Fix infer_arbiter_indexes() to not barf on system columns.

*Non Default settings and test*:
./postgres -c shared_buffers=8GB -N 200 -c min_wal_size=15GB -c
max_wal_size=20GB -c checkpoint_timeout=900 -c maintenance_work_mem=1GB -c
checkpoint_completion_target=0.9 &

./pgbench -i -s 1000 postgres

./pgbench -c 128 -j 128 -T 1800 -M prepared postgres

With Regards,
Ashutosh Sharma
EnterpriseDB: *http://www.enterprisedb.com <http://www.enterprisedb.com&gt;*

On Thu, May 12, 2016 at 9:22 AM, Robert Haas <robertmhaas@gmail.com> wrote:

On Thu, May 12, 2016 at 8:39 AM, Ashutosh Sharma <ashu.coek88@gmail.com> wrote:

Please find the test results for the following set of combinations taken at
128 client counts:

1) Unpatched master, default *_flush_after : TPS = 10925.882396

2) Unpatched master, *_flush_after=0 : TPS = 18613.343529

3) That line removed with #if 0, default *_flush_after : TPS = 9856.809278

4) That line removed with #if 0, *_flush_after=0 : TPS = 18158.648023

I'm getting increasingly unhappy about the checkpoint flush control.
I saw major regressions on my parallel COPY test, too:

/messages/by-id/CA+TgmoYoUQf9cGcpgyGNgZQHcY-gCcKRyAqQtDU8KFE4N6HVkA@mail.gmail.com

That was a completely different machine (POWER7 instead of Intel,
lousy disks instead of good ones) and a completely different workload.
Considering these results, I think there's now plenty of evidence to
suggest that this feature is going to be horrible for a large number
of users. A 45% regression on pgbench is horrible. (Nobody wants to
take even a 1% hit for snapshot too old, right?) Sure, it might not
be that way for every user on every Linux system, and I'm sure it
performed well on the systems where Andres benchmarked it, or he
wouldn't have committed it. But our goal can't be to run well only on
the newest hardware with the least-buggy kernel...

Here, That line points to "AddWaitEventToSet(FeBeWaitSet,
WL_POSTMASTER_DEATH, -1, NULL, NULL); in pq_init()."

Given the above results, it's not clear whether that is making things
better or worse.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Hi,

On 2016-05-12 18:09:07 +0530, Ashutosh Sharma wrote:

Please find the test results for the following set of combinations taken at
128 client counts:

Thanks.

*1)* *Unpatched master, default *_flush_after :* TPS = 10925.882396

Could you run this one with a number of different backend_flush_after
settings? I'm suspsecting the primary issue is that the default is too low.

Greetings,

Andres Freund

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Thu, May 12, 2016 at 11:13 AM, Andres Freund <andres@anarazel.de> wrote:

Could you run this one with a number of different backend_flush_after
settings? I'm suspsecting the primary issue is that the default is too low.

What values do you think would be good to test? Maybe provide 3 or 4
suggested values to try?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 2016-05-12 11:27:31 -0400, Robert Haas wrote:

On Thu, May 12, 2016 at 11:13 AM, Andres Freund <andres@anarazel.de> wrote:

Could you run this one with a number of different backend_flush_after
settings? I'm suspsecting the primary issue is that the default is too low.

What values do you think would be good to test? Maybe provide 3 or 4
suggested values to try?

0 (disabled), 16 (current default), 32, 64, 128, 256?

I'm suspecting that only backend_flush_after_* has these negative
performance implications at this point. One path is to increase that
option's default value, another is to disable only backend guided
flushing. And add a strong hint that if you care about predictable
throughput you might want to enable it.

Greetings,

Andres Freund

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 2016-05-12 10:49:06 -0400, Robert Haas wrote:

On Thu, May 12, 2016 at 8:39 AM, Ashutosh Sharma <ashu.coek88@gmail.com> wrote:

Please find the test results for the following set of combinations taken at
128 client counts:

1) Unpatched master, default *_flush_after : TPS = 10925.882396

2) Unpatched master, *_flush_after=0 : TPS = 18613.343529

3) That line removed with #if 0, default *_flush_after : TPS = 9856.809278

4) That line removed with #if 0, *_flush_after=0 : TPS = 18158.648023

I'm getting increasingly unhappy about the checkpoint flush control.
I saw major regressions on my parallel COPY test, too:

Yes, I'm concerned too.

The workload in this thread is a bit of an "artificial" workload (all
data is constantly updated, doesn't fit into shared_buffers, fits into
the OS page cache), and only measures throughput not latency. But I
agree that that's way too large a regression to accept, and that there's
a significant number of machines with way undersized shared_buffer
values.

/messages/by-id/CA+TgmoYoUQf9cGcpgyGNgZQHcY-gCcKRyAqQtDU8KFE4N6HVkA@mail.gmail.com

That was a completely different machine (POWER7 instead of Intel,
lousy disks instead of good ones) and a completely different workload.
Considering these results, I think there's now plenty of evidence to
suggest that this feature is going to be horrible for a large number
of users. A 45% regression on pgbench is horrible.

I asked you over there whether you could benchmark with just different
values for backend_flush_after... I chose the current value because it
gives the best latency / most consistent throughput numbers, but 128kb
isn't a large window. I suspect we might need to disable backend guided
flushing if that's not sufficient :(

Here, That line points to "AddWaitEventToSet(FeBeWaitSet,
WL_POSTMASTER_DEATH, -1, NULL, NULL); in pq_init()."

Given the above results, it's not clear whether that is making things
better or worse.

Yea, me neither. I think it's doubful that you'd see performance
difference due to the original ac1d7945f866b1928c2554c0f80fd52d7f977772
, independent of the WaitEventSet stuff, at these throughput rates.

Greetings,

Andres Freund

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

I'm getting increasingly unhappy about the checkpoint flush control.
I saw major regressions on my parallel COPY test, too:

Yes, I'm concerned too.

A few thoughts:

- focussing on raw tps is not a good idea, because it may be a lot of tps
followed by a sync panic, with an unresponsive database. I wish the
performance reports would include some indication of the distribution
(eg min/q1/median/d3/max tps per second seen, standard deviation), not
just the final "tps" figure.

- checkpoint flush control (checkpoint_flush_after) should mostly always
beneficial because it flushes sorted data. I would be surprised
to see significant regressions with this on. A lot of tests showed
maybe improved tps, but mostly greatly improved performance stability,
where a database unresponsive 60% of the time (60% of seconds in the
the tps show very low or zero tps) and then becomes always responsive.

- other flush controls ({backend,bgwriter}_flush_after) may just increase
random writes, so are more risky in nature because the data is not
sorted, and it may or may not be a good idea depending on detailed
conditions. A "parallel copy" would be just such a special IO load
which degrade performance under these settings.

Maybe these two should be disabled by default because they lead to
possibly surprising regressions?

- for any particular load, the admin can decide to disable these if
they think it is better not to flush. Also, as suggested by Andres,
with 128 parallel queries the default value may not be appropriate
at all.

--
Fabien.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Hi,

Following are the performance results for read write test observed with
different numbers of "*backend_flush_after*".

1) backend_flush_after = *256kb* (32*8kb), tps = *10841.178815*
2) backend_flush_after = *512kb* (64*8kb), tps = *11098.702707*
3) backend_flush_after = *1MB* (128*8kb), tps = *11434.964545*
4) backend_flush_after = *2MB* (256*8kb), tps = *13477.089417*

*Note:* Above test has been performed on Unpatched master with default
values for checkpoint_flush_after, bgwriter_flush_after
and wal_writer_flush_after.

With Regards,
Ashutosh Sharma
EnterpriseDB:* http://www.enterprisedb.com <http://www.enterprisedb.com&gt;*

On Thu, May 12, 2016 at 9:20 PM, Andres Freund <andres@anarazel.de> wrote:

On Fri, May 13, 2016 at 7:08 AM, Ashutosh Sharma <ashu.coek88@gmail.com> wrote:

Following are the performance results for read write test observed with
different numbers of "backend_flush_after".

1) backend_flush_after = 256kb (32*8kb), tps = 10841.178815
2) backend_flush_after = 512kb (64*8kb), tps = 11098.702707
3) backend_flush_after = 1MB (128*8kb), tps = 11434.964545
4) backend_flush_after = 2MB (256*8kb), tps = 13477.089417

So even at 2MB we don't come close to recovering all of the lost
performance. Can you please test these three scenarios?

1. Default settings for *_flush_after
2. backend_flush_after=0, rest defaults
3. backend_flush_after=0, bgwriter_flush_after=0,
wal_writer_flush_after=0, checkpoint_flush_after=0

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 2016-05-13 10:20:04 -0400, Robert Haas wrote:

On Fri, May 13, 2016 at 7:08 AM, Ashutosh Sharma <ashu.coek88@gmail.com> wrote:

Following are the performance results for read write test observed with
different numbers of "backend_flush_after".

1) backend_flush_after = 256kb (32*8kb), tps = 10841.178815
2) backend_flush_after = 512kb (64*8kb), tps = 11098.702707
3) backend_flush_after = 1MB (128*8kb), tps = 11434.964545
4) backend_flush_after = 2MB (256*8kb), tps = 13477.089417

So even at 2MB we don't come close to recovering all of the lost
performance. Can you please test these three scenarios?

1. Default settings for *_flush_after
2. backend_flush_after=0, rest defaults
3. backend_flush_after=0, bgwriter_flush_after=0,
wal_writer_flush_after=0, checkpoint_flush_after=0

4) 1) + a shared_buffers setting appropriate to the workload.

I just want to emphasize what we're discussing here is a bit of an
extreme setup. A workload that's bigger than shared buffers, but smaller
than the OS's cache size; with a noticeable likelihood of rewriting
individual OS page cache pages within 30s.

Greetings,

Andres Freund

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Fri, May 13, 2016 at 1:43 PM, Andres Freund <andres@anarazel.de> wrote:

On 2016-05-13 10:20:04 -0400, Robert Haas wrote:

On Fri, May 13, 2016 at 7:08 AM, Ashutosh Sharma <ashu.coek88@gmail.com> wrote:

Following are the performance results for read write test observed with
different numbers of "backend_flush_after".

1) backend_flush_after = 256kb (32*8kb), tps = 10841.178815
2) backend_flush_after = 512kb (64*8kb), tps = 11098.702707
3) backend_flush_after = 1MB (128*8kb), tps = 11434.964545
4) backend_flush_after = 2MB (256*8kb), tps = 13477.089417

So even at 2MB we don't come close to recovering all of the lost
performance. Can you please test these three scenarios?

1. Default settings for *_flush_after
2. backend_flush_after=0, rest defaults
3. backend_flush_after=0, bgwriter_flush_after=0,
wal_writer_flush_after=0, checkpoint_flush_after=0

4) 1) + a shared_buffers setting appropriate to the workload.

I just want to emphasize what we're discussing here is a bit of an
extreme setup. A workload that's bigger than shared buffers, but smaller
than the OS's cache size; with a noticeable likelihood of rewriting
individual OS page cache pages within 30s.

You're just describing pgbench with a scale factor too large to fit in
shared_buffers. I think it's unfair to paint that as some kind of
niche use case.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 2016-05-13 14:43:15 -0400, Robert Haas wrote:

On Fri, May 13, 2016 at 1:43 PM, Andres Freund <andres@anarazel.de> wrote:

I just want to emphasize what we're discussing here is a bit of an
extreme setup. A workload that's bigger than shared buffers, but smaller
than the OS's cache size; with a noticeable likelihood of rewriting
individual OS page cache pages within 30s.

You're just describing pgbench with a scale factor too large to fit in
shared_buffers.

Well, that *and* a scale factor smaller than 20% of the memory
available, *and* a scale factor small enough that make re-dirtying of
already written out pages likely.

I think it's unfair to paint that as some kind of niche use case.

I'm not saying we don't need to do something about it. Just that it's a
hard tradeoff to make. The massive performance / latency we've observed
originate from the kernel caching too much dirty IO. The fix is making
is cache fewer dirty pages. But there's workloads where the kernel's
buffer cache works as an extension of our page cache.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Fri, May 13, 2016 at 11:13 PM, Andres Freund <andres@anarazel.de> wrote:

On 2016-05-13 10:20:04 -0400, Robert Haas wrote:

On Fri, May 13, 2016 at 7:08 AM, Ashutosh Sharma <ashu.coek88@gmail.com>

wrote:

Following are the performance results for read write test observed

with

different numbers of "backend_flush_after".

1) backend_flush_after = 256kb (32*8kb), tps = 10841.178815
2) backend_flush_after = 512kb (64*8kb), tps = 11098.702707
3) backend_flush_after = 1MB (128*8kb), tps = 11434.964545
4) backend_flush_after = 2MB (256*8kb), tps = 13477.089417

So even at 2MB we don't come close to recovering all of the lost
performance. Can you please test these three scenarios?

1. Default settings for *_flush_after
2. backend_flush_after=0, rest defaults
3. backend_flush_after=0, bgwriter_flush_after=0,
wal_writer_flush_after=0, checkpoint_flush_after=0

4) 1) + a shared_buffers setting appropriate to the workload.

If by 4th point, you mean to test the case when data fits in shared
buffers, then Mithun has already reported above [1]/messages/by-id/CAD__OuiObzNVTt_hO__P5AEnU4iNqcFWgArXR4TbLKe-UXyukQ@mail.gmail.com Read line - Even for READ-WRITE when data fits into shared buffer (scale_factor=300 and shared_buffers=8GB) performance has improved. that it didn't see any
regression for that case

[1]: /messages/by-id/CAD__OuiObzNVTt_hO__P5AEnU4iNqcFWgArXR4TbLKe-UXyukQ@mail.gmail.com Read line - Even for READ-WRITE when data fits into shared buffer (scale_factor=300 and shared_buffers=8GB) performance has improved.
/messages/by-id/CAD__OuiObzNVTt_hO__P5AEnU4iNqcFWgArXR4TbLKe-UXyukQ@mail.gmail.com
Read line - Even for READ-WRITE when data fits into shared buffer
(scale_factor=300 and shared_buffers=8GB) performance has improved.

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

Hi,

Please find the results for the following 3 scenarios with unpatched master:

1. Default settings for *_flush_after : TPS = *10677.662356*
2. backend_flush_after=0, rest defaults : TPS = *18452.655936*
3. backend_flush_after=0, bgwriter_flush_after=0,
wal_writer_flush_after=0, checkpoint_flush_after=0 : TPS = *18614.479962*

With Regards,
Ashutosh Sharma
EnterpriseDB: http://www.enterprisedb.com

On Fri, May 13, 2016 at 7:50 PM, Robert Haas <robertmhaas@gmail.com> wrote: