Horton

Thread

  1. hint in determining effective_io_concurrency

    Luca Ferrari <fluca1978@gmail.com> — 2021-04-22T19:45:15Z

    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

  2. Re: hint in determining effective_io_concurrency

    Bruce Momjian <bruce@momjian.us> — 2021-04-22T19:52:32Z

    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@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

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

  3. Re: hint in determining effective_io_concurrency

    Luca Ferrari <fluca1978@gmail.com> — 2021-04-22T19:54:56Z

    On Thu, Apr 22, 2021 at 9:52 PM Bruce Momjian <bruce@momjian.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

  4. Re: hint in determining effective_io_concurrency

    Bruce Momjian <bruce@momjian.us> — 2021-04-22T19:58:27Z

    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@momjian.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@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

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

  5. Re: hint in determining effective_io_concurrency

    Justin Pryzby <pryzby@telsasoft.com> — 2021-04-22T20:15:06Z

    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

  6. Re: hint in determining effective_io_concurrency

    Luca Ferrari <fluca1978@gmail.com> — 2021-04-22T20:22:59Z

    On Thu, Apr 22, 2021 at 10:15 PM Justin Pryzby <pryzby@telsasoft.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

  7. Re: hint in determining effective_io_concurrency

    Justin Pryzby <pryzby@telsasoft.com> — 2021-04-22T20:27:39Z

    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@telsasoft.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

  8. Re: hint in determining effective_io_concurrency

    Bruce Momjian <bruce@momjian.us> — 2021-04-22T20:51:57Z

    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@telsasoft.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@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

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

  9. Re: hint in determining effective_io_concurrency

    Merlin Moncure <mmoncure@gmail.com> — 2021-07-07T21:42:40Z

    On Thu, Apr 22, 2021 at 2:55 PM Luca Ferrari <fluca1978@gmail.com> wrote:
>
> On Thu, Apr 22, 2021 at 9:52 PM Bruce Momjian <bruce@momjian.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

  10. Re: hint in determining effective_io_concurrency

    Luca Ferrari <fluca1978@gmail.com> — 2021-07-08T06:50:30Z

    On Wed, Jul 7, 2021 at 11:42 PM Merlin Moncure <mmoncure@gmail.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