OtterTune November 2023 Product Update

Since releasing the much-anticipated OtterTune v2.0 in April of this year, we’ve been working hard to further enhance our core feature functionality and polish existing workflows while delivering some exciting new features.

🔥 Announcing the OtterTune API

We’ve worked hard to provide you with more developer-friendly ways to interact with your OtterTune recommendations. We know that some users would like additional ways to review and apply recommendations – other than via the UI – and that’s one of the reasons we’ve decided to release the OtterTune REST API.

This means you can now call our API and receive your actionable recommendations programmatically. You may want to use this to display information on an internal dashboard or report, or transform them to create actionable tasks to assign to members of your team.

The easiest way to get started with the OtterTune API is to use Postman to send requests. Check out our API Documentation for instructions on how to load the API into Postman and configure your API key so it’s automatically included when you send requests.

For example, if you wanted to retrieve all your OtterTune recommendations for a database, you’d do a curl request like this:

curl --location 'https://service.ottertune.com/api/databases/:databaseId/recommendations' \
--header 'Accept: application/json' \
--header 'OT-API-KEY: <API Key>

And you’d get a JSON response like this. In this example scenario, OtterTune has flagged two duplicate indexes that can be dropped to free up disk space and improve query performance:

{
  "count": 2,
  "previous": null,
  "next": null,
  "results": [
    {
      "id": 7654,
      "name": "Drop a duplicate index",
      "description": "Dropping this may improve query performance and data integrity.",
      "type": "index",
      "status": "applied",
      "createdTime": "2023-08-16T09:18:50.508710Z",
      "databaseId": 12345,
      "awsAccountId": "111222333445",
      "region": "us-east-2",
      "dbIdentifier": "prod-reader-1",
      "dbSystem": "Aurora PostgreSQL",
      "dbSystemVersion": "14",
      "indexId": 876,
      "indexName": "duplicate_index_1",
      "indexType": "btree",
      "action": "ALTER TABLE stock DROP INDEX duplicate_index_1;",
      "sizeBytes": 48005120,
      "columns": "OL_SUPPLY_W_ID",
      "tableName": "stock",
      "schema": "public",
      "logicalDb": "marketing"
    },
    {
      "id": 7654,
      "name": "Drop a duplicate index",
      "description": "Dropping this may improve query performance and data integrity.",
      "type": "index",
      "status": "applied",
      "createdTime": "2023-08-16T09:18:50.508710Z",
      "databaseId": 12345,
      "awsAccountId": "111222333445",
      "region": "us-east-2",
      "dbIdentifier": "prod-reader-1",
      "dbSystem": "Aurora PostgreSQL",
      "dbSystemVersion": "14",
      "indexId": 876,
      "indexName": "duplicate_index_1",
      "indexType": "btree",
      "action": "ALTER TABLE stock DROP INDEX duplicate_index_1;",
      "sizeBytes": 48005120,
      "columns": "OL_SUPPLY_W_ID",
      "tableName": "stock",
      "schema": "public",
      "logicalDb": "marketing"
    }
  ]
}

We’re excited about expanding this offering in the future! If there are specific resources or use cases you'd like the API to support, get in touch with us or submit a feature request.

🪣 AWS S3 bucket support (for Enterprise accounts)

For our Enterprise customers with additional security needs, we’ve updated the Otte​​rTune Agent to support AWS S3 as an endpoint, so it will write the telemetry we collect from the database to an S3 bucket instead of sending it directly to the OtterTune service.

Using this feature, OtterTune can securely access customer data on AWS when public egress is not permitted. You can also use it as a way to track all of the data that OtterTune has accessed for auditing purposes.

Please reach out to sales@ottertune.com if you are interested in using this feature.

🛢️ Let OtterTune manage your AWS Database (DB) Parameter Groups

OtterTune uses artificial intelligence (AI) to automatically optimize a database’s configuration knobs by periodically applying new configurations, each time gaining a better understanding of how the knob settings impact your database’s performance. One thing that sets OtterTune apart from other database tuning solutions is it can perform the optimization task in a “closed loop”, meaning that it can continue learning and finding better configurations without human intervention. 

