Re: hint in determining effective_io_concurrency

Hi all,
I'm unable to find (apparently) a way to find out a possible value to
start with for effective_io_concurrency.
I suspect that benchmarking, e.g., using bonnie++ or sysbench and
testing with different values of concurrency could help to determine
the max number of concurrent request, (tps, lower latency, ecc.).
Is thjs correct or is there another suggested way?

Thanks,
Luca

On Thu, Apr 22, 2021 at 09:45:15PM +0200, Luca Ferrari wrote:
> Hi all,
> I'm unable to find (apparently) a way to find out a possible value to
> start with for effective_io_concurrency.
> I suspect that benchmarking, e.g., using bonnie++ or sysbench and
> testing with different values of concurrency could help to determine
> the max number of concurrent request, (tps, lower latency, ecc.).
> Is thjs correct or is there another suggested way?

I recommend 256 for SSDs or other RAM-like fsync systems, and maybe
maybe 16 for magnetic.

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

If only the physical world exists, free will is an illusion.

On Thu, Apr 22, 2021 at 9:52 PM Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>
> On Thu, Apr 22, 2021 at 09:45:15PM +0200, Luca Ferrari wrote:
> > Hi all,
> > I'm unable to find (apparently) a way to find out a possible value to
> > start with for effective_io_concurrency.
> > I suspect that benchmarking, e.g., using bonnie++ or sysbench and
> > testing with different values of concurrency could help to determine
> > the max number of concurrent request, (tps, lower latency, ecc.).
> > Is thjs correct or is there another suggested way?
>
> I recommend 256 for SSDs or other RAM-like fsync systems, and maybe
> maybe 16 for magnetic.

Thanks Bruce, this is a very good starting point.
But is there a rationale about those numbers? I mean, if I change the
storage system, how should I set a correct number?

Luca

On Thu, Apr 22, 2021 at 09:54:56PM +0200, Luca Ferrari wrote:
> On Thu, Apr 22, 2021 at 9:52 PM Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> >
> > On Thu, Apr 22, 2021 at 09:45:15PM +0200, Luca Ferrari wrote:
> > > Hi all,
> > > I'm unable to find (apparently) a way to find out a possible value to
> > > start with for effective_io_concurrency.
> > > I suspect that benchmarking, e.g., using bonnie++ or sysbench and
> > > testing with different values of concurrency could help to determine
> > > the max number of concurrent request, (tps, lower latency, ecc.).
> > > Is thjs correct or is there another suggested way?
> >
> > I recommend 256 for SSDs or other RAM-like fsync systems, and maybe
> > maybe 16 for magnetic.
>
>
> Thanks Bruce, this is a very good starting point.
> But is there a rationale about those numbers? I mean, if I change the
> storage system, how should I set a correct number?

Uh, you need to study the queue length of the device to see how many
concurrent requests it can process.

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

If only the physical world exists, free will is an illusion.

On Thu, Apr 22, 2021 at 03:52:32PM -0400, Bruce Momjian wrote:
> On Thu, Apr 22, 2021 at 09:45:15PM +0200, Luca Ferrari wrote:
> > Hi all,
> > I'm unable to find (apparently) a way to find out a possible value to
> > start with for effective_io_concurrency.
> > I suspect that benchmarking, e.g., using bonnie++ or sysbench and
> > testing with different values of concurrency could help to determine
> > the max number of concurrent request, (tps, lower latency, ecc.).
> > Is thjs correct or is there another suggested way?
>
> I recommend 256 for SSDs or other RAM-like fsync systems, and maybe
> maybe 16 for magnetic.

Note that the interpretation of this GUC changed in v13.
https://www.postgresql.org/docs/13/release-13.html
|Change the way non-default effective_io_concurrency values affect concurrency (Thomas Munro)
|Previously, this value was adjusted before setting the number of concurrent requests. The value is now used directly. Conversion of old values to new ones can be done using:
|SELECT round(sum(OLDVALUE / n::float)) AS newvalue FROM generate_series(1, OLDVALUE) s(n);

--
Justin

On Thu, Apr 22, 2021 at 10:15 PM Justin Pryzby <pryzby(at)telsasoft(dot)com> wrote:
> Note that the interpretation of this GUC changed in v13.
> https://www.postgresql.org/docs/13/release-13.html
> |Change the way non-default effective_io_concurrency values affect concurrency (Thomas Munro)
> |Previously, this value was adjusted before setting the number of concurrent requests. The value is now used directly. Conversion of old values to new ones can be done using:
> |SELECT round(sum(OLDVALUE / n::float)) AS newvalue FROM generate_series(1, OLDVALUE) s(n);
>

