All about Random reads (IO Response)

Also known as Random IO (ms) in ProTop RT and found in ProTop Trends Advanced Dashboard under I/O Response. The alertable metric name is ioResponse.

The Random IO metric generally measures the time it takes to perform a random read of a byte from a database file that is (most likely) not already in memory. It is a gross measure of how the OS responds to disk I/O requests. It can shine some light and help distinguish between db and OS issues. The key point is that this is an END-TO-END measure from the application's point of view rather than having to pull together disconnected logs from multiple sources.

Looking at variances in the data over time on the Portal, i.e., what the value is currently versus this system’s typical level, helps answer questions like “why is the application slow/why is the database slow” by identifying that the database or application may be the victim of the same underlying system contention issues that are currently increasing the value of ioResponse.

Note: if you see a metric chart for IO REsponse that looks like this:

This chart reflects behavior consistent with a very small database and a large buffer pool for which the byte we are reading is increasingly likely to be found in memory. The algorithm for selecting a byte not already in memory is based on more "normal" sized databases. This database is only 510 MB.

Not seeing random reads for a really small database?

Random Reads is a loop that we run against the largest db extents for the db in question. If there are no large extents, there is a localenv variable, IOFILENAME, that you can set instead. The minimum size to be considered for use is 128MB. So, if the db is small enough (or the extents are all small), there is a chance that the list of eligible files to read from will be empty, and the random read will not occur.

If the extents are "big enough" but still small enough to fit comfortably within the server RAM footprint, the assumption that a sufficiently random read is a disk read will also start to fall apart, and the reads will be satisfied from the OS cache. This will also result in zero or near zero apparent random read times.

In cases where the db is small, you can find some multi-gigabyte file, copy it somewhere it will not get accidentally deleted, and point IOFILENAME at that.

Or, if it is small because it is a "bolt-on," just point it at some other database's bigger extents.