15 important variables you must know about MySQL performance tuning (summary)

Preface:

MYSQL should be the most popular WEB backend database. Although NOSQL has been mentioned more and more recently, I believe that most architects will still choose MYSQL for data storage. The author of this article summarizes and sorts out 15 important variables for MySQL performance tuning. If there are any deficiencies that need to be supplemented, please point them out.

1.DEFAULT_STORAGE_ENGINE

If you are already using MySQL 5.6 or 5.7 and your tables are all InnoDB, then you are already set. If not, make sure to convert your tables to InnoDB and set default_storage_engine to InnoDB.

Why? In short, because InnoDB is the best storage engine for MySQL (including Percona Server and MariaDB) - it supports transactions, high concurrency, and has very good performance (when configured correctly). Here is a detailed version explaining why

2. INNODB_BUFFER_POOL_SIZE

This is the most important variable of InnoDB. In fact, if your primary storage engine is InnoDB, then this variable is the most important for you with MySQL.

Basically, innodb_buffer_pool_size specifies how much memory MySQL should allocate to the InnoDB buffer pool, which is used to store cached data, secondary indexes, dirty data (data that has been changed but not flushed to disk), and various internal structures such as adaptive hash indexes.

As a rule of thumb, on a standalone MySQL server you should allocate 80% of the total memory on the machine to MySQL. If your MySQL is running on a shared server, or you want to know if the InnoDB buffer pool size is set correctly, see here for details.

3. INNODB_LOG_FILE_SIZE

The InnoDB redo log file setting is also called transaction log in the MySQL community. Until MySQL 5.6.8 the transaction log default value innodb_log_file_size=5M was the single biggest InnoDB performance killer. Starting with MySQL 5.6.8, the default value was raised to 48M, but for many slightly busy systems, it is still far too low.

As a rule of thumb, you should set the log size to store 1-2 hours of writes when your server is busy. If you don't want to go through so much trouble, setting the size to 1-2G will give you a good performance. This variable is also quite important, please see here for more detailed introduction.

Before we move on to the next variable, let’s quickly mention innodb_log_buffer_size. “Quick Mention” because it is often poorly understood and often over-focused. In fact most of the time you only need to use a small buffer - just enough to hold your small transaction changes before the transaction is committed and written to disk.

Of course, if you have a lot of large transaction changes, then changing the innodb log buffer size to a larger value than the default will improve your performance to a certain extent, but if you are using autocommit, or your transaction changes are less than a few KB, it is better to keep the default value.

4.INNODB_FLUSH_LOG_AT_TRX_COMMIT

By default, innodb_flush_log_at_trx_commit is set to 1, which means that InnoDB flushes the synchronization data to the disk immediately after each transaction is committed. If you use autocommit, then each of your INSERT, UPDATE, or DELETE statements is a transaction commit.

Syncing is an expensive operation (especially when you don't have a write-back cache) because it involves an actual synchronous physical write to the hard disk. So it is not recommended to use the default value if possible.

The two possible values ​​are 0 and 2:

* 0 means flush to hard disk, but not synchronized (no actual IO operation when committing a transaction)

* 2 means no refresh and no synchronization (no actual IO operation)

So if you set it to 0 or 2, the synchronization operation will be performed once per second. So the obvious disadvantage is that you may lose the submitted data from the last second. Specifically, if your transaction has been submitted, but the server loses power immediately, your submission is equivalent to not happening.

As it turns out, for financial institutions, such as banks, this is unbearable. However, for most websites, you can set innodb_flush_log_at_trx_commit=0|2, and there will be no big problem even if the server eventually crashes. After all, only a few years ago many sites were using MyISAM and would lose 30s of data when they crashed (not to mention the maddeningly slow recovery process).

So, what is the actual difference between 0 and 2? The noticeable difference in performance is negligible, since flushing to the OS cache is very fast. So it should obviously be set to 0, in case MySQL crashes (not the whole machine), you won't lose any data, because the data is already in the OS cache and will eventually be synced to the disk.

5.SYNC_BINLOG

There is already a lot of documentation on sync_binlog and its relationship to innodb_flush_log_at_trx_commit, so let's briefly introduce it:

a) If your server is not set up as a slave and you do not do backups, setting sync_binlog=0 will be good for performance.

b) If you have slave servers and do backups, but you don't mind losing some events in the binary log if the master crashes, then for better performance set sync_binlog=0.

c) If you have slaves and backups, and you care deeply about the consistency of the slaves and being able to recover to a point in time (the ability to restore the database to a specific point in time using the most recent consistent backup and the binary logs), then you should set innodb_flush_log_at_trx_commit=1, and you should seriously consider using sync_binlog=1.