Yeah, I know, thanks.
However, I'm still curious about which tools to use to get info about
the storage queue/concurrency.

Luca

On Thu, Apr 22, 2021 at 10:22:59PM +0200, Luca Ferrari wrote:
> On Thu, Apr 22, 2021 at 10:15 PM Justin Pryzby <pryzby(at)telsasoft(dot)com> wrote:
> > Note that the interpretation of this GUC changed in v13.
> > https://www.postgresql.org/docs/13/release-13.html
> > |Change the way non-default effective_io_concurrency values affect concurrency (Thomas Munro)
> > |Previously, this value was adjusted before setting the number of concurrent requests. The value is now used directly. Conversion of old values to new ones can be done using:
> > |SELECT round(sum(OLDVALUE / n::float)) AS newvalue FROM generate_series(1, OLDVALUE) s(n);
>
> Yeah, I know, thanks.
> However, I'm still curious about which tools to use to get info about
> the storage queue/concurrency.

I think you'd run something like iostat -dkx 1 and watch avgqu-sz.

--
Justin

On Thu, Apr 22, 2021 at 03:27:39PM -0500, Justin Pryzby wrote:
> On Thu, Apr 22, 2021 at 10:22:59PM +0200, Luca Ferrari wrote:
> > On Thu, Apr 22, 2021 at 10:15 PM Justin Pryzby <pryzby(at)telsasoft(dot)com> wrote:
> > > Note that the interpretation of this GUC changed in v13.
> > > https://www.postgresql.org/docs/13/release-13.html
> > > |Change the way non-default effective_io_concurrency values affect concurrency (Thomas Munro)
> > > |Previously, this value was adjusted before setting the number of concurrent requests. The value is now used directly. Conversion of old values to new ones can be done using:
> > > |SELECT round(sum(OLDVALUE / n::float)) AS newvalue FROM generate_series(1, OLDVALUE) s(n);
> >
> > Yeah, I know, thanks.
> > However, I'm still curious about which tools to use to get info about
> > the storage queue/concurrency.
>
> I think you'd run something like iostat -dkx 1 and watch avgqu-sz.

FYI, avgqu-sz, as far as I know, is the OS queue size, not the device
queue size:

aqu-sz
The average queue length of the requests that were issued to the device.
Note: In previous versions, this field was known as avgqu-sz.

Is that requests that were issued to the device from user-space, or
requests that were issued to the device by the kernel? I can't tell if:

https://www.circonus.com/2017/07/monitoring-queue-sizes/

clarifies this, but this says it is the former:

https://coderwall.com/p/utc42q/understanding-iostat

The avgqu-sz metric is an important value. Its name is rather poorly
chosen as it does not in fact show the number of operations queued but
not yet serviced. Instead, it shows the number of operations that were
either queued or being serviced. Ideally you want to have an idea of the
value of this metric during normal operations for use as a reference
when trouble occurs. Single digit numbers with the occasional double
digit spike are safe(ish) values. Triple digit numbers are generally
not.

It think it is the former, or pending OS I/O, whether that I/O request
has been sent to the device, or is waiting to be sent. For example, if
the device queue sizxe is 8, and the OS avgqu-sz is 12, it means 8 have
been sent to the device, and four are pending to be send to the device,
or at least that is how I understand it. Therefore, I am unclear if
avgqu-sz helps here.

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

If only the physical world exists, free will is an illusion.

On Thu, Apr 22, 2021 at 2:55 PM Luca Ferrari <fluca1978(at)gmail(dot)com> wrote:
>
> On Thu, Apr 22, 2021 at 9:52 PM Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> >
> > On Thu, Apr 22, 2021 at 09:45:15PM +0200, Luca Ferrari wrote:
> > > Hi all,
> > > I'm unable to find (apparently) a way to find out a possible value to
> > > start with for effective_io_concurrency.
> > > I suspect that benchmarking, e.g., using bonnie++ or sysbench and
> > > testing with different values of concurrency could help to determine
> > > the max number of concurrent request, (tps, lower latency, ecc.).
> > > Is thjs correct or is there another suggested way?
> >
> > I recommend 256 for SSDs or other RAM-like fsync systems, and maybe
> > maybe 16 for magnetic.
>
>
> Thanks Bruce, this is a very good starting point.
> But is there a rationale about those numbers? I mean, if I change the
> storage system, how should I set a correct number?

See thread, https://postgrespro.com/list/thread-id/2069516

The setting only impacts certain scan operations, it's not a gamechanger.

merlin

On Wed, Jul 7, 2021 at 11:42 PM Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> See thread, https://postgrespro.com/list/thread-id/2069516
>
> The setting only impacts certain scan operations, it's not a gamechanger.

Useful, thanks.

Luca