Re: Upgrade to 11.70 from 11.50 results in low Read Ahead Utilization

Hi, In 11.70.xC3, the new AUTO_READAHEAD ONCONFIG parameter was introduced. This parameter reflects a new read-ahead algorithm in the database server. Basically, in mode 1 setting, auto read-ahead will turn off read-aheads for a scan if it detects that the pages are already cached in the buffer pool. If the database server detects that the query is encountering I/O, it will turn on read-aheads again for the scan/query. This is the auto part of AUTO_READAHEAD. If you want AUTO_READAHEAD to do read-aheads no matter what, you can set AUTO_READAHEAD to 2 (use the mode 2 setting). The recommended setting is 1. Note 1: You can use "onstat -g rah" to view read-ahead stats. One thing to note is that even if you turn off AUTO_READAHEAD globally, you will still see some read-aheads being done because there are internal uses of read-aheads by the database server and some utilities. Note 2: You can also set/change AUTO_READAHEAD settings for a session using the " Set Environment AUTO_READAHEAD '...' " command. Hyun-Ju > > ---------- Forwarded message ---------- > From: Andrew Ford <aford@networkip.net> > To: "informix-list@iiug.org" <informix-list@iiug.org> > Date: Tue, 26 Jul 2011 16:13:41 +0000 > Subject: Upgrade to 11.70 from 11.50 results in low Read Ahead Utilization > I recently upgraded from 11.50.FC3 to 11.70.FC3. > > As expected, the upgrade was painless and there are no apparent issues as a result of the upgrade. > > One thing that I do notice is my Read Ahead Utilization (RA-pgsused / (ixda-RA + idx-RA + da-RA)) is in the toilet after the upgrade. > > Pre upgrade - 11.50.FC3 - 99.05% > > RA_PAGES     128 > RA_THRESHOLD 32> > ixda-RA    idx-RA     da-RA      RA-pgsused lchwaits > 3724876    4323945    321113     8290237    3197641 > > Post upgrage - 11.70.FC3 - 15.14% > > AUTO_READAHEAD  1   # equivalent to RA_PAGES 128, RA_THRESHOLD 64, right?> > ixda-RA    idx-RA     da-RA      logrec-RA  RA-pgsused lchwaits > 0          35100202   136980     0          5334831    1114786 > > My Read Cache Hit % has not suffered, it has even increased from 95.45% to 98.84%. > I believe this to be a result of a new Auto Light Scan feature and some reconfiguration of buffer pools I did as part of the upgrade. > > Changing AUTO_READAHEAD from the default to AUTO_READAHEAD 1,16 has increased my RAU to 25%. > > I'm pretty sure this increase in idx-RA is a result of enabling BATCHEDREAD_INDEX and not a cause for alarm because performance is fine. > > Just curious if anyone else running 11.70 is seeing the same thing and if the recommendations for RAU at or above 99% needs to be modified when BATCHEDREAD_INDEX is enabled. > > Thanks, > > Andrew > >

Hi Hyun-Ju, Am 30.07.2011 05:28, schrieb Hyun-Ju Vega: > Hi, > > In 11.70.xC3, the new AUTO_READAHEAD ONCONFIG parameter was introduced. > > This parameter reflects a new read-ahead algorithm in the database server. > > Basically, in mode 1 setting, auto read-ahead will turn off > read-aheads for a scan if it detects that the pages are already cached > in the buffer pool. If the database server detects that the query is > encountering I/O, it will turn on read-aheads again for the how fast will this be detected? And how fast will RA start again? Some customers do 400,000++ reads/second of 2KB pages in a scan. So if the reaction comes 'too late' then this is a good example for a feature, which is best not used. At least this is true in large setups, where tables or fragments having less than 100 * 10**6 rows are considered to be small. Some customers are still running INFORMIX or simply have to, because their DBs are large and there is no competitive solution around. So finally I want to ask: What was the aim targeted by this new feature? I/O reduction? If so I want the feature architects/designers to do some testing on IBM equipment: The EX5 shows, that fast I/O on SSDs is faster than BUFFERPOOL-thruput, so if you have I/O because it is needed and not an outcome of bad queries, then you want more of it and not less. To buy very fast I/O is a bargain these days. dic_k > scan/query. This is the auto part of AUTO_READAHEAD. > > If you want AUTO_READAHEAD to do read-aheads no matter what, you can > set AUTO_READAHEAD to 2 (use the mode 2 setting). > > The recommended setting is 1. > > Note 1: You can use "onstat -g rah" to view read-ahead stats. One > thing to note is that even if you turn off AUTO_READAHEAD globally, > you will still see some read-aheads being done because there are > internal uses of read-aheads by the database server and some > utilities. > > Note 2: You can also set/change AUTO_READAHEAD settings for a session > using the " Set Environment AUTO_READAHEAD '...' " command. > > > Hyun-Ju > > >> >> ---------- Forwarded message ---------- >> From: Andrew Ford<aford@networkip.net> >> To: "informix-list@iiug.org"<informix-list@iiug.org> >> Date: Tue, 26 Jul 2011 16:13:41 +0000 >> Subject: Upgrade to 11.70 from 11.50 results in low Read Ahead Utilization >> I recently upgraded from 11.50.FC3 to 11.70.FC3. >> >> As expected, the upgrade was painless and there are no apparent issues as a result of the upgrade. >> >> One thing that I do notice is my Read Ahead Utilization (RA-pgsused / (ixda-RA + idx-RA + da-RA)) is in the toilet after the upgrade. >> >> Pre upgrade - 11.50.FC3 - 99.05% >> >> RA_PAGES 128 >> RA_THRESHOLD 32>> >> ixda-RA idx-RA da-RA RA-pgsused lchwaits >> 3724876 4323945 321113 8290237 3197641 >> >> Post upgrage - 11.70.FC3 - 15.14% >> >> AUTO_READAHEAD 1 # equivalent to RA_PAGES 128, RA_THRESHOLD 64, right?>> >> ixda-RA idx-RA da-RA logrec-RA RA-pgsused lchwaits >> 0 35100202 136980 0 5334831 1114786 >> >> My Read Cache Hit % has not suffered, it has even increased from 95.45% to 98.84%. >> I believe this to be a result of a new Auto Light Scan feature and some reconfiguration of buffer pools I did as part of the upgrade. >> >> Changing AUTO_READAHEAD from the default to AUTO_READAHEAD 1,16 has increased my RAU to 25%. >> >> I'm pretty sure this increase in idx-RA is a result of enabling BATCHEDREAD_INDEX and not a cause for alarm because performance is fine. >> >> Just curious if anyone else running 11.70 is seeing the same thing and if the recommendations for RAU at or above 99% needs to be modified when BATCHEDREAD_INDEX is enabled. >> >> Thanks, >> >> Andrew >> >> -- Richard Kofler SOLID STATE EDV Dienstleistungen GmbH Vienna/Austria/Europe