Get granular without the grunt work: OtterTune Table Health Checks let you diagnose issues automatically

No one wants to get lit up for database downtime or sluggish performance. OtterTune is here for you and your trap.

We introduced Database Health Checks back in March as a powerful way to automatically check whether your MySQL and PostgreSQL databases are running in top shape on Amazon RDS and Amazon Aurora. The health checks automatically detect resource utilization, efficiency, and cache management issues in your databases in real-time and provides you with recommendations to fix them.

Today we’re happy to announce that we’ve expanded health checks to go a level deeper. OtterTune’s new Table Health Checks automatically examine vital statistics and health checks to diagnose and resolve common database problems within tables. It provides a real-time and historical analysis of your tables’ health conditions and suggests actions to fix the issues.

Table Health Checks & Table Statistics monitoring

When you connect your database to OtterTune, the service first retrieves basic information about all the tables in your database. It then selects the largest 1000 tables (based on the number of tuples) and starts monitoring their runtime metrics. We have found that the tables with the most problems are the ones with the most tuples (especially in PostgreSQL), versus prioritizing other metrics like the tables with the largest storage size or tables that are used in the most queries.

OtterTune then runs its health check engine to determine whether your tables are in good shape or whether they are sick. You can then check whether your database tables are passing or failing these checks on OtterTune’s Table Health dashboard. If your tables are failing health checks, OtterTune recommends corrective actions that improve table health and performance. This reduces the time, guess-work, and worry required for proper database administration,

In addition to the checks, OtterTune’s dashboard also shows you a multitude of table statistics that we have curated for helping you diagnose problems. You can customize which statistics are displayed, and you can also look at how statistics have changed over time. OtterTune knows to display health checks and statistics that are specific to the PostgreSQL or MySQL databases you’re running.

PostgreSQL

Because of the way PostgreSQL implements multi-versioning for tables (you can think of it like how git stores diffs for commits but more inefficient), it is more susceptible than other DBMSs to table health issues. Every time your application updates a tuple, the DBMS first copies the original tuple and then updates the copy. The original version is then marked as deprecated and will be cleaned up by the vacuum process at some point in the future.

The current version of OtterTune’s Table Health Checks for PostgreSQL tracks the following information about the most important tables in your database:

• Dead Tuple Ratio — A “dead tuple” is a row in your table that has either been (1) recently updated and is not visible to any active transaction or (2) recently deleted. Dead tuples slow down queries and causes inefficient storage utilization (time to vacuum!).

• Stale Statistics — The DBMS maintains statistical summaries about each column’s values. As your application inserts, updates, and deletes records in the database, these statistics get out of date. Old statistics can lead the DBMS’s optimizer to make bad choices when generating query plans. Thus, this check indicates that it might be time to RUN ANALYZE to keep SQL queries performing efficiently.

• Excessive Reads — Lastly, OtterTune checks whether your application executes queries that read too much data from the table compared to other tables. If you’re running an operational workload (i.e., not analytics), then this could indicate that your DBMS is missing the right indexes and the system is not executing queries as efficient as it could. (Spoiler: OtterTune is working on automatic index tuning!)

OtterTune also tracks over 30 different PostgreSQL table statistics that provide at-a-glance information about (1) table sizes, (2) I/O efficiency, (3) Heap, Page, and Buffer utilization, and (4) vacuum and analyze status.

MySQL

OtterTune tracks only a single health check for MySQL tables. This is because MySQL doesn’t have to run an additional autovacuum worker in the background to clean up dead tuples. MySQL also does not enable all its performance schema tracking by default, so that limits what OtterTune can discover on its own about your tables.

As such, OtterTune’s Table Health Checks for MySQL will identify the following problems about your tables: performs a single table health check for MySQL (InnoDB only):

• Fragmentation Ratio — As queries update records with variable-length data (e.g., VARCHAR, TEXT, BLOB columns), MySQL could split a record into two or more new pages to store the changes. These new pages will initially contain empty space because the DBMS will want to be able to store new data in them in the future without having to allocate new pages each time. But this empty space can lead to performance degradation (e.g., each page read from disk provides less data that is actually useful) and wasted storage allocation. Thus, this check indicates whether your table has too much fragmentation and is slowing down the system.

And of course OtterTune monitors and groups together 12 MySQL table statistics that is available in the dashboard: (1) table storage information, (2) index storage information, (3) allocated vs. utilized storage space, and (4) fragmentation ratios.

OtterTune is watching your back

If you’re spending too much (or not enough!) time tending to your database and tables, let OtterTune help you out. It automates database management for you with database health, table health, and machine learning-driven automatic configuration tuning. OtterTune helps ensure optimum performance and uptime, while you stay focused on development, customers, eating oysters, or harassing kayakers.

Create an OtterTune account to run our automated health checks for your database and its tables. Then use its AI-powered tuning algorithm to auto-tune your database for free. Join us on Slack to ask us, or other OtterTune community members, questions.