At OtterTune, we continue to plow ahead with our plan to make databases better and easier to manage for everyone. We told you in our previous product update about our automated table-level health checks for PostgreSQL and MySQL. Today we’re pleased to announce OtterTune’s new health checks for database tables and indexes. This product update also includes new ways to interact with and collaborate with your people in lockdown on your OtterTune-connected databases.
Refer to our release notes if you want to see the complete run-down of all the fresh features we’ve added to OtterTune in the last few months.
We have also updated our pricing to allow you to pay a low monthly subscription fee per database. And as always, you can get started with OtterTune on your first database for free.
See our documentation for upgrading instructions.
Table Health Checks
One recurring issue we find with customers’ databases is that even after OtterTune optimizes their configuration knobs, they still have unexpected performance anomalies. Often after our engineers look into the issue with the customer, we find the root cause of the problem because their tables are unhealthy. PostgreSQL is more susceptible to this problem because of the way it implements multi-versioning.
To address this issue, we introduced OtterTune’s new Table Health Checks automatically examine the internal information about each table and then checks whether they are having problems. These checks enable you to diagnose and resolve common database problems within tables. OtterTune also tracks table-specific metrics about (1) table sizes, (2) I/O efficiency, (3) memory utilization, (4) vacuum status (PostgreSQL only), and (5)
ANALYZE status. Table Checks supports all versions of PostgreSQL and MySQL for both Amazon RDS and Amazon Aurora.
OtterTune currently limits its monitoring to the largest 1000 tables per database (based on the number of tuples in the table). We have found that the tables with the most problems have the most tuples compared to other tables (especially in PostgreSQL) versus prioritizing other metrics like storage size or query usage. Please contact us if your database has more than 1000 tables that you think OtterTune should track.
- 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 cause inefficient storage utilization.
- Stale Statistics — As your application inserts, updates, and deletes records in a table, the system’s statistical summaries about the table get out of date. Old statistics are often the reason why the DBMS’s optimizer chooses bad query plans. This check indicates that it is time to run PostgreSQL’s ANALYZE command to update the table’s statistics.
- Excessive Reads — OtterTune checks whether queries read more data from a table relative to other tables in your database. Suppose you’re running an operational workload (i.e., not analytics). In that case, this could indicate that your table is missing indexes, and the system is not executing queries as efficiently as it could.
- Fragmentation Ratio — As queries update records with variable-length data (e.g.,
BLOBcolumns), MySQL may need to allocate new pages and store a large record across multiple pages. These new pages will initially contain some empty space that can add up and lead to performance degradation. This occurs when the DBMS reads a page from the disk that mostly contains empty space. Thus, this check indicates whether your table has too much fragmentation and is slowing down the system.
We announced this new feature two weeks ago because we wanted to show it off immediately.
Index Health Checks
Most databases need indexes to ensure queries avoid slow table scans. But indexes can be fickle jawns like tables. If your database has too many of them, they can waste storage and make queries run slower.
This latest release of OtterTune includes our initial automated Index Health Checks. With these new checks, OtterTune connects to your database and retrieves usage statistics about its indexes. OtterTune tracks up to 10,000 indexes for the tables that it monitors with its Table Health Checks. To avoid overwhelming you with too much information, OtterTune ignores any index smaller than 32MB. We support these Index Checks on all versions of PostgreSQL and MySQL for both Amazon RDS and Amazon Aurora.
OtterTune will automatically identify whether you have superfluous indexes in two different ways:
- Unused Indexes — OtterTune deems an index as “unused” if the DBMS has not executed a query that accessed the index since the last system restart. Note we define “access” as using the index to find records (e.g.,
WHEREclause) and not if the DBMS had to update the index because the underlying table changed.
- Duplicate Indexes — If a table contains multiple indexes with the same columns, then OtterTune flags them as duplicates. The current version of this check only identifies duplicate indexes with the same columns listed in the same order. That is, OtterTune will not flag two indexes
I2(b, a)as duplicates of each other. We are working on an enhanced version of this check that can find these kinds of duplicate indexes and ensure that no queries will suffer performance degradation.
To enable this new feature, make sure that you have connected OtterTune’s agent to your database. Then head over to the Index Health tab at the top of the Database Overview page. You can see the latest information about your indexes, including the detailed metrics that OtterTune collects. We also provide historical statistics about your indexes to see how they evolve. See our documentation for information about OtterTune’s Index Health Checks for MySQL and PostgreSQL.
We know it can be lonely when you’re out on the corner by yourself, ensuring your database is running smoothly. Sometimes you need to reach out to somebody to help you solve database issues. To make your job easier, we’ve added new ways in OtterTune to connect with others and tools to help get you what you need to keep your databases — and trap — swimming along. In this latest release, you can invite Otters from your team to your OtterTune account and get important OtterTune notifications within Slack.
As always, contact us if you need help getting your database hooked up to OtterTune.
Invite Teammates to OtterTune
Let’s say you’ve started setting up OtterTune but you’re stumped by deploying the OtterTune Agent. Or you don’t have the right AWS permissions to even connect your database for monitoring.
You can now invite others from your team to your OtterTune account. Now if these or other annoying things happen to you, you can request the right person from your organization to help, and now both can see all the databases connected to your account. Your invited user will receive an email invitation for them to create an OtterTune account and connect to your organization. The email will copy you on it so that your homey knows it’s legit. Your invited teammate can create their account and confirm that we should connect your two accounts. This ensures we don’t connect anyone’s account without explicit permissions from both users.
We know notification noise is annoying. We also know it’s slightly less annoying when all your actually important notifications are in one place. This is one of the reasons why we added Slack integration in the latest version of OtterTune. Well, that, and that one of our customers asked us to — and we aim to please (we got you, fam).
Once you add OtterTune’s bot to your Slack workspace, you’ll get notified whenever OtterTune has a new configuration ready for your review on your databases. Alternatively, if you have human-in-the-loop control disabled, you’ll see notifications when OtterTune has applied a new configuration to your database.
See our documentation on how to enable Slack notifications for your account.
User Docs and Support Community on Slack
Check out our OtterTune user documentation, which details how to connect OtterTune to your database and start the machine learning (ML)-driven tuning process.
Create your free OtterTune account and join us on Slack to let us know how it’s going. We love helping people that have nasty database problems that no DBA wants to touch.