Table Health

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

Overview
OtterTune's Table Health contains a collection of statistics and health checks to diagnose and resolve common database problems within tables. It provides real-time and historical analysis of your tables' health conditions and suggests actions to fix the issues it identifies.
You can see the table-level check results and table-level statistics from the Table Health tab at the top of the Database Overview page. To enable Table Health, please connect the agent and upgrade the agent to the latest version.
Table Health of a PostgreSQL database
Table-level Health Checks
We currently have the following table-level health checks.
MySQL
PostgreSQL
Name
Description
Fragmentation
MySQL tables may experience fragmentation as data is inserted, updated, and deleted randomly. Fragmentation can leave large holes in your table blocks which must be read when scanning the table. If the percentage of fragmentation in a table is higher than 40%, this can indicate that the table has too much unused space and needs to be optimized.
Name
Description
Dead Tuples
When a row is deleted from a table, PostgreSQL marks it as dead but does not physically remove it. These dead tuples consume unnecessary storage and can degrade database performance. VACUUM reclaims the storage occupied by dead tuples for reuse. If the percentage of dead tuples in a table is higher than 30%, this can indicate inefficient autovacuum management of the table.
Stale Statistics
When a table has many modified tuples since it was last analyzed, the table statistics are not updated timely, which can cause bad query plans. If the percentage of modified tuples in a table since last analyzed is higher than 30%, this can indicate inefficient autoanalyze management of the table.
Excessive Reads
An index scan reads through an index to quickly look up the rows that match your filter. A sequential scan reads ALL rows from the table sequentially, which typically takes longer than an index scan. If the percentage of index scans is lower than 90%, this can indicate inefficient index management of the table. The calculation is based on data collected from the past 24 hours.
You can see a summary of the check results at the top of the Table Health page. You can also find detailed metrics for tables that fail checks under each health check tab.
Tables that fail the Dead Tuples check
Table-level Statistics
The real-time and historical statistics are displayed in Table Health. For MySQL databases, table-level statistics are displayed under the All tab, and for PostgreSQL databases, table-level statistics are displayed under the Autovacuum tab and the IO 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 also view historical statistics by selecting a timestamp (MySQL/PostgreSQL Autovacuum) or a time range (PostgreSQL IO) on the left side.
Timestamp selection
The following table describes each table-level metric.
MySQL
PostgreSQL Autovacuum
PostgreSQL IO
Name
Display by Default
Description
Table
Yes
Name of the table
Schema
Yes
Name of the schema that the table is in
Table Rows
Yes
The approximate number of rows
Data Length
Yes
The approximate length of row data in bytes
Index Length
Yes
The approximate length of index in bytes
Data Free
Yes
The approximate number of allocated but unused bytes
Fragmentation Ratio
Yes
The ratio of fragments in the table in size. The fragments are wasted/unused space in a table, which can cause the performance degradation
Average Row Length
No
The average row length
Table Type
No
BASE TABLE for a table, VIEW for a view, or SYSTEM VIEW for an INFORMATION_SCHEMA table
Row Format
No
The row-storage format (Fixed, Dynamic, Compressed, Redundant, Compact)
Engine
No
The storage engine for the table
Name
Display by Default
Description
Table
Yes
Name of the table
Schema
Yes
Name of the schema that the table is in
Table Size
Yes
Disk space used by the table, excluding indexes and TOAST data
Index Size
No
Disk space used by indexes attached to the specified table
TOAST Size
No
Disk space used by TOAST data attached to the specified table. TOAST is a separate table associated with the data table. It is used to store large pieces of data of TOAST-able columns (e.g., the text data type is TOAST-able)
Total Size
No
Total disk space used by the specified table, including indexes and TOAST data. Specifically, Total size is the sum of Table size, Indexes size and TOAST size
Live Tuples
Yes
Estimated number of live rows. For very large tables with many rows, the default autovacuum settings might not work well and they may need more frequent cleanup of dead tuples
Dead Tuple Ratio
Yes
The percentage of dead tuples in the specified table. If the dead tuple ratio is high, it indicates there are many dead tuples that are not reclaimed for reuse, which can slow down queries
Dead Tuples
False
Estimated number of dead rows
Tuples Modified Since Analyzed
No
Estimated number of rows modified since this table was last analyzed
Modified Tuple Ratio
Yes
The percentage of modified tuples since the table was last analyzed. If the modified tuple ratio is high, it can indicate the table statistics are not updated timely, which can cause bad query plans
Last Autovacuum
Yes
Last time at which this table was vacuumed by the autovacuum daemon
Last Vacuum
No
Last time at which this table was manually vacuumed (not counting VACUUM FULL)
Last Autoanalyze
No
Last time at which this table was analyzed by the autovacuum daemon
Last Analyze
No
Last time at which this table was manually analyzed
Autovacuum Count
No
Number of times this table has been vacuumed by the autovacuum daemon
Vacuum Count
No
Number of times this table has been manually vacuumed (not counting VACUUM FULL)
Autoanalyze Count
No
Number of times this table has been analyzed by the autovacuum daemon
Analyze Count
No
Number of times this table has been manually analyzed
Name
Display by Default
Description
Table
Yes
Name of the table
Schema
Yes
Name of the schema that the table is in
Total Blocks Read
Yes
Total number of disk blocks read from this table per hour, including its index and TOAST data. Specifically, Total blocks read is the sum of Heap blocks read, Index blocks read, TOAST blocks read and TOAST index blocks read
Total Blocks Hit
No
Total number of buffer hits in this table per hour, including its index and TOAST data. Specifically, Total blocks hit is the sum of Heap blocks hit, Index blocks hit, TOAST blocks hit and TOAST index blocks hit
Buffer Cache Hit Rate
Yes
The percentage of time that data blocks of the specified table (including its index and TOAST data) requested by queries are in memory
Sequential Scans
Yes
Number of sequential scans initiated on this table per hour
Index Scans
Yes
Number of index scans initiated on this table per hour
Index Scan Ratio
Yes
The percentage of index scans in the specified table
Tuples Inserted
No
Number of rows inserted per hour
Tuples Updated
No
Number of rows updated (includes HOT updated rows) per hour
Tuples Deleted
No
Number of rows deleted per hour
Sequential Scan Tuples
No
Number of live rows fetched by sequential scans per hour
Index Scan Tuples
No
Number of live rows fetched by index scans per hour
Tuples HOT Updated
No
Number of rows HOT updated (i.e., with no separate index update required) per hour
Heap Blocks Read
No
Number of disk blocks read from this table per hour
Heap Blocks Hit
No
Number of buffer hits in this table per hour
Index Blocks Read
No
Number of disk blocks read from all indexes on this table per hour
Index Blocks Hit
No
Number of buffer hits in all indexes on this table per hour
TOAST Blocks Read
No
Number of disk blocks read from this table's TOAST table (if any) per hour
TOAST Blocks Hit
No
Number of buffer hits in this table's TOAST table (if any) per hour
TOAST Index Blocks Read
No
Number of disk blocks read from this table's TOAST table indexes (if any) per hour
TOAST Index Blocks Hit
No
Number of buffer hits in this table's TOAST table indexes (if any) per hour
​

