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 error and many other MySQL performance-related issues. As such, I wanted to share the solution to a common MySQL configuration mistake. I’ve seen this done about a dozen times so far this year.

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. Four lines are often responsible for poor MySQL database performance and scaling due to a lack of available server memory for incoming connections. This article also applies to MariaDB and Percona drop-in replacements for MySQL.

Avoid arbitrarily increasing MySQL per-connection buffers.

Excerpt of my.cnf config. (some lines deleted or blurred to avoid dangerous misuse)

Who knows where or when this bad practice started. It has been common for me to ssh into production servers for the first time and find the value of almost every my.cnf variable increased and increased without sound reasoning behind those changes.

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

This is never a good practice and especially so with these four my.cnf variables. 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.

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

MySQL status output, two weeks after the first optimization pass. 

Often, I’ve seen improved MySQL performance by simply reverting these buffers in question to their defaults. Let’s take a quick 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 as well. 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

Some of you still use MyISAM over InnoDB. In some cases, with good reason. MyISAM is based on the old ISAM storage engine. It has many useful extensions, as discussed here.

read_buffer_size Applies generally to MyISAM only and does not affect InnoDB. Consider converting your MySQL tables to InnoDB storage engine before increasing this buffer. InnoDB is the default storage engine of MySQL 5.7 and MySQL 8.0. InnoDB features rollback and crash-recovery capabilities to protect data.

read_buffer_size Is also used to determine the memory block size for Memory tables. The read_rnd_buffer_size variable is also used mainly for MyISAM reads from tables. Also, consider InnoDB or MariaDB’s Aria storage engines. For the past decade, the default values of these buffers have remained the same.

Conclusion

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, then make one or two changes per restart, test for 24 to 48 hours before making another pass.