Remove the comment on the countereffectiveness of large shared_buffers on Windows

Hi Takayuki-san,

IMHO, I think we could remove third paragraph completely and
generalised starting of second paragraph, somewhat looks likes as
follow:

        <para>
-        If you have a dedicated database server with 1GB or more of RAM, a
-        reasonable starting value for <varname>shared_buffers</varname> is 25%
-        of the memory in your system.  There are some workloads where even
+        A reasonable starting value for
<varname>shared_buffers</varname> is 25%
+       of the RAM in your system.  There are some workloads where even
         large settings for <varname>shared_buffers</varname> are effective, but
         because <productname>PostgreSQL</productname> also relies on the
         operating system cache, it is unlikely that an allocation of more than

I may be wrong here, would like know your and/or community's thought
on this. Thanks.

Regards,
Amul Sul

The new status of this patch is: Waiting on Author

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

Change "even large" to "even larger" because it is used in a comparison.

... a reasonable starting value for <varname>shared_buffers</varname> is 25%
of the memory in your system. There are some workloads where even large settings
for <varname>shared_buffers</varname> are effective, ...

... are some workloads where even larger settings ...

Regards,
Brad DeJong

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

From: amul sul [mailto:sulamul@gmail.com]

IMHO, I think we could remove third paragraph completely and generalised
starting of second paragraph, somewhat looks likes as
follow:

<para>
-        If you have a dedicated database server with 1GB or more of RAM,
a
-        reasonable starting value for <varname>shared_buffers</varname>
is 25%
-        of the memory in your system.  There are some workloads where even
+        A reasonable starting value for
<varname>shared_buffers</varname> is 25%
+       of the RAM in your system.  There are some workloads where even
large settings for <varname>shared_buffers</varname> are
effective, but
because <productname>PostgreSQL</productname> also relies on the
operating system cache, it is unlikely that an allocation of more
than

The third paragraph may be redundant, I'm a bit inclined to leave it for kindness and completeness. The attached revised patch just correct the existing typo (large -> larger).

I'll change the status to needs review.

Regards
Takayuki Tsunakawa

On Mon, Nov 7, 2016 at 10:46 AM, Tsunakawa, Takayuki
<tsunakawa.takay@jp.fujitsu.com> wrote:

The third paragraph may be redundant, I'm a bit inclined to leave it for kindness and completeness. The attached revised patch just correct the existing typo (large -> larger).

I am not agree to having this paragraph either, I'll leave the
decision to committer.

I'll change the status to needs review.

The new status of this patch is: Ready for Committer

Regards,
Amul Sul

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

On Tue, Sep 20, 2016 at 8:15 AM, Tsunakawa, Takayuki
<tsunakawa.takay@jp.fujitsu.com> wrote:

Hello,

