Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.