Tuning MySQL my.cnf? Avoid this common pitfall!

It took me some time to decide on the title of this article. MariaDB has been fast replacing MySQL as many Linux distributions now default to MariaDB over MySQL. MariaDB is an enhanced drop-in replacement for MySQL.

Therein lies my predominantly self-made conundrum, MySQL or MariaDB? The MySQL tuning advice below applies to MySQL, MariaDB and Percona. After tuning MySQL over the years, I can safely say that the following pitfalls are widespread.

Avoid arbitrarily increasing the size of MySQL’s per-connection buffers

my.cnf basic config example

The my.cnf config file is well-known, but also often grossly misconfigured. I’m not sure where or when it all started, but it has become the norm to keep increasing the size and value of almost every setting in my.cnf without much reasoning behind those increases.

Let’s look at some important my.cnf parameters, where doing this will hurt MySQL’s performance, waste large chunks of server memory, and as a result, reduce MySQL’s overall capacity and throughput.

Buffers such as join_buffer_sizesort_buffer_size, read_buffer_size and read_rnd_buffer_size are allocated per connection. Therefore, a setting of read_buffer_size=1M and max_connections=150 configures MySQL to allocate – from startup – 1MB per connection x 150 connections. For almost two decades, the default read_buffer_size remains at 128KB. Increasing the default is a waste of server memory and often does not help performance.

In nearly all cases, it’s best to use the defaults by removing or commenting out these four buffer config lines. For a more gradual approach, reduce your current large values to free up wasted RAM, and keep reducing them towards default values over time. I’ve seen improved throughput by reducing these buffers. Avoid arbitrarily increasing these!

Tuning 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.”

It 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(s) 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.

Tuning MySQL sort_buffer_size

Unless you have data indicating otherwise, you should also 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 can eliminate any benefits.

Rule of thumb when tuning MySQL

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. (comment-out of config). Unfortunately, misconfiguring these four config options often attract significant performance issues. Hopefully, you’ve found these quick MySQL tuning tips helpful.

Also, see MySQL Query Cache Size and Performance (Update – May 17th, 2021: The query cache is deprecated as of MySQL 5.7.20 and is removed in MySQL 8.0.) If you mainly use InnoDB tables, you are better off just disabling query_cache completely.

Conclusion

The tendency to arbitrarily increase the sizes of various buffers, as influenced by a misguided interpretation of the my.cnf configuration, can lead to suboptimal performance, wasted server memory, and decreased overall capacity and throughput.

With features like the query cache deprecated, it becomes increasingly crucial to base tuning decisions on a solid understanding of underlying mechanisms and a analysis of actual performance data. Adhering to these principles fosters a more efficient database environment and paves the way for a more robust, scalable, and performance-optimized data management infrastructure.

Published: March 4th, 2015 | Last updated: Feb 5th, 2024

Tags: , , , , ,

Discussion

  1. I remember read this on the main website a while back. It was very helpful and I’ve kept the buffer sizes low ever since.

    Another great resource is MariaDB’s knowledgebase. Not just for understanding what each variable does, but for tuning tips as well.

  2. A couple of tuning questions for you.

    What do you think of query_cache these days? Is it even useful anymore? I see mixed answers about this?

    For a web server with 50+ WordPress websites, what about thread handling? Should I be setting a pool size or using pool of threads?

  3. Query cache is deprecated as of MySQL 5.7.20 and is removed in MySQL 8. You are better off disabling query_cache completely now.



Top ↑