From: pgsql-hackers-owner@postgresql.org
[mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Magnus Hagander
On Wed, Aug 24, 2016 at 4:35 AM, Tsunakawa, Takayuki
<tsunakawa.takay@jp.fujitsu.com> wrote:
As a similar topic, I wonder whether the following still holds true,
after many improvements on shared buffer lock contention.

https://www.postgresql.org/docs/devel/static/runtime-config-re
source.html

"The useful range for shared_buffers on Windows systems
is generally from 64MB to 512MB."

Yes, that may very much be out of date as well. A good set of benchmarks
around that would definitely be welcome.

I'd like to propose the above-mentioned comment from the manual. The patch is attached.

I ran read-only and read-write modes of pgbench, and could not see any apparent decrease in performance when I increased shared_buffers. The scaling factor is 200, where the database size is roughly 3GB. I ran the benchmark on my Windows 10 PC with 6 CPU cores and 16GB of RAM. The database and WAL is stored on the same HDD.

<<Test batch file>>
@echo off
for %%s in (256MB 512MB 1GB 2GB 4GB) do (
pg_ctl -w -o "-c shared_buffers=%%s" start
pgbench -c18 -j6 -T60 -S bench >> g:\b.txt 2>&1
pg_ctl -t 3600 stop
)

<<Select-only (with -S)>>
shared_buffers tps
256MB 63056
512MB 63918
1GB 65520
2GB 66840
4GB 68270

<<Read-write (without -S)>>
shared_buffers tps
256MB 1138
512MB 1187
1GB 1571
2GB 1650
4GB 1598

Isn't it somewhat strange that writes are showing big win whereas
reads doesn't show much win? Can you once do some longer read-write
tests to see if we get consistent data and take the median-of-three
runs data? Above benchmarks doesn't seem to indicate that increase in
shared buffers upto 25% of RAM shows significant win on Windows
systems, because the performance of writes increase upto 2GB and then
starts decreasing.

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

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

On Mon, Nov 7, 2016 at 5:55 AM, Amit Kapila <amit.kapila16@gmail.com> wrote:

On Tue, Sep 20, 2016 at 8:15 AM, Tsunakawa, Takayuki
<tsunakawa.takay@jp.fujitsu.com> wrote:

Hello,

From: pgsql-hackers-owner@postgresql.org
[mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Magnus

Hagander

On Wed, Aug 24, 2016 at 4:35 AM, Tsunakawa, Takayuki
<tsunakawa.takay@jp.fujitsu.com> wrote:
As a similar topic, I wonder whether the following still holds

true,

after many improvements on shared buffer lock contention.

https://www.postgresql.org/docs/devel/static/runtime-config-re
source.html

"The useful range for shared_buffers on Windows systems
is generally from 64MB to 512MB."

Yes, that may very much be out of date as well. A good set of benchmarks
around that would definitely be welcome.

I'd like to propose the above-mentioned comment from the manual. The

patch is attached.

I ran read-only and read-write modes of pgbench, and could not see any

apparent decrease in performance when I increased shared_buffers. The
scaling factor is 200, where the database size is roughly 3GB. I ran the
benchmark on my Windows 10 PC with 6 CPU cores and 16GB of RAM. The
database and WAL is stored on the same HDD.

<<Test batch file>>
@echo off
for %%s in (256MB 512MB 1GB 2GB 4GB) do (
pg_ctl -w -o "-c shared_buffers=%%s" start
pgbench -c18 -j6 -T60 -S bench >> g:\b.txt 2>&1
pg_ctl -t 3600 stop
)

<<Select-only (with -S)>>
shared_buffers tps
256MB 63056
512MB 63918
1GB 65520
2GB 66840
4GB 68270

<<Read-write (without -S)>>
shared_buffers tps
256MB 1138
512MB 1187
1GB 1571
2GB 1650
4GB 1598

Isn't it somewhat strange that writes are showing big win whereas
reads doesn't show much win?

I don't find that unusual, and have seen the same thing on Linux.

With small shared_buffers, you are constantly throwing dirty buffers at the
kernel in no particular order, and the kernel does a poor job of predicting
when the same buffer will be dirtied repeatedly and only needs the final
version of the data actually written to disk. With large shared_buffers,
you only send each dirty buffer to the kernel once per checkpoint.

Cheers,

Jeff

On Tue, Nov 8, 2016 at 5:12 AM, Jeff Janes <jeff.janes@gmail.com> wrote:

On Mon, Nov 7, 2016 at 5:55 AM, Amit Kapila <amit.kapila16@gmail.com> wrote:

On Tue, Sep 20, 2016 at 8:15 AM, Tsunakawa, Takayuki
<tsunakawa.takay@jp.fujitsu.com> wrote:

I ran read-only and read-write modes of pgbench, and could not see any
apparent decrease in performance when I increased shared_buffers. The
scaling factor is 200, where the database size is roughly 3GB. I ran the
benchmark on my Windows 10 PC with 6 CPU cores and 16GB of RAM. The
database and WAL is stored on the same HDD.

<<Test batch file>>
@echo off
for %%s in (256MB 512MB 1GB 2GB 4GB) do (
pg_ctl -w -o "-c shared_buffers=%%s" start
pgbench -c18 -j6 -T60 -S bench >> g:\b.txt 2>&1
pg_ctl -t 3600 stop
)

<<Select-only (with -S)>>
shared_buffers tps
256MB 63056
512MB 63918
1GB 65520
2GB 66840
4GB 68270

<<Read-write (without -S)>>
shared_buffers tps
256MB 1138
512MB 1187
1GB 1571
2GB 1650
4GB 1598

Isn't it somewhat strange that writes are showing big win whereas
reads doesn't show much win?

I don't find that unusual, and have seen the same thing on Linux.

With small shared_buffers, you are constantly throwing dirty buffers at the
kernel in no particular order, and the kernel does a poor job of predicting
when the same buffer will be dirtied repeatedly and only needs the final
version of the data actually written to disk.

Okay and I think partially it might be because we don't have writeback
optimization (done in 9.6) for Windows. However, still the broader
question stands that whether above data is sufficient to say that we
can recommend the settings of shared_buffers on Windows similar to
Linux?

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

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

On Tue, Nov 8, 2016 at 5:03 AM, Amit Kapila <amit.kapila16@gmail.com> wrote:

On Tue, Nov 8, 2016 at 5:12 AM, Jeff Janes <jeff.janes@gmail.com> wrote:

On Mon, Nov 7, 2016 at 5:55 AM, Amit Kapila <amit.kapila16@gmail.com>

wrote:

On Tue, Sep 20, 2016 at 8:15 AM, Tsunakawa, Takayuki
<tsunakawa.takay@jp.fujitsu.com> wrote:

I ran read-only and read-write modes of pgbench, and could not see any
apparent decrease in performance when I increased shared_buffers. The
scaling factor is 200, where the database size is roughly 3GB. I ran

the

benchmark on my Windows 10 PC with 6 CPU cores and 16GB of RAM. The
database and WAL is stored on the same HDD.

<<Test batch file>>
@echo off
for %%s in (256MB 512MB 1GB 2GB 4GB) do (
pg_ctl -w -o "-c shared_buffers=%%s" start
pgbench -c18 -j6 -T60 -S bench >> g:\b.txt 2>&1
pg_ctl -t 3600 stop
)

<<Select-only (with -S)>>
shared_buffers tps
256MB 63056
512MB 63918
1GB 65520
2GB 66840
4GB 68270

<<Read-write (without -S)>>
shared_buffers tps
256MB 1138
512MB 1187
1GB 1571
2GB 1650
4GB 1598

Isn't it somewhat strange that writes are showing big win whereas
reads doesn't show much win?

I don't find that unusual, and have seen the same thing on Linux.

With small shared_buffers, you are constantly throwing dirty buffers at

the

kernel in no particular order, and the kernel does a poor job of

predicting

when the same buffer will be dirtied repeatedly and only needs the final
version of the data actually written to disk.

Okay and I think partially it might be because we don't have writeback
optimization (done in 9.6) for Windows. However, still the broader
question stands that whether above data is sufficient to say that we
can recommend the settings of shared_buffers on Windows similar to
Linux?

Based on this optimization we might want to keep the text that says large
shared buffers on Windows aren't as effective perhaps, and just remove the
sentence that explicitly says don't go over 512MB?

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

From: pgsql-hackers-owner@postgresql.org

[mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Magnus Hagander

Okay and I think partially it might be because we don't have

writeback
optimization (done in 9.6) for Windows. However, still the broader
question stands that whether above data is sufficient to say that
we
can recommend the settings of shared_buffers on Windows similar
to
Linux?

Based on this optimization we might want to keep the text that says large
shared buffers on Windows aren't as effective perhaps, and just remove the
sentence that explicitly says don't go over 512MB?

Just removing the reference to the size would make users ask a question "What size is the effective upper limit?"

Regards
Takayuki Tsunakawa

--
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, Nov 11, 2016 at 1:54 AM, Tsunakawa, Takayuki <
tsunakawa.takay@jp.fujitsu.com> wrote:

From: pgsql-hackers-owner@postgresql.org

[mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Magnus Hagander

Okay and I think partially it might be because we don't have

writeback
optimization (done in 9.6) for Windows. However, still the broader
question stands that whether above data is sufficient to say that
we
can recommend the settings of shared_buffers on Windows similar
to
Linux?

Based on this optimization we might want to keep the text that says large
shared buffers on Windows aren't as effective perhaps, and just remove

the

sentence that explicitly says don't go over 512MB?

Just removing the reference to the size would make users ask a question
"What size is the effective upper limit?"

True, but that's a question for other platforms as well, isn't it? We can
certainly find a different phrasing for it, but ISTM that we know that it
might be a problem, but we just don't know where the limit is? Maybe
something that suggests to people that they need to test their way to the
answer?

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

On Fri, Nov 11, 2016 at 3:01 PM, Magnus Hagander <magnus@hagander.net> wrote:

Based on this optimization we might want to keep the text that says
large
shared buffers on Windows aren't as effective perhaps,

Sounds sensible or may add a line to say why it isn't as effective as on Linux.

and just remove

the
sentence that explicitly says don't go over 512MB?

Have we done any windows specific optimization since it was originally
mentioned as 512MB which indicates that we can remove it? Are you
telling it based on results in this thread, if so, I think it is
better to do few more tests before changing it.

Just removing the reference to the size would make users ask a question
"What size is the effective upper limit?"

True, but that's a question for other platforms as well, isn't it?

Right, but for other platforms, the recommendation seems to be 25% of
RAM, can we safely say that for Windows as well? As per test results
in this thread, it seems the read-write performance degrades when
shared buffers have increased from 12.5 to 25%. I think as the test
is done for a short duration so that degradation could be just a run
to run to run variation, that's why I suggested doing few more tests.

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

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

On Sat, Nov 12, 2016 at 4:03 AM, Amit Kapila <amit.kapila16@gmail.com>
wrote:

On Fri, Nov 11, 2016 at 3:01 PM, Magnus Hagander <magnus@hagander.net>
wrote:

Based on this optimization we might want to keep the text that says
large
shared buffers on Windows aren't as effective perhaps,

Sounds sensible or may add a line to say why it isn't as effective as on
Linux.

Do we actually know *why*?

and just remove

the
sentence that explicitly says don't go over 512MB?

Have we done any windows specific optimization since it was originally
mentioned as 512MB which indicates that we can remove it? Are you
telling it based on results in this thread, if so, I think it is
better to do few more tests before changing it.

Well, that advice goes *way* back. Many things have changed since then -
and just look at things like the updating of the stats target. For one
thing, we're in 64-bit now, not 32, for the majority of users. We reserve
the shared memory on startup which was done for address probability issues,
but may have had side effects. And *Windows itself* has changed in those 10
or so years.

I've heard it from other people as well, but this thread is definitely one
of them. I think the old truth about "never go above that" is invalid at
this point, but it may never have been valid. But I also don't think we
know what to put there instead, as a recommendation.

Just removing the reference to the size would make users ask a question
"What size is the effective upper limit?"

True, but that's a question for other platforms as well, isn't it?

Right, but for other platforms, the recommendation seems to be 25% of
RAM, can we safely say that for Windows as well? As per test results
in this thread, it seems the read-write performance degrades when
shared buffers have increased from 12.5 to 25%. I think as the test
is done for a short duration so that degradation could be just a run
to run to run variation, that's why I suggested doing few more tests.

We talk about 25%, but only up to a certain size. It's suggested as a
starting point. The 25% value us probably good as a starting point, as it's
recommended, but not as a "recommended setting". I'm fine with doing
something similar for Windows -- say "10-15% as a starting point, but you
have to check with your workload" kind of statements.

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

On Mon, Nov 7, 2016 at 7:03 PM, Amit Kapila <amit.kapila16@gmail.com> wrote:

On Tue, Nov 8, 2016 at 5:12 AM, Jeff Janes <jeff.janes@gmail.com> wrote:

On Mon, Nov 7, 2016 at 5:55 AM, Amit Kapila <amit.kapila16@gmail.com>

wrote:

Isn't it somewhat strange that writes are showing big win whereas
reads doesn't show much win?

I don't find that unusual, and have seen the same thing on Linux.

With small shared_buffers, you are constantly throwing dirty buffers at

the

kernel in no particular order, and the kernel does a poor job of

predicting

when the same buffer will be dirtied repeatedly and only needs the final
version of the data actually written to disk.

Okay and I think partially it might be because we don't have writeback
optimization (done in 9.6) for Windows.

Is the writeback optimization the introduction of checkpoint_flush_after,
or is it something else?

If it is checkpoint_flush_after, then I don't think that that is related.
In fact, they operate in opposite directions. The writeback optimization
forces the kernel to be more eager about writing out dirty data, so it
doesn't have a giant pile of it when the fsyncs comes at the end of the
checkpoint. Using a large shared_buffers forces it to be less eager, by
not turning the dirty buffers over to the kernel as often.

However, still the broader
question stands that whether above data is sufficient to say that we
can recommend the settings of shared_buffers on Windows similar to
Linux?

We do have evidence that the old advice is out of date. So I think we
should just remove it. We have no evidence that the optimal Windows
setting on modern Windows is the same as it is for Linux, but also no
evidence that it is not the same.

Cheers,

Jeff

On Sat, Nov 12, 2016 at 11:00 PM, Magnus Hagander <magnus@hagander.net> wrote:

On Sat, Nov 12, 2016 at 4:03 AM, Amit Kapila <amit.kapila16@gmail.com>
wrote:

On Fri, Nov 11, 2016 at 3:01 PM, Magnus Hagander <magnus@hagander.net>
wrote:

Based on this optimization we might want to keep the text that says
large
shared buffers on Windows aren't as effective perhaps,

Sounds sensible or may add a line to say why it isn't as effective as on
Linux.

Do we actually know *why*?

No, I have never investigated it for Windows. I am just telling based
on results reported in this thread. I have seen that there is a
noticeable difference of read-only performance when data fits in
shared buffers as compared to when it doesn't fit on Linux systems.

Right, but for other platforms, the recommendation seems to be 25% of
RAM, can we safely say that for Windows as well? As per test results
in this thread, it seems the read-write performance degrades when
shared buffers have increased from 12.5 to 25%. I think as the test
is done for a short duration so that degradation could be just a run
to run to run variation, that's why I suggested doing few more tests.

We talk about 25%, but only up to a certain size. It's suggested as a
starting point. The 25% value us probably good as a starting point, as it's
recommended, but not as a "recommended setting". I'm fine with doing
something similar for Windows -- say "10-15% as a starting point, but you
have to check with your workload" kind of statements.

Okay, not a problem. However, I am not sure the results in this
thread are sufficient proof as for read-only tests, there is no
noticeable win by increasing shared buffers and read-write tests seems
to be quite short (60 seconds) to rely on it.

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

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

On Sun, Nov 13, 2016 at 1:23 AM, Jeff Janes <jeff.janes@gmail.com> wrote:

On Mon, Nov 7, 2016 at 7:03 PM, Amit Kapila <amit.kapila16@gmail.com> wrote:

On Tue, Nov 8, 2016 at 5:12 AM, Jeff Janes <jeff.janes@gmail.com> wrote:

On Mon, Nov 7, 2016 at 5:55 AM, Amit Kapila <amit.kapila16@gmail.com>
wrote:

Isn't it somewhat strange that writes are showing big win whereas
reads doesn't show much win?

I don't find that unusual, and have seen the same thing on Linux.

With small shared_buffers, you are constantly throwing dirty buffers at
the
kernel in no particular order, and the kernel does a poor job of
predicting
when the same buffer will be dirtied repeatedly and only needs the final
version of the data actually written to disk.

Okay and I think partially it might be because we don't have writeback
optimization (done in 9.6) for Windows.

Is the writeback optimization the introduction of checkpoint_flush_after, or
is it something else?

Yes.

If it is checkpoint_flush_after, then I don't think that that is related.
In fact, they operate in opposite directions. The writeback optimization
forces the kernel to be more eager about writing out dirty data, so it
doesn't have a giant pile of it when the fsyncs comes at the end of the
checkpoint. Using a large shared_buffers forces it to be less eager, by not
turning the dirty buffers over to the kernel as often.

Okay, I got your point.

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

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

From: pgsql-hackers-owner@postgresql.org

[mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Amit Kapila
Okay, not a problem. However, I am not sure the results in this thread
are sufficient proof as for read-only tests, there is no noticeable win
by increasing shared buffers and read-write tests seems to be quite short
(60 seconds) to rely on it.

I think the reason why increasing shared_buffers didn't give better performance for read-only tests than you expect is that the relation files are cached in the filesystem cache. The purpose of this verification is to know that the effective upper limit is not 512MB (which is too small now), and I think the purpose is achieved. There may be another threshold, say 32GB or 128GB, over which the performance degrades due to PostgreSQL implementation, but that's another topic which also applies to other OSes.

How about 3 minutes for read-write tests? How long do you typically run?

Regards
Takayuki Tsunakawa

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

On Tue, Nov 15, 2016 at 7:14 AM, Tsunakawa, Takayuki
<tsunakawa.takay@jp.fujitsu.com> wrote:

From: pgsql-hackers-owner@postgresql.org

[mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Amit Kapila
Okay, not a problem. However, I am not sure the results in this thread
are sufficient proof as for read-only tests, there is no noticeable win
by increasing shared buffers and read-write tests seems to be quite short
(60 seconds) to rely on it.

I think the reason why increasing shared_buffers didn't give better performance for read-only tests than you expect is that the relation files are cached in the filesystem cache. The purpose of this verification is to know that the effective upper limit is not 512MB (which is too small now), and I think the purpose is achieved. There may be another threshold, say 32GB or 128GB, over which the performance degrades due to PostgreSQL implementation, but that's another topic which also applies to other OSes.

If we don't get any benefit by increasing the shared_buffers on
windows, then what advantage do you see in recommending higher value?

How about 3 minutes for read-write tests? How long do you typically run?

I generally run it for 20 to 30 mins for read-write tests. Also, to
ensure consistent data, please consider changing following parameters
in postgresql.conf
checkpoint_timeout = 35 minutes or so, min_wal_size = 5GB or so,
max_wal_size = 20GB or so and checkpoint_completion_target=0.9.

Apart from above, ensure to run manual checkpoint (checkpoint command)
after each test.

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

--
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, Nov 11, 2016 at 10:03 PM, Amit Kapila <amit.kapila16@gmail.com> wrote:

Right, but for other platforms, the recommendation seems to be 25% of
RAM, can we safely say that for Windows as well? As per test results
in this thread, it seems the read-write performance degrades when
shared buffers have increased from 12.5 to 25%. I think as the test
is done for a short duration so that degradation could be just a run
to run to run variation, that's why I suggested doing few more tests.

Really, 25% of RAM with no upper limit? I've usually heard 25% of RAM
with a limit of 8GB, or less. I suspect that the performance for
large values of shared_buffers has improved in recent releases, but
there's one huge reason for not going too high: you're going to get
double buffering between shared_buffers and the OS cache, and the more
you raise shared_buffers, the worse that double-buffering is going to
get. Now, on the flip side, on a write-heavy workload, raising
shared_buffers will reduce the rate at which dirty buffers are evicted
which may save substantial amounts of I/O. But if you have, say, a
200GB and 128GB of RAM, would you really set shared_buffers to 32GB?
I wouldn't.

We're not really going to get out from under these issues until we
rewrite the system not to depend on buffered I/O, but I haven't gotten
around to that yet. :-)

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

From: pgsql-hackers-owner@postgresql.org

[mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Amit Kapila

I think the reason why increasing shared_buffers didn't give better

performance for read-only tests than you expect is that the relation files
are cached in the filesystem cache. The purpose of this verification is
to know that the effective upper limit is not 512MB (which is too small
now), and I think the purpose is achieved. There may be another threshold,
say 32GB or 128GB, over which the performance degrades due to PostgreSQL
implementation, but that's another topic which also applies to other OSes.

If we don't get any benefit by increasing the shared_buffers on windows,
then what advantage do you see in recommending higher value?

No, I'm not recommending a higher value, but just removing the doubtful sentences of 512MB upper limit. The advantage is that eliminating this sentence will make a chance for users to try best setting.

I generally run it for 20 to 30 mins for read-write tests. Also, to ensure
consistent data, please consider changing following parameters in
postgresql.conf checkpoint_timeout = 35 minutes or so, min_wal_size = 5GB
or so, max_wal_size = 20GB or so and checkpoint_completion_target=0.9.

Apart from above, ensure to run manual checkpoint (checkpoint command) after
each test.

Thank you, I'll try the read-write test with these settings on the weekend, when my PC is available. I understood that your intention is to avoid being affected by checkpointing and WAL segment creation.

Regards
Takayuki Tsunakawa

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

On 11/17/16 12:30 AM, Tsunakawa, Takayuki wrote:

No, I'm not recommending a higher value, but just removing the doubtful sentences of 512MB upper limit. The advantage is that eliminating this sentence will make a chance for users to try best setting.

I think this is a good point. The information is clearly
wrong/outdated. We have no new better information, but we should remove
misleading outdated advice and let users find new advice. Basically,
this just puts Windows on par with other platforms with regard to
shared_buffers tuning, doesn't it?

I'm inclined to commit the original patch if there are no objections.

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

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

From: Tsunakawa, Takayuki/綱川 貴之

Thank you, I'll try the read-write test with these settings on the weekend,
when my PC is available. I understood that your intention is to avoid being
affected by checkpointing and WAL segment creation.

The result looks nice as follows. I took the mean value of three runs.

shared_buffers tps
256MB 990
512MB 813
1GB 1189
2GB 2258
4GB 5003
8GB 5062

"512MB is the largest effective size" seems to be a superstition, although I don't know the reason for the drop at 512MB.

Regards
Takayuki Tsunakawa

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

On Mon, Nov 21, 2016 at 5:22 AM, Tsunakawa, Takayuki
<tsunakawa.takay@jp.fujitsu.com> wrote:

From: Tsunakawa, Takayuki/綱川 貴之

Thank you, I'll try the read-write test with these settings on the weekend,
when my PC is available. I understood that your intention is to avoid being
affected by checkpointing and WAL segment creation.

The result looks nice as follows. I took the mean value of three runs.

shared_buffers tps
256MB 990
512MB 813
1GB 1189
2GB 2258
4GB 5003
8GB 5062

"512MB is the largest effective size" seems to be a superstition, although I don't know the reason for the drop at 512MB.

It is difficult to say why the performance drops at 512MB, it could be
run-to-run variation. How long have you run each test?

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

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

From: pgsql-hackers-owner@postgresql.org

[mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Amit Kapila

shared_buffers tps
256MB 990
512MB 813
1GB 1189
2GB 2258
4GB 5003
8GB 5062

"512MB is the largest effective size" seems to be a superstition, although

I don't know the reason for the drop at 512MB.

It is difficult to say why the performance drops at 512MB, it could be
run-to-run variation. How long have you run each test?

5 minutes (-T 300). I avoided 20-30 minutes runs for fear of wearing out and destroying my disk...

Regards
Takayuki Tsunakawa

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

On Mon, Nov 21, 2016 at 7:46 AM, Tsunakawa, Takayuki
<tsunakawa.takay@jp.fujitsu.com> wrote:

From: pgsql-hackers-owner@postgresql.org

[mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Amit Kapila

shared_buffers tps
256MB 990
512MB 813
1GB 1189
2GB 2258
4GB 5003
8GB 5062

"512MB is the largest effective size" seems to be a superstition, although

I don't know the reason for the drop at 512MB.

Okay, not an issue. I think above data indicates that we can remove
512MB limit for Windows from docs.

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

--
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, Nov 18, 2016 at 3:23 PM, Peter Eisentraut
<peter.eisentraut@2ndquadrant.com> wrote:

On 11/17/16 12:30 AM, Tsunakawa, Takayuki wrote:

No, I'm not recommending a higher value, but just removing the doubtful sentences of 512MB upper limit. The advantage is that eliminating this sentence will make a chance for users to try best setting.

I think this is a good point. The information is clearly
wrong/outdated. We have no new better information, but we should remove
misleading outdated advice and let users find new advice. Basically,
this just puts Windows on par with other platforms with regard to
shared_buffers tuning, doesn't it?

I'm inclined to commit the original patch if there are no objections.

+1.

--
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 Tuesday, November 22, 2016, Robert Haas <robertmhaas@gmail.com> wrote:

On Fri, Nov 18, 2016 at 3:23 PM, Peter Eisentraut
<peter.eisentraut@2ndquadrant.com <javascript:;>> wrote:

On 11/17/16 12:30 AM, Tsunakawa, Takayuki wrote:

No, I'm not recommending a higher value, but just removing the doubtful

sentences of 512MB upper limit. The advantage is that eliminating this
sentence will make a chance for users to try best setting.

I think this is a good point. The information is clearly
wrong/outdated. We have no new better information, but we should remove
misleading outdated advice and let users find new advice. Basically,
this just puts Windows on par with other platforms with regard to
shared_buffers tuning, doesn't it?

I'm inclined to commit the original patch if there are no objections.

+1.

+1. In light of the further data that's in, my earlier objection is
withdrawn :)

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

On 11/7/16 12:43 AM, amul sul wrote:

On Mon, Nov 7, 2016 at 10:46 AM, Tsunakawa, Takayuki
<tsunakawa.takay@jp.fujitsu.com> wrote:

The third paragraph may be redundant, I'm a bit inclined to leave it for kindness and completeness. The attached revised patch just correct the existing typo (large -> larger).

I am not agree to having this paragraph either, I'll leave the
decision to committer.

I'll change the status to needs review.

The new status of this patch is: Ready for Committer

committed

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

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