IO WAIT

Hello Traced some sql statements and noticed some iowaits, i checked io on chunk (-g iof) but needed also any feedbacks about analysing io wait please this example is useful to understand my case, runtime in 2 sec, but totaltime 12 sec, no locks, no sorts ...nothing but io waits see also below iof output (it s about raw devices with KAIO activated on a AIX LPAR) sql_id 0 sql_sid 1238153 sql_uid 1234 sql_runtime 2,081772664059 sql_lockreq 667 sql_lockwaits 0 sql_lockwttime 0,00 sql_logspace 0 sql_sorttotal 0 sql_sortdisk 0 sql_sortmem 0 sql_executions 47501 sql_totaltime 12,99474855827 =====================> totaltime 13 seconds sql_numiowaits 115 ===================================> 115 iowaits sql_avgiowaits 0,001369563858 sql_totaliowaits 0,157499843667 sql_sqlerror 0 sql_isamerror 0 sql_isollevel 7 sql_statement SELECT table.* FROM table WHERE Table.col1 = ? AND table.col2 = ? AND table.col3 = ? sql_pdq 0 data_chk1 463028224 113044 5292032 1292 471.1 op type count avg. time seeks 0 N/A reads 0 N/A writes 0 N/A kaio_reads 6044 0.0012 kaio_writes 1059 0.0076 ------------------------------ John Smith ------------------------------ #Informix

John: The "sql_totaltime" includes the total summary runtime across all executions of that query. The output shows that the query was executed 47,501 times so an sql_totaltime of 12.99 seconds means that the average run was only 0.00027 seconds. The 2.08 second run was just the last iteration. Also 115 IO waits across 47,501 runs of the query is excellent. Most iowaits are just normal because the data needed for the query is not yet in the buffers and the Did you actually observe any delays in processing or are just concerned by the SQLTRACE details? Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Thanks for the explanation Art :) in fact its needed to be very quick, less than 1 sec, and this 2 sec duration is the max i found within 2 hours tracing about buffers i also checked with your ratios script and no btrs are good i see, output after a full day from 8am to 8pm which 4K br 0,24 rau -1,00 btr 5,27 ubtr 5,27 btr2 1,87 btr3 4,68 lwr 0,00 ssr 0,00 since_reset 1:25:33 reset_time 2022-10-07 19:04:27 ------------------------------ John Smith ------------------------------

Is that the real query or just an example? You may not be able to review the one query in isolation, as there may be more going on to stretch out your execution time to 12 seconds. Get an explain plan and look at the query statistics at the end to see if this was using the expected index, rows returned, etc, but also the real timing. Is the 12 second runtime something that you have actually observed in the real world for this ONE query, or are you just looking at the sql trace How are your checkpoints looking? Are you seeing a lot of long checkpoints that might indicate an IO issue? The kaio_reads/kaio_writes are fair, and not slow enough to indicate a failing device. It would be better to use onstat -g ioh to review the recent IO while running the query several times. Also use onstat -g iov to see io/s per VP and io per wakeup. You should be looking at whether your individual cores (not overall CPU) are not running close to 100%, as there may not be enough horsepower left to perform timely IO operations. What SMT level are you using, and what CPU type? IO Waits are a normal part of a busy system, but it's always fun to keep an eye on whether there are many threads waiting on IO overall. onstat -g ath | grep "IO" Of course, the SAN can be an issue, and if anything else is sharing the disk, then that can give intermittent problems. But don't just focus on IO...there may be more at play here if you really are seeing such a long runtime for a simple query that I assume doesn't return many records. ------------------------------ Mike Walker xDB Systems, Inc www.xdbsystems.com ------------------------------

John: Yes, those metrics are all ideal range. A BTR3 of 4.5 is excellent so I don't think that it's the buffer cache sizing, though, if the parameters of that 2 second run are repeated but occasional then more buffers would improve the likelihood that the data for that iteration of the query are still in memory from a previous run. So ... If you have the memory available, go for it and see. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

OK question : if 2 users select the same table, will the table be loaded twice on bufferpool ? or only once ? ------------------------------ John Smith ------------------------------

Nope, once only, that's why it's also called buffer cache. Every page on disk can be in bufferpool only once. ------------------------------ Andreas Legner ------------------------------

Hi thanks for your reply :) No i wanted to say Sample not example, sorry :), it is a real query, ran more than 47000 times in a 2 hours intervals, and 2 sec was the max runtime.. the execution plan is good and already using a index the checkpoint are good The kaio_reads/kaio_writes are fair, and not slow enough to indicate a failing device. => OK It would be better to use onstat -g ioh to review the recent IO while running the query several times. => not available , 11.70, i know, upgrading is in the pipe :) Also use onstat -g iov to see io/s per VP and io per wakeup. ==> AIO I/O vps: class/vp/id s io/s totalops dskread dskwrite dskcopy wakeups io/wup errors tempops fifo 7 0 i 0.0 0 0 0 0 0 0.0 0 0 kio -1 0 i 762.7 15055893 14967031 88862 0 10541566 1.4 0 0 kio -1 1 i 677.2 13366528 13287533 78995 0 9308721 1.4 0 0 kio -1 2 i 633.7 12509153 12432752 76401 0 8470272 1.5 0 0 kio -1 3 i 830.6 16395165 16304576 90589 0 11533614 1.4 0 0 kio -1 4 i 649.2 12813793 12740166 73627 0 8705593 1.5 0 0 kio -1 5 i 655.5 12939753 12862025 77728 0 8756982 1.5 0 0 kio -1 6 i 515.0 10166417 10092066 74351 0 6440892 1.6 0 0 kio -1 7 i 723.8 14287930 14207722 80208 0 9894157 1.4 0 0 kio -1 8 i 497.6 9823078 9751387 71691 0 6171592 1.6 0 0 kio -1 9 i 606.0 11961931 11884235 77696 0 7913544 1.5 0 0 kio -1 10 i 544.6 10750719 10683320 67399 0 6971057 1.5 0 0 kio -1 11 i 339.3 6697720 6646076 51644 0 3854125 1.7 0 0 kio -1 12 i 396.7 7831247 7772254 58993 0 4646074 1.7 0 0 kio -1 13 i 340.3 6717816 6657758 60058 0 3943763 1.7 0 0 kio -1 14 i 553.0 10916102 10839295 76807 0 7049523 1.5 0 0 kio -1 15 i 293.1 5785159 5722695 62464 0 3253776 1.8 0 0 kio -1 16 i 436.2 8611011 8548977 62034 0 5201195 1.7 0 0 kio -1 17 i 392.6 7749044 7679118 69926 0 4581861 1.7 0 0 kio -1 18 i 537.3 10606624 10536035 70589 0 6633993 1.6 0 0 kio -1 19 i 515.6 10176536 10106638 69898 0 6267016 1.6 0 0 msc 6 0 i 4.6 91441 0 0 0 90498 1.0 0 2323503 aio 5 0 i 59.7 1178857 724749 70237 0 965543 1.2 0 9765 aio 27 1 i 12.8 253496 232102 5222 0 121332 2.1 0 732 aio 28 2 i 11.5 227851 222372 1124 0 67090 3.4 0 469 aio 29 3 i 10.3 202888 200632 601 0 56836 3.6 0 406 aio 30 4 i 10.3 203106 201597 299 0 52333 3.9 0 369 aio 31 5 i 10.2 200798 199798 151 0 49189 4.1 0 406 aio 32 6 i 9.9 195738 194887 76 0 47471 4.1 0 360 aio 33 7 i 9.6 189731 188639 278 0 46035 4.1 0 360 aio 34 8 i 9.0 178278 176784 57 0 34140 5.2 0 293 aio 35 9 i 8.8 174519 173885 33 0 30072 5.8 0 282 pio 4 0 i 0.0 0 0 0 0 0 0.0 0 0 lio 3 0 i 0.0 0 0 0 0 0 0.0 0 0 Processor Type: PowerPC_POWER8 Processor Implementation Mode: POWER 7 SMT4 ------------------------------ John Smith ------------------------------

