MySQL Database Performance: Avoid this common mistake

One frequent topic of support request emails I receive is MySQL database performance. Clients complain about MySQL using too much server memory, too many MySQL slow queries, the famous Mysql server has gone away errors, and many other MySQL performance-related issues. As such, I wanted to share solutions to some common MySQL configuration mistakes.

If you are a DBA, feel free to share your experiences or suggestions in the comments section. I’m referring to four MySQL config variables. These four my.cnf config lines are often responsible for poor MySQL database performance and scaling due to insufficient server memory for incoming connections. This article also applies to MariaDB and Percona drop-in replacements for MySQL.

Note: This is an update and follow-up to this article. I’ve added some relevant and more up-to-date information and links, as well as a new section that covers some additional my.cnf performance config tips.

Avoid arbitrarily increasing MySQL per-connection buffers

Excerpt of my.cnf edits for a client. (some lines were removed and some blurred to avoid misuse)

Who knows where or when this bad practice started. I frequently encounter instances where I log into production servers for the first time and find that the values of virtually every my.cnf variable have been arbitrarily increased without sound reasoning behind the changes.

This is never a good practice, especially for four my.cnf variables discussed below. Although increasing the value of some variables can improve performance, these four will almost always degrade MySQL server performance and capacity when increased.

The four buffers in question are the join_buffer_size, sort_buffer_size, read_buffer_size and read_rnd_buffer_size.

These four buffers are allocated per connection. For example, a setting of join_buffer_size=1M with max_connections=200 will configure MySQL to allocate an additional 1M per connection (1M x 200). The same goes for the other three buffers. Again, all are per connection.

A good rule of thumb is that if you can’t provide a valid reason to increase any of these buffers, keep them set to the default values.

In nearly all cases, it’s best to keep the defaults by removing or commenting out these four config lines. As connections increase with traffic, queries that need more space than what’s available due to larger buffer settings may trigger paging those buffers to disk. This dramatically slows down your DB server and creates a bottleneck.

I’ve often seen significantly improved MySQL performance by simply reverting these buffers to their defaults. Also, read Analyzing Linux server performance with atop.

The above screenshot shows MySQL status output two weeks after I made the first my.cnf optimization pass. Now, let’s take a closer look at each of these buffers.

MySQL join_buffer_size

The join_buffer_size is allocated for each full join between two tables. From MySQL’s documentation the 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 goes on to say: “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, it cannot be solved by increasing join_buffer_size. The problem is “joins performed without indexes.” Thus, the solution for faster joins is to add indexes.

MySQL sort_buffer_size

Unless you have data indicating otherwise, you should avoid arbitrarily increasing the sort_buffer_size. Memory here is also assigned per connection!

MySQL’s documentation warns: “On Linux, there are thresholds of 256KB and 2MB 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 2M since there is a performance penalty that will eliminate benefits.

MySQL read_buffer_size & read_rnd_buffer_size

Update: Some of you still use MyISAM over InnoDB. MyISAM is based on the old ISAM storage engine. It has many useful extensions, as discussed here. You should consider converting your MySQL tables to the InnoDB storage engine.

InnoDB is the default storage engine of MySQL 5.7 and MySQL 8.0. InnoDB features improved performance, rollback and crash-recovery capabilities to protect data. 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.

Starting from MySQL 8.0.22, the value of select_into_buffer_size overrides the value of 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. Again, consider InnoDB or MariaDB’s Aria storage engines. For the past decade, the default values of these buffers have remained the same.

Additional my.cnf Performance config tips

Monitor the appropriate MySQL status variable_name(s) to assist you in tuning the below config lines.

An example of viewing the status of MySQL runtime variables. (So I increased max_connections)

max_connections

A MySQL configuration parameter that sets the maximum number of concurrent connections the server can handle. A value that is too large can allocate too much server memory and lead to performance issues, while a low value can result in connection rejections.

To tune this parameter, monitor the max_used_connections status variable; try to set this to ~ 2x to 3x your max used connections after a few days of uptime. However, if your MySQL max connections are more than 200 then make sure to have at least 100 extra connections available. So if max_used_connections is 300 then set max_connections to at least 400. Watch memory usage!!

thread_cache_size

Determines the number of threads that can be cached and reused by the server. Properly tuning this parameter can help improve the performance of your MySQL server by reducing the overhead of creating new threads and freeing up system resources.

