Re: effective_io_concurrency increasing

On Mon, Jun 19, 2017 at 10:49:59AM -0500, Merlin Moncure wrote:
> On Mon, Jun 19, 2017 at 10:36 AM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
> > If you have a RAID, set it to the number of spindles in your RAID and forget
> > it. It is usually one of the less interesting knobs to play with. (Unless
> > your usage pattern of the database is unusual and exact fits the above
> > pattern.)
>
> Isn't that advice obsolete in a SSD world though? I was able to show
> values up to 256 for a single device provided measurable gains for a
> single S3500. It's true though that the class of queries that this
> would help is pretty narrow.

Our developer docs are much clearer:

https://www.postgresql.org/docs/10/static/runtime-config-resource.html#runtime-config-resource-disk

For magnetic drives, a good starting point for this setting is the
number of separate drives comprising a RAID 0 stripe or RAID 1 mirror
being used for the database. (For RAID 5 the parity drive should not be
counted.) However, if the database is often busy with multiple queries
issued in concurrent sessions, lower values may be sufficient to keep
the disk array busy. A value higher than needed to keep the disks busy
will only result in extra CPU overhead. SSDs and other memory-based
storage can often process many concurrent requests, so the best value
might be in the hundreds.

I didn't backpatch this change since the original docs were not
incorrect.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +