Sync Vs Async Read

db2udbgirl · #1 (**permalink**) 09-03-09, 16:16

Which is more important for best performance in an OLTP and DW system ?

Few blog suggests that Synchronous I/O is best since it scans only the relevant page based on index..as in
DBI :: Brother-Eagle™ Advice :: DB2 Sync Read Percent

While few other blog suggests that Async I/O is better as in
Asynchronous Read activity | db2ude

Any thoughts based on your experience in prod systems ?

ARWinner · #2 (**permalink**) 09-03-09, 16:34

If you read the DBI link closely, high Sync is good for OLTP, where high async is good for DW.

Andy

n_i · #3 (**permalink**) 09-03-09, 16:37

Quote:

Originally Posted by Brother-Eagle™ Advice

When DB2 has to scan many pages to find result sets, usually because indexes are missing or sub-optimally defined, DB2 uses prefetch I/O which is performed asynchronously. When the percentage of asynchronous I/O is high, then synchronous I/O is low.

When the Synchronous Read Percentage is low, DB2 is doing a lot of scanning to find result sets. Scans occur when indexes are missing or are sub-optimally defined.

I think the statement above is over-simplified to the point where it becomes incorrect. In my view asynchronous I/O is good, unless the "time waited for prefetch" and "unread prefetch pages" are high compared with the read time and the total number of prefetched pages respectively. The ratio of rows selected to rows read is often a better indicator of index access (in)efficiency.

Internet Services

Web Development

Digital Marketing

Consumer Tech