One of the great things about working at Percona is the constant innovation that occurs as a result of a deep level of technical expertise. A more recent conversation about the Information Schema table: innodb_cached_indexes led to the desire to produce this information in an easy to digest and useful format. Enter PMM.
Our goal with creating this dashboard was to help bring further insight into how your MySQL database cache is being used. Why is this important? Data is accessed significantly faster when it is cached, so indexes that are cached will allow for an increase in query performance. Until now there has not been an easy way to see which indexes are cached and which are not. We want to take the guesswork out of the equation and present this information quickly in an easy to read format. What other information can we learn from using this dashboard?
- Does your cache store the indexes you are expecting or is there interesting behavior that needs to be looked into?
- Where are your largest indexes and which indexes take up the most space?
- Which indexes are changing over time?
- Which indexes are not in cache?
The MySQL InnoDB Cached Indexes dashboard displays the top 20 indexes that are memory resident and the sizes of each index. The pie chart displays the same information in a different view but combines all indexes that take up 3% or less of the total size of all memory-resident indexes, in a pie slice labeled Others. The naming convention is schema_name.table_name.index_name. You are able to display any combination of tables and indexes as you see fit. The five statistics displayed at the bottom are the total size of cached indexes on your MySQL instance, the size of the InnoDB buffer pool, the ratio of cached indexes to the size of the InnoDB buffer pool, the size of the selected schema’s cached indexes, and the size of the selected table’s cached indexes.
To install this dashboard you can do one of 2 options:
- Download the files here. The README.md has installation instructions.
- Download the queries-mysqld.yml file from here. The README.md has installation instructions for queries-mysqld.yml. Instead of importing the .json, paste the ID 10815 for the dashboard ID, and select Load.
Feel free to provide your thoughts and feedback in the comments below.
For more information, check out Custom Queries in PMM by Daniel Guzmán Burgos, one of our Managed Services Technical Leads.
Trying to Load the dashboard ID throws me err:
Unexpected token u in JSON at position 0
So I appended query to file on one of the staging server
/usr/local/percona/pmm-client/queries-mysqld.yml
And uploaded .json dashboard, Host is None and no data comes in, do I have to restart collector or something to pick up new data for the dashboard?
exactly my issue.
Which Version ? of PMM client /server is supported ?
Hello, Custom Queries were introduced in PMM 1.15, so PMM 1.15 through PMM 1.17 would have the functionality to support this.
Please see this blog post for reference: https://www.percona.com/blog/2018/10/10/percona-monitoring-and-management-pmm-1-15-0-is-now-available/