Remove the comment on the countereffectiveness of large shared_buffers on Windows

Hello,

> From: pgsql-hackers-owner(at)postgresql(dot)org
> [mailto:pgsql-hackers-owner(at)postgresql(dot)org] On Behalf Of Magnus Hagander
> On Wed, Aug 24, 2016 at 4:35 AM, Tsunakawa, Takayuki
> <tsunakawa(dot)takay(at)jp(dot)fujitsu(dot)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

Regards
Takayuki Tsunakawa

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

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

From: amul sul [mailto:sulamul(at)gmail(dot)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(dot)takay(at)jp(dot)fujitsu(dot)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

On Tue, Sep 20, 2016 at 8:15 AM, Tsunakawa, Takayuki
<tsunakawa(dot)takay(at)jp(dot)fujitsu(dot)com> wrote:
> Hello,
>
>> From: pgsql-hackers-owner(at)postgresql(dot)org
>> [mailto:pgsql-hackers-owner(at)postgresql(dot)org] On Behalf Of Magnus Hagander
>> On Wed, Aug 24, 2016 at 4:35 AM, Tsunakawa, Takayuki
>> <tsunakawa(dot)takay(at)jp(dot)fujitsu(dot)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

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

> On Tue, Sep 20, 2016 at 8:15 AM, Tsunakawa, Takayuki
> <tsunakawa(dot)takay(at)jp(dot)fujitsu(dot)com> wrote:
> > Hello,
> >
> >> From: pgsql-hackers-owner(at)postgresql(dot)org
> >> [mailto:pgsql-hackers-owner(at)postgresql(dot)org] On Behalf Of Magnus
> Hagander
> >> On Wed, Aug 24, 2016 at 4:35 AM, Tsunakawa, Takayuki
> >> <tsunakawa(dot)takay(at)jp(dot)fujitsu(dot)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(dot)janes(at)gmail(dot)com> wrote:
> On Mon, Nov 7, 2016 at 5:55 AM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>>
>> On Tue, Sep 20, 2016 at 8:15 AM, Tsunakawa, Takayuki
>> <tsunakawa(dot)takay(at)jp(dot)fujitsu(dot)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

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

> On Tue, Nov 8, 2016 at 5:12 AM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
> > On Mon, Nov 7, 2016 at 5:55 AM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>
> wrote:
> >>
> >> On Tue, Sep 20, 2016 at 8:15 AM, Tsunakawa, Takayuki
> >> <tsunakawa(dot)takay(at)jp(dot)fujitsu(dot)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(at)postgresql(dot)org
> [mailto:pgsql-hackers-owner(at)postgresql(dot)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

On Fri, Nov 11, 2016 at 1:54 AM, Tsunakawa, Takayuki <
tsunakawa(dot)takay(at)jp(dot)fujitsu(dot)com> wrote:

> From: pgsql-hackers-owner(at)postgresql(dot)org
> > [mailto:pgsql-hackers-owner(at)postgresql(dot)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(at)hagander(dot)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

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

> On Fri, Nov 11, 2016 at 3:01 PM, Magnus Hagander <magnus(at)hagander(dot)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(dot)kapila16(at)gmail(dot)com> wrote:

> On Tue, Nov 8, 2016 at 5:12 AM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
> > On Mon, Nov 7, 2016 at 5:55 AM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)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(at)hagander(dot)net> wrote:
> On Sat, Nov 12, 2016 at 4:03 AM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>
> wrote:
>>
>> On Fri, Nov 11, 2016 at 3:01 PM, Magnus Hagander <magnus(at)hagander(dot)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

On Sun, Nov 13, 2016 at 1:23 AM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
> On Mon, Nov 7, 2016 at 7:03 PM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>>
>> On Tue, Nov 8, 2016 at 5:12 AM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
>> > On Mon, Nov 7, 2016 at 5:55 AM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)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

From: pgsql-hackers-owner(at)postgresql(dot)org
> [mailto:pgsql-hackers-owner(at)postgresql(dot)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

On Tue, Nov 15, 2016 at 7:14 AM, Tsunakawa, Takayuki
<tsunakawa(dot)takay(at)jp(dot)fujitsu(dot)com> wrote:
> From: pgsql-hackers-owner(at)postgresql(dot)org
>> [mailto:pgsql-hackers-owner(at)postgresql(dot)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

On Fri, Nov 11, 2016 at 10:03 PM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)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

From: pgsql-hackers-owner(at)postgresql(dot)org
> [mailto:pgsql-hackers-owner(at)postgresql(dot)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

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

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

On Mon, Nov 21, 2016 at 5:22 AM, Tsunakawa, Takayuki
<tsunakawa(dot)takay(at)jp(dot)fujitsu(dot)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

From: pgsql-hackers-owner(at)postgresql(dot)org
> [mailto:pgsql-hackers-owner(at)postgresql(dot)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

On Mon, Nov 21, 2016 at 7:46 AM, Tsunakawa, Takayuki
<tsunakawa(dot)takay(at)jp(dot)fujitsu(dot)com> wrote:
> From: pgsql-hackers-owner(at)postgresql(dot)org
>> [mailto:pgsql-hackers-owner(at)postgresql(dot)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

On Fri, Nov 18, 2016 at 3:23 PM, Peter Eisentraut
<peter(dot)eisentraut(at)2ndquadrant(dot)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

On Tuesday, November 22, 2016, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> On Fri, Nov 18, 2016 at 3:23 PM, Peter Eisentraut
> <peter(dot)eisentraut(at)2ndquadrant(dot)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(dot)takay(at)jp(dot)fujitsu(dot)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