The problem is that sync_binlog=1 is expensive – now each transaction has to be synced to disk as well. You might be thinking why not combine the two synchronizations into one, and you’re right – newer versions of MySQL (5.6 and 5.7, MariaDB and Percona Server) already have the ability to merge commits, so in this case the sync_binlog=1 operation is not so expensive, but it still has a significant impact on performance in older MySQL versions.

6. INNODB_FLUSH_METHOD

Set innodb_flush_method to O_DIRECT to avoid double buffering. The only time you shouldn't use O_DIRECT is when your operating system doesn't support it. But if you are running Linux, use O_DIRECT to activate direct IO.

Without direct IO, double buffering will occur because all database changes are first written to the OS cache and then synced to disk - so the InnoDB buffer pool and the OS cache will hold a copy of the same data at the same time. Especially if your buffer pool is limited to 50% of total memory, that means you could be wasting up to 50% of your memory in a write-intensive environment. If it is not limited to 50%, the server may use swap due to high pressure on the OS cache.

Simply put, set innodb_flush_method=O_DIRECT.

7. INNODB_BUFFER_POOL_INSTANCES

MySQL 5.5 introduced buffered instances as a means of reducing internal lock contention and thus improving MySQL throughput.

In MySQL 5.5 this helps very little in improving throughput, but in MySQL 5.6 this improvement is very large, so in MySQL 5.5 you might conservatively set innodb_buffer_pool_instances=4, and in MySQL 5.6 and 5.7 you can set it to 8-16 buffer pool instances.

You won't see much of a performance improvement once you set it, but it should give you decent performance in most high-load situations.

By the way, don't expect this setting to reduce the response time of your individual queries. This difference can only be seen on servers with high concurrent load. For example, multiple threads do many things at the same time.

8. INNODB_THREAD_CONCURRENCY

You may often hear that you should set innodb_thread_concurrency=0 and forget about it. However this is only true when used on a low-load server. However, if your server's CPU or IO usage is saturated, especially with occasional peaks, and the system wants to be able to handle queries normally when overloaded, then it is strongly recommended to pay attention to innodb_thread_concurrency.

InnoDB has a way to control the number of threads executing in parallel - we call it concurrency control mechanism. Most of this is controlled by the innodb_thread_concurrency value. If set to 0, concurrency control is turned off, so InnoDB processes all incoming requests immediately (as many as possible).

This is fine if you have 32 CPU cores and only 4 requests. But imagine you only have 4 CPU cores and 32 requests - if you let 32 ​​requests process at the same time, you are asking for trouble. Since these 32 requests only have 4 CPU cores, it will obviously be at least 8 times slower than usual (actually more than 8 times), and yet each of these requests has its own outer and inner locks, which has a high probability of request stacking.

Here's how to change this variable, executed at the MySQL command prompt:

SET global innodb_thread_concurrency=X;

For most workloads and servers, a setting of 8 is a good starting point, and you can then increase it gradually as the server reaches this limit and resource usage becomes underutilized. You can view the current query processing status through show engine innodb status\G, and look for lines similar to the following:

22 queries inside InnoDB, 104 queries in queue

9.SKIP_NAME_RESOLVE

This one has to be mentioned because there are still a lot of people who haven't added it. You should add skip_name_resolve to avoid DNS resolution when connecting.

In most cases you won't notice anything when you change this, as DNS server resolution is pretty fast in most cases. But when the DNS server fails, it will appear as "unauthenticated connections" on your server, and that's why all requests suddenly start to slow down.

So don't wait until this happens to make changes. Now add this variable and avoid hostname based authorization.

10. INNODB_IO_CAPACITY, INNODB_IO_CAPACITY_MAX

* innodb_io_capacity: used to control the amount of write IO performed by MySQL per second when refreshing dirty data.

* innodb_io_capacity_max: Under pressure, controls the amount of write IO per second that MySQL performs when flushing dirty data

First, this has nothing to do with reads – what a SELECT query does. For read operations, MySQL will do its best to process and return the results. As for write operations, MySQL will refresh in a loop in the background. In each loop, it will check how much data needs to be refreshed, and will not use more than the number specified by innodb_io_capacity for refresh operations. This also includes change buffer merging (the change buffer is key to secondary dirty page storage before they are flushed to disk).

Second, I need to explain what is meant by "under pressure". In MySQL, it is called "emergency", which is when MySQL is flushing in the background and it needs to flush some data in order to allow new write operations to come in. Then, MySQL will use innodb_io_capacity_max.