We expose this option in our UI by providing an auto-tuning mode that you can enable to let OtterTune to apply knob recommendations on your behalf (with or without your approval). If you’re not ready to relinquish that level of control, you can also apply OtterTune’s recommendations manually on your own – we also provide the AWS CLI commands to update your database’s AWS RDS DB Parameter Group accordingly for convenience.

The majority of our customers (~80%) choose to enable auto-tuning mode, but based on talking with customers and our own experiences, we understand that configuring a DB Parameter Group and its permissions so OtterTune can modify it can be tricky, and issues are hard to debug.

To make this step faster and easier, you now have the option to offload the responsibility of configuring DB Parameter Groups to OtterTune. Our efforts are twofold.

One, when creating the AWS IAM Role for OtterTune in the onboarding process, you can now choose between two different permission levels. The Read-only option is the level we supported previously, which grants OtterTune the read permissions it needs for monitoring database activity. The new Write:Limited level additionally grants the write permissions OtterTune needs to clone the existing DB Parameter Group, associate it with your database or cluster, and modify its knob settings.

Two, If you grant OtterTune the Write:Limited permissions when creating the IAM Role, for any database belonging to that AWS account, in the Tuning Options tab you will now have the option to copy its DB Parameter Group and associate it with the instance. In the example below, selecting the “Create Parameter Group” button will copy DB Parameter Group postgres-14-pg to a new DB Parameter Group named ottertune-database-pg14, and assign it to this database instance.

Check out our documentation for more details on granting IAM Role permissions, creating DB Parameter Groups, and the specific AWS permissions granted at each permission levels.

🐘 Support for PostgreSQL v15

To keep up with the changing needs of our customers as they build new applications and upgrade older systems, we’ve added support for PostgreSQL version 15 on Amazon RDS and Aurora.

OtterTune support for PostgreSQL version 16 on Amazon RDS and Aurora is tentatively on our roadmap for early 2024, pending its availability on Amazon Aurora (this is around the time we expect it’ll be available based on the historical lag between releases of open source PostgreSQL, Amazon RDS, and Amazon Aurora).

🚦 No interruptions when your database’s storage size changes

If OtterTune is optimizing a database’s configuration in auto-tuning mode and the instance’s hardware changes, any training data and safeguards like allowed knob ranges related to impacted resources (e.g., RAM, storage size, etc.) may no longer be valid. As such, it’s important to transform this data to be compatible with the DB instance’s new hardware to ensure OtterTune generates knob recommendations that are safe to apply. 

Automatically handling DB instance hardware changes is non-trivial, and after observing how rarely such events happen with our customers, we opted for a simpler, more manual solution. Specifically, if we detect any changes to a DB instance’s hardware, we disable auto-tuning (if needed) and adjust the impacted knob bounds and data accordingly. We then notify the customer of what happened, and ask them to review the updated tuning bounds before re-enabling auto-tuning on the database.

Over the past few months, we’ve seen an uptick in the frequency of hardware changes to our customers’ databases. We did some digging and found that nearly all of the events were triggered by Amazon’s storage auto-scaling features for RDS and Aurora, which have become increasingly popular among our customers. We spoke with a few customers using storage auto-scaling and learned that the way we handled hardware changes was a major pain point since they happen a lot more frequently, and having to re-enable auto-tuning each time becomes a chore.

We’re excited to announce that OtterTune now automatically handles DB instance storage size changes, without any disruptions to you or your database tuning efforts. When OtterTune detects a change, it’ll automatically adapt data and knob bounds for the latest storage size and then continue tuning your database.

✨ Carrying over requested features from OtterTune Classic

Some of our long-time fans may already know this, but for those of you who don’t – OtterTune version 1.0 (now referred to as “OtterTune Classic”) was built with the needs of researchers and academics in mind. When we moved to OtterTune version 2.0, we shifted our focus to satisfying the daily needs of those who manage their teams’ databases, whether that person is a database administrator or developer, or someone else tasked with that responsibility.  

When some of our users noticed that functionality was missing and reached out, we prioritized adding those feature functionalities to OtterTune v2.0.

