I recently upgraded from 11.50.FC3 to 11.70.FC3.

As expected, the upgrade was painless and there are no apparent issues as aresult 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,

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

Hi Hyun-Ju,

Am 30.07.2011 05:28, schrieb Hyun-Ju Vega:
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



--
Richard Kofler
SOLID STATE EDV
Dienstleistungen GmbH
Vienna/Austria/Europe

Hi Richard,


1) The first time the query encounters a page needing to be read from
disk, read-ahead will be turned on.

2) May want to check out this blog:

http://jfmiii.com/informix/?p=107

3) The new read-ahead algorithm replaces the old algorithm completely
(changes include new readahead threads that take the burden of dealing
with the read-ahead code off of the query threads).

Hyun-Ju