Tuning MySQL system variables for high performance

For most application developers, databases are altars of the devil best kept away from. But it doesn’t have to be that way.

All else being equal, a developer’s level of seniority is determined by how comfortable he or she is with the underlying database. Little to no database or coding experience = young developer. Fewer databases, more coding experience = mid-level developer. Good database and good coding experience = Senior Developer.

The harsh reality is that even developers with 6-8 years of experience struggle to explain the intricacies of query optimizers and look to the heavens when asked about database tuning.

why?

Surprisingly, the reason is not laziness (although some say it is).

The point is that the database is itself a counterforce. Even in the past, when the only databases to work with were relational types, mastering them was a miracle and a career path in itself. There are so many types of databases these days that it is simply impossible to expect a single mortal soul to master them all.

That said, there’s a good chance that you’re still happy with relational databases, or are part of a team that has happily run a product on relational databases for a long time. And most likely, you’ll be using MySQL (or MariaDB). In these cases, every part is worth learning, as even a little deeper dive under the hood can have significant benefits in improving the performance of your application.

Are you curious? Let’s dive in!

Interested? Well, let’s just jump in. Because your career depends on it. 😛

Optimize MySQL query cache

Almost all optimizations in computing come down to caching. On the one hand, the CPU maintains some level of cache to speed up computations, and on the other hand, web apps actively utilize caching solutions such as Redis instead of accessing the database every time. Provide pre-computed results to the user.

But hey, even the poor MySQL database has its own query cache. This means that every time you query something while the data is still stale, MySQL serves these cached results instead of running the query again, making your app blazingly fast.

You can check whether query caching is enabled (note, available, not enabled) in your database by running the following query in the database console:

 MariaDB [(none)]> SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+

So you know you’re running MariaDB and you can enable query caching. If you have a standard MySQL installation, you are unlikely to turn this off.

Next, let’s check to see if query caching is actually turned on.

 MariaDB [(none)]> SHOW VARIABLES LIKE 'query_cache_type';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_type | ON    |
+------------------+-------+

yes i will. However, if it’s not, you can turn it on by saying:

 MariaDB [(none)]> SET GLOBAL query_cache_type = ON;

Interestingly, this variable also accepts a third value indicating “on demand”. This means that MySQL only caches the queries you direct it to, but we won’t discuss that here.

Now that query caching is turned on, you’ve taken the first step toward a more robust MySQL setup. I say first step because while turning this on is a big improvement, you’ll need to adjust the query cache to suit your setup. So let’s learn how.

Another variable of interest here is query_cache_size , whose function is self-explanatory.

 MariaDB [(none)]> SHOW VARIABLES LIKE 'query_cache_size';
+------------------+----------+
| Variable_name    | Value    |
+------------------+----------+
| query_cache_size | 16777216 |
+------------------+----------+

Therefore, we have a query cache of approximately 16 MB in size. Note that even if the query cache is turned on, even if this size is zero, the cache is effectively turned off. Therefore, checking only one variable is not sufficient. Now we need to set the query cache size, but how high should we set it? First, the query cache feature itself requires 4 KB to store metadata, so what should we choose? Note that you also need to exceed it.

Suppose you set the query cache size to 500 KB.

MariaDB [(none)]> SET GLOBAL query_cache_size = 500000;

Is this enough? No, because how the query engine actually performs depends on a few more things.

• First, the query_cache_size variable must be large enough to hold the results of the query. If it’s too small, nothing will be cached.
• Second, two types of problems occur if query_cache_size setting is too large: 1) The engine has to do extra work to store and retrieve query results in this huge memory space. 2) If the size of most queries becomes significantly smaller, the cache becomes fragmented and the benefit of using the cache is lost.

How do we know that the cache is becoming fragmented? Check the total number of blocks in the cache like this:

 MariaDB [(none)]> show status like 'Qcache_total_blocks';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| Qcache_total_blocks | 33    |
+---------------------+-------+

If this number is very large, the cache is fragmented and needs to be flushed.

Therefore, to avoid these issues, be sure to choose the size of query_cache_size wisely. If you’re frustrated that I didn’t leave specific numbers here, I guess that’s how things go once you step beyond development and into engineering. You should examine the app you are running and determine the query size for important query results before setting this number. And even then, mistakes can still be made. 🙂

Threads, thread pools, waits, and timeouts

This is probably the most interesting part of how MySQL works, and getting it right means your app will be several times faster.

thread cutting

MySQL is a multi-threaded server. This means that every time a new connection is established to a MySQL server, a new thread containing the connection data is opened and its handle is passed to the client. (If you are wondering what a thread is, check out this article. Please read). The client then sends all its queries through this thread and receives the results. This raises a natural question. How many threads can MySQL spawn? The answer is in the next section.

thread pool

Programs in a computer system cannot open as many threads as they want. There are two reasons. 1) Threads consume memory (RAM). The operating system won’t let you run amok and use up all your memory (RAM). 2) Managing, say, a million threads is a large task in itself, and if the MySQL server could create that many threads, it would grind to a halt trying to cope with the overhead.

