I have a system where the customer mentions that they are facing a high I/O wait issue in general. Sometimes it can go upto 20%.During backlog processing i saw it go upto 60% as well.
In order to remedy the situaiton i have made a few generic checks, can someone please let me know which of these have direct impact on wait I/O
1. Their queries are sometimes having high I/O skew like 99% in the load jobs .
2.I can see high spool queries.
3. I can see that they have used set tables in general and using PI in fastloads because due to parser limitations they are doing fastloads into a single PI based table form 4 jobs. and this is rampant throughout their design,
4. I have seen that locking modifires are applied are locking table for access or not applied at all in their views and macros.There is no concept of locking row for access. Do you think that this can also be leading to high I/O wait as locking entire table results in lock contention. They havent faced deadlock issues but what i can see is that it leads to waiting time for I/O while waiting for a lock to get relased. If i fix these ,can i expect this to have a significat or moderate impact on the system? Exactly how is this linked to I/O wait percent
5.From which Reusage tables can i see I/O wait percents etc. is there any generic article related to I/O
Wait I/O by itself isn't necessarily a problem, particularly at low concurrency. Even SSD is much slower than CPU so a single query step will often have to wait on I/O. If you have a large number of requests all waiting for I/O then that's a symptom of some other problem.
1. I/O skew could be a problem worth addressing. But be careful - if you look at the parallel "special sessions" for FastLoad in Viewpoint, for example, they will generally report high skew (because by design data blocks are sent to one AMP at a time).
2. Yes, high spool can be an issue worth tuning; potentially could be related to Wait I/O.
3. Not sure I understand this point. If the PI is highly non-unique then SET tables introduce additional overhead that would be an opportunity for tuning. But typically it's more of a CPU impact (which would have the effect of reducing reported Wait I/O).
4. Locking modifiers impact Wait I/O only to the extent that lock conflicts are reducing concurrency (so the system has nothing it can work on while other requests are waiting for I/O).
5. Wait I/O is reported in Spma (at node level). But I would focus on other things - CPU and I/O skew, concurrency (throttles), queries with high UII, ReqPhysIO, or ReqPhysIOKB, etc. and not Wait I/O.