Thread: 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@momjian.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@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
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.
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. Conversionof 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@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
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
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.
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
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