Hello,
The iostat looks fine with utilization of 4.3%.
Did you run iostat while running the queries? You can run it at fixed interval, e.g. iostat -xdh -t 1 <device_name>.
Also, have you checked CPU load? It looks like you’re running multiple queries concurrently, so if one of them is “heavier” than others, this could explain why certain queries have to wait in the queue occasionally, leading to latency spikes.
Bingo!!! Thanks for the idea and the interval scan of iostat. Pinpointed the issue to the IO and observed that %util was hitting 100% when the query was getting stalled. The rkB/s was reaching 200 to 450M.
Yes, I am running multiple queries (multi-processing from python) across various days of the summarized tick data in order to respond quickly to analytics. The CPU load was < 20%.
Any ideas on how to reduce the I/O hits. I tried the touch idea proposed by @nwoolmer but get the following error message : “query does not support framing execution and cannot be pre-touched”. So I am running a summary count query on the table.
Do you think moving the partitions / table to parquet will help?
thanks.