🎛️ Configurations

Configurations enables you to drill down and analyze the configurations that have been applied to your database in greater detail. It makes it easy to view the raw knob values in any configuration and compare them with other configurations. 

The list of configurations is sorted by the time it was applied by default, starting with the latest, but you can also sort by the target objective to see which configurations have achieved the highest performance so far.

📈 Performance charts

The Performance Charts now have all the same bells and whistles provided in v1.0. You’re able to create custom performance charts to analyze current and historical performance of the metrics, target objectives, and knobs among the databases in your fleet.

On the Performance Charts, you can check the “Show Configurations” box to see the configurations that were applied to your database right on the chart. From here, you can click on that config to see the full details, compare it to other configurations, or even apply it to your database right away.

🛠️ Enhancements to Query and Index Recommendations

👍 Query Recommendations

Earlier this year, we released our long-awaited Query Recommendations feature. Since release, we’ve made several impactful improvements. We’ve reduced the amount of noise in our query checks by filtering out “non-slow” queries (that run in less than 1 second), tightening the query-check failure criteria, and improving the accuracy of our checks.

We’ve added a handful of new query checks to identify and help resolve potential issues with slow queries. Here are a few of them:

• We check for queries that have a LIMIT, ORDER BY (or equivalently, min/max), and WHERE clause because it may counter-intuitively slow down queries, especially when the WHERE clause has low selectivity. We discuss the details in this blog article. 

• We check for too many OR clauses in queries (the threshold is 10), as described in this blog article. 

• We check if queries have a NOT IN predicate that excludes many values (the threshold is 25).

🗄️ Index Recommendations

We’ve further refined our Index Recommendations feature to more accurately identify the unused and duplicate indexes that are good candidates for removal, primarily focused on ensuring OtterTune is omitting primary keys from consideration. We also display the index type and referencing columns of indexes (when available).

You can also see all indexes reported to OtterTune via the UI now. 

👏 Onboarding enhancements for new users

Getting the right connections and permissions to interact with databases is no joke! We’ve put a lot of effort into making this process simpler and easier to troubleshoot for any issues encountered without needing to reach out to our support team (but we’re always happy to chat with you and provide support via Slack, email, chat or a video support call if needed).

🚫 Error handling

If users encounter errors during the IAM role or agent-connection process, we don’t simply flash a generic error message – we make the error message tangible, i.e., the user can act off it and should be able to quickly resolve the issue like in this example:

🎦 Videos, videos and more videos

We get it: some people like written documentation while others like instructional videos. We continue to update and improve our documentation, but for those that are more visual learners, we’ve also made instructional videos for the main steps you need to complete to set up OtterTune.

Additional videos we've created can be found below, and you can always access this playlist on YouTube for all our tutorial videos.

• How to set up an IAM role

• How to connect the OtterTune agent to your database

• How to enable a database in OtterTune

• How to add additional AWS accounts

🪜 Progress stepper

Four steps are required to get up and running with OtterTune: add an IAM role, enable a database, add our agent and apply knob recommendations. OtterTune now features a progress stepper that’s pinned to the right side of the screen to let users know if they still need to complete a step and it links to relevant documentation about that step.

🦦 Agent status and troubleshooting tips

OtterTune’s agent is critical – it’s what listens and talks to your databases and helps inform our AI. Sometimes the agent can get disconnected and to help users more easily debug what is going on, we now provide an Agent Status section that reports on the health of the agent and offers an error log and suggestions to fix it if the connection is incomplete.

⏲️ Best configuration timeline and tracking

OtterTune’s AI needs to apply eight sets of knob configurations before it can get to the best configuration that improves your target objective over baseline, or where you started. The quicker users get to their initial best configuration, the faster our AI can be trained to improve database performance.

To help make that clearer and give it a visual treatment, we’ve added an pie chart visualization that helps users track their progress:

The team has been primarily focused on elevating the OtterTune application experience this year. We’ve been tweaking and improving feature functionality, user-experience flows, error handling, and UI components for a smoother, easier-to-use OtterTune. 

We’d love your feedback on improvements and new features! Please get in touch with us or submit a feature request.