Frequently Asked Questions
This page contains answers to the most common and pressing questions that we get about OtterTune. We will cover what databases OtterTune supports and how does the service work.
A "knob" is a configuration parameter that a database exposes that can control some aspects of its runtime behavior, such as buffer pool sizes, caching policies, and other features. Every database system has knobs, but they may call them different names. For example, MySQL calls them system variables and has over 500 of them. Postgres calls them setting parameters and has approximately 300 of them.
OtterTune collects configuration knobs (e.g., the size of the buffer pool or redo log) and runtime metrics (e.g., the number of transactions executed or dirty pages in the buffer pool) from the target database every minute. It also collects table metrics (e.g., the number of scans in a table), index metrics (e.g., the number of scans in an index), schema data, and query digest (the query is normalized and user data is removed) every hour. It does not access any sensitive application or user data. You can disable collecting any of them in the agent. For example, you can set the variable DisableSchemaMonitoring to true to disable schema collection in the agent.
As of March 2022, OtterTune supports the following Amazon RDS databases:
- PostgreSQL (versions 10, 11, 12, 13, 14)
- MySQL (versions 5.7, 8.0)
- Aurora Postgres (versions 10, 11, 12, 13)
- Aurora MySQL (versions 5.6, 5.7)
- Aurora Serverless Postgres (versions 13, 14)
- Aurora Serverless MySQL (version 8.0)
Many factors will affect how long it takes for OtterTune to generate a better configuration for your database. Some of these factors include:
- How many knobs is the service tuning?
- How complex is the workload?
- How often does the service try a new configuration?
- How many databases that OtterTune has tuned in the past that are similar to the new database?
Database tuning is a complex problem. With that said, in practice, we have found that OtterTune produces some initial improvement within days.
OtterTune automatically analyzes and optimizes database configuration settings. It provides index health checks that identify duplicate and unused indexes. OtterTune will very shortly support query recommendations. Please check back for more details soon!
OtterTune can tune most knobs that control resources and policies (e.g., how much memory to use per query or the checkpointing frequency). Nevertheless, there are certain knobs that OtterTune cannot tune. They generally fall into one of the following groups:
- 1.Knobs that are read-onlyDatabase systems have "internal" knobs that cannot be modified after instance initialization (usually compile-time constants). Cloud providers also disable knobs that are irrelevant for their architectures or customizations. For example, AWS Aurora provides highly customized MySQL and PostgreSQL engines. They removed several knobs, namely from the storage layer, that no longer served a purpose (e.g., MySQL's
innodb_log_file_size, PostgreSQL'smax_wal_size). - 2.Knobs that do not make sense to tuneMany of a database's knobs do not make sense to tune. This includes knobs that specify the locations of resources (file paths) and how to interact with the outside world (network ports). Examples include MySQL's
datadirthat specifies the path to the data directory and PostgreSQL'slisten_addressesthat sets the hostname to listen to. - 3.Knobs that require human judgmentIt's best to have a human in the loop when configuring knobs that have tradeoffs since poor settings can have hidden or severe consequences. For example, changing the default values of MySQL's
innodb_flush_at_trx_commitand PostgreSQL'sfsyncknobs can improve performance but may result in data loss or corruption in the event of a crash. We do not allow OtterTune to tune knobs like this.
There are two requirements for the security group and subnet chosen to host your Agent:
- 1.They must have connection access to your database. This can be controlled to only PostgreSQL or only MySQL traffic depending on your database.
- 2.They must have outbound https access to all ipv4 addresses (0.0.0.0/0).
For instance, if you have an application that connects to your database within AWS that can send outbound information via https, you can use the same security group and subnet for your agent.
If you choose to use the same security group and subnet your database itself is in, make sure that you allow self-referential traffic as this is not true by default; ie: make an outbound rule from the group to itself and an inbound rule from the group to itself for database traffic.
Last modified 2mo ago