Index Health
OtterTune's Index Health automatically detects the possible issues in your database indexes and provides recommendations to improve your database's performance.

Overview

OtterTune's Index Health contains a collection of statistics and health checks to diagnose and resolve common database problems within indexes. It provides real-time and historical analysis of your indexes' health conditions and suggests actions to fix the issues it identifies.
You can see the index-level check results and index-level statistics from the Index Health tab at the top of the Database Overview page. To enable Index Health, please connect the agent and upgrade the agent to the latest version.
Index Health of a PostgreSQL database

Index-level Health Checks

We currently have the following index-level health checks.
MySQL
PostgreSQL
Name
Description
Unused Indexes
There could be indexes that have never been used since the last database restart or manual stats reset. They could cost you a lot of CPU, disk, and memory to maintain but may provide no benefit. If any table has unused indexes, this can indicate an inappropriate index strategy and unused indexes may be dropped.
Duplicate Indexes
There could be indexes that index exactly the same columns in the same table. They have no extra benefits, but could cost you a lot of CPU, disk, and memory to maintain. If any table has duplicate indexes, this can indicate inefficient index management and redundant indexes need to be dropped.
Name
Description
Unused Indexes
There could be indexes that have never been used since the last database restart or manual stats reset. They could cost you a lot of CPU, disk, and memory to maintain but may provide no benefit. If any table has unused indexes, this can indicate an inappropriate index strategy and unused indexes may be dropped.
Duplicate Indexes
There could be indexes that index exactly the same columns in the same table. They have no extra benefits, but could cost you a lot of CPU, disk, and memory to maintain. If any table has duplicate indexes, this can indicate inefficient index management and redundant indexes need to be dropped.
You can see a summary of the check results at the top of the Index Health page. You can also find detailed metrics for indexes that fail checks under each health check tab.
Indexes that fail the Duplicated Indexes check

Index-level Statistics

The real-time and historical statistics for the top 1k largest indexes are displayed in Index Health. For both MySQL and PostgreSQL, index-level statistics are displayed under the Indexes tab.
By default, only a subset of the metrics is displayed. The metrics that are not displayed by default can be enabled by clicking the gear icon on the right side.
Metrics Selection
You may view historical statistics by selecting a time range on the left side. The index size is calculated based on the end time of the time range.
Time range selection
The following table describes each index-level metric.
MySQL Indexes
PostgreSQL Indexes
Name
Display by Default
Description
Table
Yes
Name of the table
Schema
Yes
Name of the schema that the table is in
Index
Yes
Name of the index
Index Size
Yes
The size of the index at the end of selected time range
Index Read Row Count
Yes
Average number of rows returned per hour from read queries that used the index
Index Read IO Time
No
Average time spent per hour on read queries that used the index
Index Write Row Count
Yes
Average number of rows wrote per hour by write queries that used the index
Index Write IO Time
No
Average time spent per hour on write queries that used the index
Name
Display by Default
Description
Table
Yes
Name of the table
Schema
Yes
Name of the schema that the table is in
Index
Yes
Name of the index
Index Size
Yes
The size of the index at the end of selected time range
Index Scan
Yes
Number of index scans initiated on the index per hour
Index Tuple Read
Yes
Number of index entries returned by scans on the index per hour
Index Tuple Fetch
No
Number of live table rows fetched by simple index scans using the index per hour
Index Blocks Read
No
Number of disk blocks read from the index per hour
Index Blocks Hit
No
Number of buffer hits in the index per hour
Index Buffer Cache Hit Rate
No
The percentage of time that data blocks of the specified index needed by queries are in memory

Permissions

For MySQL databases, the following permissions are required to enable Index Health:
GRANT SELECT ON mysql.innodb_index_stats TO 'ottertune';
GRANT SELECT ON performance_schema.table_io_waits_summary_by_index_usage TO 'ottertune';
For PostgreSQL databases, no extra permissions are needed if you have granted the ones we asked for here.

Agent Version

Index Health is supported by agents whose version is >= 0.3.10. If you are using an old version of the agent, please upgrade to see index-level statistics and checks.
Copy link
On this page
Overview
Index-level Health Checks
Index-level Statistics
Permissions
Agent Version