So I have metrics that look like this.
And here is my dumb question… Does the rw i/o directly correlate to rw query?
Using the example below, does that mean there was increased read query activity?
I don’t have some flags enabled yet to have metrics on the transaction level, but i’ll eventually do so it’s a blackbox for now.
Advertisement
Answer
It does not necessarily mean that it was the result of a read query.
Even if it was a write, InnoDB can only change data pages after they have been copied from storage into the InnoDB buffer pool (i.e. in RAM). Any access to data rows or indexes is done in the buffer pool. Then the modified page in RAM is eventually flushed to storage.
Both read and write queries might not need to cause read I/O if they are acting on pages that have previously been loaded into the buffer pool. You see read I/O only when a query needs access to a page that isn’t in the buffer pool yet. The page is then loaded on demand.
So a write query such as:
UPDATE mytable SET somecolumn = 'value' WHERE id = 8675309;
might require read I/O because the page that held that row had not been in the buffer pool until that moment.