MySQL database performance issues, such as high memory usage, slow queries, and “server has gone away” errors, are common concerns. This article addresses these issues by providing solutions to common MySQL configuration mistakes. DBAs are encouraged to share their experiences and suggestions in the comments. The focus is on MySQL configuration variables within the my.cnf file that can negatively impact database performance and scaling due to insufficient server memory for incoming connections. This guidance is also applicable to MariaDB and Percona.
Update: Key Changes for Newer MySQL Versions
Newer MySQL 8.x releases introduce minor changes to default settings and the performance_schema instruments. Default timeouts like wait_timeout, interactive_timeout, and connect_timeout might vary slightly. More granular metrics are available for real-time memory usage tracking.
InnoDB continues to be optimized for crash recovery and high concurrency, whereas MyISAM is becoming less common. The advice provided here regarding per-connection buffers – avoiding excessive increases, utilizing proper indexing, and gradually adjusting memory-related variables – remains relevant.
Be aware that MariaDB and Percona may have diverged from upstream MySQL in certain features. Small differences in default configurations or performance_schema data may be noticeable if you are not running Oracle’s MySQL. Always monitor real-world workloads and make incremental adjustments.
Additional my.cnf Performance Configuration Tips
To effectively tune the configuration lines below, monitor the relevant MySQL status variables.
max_heap_table_size
max_heap_table_size is a MySQL configuration parameter that determines the maximum size of in-memory temporary tables created using the MEMORY storage engine. If a query creates a temporary table larger than max_heap_table_size, it is automatically converted to an on-disk table, which can reduce performance.
Step 1: Set max_heap_table_size to the same value as tmp_table_size.
tmp_table_size
tmp_table_size is a MySQL configuration parameter that specifies the maximum size of in-memory temporary tables used by the server. When a query creates a temporary table that exceeds the tmp_table_size, the table is converted to an on-disk table, potentially decreasing performance.
Step 1: Set tmp_table_size to a reasonable value based on the available memory and the created_tmp_disk_table variable.
interactive_timeout
interactive_timeout is a MySQL configuration parameter defining the number of seconds the server waits for activity on an interactive connection before closing it. A high interactive_timeout can lead to increased memory usage and concurrent connections, while a low value may result in frequent disconnections for long-running queries.
Step 1: Set interactive_timeout to a value based on the expected duration of your longest-running requests.
connect_timeout
connect_timeout is a MySQL configuration parameter defining how long the server waits for a successful connection to be established before timing out. A high connect_timeout can increase wait times for clients and increase max_connections, while a low value can cause connection failures for slow or heavily loaded servers.
Step 1: Set connect_timeout to a reasonable value based on the expected response time of the server.
Step 2: Consider factors like network latency, mobile users, and server load when tuning. A setting of 15 seconds or less is generally suitable.
For example, a blog’s MySQL connection timeout might be set to 5 seconds.
wait_timeout
wait_timeout is a MySQL configuration parameter that sets the number of seconds the server waits for activity on a non-interactive connection before closing it. A high wait_timeout increases memory usage and the number of connections, whereas a low value can cause frequent disconnections for long-running queries.
Step 1: Set wait_timeout to the lowest reasonable expected duration of non-interactive connections and adjust as necessary.
Step 2: The default value of 28800 seconds is often too high and consumes excessive system memory. Consider a setting of 30 seconds or less.
table_open_cache
table_open_cache is a MySQL configuration parameter determining the number of open table objects that can be stored in the cache. A high table_open_cache improves performance by reducing the time required to open tables, whereas a low value increases the overhead of opening tables from disk.
Step 1: Monitor the opened_tables and open_tables status variables.
Step 2: Set table_open_cache to approximately 2x the number of open tables and adjust as needed.
Step 3: Consider factors like memory and query complexity during tuning.
Step 4: Generally, table_open_cache and table_definition_cache are set to the same value to ensure consistent and efficient storage of metadata and open table objects in the cache.
table_definition_cache
table_definition_cache is a MySQL configuration parameter that sets the number of table definitions (metadata) that can be stored in the cache. A high table_definition_cache can improve performance by reducing the time required to open tables, while a low value can increase the overhead of reading the table definition from disk.
Step 1: Monitor the opened_table_definitions and open_table_definitions status variables.
Step 2: Set table_definition_cache to approximately 2x the number of open table definitions and adjust as needed.
Step 3: Consider factors like system memory and query complexity during tuning.
thread_cache_size
thread_cache_size determines the number of threads that the server can cache and reuse. Proper tuning of this parameter can improve MySQL server performance by reducing the overhead of creating new threads.
Step 1: Monitor the threads_created and threads_cached status variables.
Step 2: Start with a value of 16.
Step 3: Gradually increase the value until it is at least 50% of max_connections.
For example, if threads_cached is 16 and max_used_connections is 40, increase thread_cache_size from 16 to 32. Also, if max_used_connections is 40, set max_connections to at least 80.
max_connections
max_connections is a MySQL configuration parameter that sets the maximum number of concurrent connections the server can handle. An excessively high value can allocate too much server memory, leading to performance problems, while a value that is too low can result in connection rejections.
Step 1: Monitor the max_used_connections status variable.
Step 2: Aim to set max_connections to approximately 2x to 3x your maximum used connections after a few days of uptime.
Step 3: If max_connections exceeds 200, ensure there are at least 100 extra connections available. For instance, if max_used_connections is 300, set max_connections to at least 400.
Step 4: Monitor memory usage.
Avoid Arbitrarily Increasing MySQL Per-Connection Buffers
It’s a poor practice to arbitrarily increase the values of my.cnf variables without a clear understanding of the implications. This is especially true for the four variables discussed below. While increasing the value of some variables can improve performance, increasing these four will almost always degrade MySQL server performance and capacity.
The four buffers in question are join_buffer_size, sort_buffer_size, read_buffer_size, and read_rnd_buffer_size.
These four buffers are allocated per connection. For example, join_buffer_size=1M with max_connections=200 will configure MySQL to allocate an additional 1MB per connection (1MB x 200). The same applies to the other three buffers.
In nearly all cases, it’s best to keep the defaults by removing or commenting out these four configuration lines. As connections increase with traffic, queries that need more space than is available due to larger buffer settings may trigger paging those buffers to disk, which dramatically slows down the database server.
Reverting these buffers to their defaults often significantly improves MySQL performance.
MySQL sort_buffer_size
Unless there’s data indicating otherwise, avoid arbitrarily increasing the sort_buffer_size. Memory here is also assigned per connection.
MySQL’s documentation warns: “On Linux, there are thresholds of 256 KB and 2 MB where larger values may significantly slow down memory allocation, so you should consider staying below one of those values.”
Avoid increasing sort_buffer_size above 2MB, since there is a performance penalty that eliminates any potential benefits.
MySQL read_buffer_size & read_rnd_buffer_size
Update: read_buffer_size applies generally to MyISAM.
Each request that performs a sequential table scan allocates a read buffer. The read_buffer_size system variable determines the buffer size.
From MySQL 8.0.22, the value of select_into_buffer_size overrides read_buffer_size when performing SELECT INTO DUMPFILE and SELECT INTO OUTFILE statements. read_buffer_size is used for the I/O cache buffer size in all other cases.
The read_rnd_buffer_size variable is also used mainly for MyISAM reads from tables. Consider InnoDB or MariaDB’s Aria storage engines. The default values of these buffers have remained the same for the past decade.
MySQL join_buffer_size
The join_buffer_size is allocated for each full join between two tables. From MySQL’s documentation, join_buffer_size is described as: “The minimum size of the buffer that is used for plain index scans, range index scans, and joins that do not use indexes and thus perform full table scans.”
MySQL’s documentation states: “Memory allocation time can cause substantial performance drops if the global size is larger than needed by most queries that use it.” The join buffer is allocated to cache table rows when the join can’t use an index.
If your database suffers from many joins performed without indexes, increasing join_buffer_size is not the solution. The core issue is “joins performed without indexes,” and the solution is to add indexes.
In conclusion, optimizing MySQL’s performance involves addressing common issues such as memory usage, slow queries, and connection errors by carefully configuring key variables. Remember to exercise caution and test changes incrementally to avoid unintended consequences.