Name	Description
Fragmentation	MySQL tables may experience fragmentation as data is inserted, updated, and deleted randomly. Fragmentation can leave large holes in your table blocks which must be read when scanning the table. If the percentage of fragmentation in a table is higher than 40%, this can indicate that the table has too much unused space and needs to be optimized.

Name	Description
Dead Tuples	When a row is deleted from a table, PostgreSQL marks it as dead but does not physically remove it. These dead tuples consume unnecessary storage and can degrade database performance. VACUUM reclaims the storage occupied by dead tuples for reuse. If the percentage of dead tuples in a table is higher than 30%, this can indicate inefficient autovacuum management of the table.
Stale Statistics	When a table has many modified tuples since it was last analyzed, the table statistics are not updated timely, which can cause bad query plans. If the percentage of modified tuples in a table since last analyzed is higher than 30%, this can indicate inefficient autoanalyze management of the table.
Excessive Reads	An index scan reads through an index to quickly look up the rows that match your filter. A sequential scan reads ALL rows from the table sequentially, which typically takes longer than an index scan. If the percentage of index scans is lower than 90%, this can indicate inefficient index management of the table. The calculation is based on data collected from the past 24 hours.

Name	Display by Default	Description
Table	Yes	Name of the table
Schema	Yes	Name of the schema that the table is in
Table Rows	Yes	The approximate number of rows
Data Length	Yes	The approximate length of row data in bytes
Index Length	Yes	The approximate length of index in bytes
Data Free	Yes	The approximate number of allocated but unused bytes
Fragmentation Ratio	Yes	The ratio of fragments in the table in size. The fragments are wasted/unused space in a table, which can cause the performance degradation
Average Row Length	No	The average row length
Table Type	No	BASE TABLE for a table, VIEW for a view, or SYSTEM VIEW for an INFORMATION_SCHEMA table
Row Format	No	The row-storage format (Fixed, Dynamic, Compressed, Redundant, Compact)
Engine	No	The storage engine for the table