So, why should innodb_io_capacity and innodb_io_capacity_max be set?

The best approach is to measure the random write throughput of your storage setup, and then set innodb_io_capacity_max to the maximum IOPS your device can achieve. innodb_io_capacity should be set to 50-75% of it, especially if your system is mainly write-intensive.

You can usually predict what the IOPS of your system will be. For example, a RAID10 consisting of 8 15k hard disks can do about 1000 random write operations per second, so you can set innodb_io_capacity=600 and innodb_io_capacity_max=1000. Many cheap enterprise SSDs can do 4,000-10,000 IOPS etc.

It's not a big deal if this value isn't set perfectly. However, be aware that the defaults of 200 and 400 will limit your write throughput, so you may occasionally catch the flush process. If this happens, it may be that the write IO throughput of your hard disk has been reached, or the value is set too small and limits the throughput.

11. INNODB_STATS_ON_METADATA

If you are running MySQL 5.6 or 5.7, you do not need to change the default value of innodb_stats_on_metadata as it is already set correctly.

However, in MySQL 5.5 or 5.1, it is strongly recommended to turn this variable off - if it is turned on, commands like show table status will query INFORMATION_SCHEMA immediately instead of waiting a few seconds before executing, which will use additional IO operations.

Starting with version 5.1.32, this is a dynamic variable, meaning you do not need to restart the MySQL server to turn it off.

12. INNODB_BUFFER_POOL_DUMP_AT_SHUTDOWN & INNODB_BUFFER_POOL_LOAD_AT_STARTUP

The variables innodb_buffer_pool_dump_at_shutdown and innodb_buffer_pool_load_at_startup have nothing to do with performance, but if you occasionally restart the MySQL server (such as to take effect), then it is relevant. When both are activated, the contents of the MySQL buffer pool (more specifically, the cached pages) are stored to a file when you stop MySQL. The next time you start MySQL, it will start a thread in the background to load the contents of the buffer pool to increase the warm-up speed by 3-5 times.

Two things:

First, it does not actually copy the buffer pool contents to file on close, only the tablespace ID and page ID - enough information to locate the page on disk. It can then load those pages very quickly with a large number of sequential reads, rather than requiring thousands of small random reads.

Second, the content is loaded in the background at startup, because MySQL does not need to wait until the buffer pool content is loaded before starting to accept requests (so it seems that there will be no impact).

As of MySQL 5.7.7, by default only 25% of the buffer pool pages are dumped to files when mysql is shut down, but you can control this value – use innodb_buffer_pool_dump_pct, 75-100 is recommended.

This feature is only supported since MySQL 5.6.

13. INNODB_ADAPTIVE_HASH_INDEX_PARTS

If you are running a MySQL server with a lot of SELECT queries (and you have optimized it as much as possible), then the adaptive hash index will be your next bottleneck. An adaptive hash index is a dynamic index maintained internally by InnoDB that improves performance for the most commonly used query patterns. This feature can be disabled by restarting the server, but is enabled by default in all versions of MySQL.

This technique is quite complex, but in most cases it will result in a significant speedup for most types of queries. However, when you have too many queries going to the database, at a certain point it will spend too much time waiting for AHI locks and latches.

If you have MySQL 5.7, you don’t have this problem – innodb_adaptive_hash_index_parts is set to 8 by default, so the adaptive hash index is cut into 8 partitions because there is no global mutex.

However, in versions prior to MySQL 5.7, there is no control over the number of AHI partitions. In other words, there is a global mutex to protect the AHI, which may cause your select queries to frequently hit a wall.

So if you are running 5.1 or 5.6 and have a lot of select queries, the easiest solution is to switch to the same version of Percona Server and activate the AHI partition.

14.QUERY_CACHE_TYPE

If one thinks query caching is going to work well, one should definitely use it. Well, sometimes it works. However this is only useful when you have low load, especially when it is mostly reads and few or no writes.

If that's the case, just set query_cache_type=ON and query_cache_size=256M. But remember not to set a higher value than 256M, otherwise it will cause serious server pauses due to query cache failure.

If your MySQL server is under high load, it is recommended to set query_cache_size=0 and query_cache_type=OFF and restart the server to take effect. That way MySQL will stop using the query cache mutex for all queries.

15.TABLE_OPEN_CACHE_INSTANCES

Starting with MySQL 5.6.6, the table cache can be split into multiple partitions.

The table cache is used to store a list of currently open tables, and a mutex is locked for each table open or close - even if it is an implicit temporary table. Using multiple partitions definitely reduces potential contention.

Starting from MySQL 5.7.8, table_open_cache_instances=16 is the default configuration.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.