To avoid these problems, MySQL comes with thread pools. This is a fixed number of threads that are initially part of the pool. A new connection request causes MySQL to select one of these threads to return connection data. If all threads are exhausted, new connections will naturally be rejected. Let’s look at the size of the thread pool.

 ariaDB [(none)]> show variables like 'thread_pool_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| thread_pool_size | 4     |
+------------------+-------+

So my machine allows up to 4 connections at the same time. Interestingly, the number 4 comes from the fact that I have a 4-core processor. This means that my computer can only run 4 parallel tasks at a time (I’m talking about true parallel tasks here, not concurrent tasks). Ideally, this is the limit that dictates the value of thread_pool_size , but more powerful machines will see some benefit from increasing it. If you don’t want to make every new connection wait and you don’t mind some performance hit (again, this is an area where you can make the best decision based on your app’s performance under load) ), it might be a good idea to increase the value to 8.

However, unless you have a machine with 32 cores, setting it to a value higher than 16 is dangerous as it will significantly degrade performance. The MySQL thread pool rabbit hole is deep, but if you’re interested, I’ll go into more detail here .

Waits and timeouts

After a thread is created and connected to a client, if the client does not send any queries for the next few seconds (or minutes), resources are wasted. As a result, MySQL terminates connections after a period of inactivity. This is controlled by the wait_timeout variable.

 MariaDB [(none)]> show variables like 'wait%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+

The resulting value is in seconds. Yes, by default MySQL is set to wait at least 8 hours before cutting the cord. This might be good if you have long-running queries and you actually want them to wait (8 hours is still ridiculous!), but it’s terrible in most cases. Once the query is executed, this value is set to 0 (meaning forever), but typically this is set to a very low value (e.g. 5 seconds, sometimes (or lower).

Tuning temporary tables

Let’s start with what is a temporary table in MySQL?

Suppose we have a MySQL that structurally looks like this: TABLE A UNION (TABLE B INNER JOIN C). That is, I am interested in joining tables B and C and performing a union of table A with the result. Here, MySQL first starts joining tables B and C, but before performing the union, it needs to: Save this data somewhere. This is where temporary tables come into play. MySQL uses temporary tables to temporarily store data during intermediate stages of complex queries. This temporary table is destroyed when the query is finished.

Now the question is, why bother with something like this?

Temporary tables, which are simply the result of a query, are data that MySQL uses for calculations, and their access speed (among other limitations) determines how fast the query will run. For example, storing temporary tables in RAM is several times faster than storing them on disk.

There are two variables that control this behavior.

 MariaDB [(none)]> show variables like 'MariaDB [(none)]> show variables like 'tmp_table_size';  
+----------------+----------+

| Variable_name  | Value    |

+----------------+----------+

| tmp_table_size | 16777216 |

+----------------+----------+
';
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| max_heap_table_size | 16777216 |
+---------------------+----------+

MariaDB [(none)]> show variables like 'tmp_table_size';
+----------------+----------+
| Variable_name  | Value    |
+----------------+----------+
| tmp_table_size | 16777216 |
+----------------+----------+

The first, max_heap_table_size , indicates the amount of RAM that can be used by a MySQL table (‘heap’ here refers to the data structure used to allocate and manage RAM; see here for more information). The second tmp_table_size indicates: What is the maximum size of a temporary table? In my case both are set to 16 MB, but increasing just tmp_table_size doesn’t work as a whole, it means MySQL is still limited by max_table_heap_size .

The key here is that if the temporary tables created are larger than the limits allowed by these variables, MySQL will be forced to write them to the hard disk, resulting in very poor performance. . Our task now is simple. Do your best to guess the most accurate data size for your temporary table and adjust these variables until you reach that limit. However, beware of absurdity. Setting this limit to 16 GB is foolish when most temporary tables are less than 24 MB in size. So you’re just wasting potential RAM. Has been used by another query or part of the system (such as a cache).

conclusion

Since the MySQL documentation itself is thousands of words long, it is impossible to cover all system variables in one article. And it’s impossible to even cover all important variables in one article. Although we have mentioned some universal variables here, we recommend that you look at the system variables for the engine you are using ( InnoDB or MyISAM ).

My greatest hope for writing this article is for you to understand three things:

1. MySQL is typical software that operates within the limits set by the operating system. It is not some uncontrollable, mysterious program known only to God. And, thankfully, it’s not too difficult to understand how it is configured and controlled by system variables.
2. There is no single configuration to scale your MySQL installation. Look inside the running system (remember that optimization happens after the app goes into production, not before ), make your best guesses and measurements, and accept the reality that it will never be perfect. You have no choice but to accept it.
3. Adjusting variables is not the only way to optimize MySQL. Writing queries efficiently is also important, but that’s a topic for another article. But the point is, even if you do some god-like analysis and tune these parameters optimally, there’s still a chance that everything will come to a screeching halt.

What are the best system variables to tune? 🙂