a week ago
Hi IQ Experts,
We have a parallel workloads that run stored procedures and SQL concurrently including LOCK TABLE IN WRITE MODE with 5 minute waits. Our server is running RHEL 8.10 on XFS filesystems with -iqnumbercpus set to 16
As part of an upgrade from SP05 PL09 to PL16 we have increased the -iqmt from the default to OS maximum 3926 which is recommended for AIO in "KBA 2936708 - Thread usage increases through the introduction of AIO feature post upgrading".
With this configuration we have noticed that the stored procs workloads run up to 10% faster in series. However when run in parallel they are about 3-6% slower with some slower SQL and extended LOCK TABLE wait times. Could this be due to the -iqmt change? Is recommendation for the OS maximum not applicable for all workloads?
Regards, KC
Request clarification before answering.
Yes, increasing the # of threads could be contributing to the longer processing time for concurrent operations. Increasing the -iqmt allows more 'stuff' to execute concurrently since more threads are created. However, those 'extra' threads are still running on the same 16 CPUs so there isn't any increase in the total compute power available. The slower execution time for operations running in parallel would be consistent with increased CPU contention.
What do you see reported for CPU utilization?
The recommendation from KBA 2936708 to increase the # of threads is to take advantage of the async IO by providing more threads for the IO operations. Since IO operations have a high latency compared to in memory operations, adding extra threads for IO won't introduce much extra load on the CPUs, rather it will reduce time spent waiting for IO operations.
Like all recommendations for default settings, the recommendation to set -iqmt to the max allowed by the OS is intended to be a good starting point. That doesn't mean that it will be the optimal value for all systems. As a tuning step you could try setting a -iqmt value halfway between your previous setting and the OC max allowed.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
| User | Count |
|---|---|
| 8 | |
| 7 | |
| 6 | |
| 4 | |
| 3 | |
| 3 | |
| 3 | |
| 2 | |
| 2 | |
| 2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.