Tuning Options
The Tuning Options panel allows you to control when and how OtterTune connects to the database and makes changes to its configuration.
You can access the Tuning Options panel from the Database Overview tab by either selecting Edit in the Status panel or Settings -> Edit tuning options in the bottom left corner.
Changes to any of the Tuning Options will not be saved until you select the Finish button at the bottom of the page.
The Tuning Mode determines whether OtterTune is actively optimizing the database. You can change the Tuning Mode at any time. OtterTune starts every new database in "Monitoring" mode by default. If you change the Tuning Mode for a database, then OtterTune will remain in that mode until you change it again. In other words, OtterTune does not automatically switch from "Optimizing" mode to "Monitoring" mode.
Mode | Description |
|---|---|
Optimizing | This mode allows OtterTune to make changes to the database configuration. It also collects metrics from the database in the same manner as the "Monitoring" mode. |
Monitoring | Under this mode, OtterTune collects the metrics from the database. It does not make changes to the database's configuration. OtterTune collects metrics once per minute. |
The Approval Method toggle will let you control if OtterTune can automatically make changes to your database, or whether it should prompt you for approval of any recommendations it generates. We will send you email notifications to let you know when a Recommendation is ready for review. Don't worry, you can unsubscribe if you'd prefer to keep your inbox tidier. You will approve or reject recommendations from the Review tab on OtterTune.
The Tuning Interval specifies how often OtterTune will install a new configuration when in "Optimizing" mode. Changing this setting allows you to control the frequency of configuration changes and the time window that OtterTune considers when generating optimized configurations. The time of the next tuning takes both Tuning Schedule and the time of the last tuning into consideration. Regardless of whether or not your database is in "Optimizing" mode, OtterTune collects database metrics every minute.
We recommend that you leave the default setting of 24 hours (1,440 minutes) when starting out with trying to optimize the database. Using a shorter tuning interval could cause OtterTune to optimize for a segment of the database's workload that is not representative of the overall workload.
The Target Objective is what OtterTune will optimize for when it generates new configurations for the database. You can change the Target Objective to guide OtterTune towards optimizing performance versus cost.
MySQL
PostgreSQL
Objective | Description |
|---|---|
Throughput | The number of transactions that the database completes. OtterTune computes MySQL's throughput by adding together the following metrics from MySQL's INNODB_METRICS internal table: trx_rw_commits+ trx_ro_commits+ trx_nl_ro_commits. |
AverageLatency | The average latency (in milliseconds) for disk operations as reported by Amazon CloudWatch. OtterTune computes this by adding together CloudWatch's ReadLatency + WriteLatency |
P99QueryLatency | The estimated P99 query latency calculated from query digest. We only collect query IDs and latencies. We do not collect query details, tables or schemas. |
Objective | Description |
|---|---|
Throughput | The number of transactions that the database completes. OtterTune computes PostgreSQL's throughput from the xact_commitmetric in thepg_stat_databasestatistics table. |
AverageLatency | The average latency (in milliseconds) for disk operations as reported by Amazon CloudWatch. OtterTune computes this by adding together CloudWatch's ReadLatency + WriteLatency |
P99QueryLatency | The estimated P99 query latency calculated from query digest. We only collect query IDs and latencies. We do not collect query details, tables or schemas. |
It is possible to configure OtterTune to optimize other objectives in your database, such as CPU utilization or storage IOPs. Please contact us to discuss if you need this capability.
The Aurora Tuning Level specifies whether OtterTune will tune the instance-level or cluster-level parameter group for your AWS Aurora DB cluster. Please see the AWS Aurora documentation for more details.
AWS RDS exposes the database knobs that OtterTune tunes in the form of Parameter Groups. If you’d like OtterTune to tune your existing parameter group, we’d recommend first copying the parameter group to a new parameter group. This will allow you to switch back to your old, stable parameter group that OtterTune will not tune. Please see the AWS RDS Parameter Group documentation for more details.
The Parameter Group ARN can be constructed with the following format:
arn:aws:rds:<region>:<account>:pg:<parameter_group_name>(Aurora only) While Instance-level Parameter Groups have the same format as above, Cluster-level Parameter Groups have a different format:
arn:aws:rds:<region>:<account>:cluster-pg:<cluster_parameter_group_name>Ottertune cannot tune the default parameter group, you must select a custom parameter group.
OtterTune needs permission to modify your database's parameter group. The following instructions walk through how to update the IAM role you created previously with the correct permissions to do this.
Follow the instructions for the method you chose to set up the necessary AWS role.
- Automatic Setup with CloudFormation: Update the AWS IAM role by modifying the CloudFormation template.
- 1.
- 2.Find and select the stack you used to install the OtterTune integration.
- 3.Select Update.
- 4.Select Use current template.
- 5.Select Next.
- 6.Enter the tunable parameter group ARNS (see above for formatting guidelines for the parameter group ARNS). If you are tuning RDS Aurora (available with our Standard/Enterprise plans), enter the tunable Aurora cluster parameter group ARNs instead.
- 7.Select Next.
- 8.Select Use existing role for the permission and Roll back all stack resources for the stack failure option.
- 9.Select Next.
- 10.Check the acknowledgment box at the bottom of the form.
- 11.Select Update stack.
- 1.
- 2.Find and select the IAM role you created previously for OtterTune from the list.
- 3.Select Add inline policy.
- 4.Select the JSON tab and copy the policy below into the text box.
- 5.Select Review policy.
- 6.For the Name, enter a descriptive policy name such as
OtterTuneModifyPGPolicy. - 7.Select Create policy.
Non-Aurora RDS
Aurora RDS
{
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Action": "rds:ModifyDBParameterGroup",
"Resource": "arn:aws:rds:<region>:<account>:pg:<parameter_group_name>"
}
]
}
{
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Action": "rds:ModifyDBClusterParameterGroup",
"Resource": "arn:aws:rds:<region>:<account>:cluster-pg:<cluster_parameter_group_name>"
}
]
}
Database restarts are available with our Standard and Enterprise plans. Please see our pricing page or contact us if you need this capability.
OtterTune supports tuning both dynamic knobs and static knobs. With a dynamic knob, OtterTune can change its value in the database and have it take effect without restarting the database first. Static knobs, on the other hand, do require a database restart. This setting allows you to control whether OtterTune is allowed to restart the database when it is in the "Optimizing" tuning mode. If the restart setting is disabled, then OtterTune will not attempt to modify configuration knobs that require restarting.
OtterTune automatically recognizes whether a configuration that it generates for your database requires restarting. Even though you may have enabled restarts, OtterTune will not restart your database if it is not needed for a new configuration.
Since OtterTune is not aware of how applications are using a database, it does not know whether restarting the database will have adverse effects. Restarting the database means that your application will not be able to connect to the database and execute queries. How long it takes before your database comes back online after restarting also varies depending on many factors. Thus, you should only choose this option if you are comfortable with downtime.
If you enable database restarts, we recommend that you control how many times OtterTune is allowed to restart the database in a Tuning Schedule to limit the amount of incurred downtime.
OtterTune's Tuning Schedule allows you to specify time windows of when the service will attempt to optimize the database's configuration. These schedules allow you to avoid potential performance degradation at inopportune times (e.g., during an application's peak usage). Some customers have also used schedules to prevent OtterTune from optimizing the database outside of working hours.
Each Tuning Period entry in the Tuning Schedule is comprised of the day of week (Day) and a time window (Tuning Period). For example, if we want to only allow OtterTune to optimize the database's configuration from 9:00am to 5:00pm on Mondays, we click "Add a tuning period" and then specify the Day and Tuning Period.
Note that all times in the Tuning Periods are based on the UTC timezone. Be sure to adjust the times from other timezones accordingly.
Each entry in the Tuning Schedule contains actions on the right-hand side of the table. The Delete button removes the entry. The Copy button duplicates the entry.
If you enable Database Restarts, then the Tuning Schedule panel will also include the option to specify for which schedule entries is OtterTune allowed to restart the database. This allows you to control when OtterTune will potentially restart the database.
To enable restarts, enable the "Allow Database Restarts" toggle in the Tuning Schedule for a particular entry. Enabling this will expand the entry with additional options. There are two operating modes for restarting: (1) allow OtterTune to restart the database at any time with the tuning period and (2) restrict OtterTune to only restart the database within a subset of the tuning period using a schedule.
Mode | Description |
|---|---|
Restarting Anytime | With this mode, OtterTune is allowed to restart the database whenever it deems it necessary within the tuning period. |
Restart Schedule | A Restart Schedule allows you to control what time ranges within a Tuning Period that OtterTune is allowed to restart the database. Each Restart Period has a start time and end time (in UTC) that specifies when OtterTune could restart the database. |
For both modes, you can also control how many times OtterTune will restart the database system. For example, if you only want OtterTune to restart the database once per day, you set the "Restart Count Limit" field to "1". If you do not want to limit the number of restarts, toggle the "Unlimited" checkbox.
If OtterTune's Tuning Mode is set to "Optimizing", it will generate new configurations to improve your target objective function. The Tuning Knobs panel enables you to control which knobs to tune and their allowed values.
OtterTune maintains a list of recommended knobs for each database type (e.g., PostgreSQL, MySQL) that it has learned will have the most positive impact on a database's performance. These knobs are listed in the "Recommended" section of the Tuning Knobs panel. All recommended knobs will be selected by default the first time you switch the Tuning Mode to "Optimizing".
Allowed values: OtterTune also suggests ranges for recommended knobs that are based on the database's original con instance's available hardware resources, the database's original knob settings, and domain knowledge.
The recommended knobs and allowed values are guidelines and may not be appropriate for all operating environments. We highly recommend that you review them thoroughly before tuning your database to avoid performance degradation or downtime due to misconfiguration.
The ability to tune knobs other than those recommended is available with our Standard and Enterprise plans. Please see our pricing page or contact us if you need this capability.
All other knobs that OtterTune can tune are listed in the "Other knobs" section (visible after you scroll down past all recommended knobs). Note that there are some knobs that OtterTune cannot optimize. See our FAQ for details.
Allowed values: The ranges for other knobs are set to the minimum and maximum values permitted by the database system.
If you choose to tune other (non-recommended) knobs, it is very important that you select allowed values that are appropriate for your database and operating environment. For resource knobs, be sure that the maximum values you choose do not exceed the available resources for the instance.
Last modified 4mo ago