[C++] Investigate reducing I/O thread pool size to avoid CPU wastage.

[asfimport]

If we are reading over HTTP (e.g. S3) we generally want high parallelism in the I/O thread pool.

If we are reading from disk then high parallelism is usually harmless but ineffective. Most of the I/O threads will spend their time in a waiting state and the cores can be used for other work.

However, it appears that when we are reading locally, and the data is cached in memory, then having too much parallelism will be harmful, but some parallelism is beneficial. Once the DRAM <-> CPU bandwidth limit is hit then all reading threads will experience high DRAM latency. Unlike an I/O bottleneck a RAM bottleneck will waste cycles on the physical core.

Reporter: Weston Pace / @westonpace

Related issues:

• [C++] Allow setting IO thread pool size with an environment variable (relates to)

_{Note: This issue was originally created as ARROW-14354. Please see the migration documentation for further details.}

[asfimport]

Antoine Pitrou / @pitrou:
Does this stem from an actual experimentation?

[asfimport]

Antoine Pitrou / @pitrou:
Note that there is a single global IO threadpool currently, but we could also have per-filesystem thread pools (that's why each filesystem has its own IOContext). That may be better than trying to make the global thread pool fil all sizes.

[asfimport]

Weston Pace / @westonpace:
Yes, this stems from my initial stab at TPC-H profiling. I was running a basic scan->filter->project query through the exec plan and for both parquet and IPC formats the bottleneck was RAM (on my system at least). I was using cached files so no actual I/O needed to be done.

Right now, the parquet format is doing its reads on the CPU thread pool (a potentially separate problem) so as you adjust the size of the CPU thread pool the wall clock time doesn't change (past 4 threads when the RAM bottleneck is hit) but the process time does. For example, running with 16 thread both Intel and perf report an average core utilization of ~14.5. Running with 4 threads I get an average core utilization of ~4 and the wall clock time is the same for both. With IPC things are a little different because the IPC file format is correctly using the I/O executor. So regardless of what I set the CPU thread pool count to (as long as it is above 4) the core utilization is ~8 and the wall clock time is the same.

I do think we can probably set this as a filesystem property. I don't really have enough experience with different disks (e.g. supposedly some SSDs have decent support for parallel reads) but we probably don't need very many threads for a local filesystem.

On that note: For datasets, the IOContext (and thus the IO executor) is currently passed in via scan options. Should this be obtained from the filesystem instead?

[asfimport]

Antoine Pitrou / @pitrou:

Right now, the parquet format is doing its reads on the CPU thread pool (a potentially separate problem)
Hmm, ok, so reducing the IO thread pool size wouldn't fix this particular issue (of Parquet performance), right?
we probably don't need very many threads for a local filesystem.
That's also my intuition. Might be worth checking the policy used by Postgres, MariaDB and other well-tuned database engines.

As a semi-separate thought, for local filesystem access we may want to first try a non-blocking read on the current thread before deferring to the IO thread. That would avoid some thread synchronization latency if the data is already available; but might add some overhead if the non-blocking read fails.

For datasets, the IOContext (and thus the IO executor) is currently passed in via scan options. Should this be obtained from the filesystem instead?
Hmm, ideally the user should be able to override the IO context but the default IO context (if not overriden) should be filesystem-decided. Perhaps we need to pass nullptr to say "use the default" (is it already the case?).

 

[asfimport]

Weston Pace / @westonpace:

Hmm, ok, so reducing the IO thread pool size wouldn't fix this particular issue (of Parquet performance), right?

Correct

That's also my intuition. Might be worth checking the policy used by Postgres, MariaDB and other well-tuned database engines.

Good idea, I'll look into it.

Hmm, ideally the user should be able to override the IO context but the default IO context (if not overriden) should be filesystem-decided. Perhaps we need to pass nullptr to say "use the default" (is it already the case?).

Right now IOContext is passed by value. There is arrow::io::default_io_context which is often used as a default method parameter but it is global and not based on any filesystem. We can tackle this I suppose as soon as we have a good reason to differentiate (which may be a result of this issue).

[asfimport]

Weston Pace / @westonpace:
From the postgres docs:

effective_io_concurrency (integer)

Sets the number of concurrent disk I/O operations that PostgreSQL expects can be executed simultaneously. Raising this value will increase the number of I/O operations that any individual PostgreSQL session attempts to initiate in parallel. The allowed range is 1 to 1000, or zero to disable issuance of asynchronous I/O requests. Currently, this setting only affects bitmap heap scans.

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.

For more exotic systems, such as memory-based storage or a RAID array that is limited by bus bandwidth, the correct value might be the number of I/O paths available. Some experimentation may be needed to find the best value.

Asynchronous I/O depends on an effective posix_fadvise function, which some operating systems lack. If the function is not present then setting this parameter to anything but zero will result in an error. On some operating systems (e.g., Solaris), the function is present but does not actually do anything.

SQL Server seems to have a much more complex design. I did find some interesting information. One thing to note is that they have their own page cache so in-memory reads aren't really something they need to worry about. For actual disk I/O it appears they do something rather complicated:

A statement was published many years ago that sustained disk queue length greater than 2 is an indication of an I/O bottleneck. This statement is still true if the application is not designed to handle the situation. SQL Server is designed to push disk queue lengths above 2 when it is appropriate.

SQL Server uses async I/O to help maximize resource usage. SQL Server understands that it can hand off an I/O request to the I/O subsystem and continue with other activity. Let’s look an example of this.

SQL Server checkpoint posts up to 100 I/O requests and monitors the I/O response time in order to properly throttle checkpoint impact. When the I/O response time exceeds the target the number of I/Os is throttled. The disk queue length can easily exceed 2 and not be an indication of a subsystem problem. SQL Server is attempting to maximize the I/O channel.

If I read these things correctly they basically keep adding concurrent requests until the response times start to slow down.

[github-actions]

This issue has been marked as stale because it has had no activity in the past 365 days. Please remove the stale label or comment below, or this issue will be closed in 14 days. If this improvement is still desired but has no current owner, please add the 'Status: needs champion' label.