To tune this parameter, you can monitor the threads_created and threads_cached status variables and set the value to a reasonable number based on the number of expected concurrent connections. As a general rule, a value of 16 is a good starting point. Then increase gradually until it’s at least 50% of max_connections. For example, if the threads_cached value is 16 and the max_used_connections value is 40 then increase this from 16 to 32. At the same time, as per above advice, if max_used_connections is 40 then your max_connections should be at least 80.

table_definition_cache

A MySQL configuration parameter that sets the number of table definitions (metadata) that can be stored in the cache. A high value of table_definition_cache can improve performance by reducing the time required to open tables, while a low value can result in increased overhead of reading the table definition from disk.

To tune this parameter, monitor the opened_table_definitions and open_table_definitionsstatus variables, set table_definition_cache to ~ 2x the number of open table definitions and adjust as needed. Consider other factors, such as system memory and query complexity when tuning. .

table_open_cache

A MySQL configuration parameter that sets the number of open table objects that can be stored in the cache. A high value of table_open_cache can improve performance by reducing the time required to open tables, while a low value can result in increased overhead of opening tables from disk.

To tune this parameter, monitor the opened_tables and open_tables status variables, set table_open_cache to ~ 2x the number of open tables, and adjust as needed. Consider other factors such as memory and query complexity when tuning. Generally table_open_cache and table_definition_cache are set to the same value. The number of open table objects and table definitions are directly related, so setting these two parameters to the same value can ensure that the metadata and open table objects are stored in the cache in a consistent and efficient manner.

wait_timeout

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 value of wait_timeout can result in increased memory usage and increased # of connections, while a low value can result in frequent disconnections for long-running queries.

To tune this parameter, set wait_timeout to the lowest resonable expected duration of non-interactive connections, and adjust as needed. The default value is 28800 seconds. This is way to high in most cases and will consume a lot of system memory by keeping many connections open. I set most of my configs to 30 seconds or less.

connect_timeout

A MySQL configuration parameter that sets the number of seconds the server waits for a successful connection to be established before timing out. A high value of connect_timeout can result in increased wait time for clients and increase in max_connections. A low value can result in connection failures for slow or heavily loaded servers.

To tune this parameter, set connect_timeout to a reasonable value based on the expected response time of the server. Consider other factors such as network latency, mobile users, and server load when tuning. I set most of my configs to 15 seconds or less. For example, this blog’s mysql connection timeout is set to 5 seconds.

interactive_timeout

A MySQL configuration parameter that sets the number of seconds the server waits for activity on an interactive connection before closing it. A high value of interactive_timeout can result in increased memory usage and increased concurrent connetions. A low value can result in frequent disconnections for long-running queries.

To tune this parameter, set interactive_timeout to a reasonable value based on the expected duration of your longest-running requests.

tmp_table_size

A MySQL configuration parameter that sets the maximum size of in-memory temporary tables used by the server. When a query creates a temporary table that exceeds the value of tmp_table_size, the table will be automatically converted to an on-disk table, which can result in decreased performance.

To tune this parameter, set tmp_table_size to a reasonable value based on the available memory and the value of the created_tmp_disk_table variable.

max_heap_table_size

A MySQL configuration parameter that sets the maximum size of in-memory temporary tables created with the MEMORY storage engine. When a query creates a temporary table that exceeds the value of max_heap_table_size, the table will be automatically converted to an on-disk table, which can result in decreased performance.

To tune this parameter, set max_heap_table_size to the same value as tmp_table_size.

Want more tips?

For example, Innodb tuning tips, setting open_files_limit, etc. Let me know your interests in the comments section below, or hire me for MySQL optimization.

Additional MySQL performance related articles

• MySQL Performance Tuning: Tips, Scripts and Tools.
• MySQL Performance: Stop hoarding. Drop unused MySQL databases.
• “MySQL server has gone away” error – Solution(s).
• Could not increase number of max_open_files to more than… (Solution).
• Linux server performance: Is disk I/O slowing your application?
• Tuning MySQL my.cnf? Avoid this common pitfall!

Conclusion

In conclusion, we discussed some common issues with MySQL performance often raised in support requests. The focus was on four specific my.cnf variables frequently responsible for poor database performance and scaling due to a shortage of server memory.

It was emphasized that arbitrarily increasing these variables can lead to a decline in MySQL server performance and capacity and that it is important to keep these buffers set to their default values unless there is a specific reason to increase them. Then we covered tuning some additional MySQL performance config lines.

A good rule of thumb: if you can’t provide a valid reason for increasing these buffers, keep them set to the default values. This also applies less strictly to all of MySQL’s variables. Be careful when making changes, don’t overwrite your entire my.cnf all at once. Back up everything first, make one or two changes per restart and test for 24 to 48 hours before making another pass.