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

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.