Which MySQL configuration settings does OtterTune optimize?

OtterTune automatically optimizes Amazon RDS MySQL and Amazon Aurora MySQL configuration settings (“knobs”) to help database users get better database performance at a lower cost and with less DBA effort:

OtterTune works by analyzing a database’s workload, and then using machine learning algorithms to optimize each of 150+ MySQL configuration settings to support the database’s specific workload. There are 3 questions people commonly ask about OtterTune:

1. Does OtterTune need to access my data or queries? No (“How OtterTune Securely Collects Metrics“).

2. Can I control how OtterTune makes changes to my database settings? Yes (“Human-in-the-Loop Control Over Auto-tuning“).

3. What MySQL configuration settings does OtterTune optimize? Read on to learn more about what knobs OtterTune optimize.

Which MySQL configuration settings does OtterTune auto-tune?

OtterTune analyzes and auto-tunes over 150 different MySQL knobs on Amazon. The exact list of knobs depends on two things:

The edition of OtterTune you run:

• OtterTune Starter edition – free version, tunes a core set of 15 MySQL knobs.

• OtterTune Standard and Enterprise edition – paid versions, which tunes 150+ MySQL knobs.

The version of MySQL you run:

• As new, performance-related knobs are added to MySQL, OtterTune adds support for them.

• Amazon Aurora MySQL implements a different storage layer than Amazon RDS MySQL and has fewer adjustable configuration settings. Therefore, Aurora users should note that OtterTune optimizes a slightly smaller subset of the knobs it tunes for Amazon RDS MySQL.

Below, we list the Amazon RDS MySQL knobs that OtterTune optimizes.

“Core” MySQL knobs tuned by the free OtterTune starter edition

The free OtterTune Starter Edition tunes the following Amazon RDS MySQL configuration settings for MySQL versions 5.6 and higher, except where noted):

• innodb_adaptive_flushing_lwm

• innodb_adaptive_hash_index

• innodb_adaptive_max_sleep_delay

• innodb_buffer_pool_instances

• innodb_buffer_pool_size

• innodb_change_buffering

• innodb_io_capacity

• innodb_log_file_size

• innodb_max_dirty_pages_pct

• innodb_max_dirty_pages_pct_lwm (versions 5.7 and higher)

• innodb_sync_array_size

• innodb_thread_concurrency

• max_heap_table_size

• thread_cache_size

• tmp_table_size

150+ MySQL knobs tuned by OtterTune standard and enterprise editions

The paid OtterTune Standard and Enterprise Editions tune the same knobs as OtterTune Starter, above, plus these additional 150+ knobs, depending on which version of MySQL you run:

Knobs tuned in MySQL 5.6 and higher (except where noted)

• binlog_cache_size

• binlog_max_flush_queue_time

• binlog_stmt_cache_size

• eq_range_index_dive_limit

• host_cache_size

• innodb_adaptive_flushing

• innodb_autoextend_increment

• innodb_buffer_pool_dump_now

• innodb_buffer_pool_load_at_startup

• innodb_buffer_pool_load_now

• innodb_change_buffer_max_size

• innodb_commit_concurrency

• innodb_compression_failure_threshold_pct

• innodb_compression_level

• innodb_compression_pad_pct_max

• innodb_concurrency_tickets

• innodb_flush_log_at_timeout

• innodb_flush_neighbors

• innodb_flushing_avg_loops

• innodb_ft_cache_size

• innodb_ft_result_cache_limit

• innodb_ft_sort_pll_degree

• innodb_io_capacity_max

• innodb_lock_wait_timeout

• innodb_log_buffer_size

• innodb_lru_scan_depth

• innodb_max_purge_lag

• innodb_max_purge_lag_delay

• innodb_old_blocks_pct

• innodb_old_blocks_time

• innodb_online_alter_log_max_size

• innodb_page_size

• innodb_purge_batch_size

• innodb_purge_threads

• innodb_random_read_ahead

• innodb_read_ahead_threshold

• innodb_read_io_threads

• innodb_replication_delay

• innodb_rollback_segments

• innodb_sort_buffer_size

• innodb_spin_wait_delay

• innodb_sync_spin_loops

• innodb_thread_sleep_delay

• innodb_use_native_aio

• innodb_write_io_threads

• join_buffer_size

• lock_wait_timeout

• max_binlog_cache_size

• max_binlog_size

• max_binlog_stmt_cache_size

• max_delayed_threads

• max_insert_delayed_threads

• max_join_size

• max_length_for_sort_data

• max_seeks_for_key

• max_sort_length

• max_sp_recursion_depth

• max_tmp_tables (versions 5.6 and 5.7 only)

• max_write_lock_count

• metadata_locks_cache_size (versions 5.6 and 5.7 only)

• optimizer_prune_level

• optimizer_search_depth

• preload_buffer_size

• query_alloc_block_size

• query_cache_limit (versions 5.6 and 5.7 only)

• query_cache_min_res_unit (versions 5.6 and 5.7 only)

• query_cache_size (versions 5.6 and 5.7 only)

• query_cache_type (versions 5.6 and 5.7 only)

• query_cache_wlock_invalidate (versions 5.6 and 5.7 only)

• query_prealloc_size

• range_alloc_block_size

• read_buffer_size

• read_rnd_buffer_size

• slave_checkpoint_group

• slave_checkpoint_period

• slave_parallel_workers

• slave_pending_jobs_size_max

• sort_buffer_size

• stored_program_cache

• table_definition_cache

• table_open_cache

• table_open_cache_instances

• thread_stack

• timed_mutexes (version 5.6 only)

• transaction_alloc_block_size

• transaction_prealloc_size

Knobs tuned in MySQL 5.7 and higher (except where noted)

In MySQL 5.7, OtterTune tunes all of the knobs listed above (except where noted), plus these knobs:

• binlog_group_commit_sync_delay

• binlog_group_commit_sync_no_delay_count

• innodb_adaptive_hash_index_parts

• innodb_buffer_pool_chunk_size

• innodb_buffer_pool_dump_pct

• innodb_disable_sort_file_cache

• innodb_flush_sync

• innodb_ft_total_cache_size

• innodb_log_write_ahead_size

• innodb_max_undo_log_size

• innodb_page_cleaners

• innodb_purge_rseg_truncate_frequency

• range_optimizer_max_mem_size

• rpl_stop_slave_timeout

• slave_allow_batching

• slave_parallel_type

Knobs tuned in MySQL 8

In MySQL 8, OtterTune tunes all of the knobs listed above (except where noted), plus these knobs:

• innodb_dedicated_server

• innodb_doublewrite_batch_size

• innodb_doublewrite_files

• innodb_doublewrite_pages

• innodb_log_files_in_group

• innodb_log_spin_cpu_abs_lwm

• innodb_log_spin_cpu_pct_hwm

• innodb_log_wait_for_flush_spin_hwm

• max_relay_log_size

• open_files_limit

• parser_max_mem_size

• relay_log_space_limit

• rpl_read_size

• stored_program_definition_cache

• tablespace_definition_cache

• temptable_max_ram

Try it yourself

If you’d like to learn more about how OtterTune automates MySQL database configuration tuning, watch this short video.