On your buffer presence question: each page will only be loaded into the cache once if it's not already there. Whole tables are not typically loaded and even if one is, some of its pages, if not recently referenced/read/written, may be replaced by data from other tables or indexes that are needed more urgently. On the onstats you posted, all look good except that you have no aio VPs that show an io/wkup less than 1.0 indicating that the server could take advantage of more aio VPs to improve flat file and temp dbspace processing. This has nothing to do with IO in chunks that are RAW, so that does not affect this query unless it is using temp tables for sorting, grouping, intermediate results, etc. and not sorting completely in memory. If it is sorting to disk (I don't remember from the SQLTRACE output if it is or not) then this may help as well. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Thanks Art Yes i also noticed io vp / wkup :), just a littre > 1, for this moment i'm focused on this statetement , not the whole system tuning but :).. another question ; the number of kaio vp depends ont number of cpu vp, right ? (no kaio vp parameter) , so the more cpu vps we have, more kaio vps will be created ? and if more cpu/kaio vps are added, could this increase io perf ? thanks ------------------------------ John Smith ------------------------------

With such a frequent query disk i/o should not be a subject at all; it might occur with very first execution, but then never again, and the fact that it executed this many times over two hours probably only confirms this. So you don't have an i/o problem, at least not with this query, never. Now, if one execution (or some) of this query take longer than most others, that can still happen, but likely not due to disk i/o. Looking at this query, it is so straight forward and simple that I hardly could think of anything it might have to wait on, esp. if it is going by an index. Any chance the data is skewed and some values for those three parameters would have to go through and return an extra large amount of data? There also could be a remote chance of an index not cleaned properly and, maybe in certain areas only, bloated to a degree that it causes a performance problem? How big a table are we talking about? How big is the index this query relies on (per oncheck -pt), and how fresh is the index? Just another few cents ... ------------------------------ Andreas Legner ------------------------------

John: Yes, each CPU VP kicks off a second thread which is the KAIO thread (not a full VP process like the AIO VPs are) to handle IO mostly for sessions running on that VP, but also for sessions on other VPs when the system is busy. The more CPU VPs, yes, the more KAIO threads there will be. Performance might improve if: There is really an IO processing bottleneck There are sufficient KAIO resources available in the OS to handle the additional load The IO subsystem and the individual drives are not already swamped with requests I agree with Andreas that I don't see any reason to think that this is an IO issue, but reiterate that it might be if: There is sorting to temp space involved (ORDER BY, GROUP BY, certain aggregation functions, etc.) which would use the AIO VPs unless you have DIRECT_IO set to 7 (which would involve the KAIO threads instead The specific parameters of that 2 second run happens to pull lots of data into the buffer cache that the preceding runs of the query did not need. ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

If you are seeing all this variability, you need to think of other things that might be impacting it. Might the query be hitting locks? Does your ready queue indicate that things are not being executed promptly? ------------------------------ Mike Walker xDB Systems, Inc www.xdbsystems.com ------------------------------

Hello also notcied that cache is alsmost full at the statement run, the statement need other data to load in the case, so increasing buffers could be the solution ? sql_runtime 3,848010607535 sql_lockreq 3903 sql_lockwaits 0 sql_lockwttime 0,00 sql_rdcache 99,51897381080 ===>>> sql_logspace 0 sql_sorttotal 0 sql_sortdisk 0 sql_sortmem 0 sql_executions 1 sql_totaltime 3,848010607535 sql_numiowaits 72 sql_avgiowaits 0,000964965296 sql_totaliowaits 0,069477501342 ------------------------------ John Smith ------------------------------

John: First, the buffer cache is ALWAYS full or close to it except immediately after startup. That's its job: to cache as much of your recently accessed data as it can. That said, as I mentioned in another response, it is possible that more cache will improve things, but, based on your BTR numbers, I doubt it. Worth a try though! Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------