A frequent topic of the support request emails I receive is MySQL database performance. Customers complain about MySQL using too much server memory, too many MySQL slow queries, the famous MySQL server error and many other MySQL performance issues. As such, I wanted to share a solution to a common MySQL configuration error. I’ve seen it about a dozen times so far this year.
If you are a DBA, feel free to share your experiences or suggestions in the comment section. I am referring to the four MySQL configuration 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 replacement for MySQL.
Avoid increasing MySQL per-connection buffers arbitrarily.
Excerpt from my.cnf config. (Some lines were removed or blurred to avoid dangerous misuse)
Who knows where and when this evil practice started. It’s become common for me to ssh into a production server for the first time and find the value of almost every my.cnf variable increasing and increasing without a sound reasoning behind those changes.
A good rule of thumb is, if you can’t provide a valid reason for not incrementing any of these buffers, keep them set to default values.
This is never a good practice and especially with these four my.cnf variables. Although increasing the value of some variables can improve performance, these four will almost always reduce the performance and capacity of the MySQL server when increased.
There are four buffers in question
read_rnd_buffer_size, These four buffers are allocated per connection, For example, a setting of
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 almost all cases, it is best to keep the default by deleting or commenting out these four configuration lines. As connections increase with traffic, queries that require more space than is available due to an increase in buffer settings can trigger paging those buffers to disk. This dramatically slows down your DB server and creates a bottleneck. read also Linux Server Performance Analysis With Above,
MySQL status output, two weeks after the first optimization pass.
Often, I’ve seen an improvement in MySQL performance by reverting these buffers to their defaults. Let’s take a quick look at each of these buffers.
join_buffer_size Each full join between the two tables is allocated. from MySQL Documentation
join_buffer_size is described as: “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 further says: “The memory allocation time can cause a substantial performance drop if the global size is larger than required by most queries that use it.” The join buffer is allocated to cache table rows when the join cannot use the index.
If your database suffers from multiple joins performed without indexes, this cannot be solved by increasing
join_buffer_size, The problem appears to be “display without index.” Thus, the solution to fast joins is to add indexes.
Unless you have data indicating otherwise, you should avoid arbitrarily incrementing sort_buffer_size as well. Memory here is also assigned per connection! MySQL’s docs warn, “On Linux, there are limits of 256KB and 2MB where larger values can significantly slow down memory allocation, so you should consider staying below one of those values.” Avoid increasing the sort_buffer_size above 2M as there is a performance penalty that will eliminate the benefits.
MySQL read_buffer_size & Rread_rnd_buffer_size
Some of you still use MyISAM over InnoDB. In some cases, with good reason. MyISAM is based on the older ISAM storage engine. it is Many useful extensions, as discussed here,
read_buffer_size Generally only applies to MyISAM and does not affect InnoDB. Consider converting your MySQL tables to the InnoDB storage engine before increasing this buffer. InnoDB is the default storage engine for MySQL 5.7 and MySQL 8.0. InnoDB has rollback and crash-recovery capabilities to protect data.
read_buffer_size Also used to determine memory block size Commemoration Table.
read_rnd_buffer_size Variables are also mainly used for
MyISAM Reads from tables. Also, consider Areas of InnoDB or MariaDB storage engines. For the past decade, the default values of these buffers have remained the same.
A good rule of thumb: If you can’t provide a valid reason to increase these buffers, keep them set to default values. This applies even less strictly to all MySQL’s variables. Be careful when making changes, don’t Overwrite your entire my.cnf in one go. Back up everything first, then make one or two changes per restart, testing for 24 to 48 hours before doing a second pass.