This post talked about an information_schema query to give you a nice report of how much percentage wise indexes are fitting into the buffer pool
The query looked like
> SELECT `schema` AS table_schema, > innodb_sys_tables.name AS table_name, > innodb_sys_indexes.name AS index_name, > cnt, > dirty, > hashed, > ROUND(cnt * 100 / index_size, 2) fit_pct FROM (SELECT index_id, > COUNT(*) cnt, > SUM(dirty = 1) dirty, > SUM(hashed = 1) hashed > FROM innodb_buffer_pool_pages_index > GROUP BY index_id) bp > JOIN innodb_sys_indexes > ON id = index_id > JOIN innodb_sys_tables > ON table_id = innodb_sys_tables.id > JOIN innodb_index_stats > ON innodb_index_stats.table_name = innodb_sys_tables.name > AND innodb_sys_indexes.name = innodb_index_stats.index_name > AND innodb_index_stats.table_schema = innodb_sys_tables.SCHEMA ORDER BY cnt DESC LIMIT 20;
This post if from 2010 so it seems something has changed with more recent versions. I see in the comments are other people complaining about the same problem I am, "Unknown column index_size"
Which is referenced in the fit_pct portion
ROUND(cnt * 100 / index_size, 2) fit_pct
The current version I am running is Percona 5.5.29.
Does anyone know how this query could be rewritten to work with newer informatinon_schema tables?