Name	Display by Default	Description
Table	Yes	Name of the table
Schema	Yes	Name of the schema that the table is in
Table Size	Yes	Disk space used by the table, excluding indexes and TOAST data
Index Size	No	Disk space used by indexes attached to the specified table
TOAST Size	No	Disk space used by TOAST data attached to the specified table. TOAST is a separate table associated with the data table. It is used to store large pieces of data of TOAST-able columns (e.g., the text data type is TOAST-able)
Total Size	No	Total disk space used by the specified table, including indexes and TOAST data. Specifically, Total size is the sum of Table size, Indexes size and TOAST size
Live Tuples	Yes	Estimated number of live rows. For very large tables with many rows, the default autovacuum settings might not work well and they may need more frequent cleanup of dead tuples
Dead Tuple Ratio	Yes	The percentage of dead tuples in the specified table. If the dead tuple ratio is high, it indicates there are many dead tuples that are not reclaimed for reuse, which can slow down queries
Dead Tuples	False	Estimated number of dead rows
Tuples Modified Since Analyzed	No	Estimated number of rows modified since this table was last analyzed
Modified Tuple Ratio	Yes	The percentage of modified tuples since the table was last analyzed. If the modified tuple ratio is high, it can indicate the table statistics are not updated timely, which can cause bad query plans
Last Autovacuum	Yes	Last time at which this table was vacuumed by the autovacuum daemon
Last Vacuum	No	Last time at which this table was manually vacuumed (not counting VACUUM FULL)
Last Autoanalyze	No	Last time at which this table was analyzed by the autovacuum daemon
Last Analyze	No	Last time at which this table was manually analyzed
Autovacuum Count	No	Number of times this table has been vacuumed by the autovacuum daemon
Vacuum Count	No	Number of times this table has been manually vacuumed (not counting VACUUM FULL)
Autoanalyze Count	No	Number of times this table has been analyzed by the autovacuum daemon
Analyze Count	No	Number of times this table has been manually analyzed

Name	Display by Default	Description
Table	Yes	Name of the table
Schema	Yes	Name of the schema that the table is in
Total Blocks Read	Yes	Total number of disk blocks read from this table per hour, including its index and TOAST data. Specifically, Total blocks read is the sum of Heap blocks read, Index blocks read, TOAST blocks read and TOAST index blocks read
Total Blocks Hit	No	Total number of buffer hits in this table per hour, including its index and TOAST data. Specifically, Total blocks hit is the sum of Heap blocks hit, Index blocks hit, TOAST blocks hit and TOAST index blocks hit
Buffer Cache Hit Rate	Yes	The percentage of time that data blocks of the specified table (including its index and TOAST data) requested by queries are in memory
Sequential Scans	Yes	Number of sequential scans initiated on this table per hour
Index Scans	Yes	Number of index scans initiated on this table per hour
Index Scan Ratio	Yes	The percentage of index scans in the specified table
Tuples Inserted	No	Number of rows inserted per hour
Tuples Updated	No	Number of rows updated (includes HOT updated rows) per hour
Tuples Deleted	No	Number of rows deleted per hour
Sequential Scan Tuples	No	Number of live rows fetched by sequential scans per hour
Index Scan Tuples	No	Number of live rows fetched by index scans per hour
Tuples HOT Updated	No	Number of rows HOT updated (i.e., with no separate index update required) per hour
Heap Blocks Read	No	Number of disk blocks read from this table per hour
Heap Blocks Hit	No	Number of buffer hits in this table per hour
Index Blocks Read	No	Number of disk blocks read from all indexes on this table per hour
Index Blocks Hit	No	Number of buffer hits in all indexes on this table per hour
TOAST Blocks Read	No	Number of disk blocks read from this table's TOAST table (if any) per hour
TOAST Blocks Hit	No	Number of buffer hits in this table's TOAST table (if any) per hour
TOAST Index Blocks Read	No	Number of disk blocks read from this table's TOAST table indexes (if any) per hour
TOAST Index Blocks Hit	No	Number of buffer hits in this table's TOAST table indexes (if any) per hour

Index Health

Next - Documentation

AWS Permissions

Last modified 3mo ago