Index Health
OtterTune's Index Health automatically detects the possible issues in your database indexes and provides recommendations to improve your database's performance.
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
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.
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
PostgresSQL 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 |
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.
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.
Last modified 1mo ago