MySQL my.cnf tuning? Avoid this common pitfall!

It took me some time to decide on the title of this article. MariaDB is rapidly replacing MySQL as a growing number of Linux distributions Defaults to MariaDB on MySQL. MariaDB is an advanced, drop-in replacement for MySQL. In this is my mainly self-made fiddle, MySQL or MariaDB? That said, the MySQL tuning advice below is for MySQL, MariaDB and . applies to both percona, Having tuned MySQL for years, I can safely say that the following pitfalls are very common.

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

my.cnf basic configuration example

The my.cnf config file is well-known, but is often configured incorrectly. I’m not sure where or when this all started, but without much logic behind those increases it has become a norm to increase the size and value of almost every setting in my.cnf. Let’s look at some important my.cnf parameters where, by doing so, MySQL’s performance will suffer, waste large chunks of server memory, and as a result, reduce MySQL’s overall capacity and throughput.

like buffers join_buffer_size, sort_buffer_size, read_buffer_size And read_rnd_buffer_size is allocated per connection. Therefore, a setting of read_buffer_size=1M And max_connections=150 From startup – Configures MySQL to allocate 1MB per connection x 150 connections. almost two decades read_buffer_size Remains at 128KB. Increasing the default is not only a waste of server memory but often doesn’t help performance. In almost all cases, it is best to use the default by deleting or commenting out these four buffer configuration lines. For a more gradual approach, reduce your current larger values ​​to free up wasted RAM, lowering them toward the default values ​​over time. I have seen better throughput by reducing these buffers. Avoid increasing these arbitrarily!

Tuning MySQL join_buffer_size

join_buffer_size Each full join between the two tables is allocated. from MySQL Documentationjoin_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.” It continues to say: “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 the join_buffer_size. The problem appears to be “display without index.” Thus, the solution to fast joins is to add indexes.

Tuning MySQL sort_buffer_size

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 can eliminate any gains.

Rule of thumb when tuning MySQL

A good rule of thumb is that if you cannot provide a valid reason for not incrementing any of these buffers, keep them set to default values. (out of comment-config). Unfortunately, misconfiguring these four configuration options often attracts significant performance issues. Hope you find these quick MySQL tuning tips helpful.

See more MySQL Query Cache Size and Performance ,Update – May 17, 2021: The query cache has been deprecated as of MySQL 5.7.20, and removed in MySQL 8.0.) If you’re primarily using InnoDB tables, you’re better off just disabling query_cache altogether.

Published: 4 March 2015 | Last Updated: May 17, 2021

Leave a Comment