Is it possible to get the amount of bytes read by a query in aurora? If not at the query level, is it possible to have it at the table level?.
I've looked into performance insights, and the performance_schema tables, but they all seem to have data only for ROWS_SCANNED, not the amount of physical bytes. Metrics for I/O in performance insights also seem to be at the database level, not table.
For all of InnoDB, see SHOW STATUS LIKE 'Innodb_%';
There is a reason that the metrics are global, not at the database or table level -- the buffer_pool is a cache. Each query depends on and interferes with every other query. At least at the I/O level.
I dispute whether "bytes scanned" is relevant. It seems like "rows touched" is more useful. After I/O, accessing a row is the most costly task in performing a query.
Unfortunately, Innodb status values are aggregate statistics, so you need to run one query at a quiet time in order to get stats for a single statement.
Do this before and after your query, then take the differences.
SHOW STATUS LIKE 'Innodb%read%';
Caution: Some count rows, some count blocks, some count bytes.
Keep in mind that things like "bytes read from disk" will come in multiples of 16KB because of the blocking. I don't think anything watches "bytes scanned".
Please describe further what you would like to do with the data if you could get it. I may be able to give you a more focused answer.
I find that SHOW SESSION STATUS LIKE 'Handler%'; is very good for deciphering how much work happens for a single query. And it is per-session (as well as global).
"Write" metrics usually refer to temp tables built to support complex SELECTs.
