15 you don't know MySQL performance tuning parameters Introduction

1.default_storage_engine

If you're already using MySQL 5.6 or 5.7, and your datasheet is InnoDB, then you've set it up. If not, make sure to convert your table 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 very good performance (when configured correctly). Here is a detailed version of why

2.innodb_buffer_pool_size

This is the most important variable of InnoDB. In fact, if your primary storage engine is InnoDB, then for you, this variable is most important for MySQL.
Basically, innodb_buffer_pool_size specifies how much memory MySQL should allocate to the INNODB buffer pool, InnoDB buffer pool to store cached data, level two indexes, dirty data (data that has been changed but not flushed to the hard disk). and various internal structures such as adaptive hash indices.
According to experience, an independent MySQL server should be allocated 80% of total memory for MySQL's entire machine. If your MySQL runs 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 setup of the InnoDB redo log file is also called the transaction log in the MySQL community. Until the MySQL 5.6.8 transaction log default value innodb_log_file_size=5m is the only largest InnoDB performance killer. Starting with the MySQL 5.6.8, the default value is raised to 48M, but for many of the more busy systems, it is far lower.
Based on experience, you should set the log size to be able to store 1-2 hours of write volume when your server is busy. If you don't want to be so troublesome, setting the 1-2g size will give you a good performance. This variable is also very important, please see here for more detailed introduction.
Before moving on to the next variable, let's quickly mention innodb_log_buffer_size. "Quick mention" is because it is often difficult to understand and is often overly focused. In fact, in most cases you just need to use a small buffer-enough to save your small transaction changes before the transaction is committed and written to the hard disk.
Of course, if you have a large number of large transaction changes, then changing the value of the default InnoDB log buffer size will improve your performance, but you are using autocommit, or your transaction changes are less than a few k, that is still the default value.

4.innodb_flush_log_at_trx_commit

By default, the Innodb_flush_log_at_trx_commit setting of 1 means that InnoDB refreshes the synchronized data to the hard disk immediately after each transaction commits. If you use autocommit, then every insert, update, or DELETE statement you have is a transaction commit.
Synchronization is an expensive operation (especially when you are not writing back to the cache) because it involves physically synchronizing physical writes to the hard disk. Therefore, it is not recommended to use default values if possible.
The two optional values are 0 and 2:
* 0 means flush to hard drive, but not synchronized (no actual IO operation is committed when committing transaction)
* 2 means no refresh and no different steps (and no actual IO operations)
So if you set it to 0 or 2, the sync operation executes once per second. So the obvious downside is that you may lose the last second of the submission data. Specifically, your transaction has already been submitted, but the server immediately loses power, so your submission is equivalent to never happened.
Show, for financial institutions such as banks, this is intolerable. However, for most Web sites, it can be set to innodb_flush_log_at_trx_commit=0|2, even if the server finally crashes, there is no big problem. After all, just a few years ago there were many sites that used MyISAM and lost 30s of data when they crashed (not to mention the maddening slow fix process).
So what's the real difference between 0 and 2? The obvious difference in performance is negligible, since refreshing to the operating system cache is very fast. So obviously it should be set to 0, in case MySQL crashes (not the entire machine), you will not lose any data, because the data is already in the OS cache, will eventually sync to the hard disk.

5.sync_binlog

There are a lot of documents written to Sync_binlog, as well as its relationship with Innodb_flush_log_at_trx_commit, let's briefly introduce the following:
A If your server is not set up from the server, and you do not make backups, then setting up sync_binlog=0 will be good for performance.
b If you have a backup from the server and do it, but you don't mind if the primary server crashes in the binary log loss of some events, so for better performance or set to Sync_binlog=0.
c If you have from the server and backup, you are very concerned about the consistency from the server, as well as in time to restore to a point in time (by using the latest consistent backup and binary log to restore the database to a specific point in time), then you should set the Innodb_flush_log_at_trx_ Commit=1, and you need to seriously consider using sync_binlog=1.
The problem is that the sync_binlog=1 cost is high – now each transaction is synchronized to the hard drive. You might wonder why not merge two times into one, think right – new versions of MySQL (5.6 and 5.7,MARIADB and Percona Server) have been able to merge submissions, and in this case sync_binlog=1 operations are not so expensive, However, the legacy MySQL version still has a significant impact on performance.

6.innodb_flush_method

Set the Innodb_flush_method to O_direct to avoid double buffering. The only situation where you should not use O_direct is when your operating system is not supported. 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 synchronized to the hard drive – so the InnoDB buffer pool and OS cache hold the same data at the same time. Especially if your buffer pool is limited to 50% of total memory, that means you may be wasting up to 50% of your memory in a write-intensive environment. If there is no limit of 50%, the server may use swap due to high pressure on the OS cache.
Simply put, set to Innodb_flush_method=o_direct.

7.innodb_buffer_pool_instances

MySQL 5.5 introduces a buffer instance as a means of reducing internal lock contention to improve MySQL throughput.
In the 5.5 version this helps to improve throughput very little, then in the MySQL 5.6 version of this upgrade is very large, so in MySQL5.5 you may be conservatively set innodb_buffer_pool_instances=4, in MySQL In 5.6 and 5.7 you can set to 8-16 buffer pool instances.
You'll see a little performance improvement after you set it up, but it should be a good performance in most high load situations.
By the right, don't expect this setting to reduce the response time of your individual queries. This is in the high concurrency load of the server to see the difference. 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 then leave it out of the control. This is only true if the low load server is used. Then, if your server's CPU or IO usage is saturated, especially the occasional spikes, this time the system would like to be able to handle the query when overloaded, then strongly recommend attention to innodb_thread_concurrency.
InnoDB there is a way to control the number of threads executing concurrently-what we call the concurrency control mechanism. Most of them are controlled by innodb_thread_concurrency values. If set to 0, concurrency control is turned off, so InnoDB immediately handles all incoming requests (as much as possible).
There is no problem when you have a 32CPU core and only 4 requests. But imagine that you have only 4CPU cores and 32 requests – if you have 32 requests processed at the same time, you're asking for trouble. Since these 32 requests have only 4 CPU cores, it is obvious that it will be at least 8 times times slower than usual (actually greater than 8 times times), and that each of these requests has its own external and internal locks, which is likely to accumulate requests.
Here's how to change this variable to execute at the MySQL command line prompt:

SET Global innodb_thread_concurrency=x;
For most workloads and servers, setting to 8 is a good start, and you can then increase the utilization of your resources by the time the server reaches this limit. You can view the current query processing by show engine InnoDB Status\g, looking for similar lines:

Queries inside InnoDB, queries in queue
9.skip_name_resolve

This item has to be mentioned because there are still a lot of people who have not added this item. You should add skip_name_resolve to avoid DNS parsing when connecting.
In most cases, you won't feel much change because DNS server resolution is very fast in most cases. However, when a DNS server fails, it appears "unauthenticated connections" on your server, and that is why all requests suddenly start to slow down.
So don't wait for this to happen before it changes. Now add this variable and avoid authorization based on host name.

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 flushing dirty data.
* Innodb_io_capacity_max: Under pressure, control the amount of write Io per second that MySQL performs when flushing dirty data
First, this is the operation performed by a read-independent –select query. For read operations, MySQL will do its best to handle and return results. As for the write operation, MySQL in the background will be recycled, in each loop will check how much data needs to be refreshed, and will not use more than innodb_io_capacity specified number to do the refresh operation. This also includes changing buffer merges (changing buffers are key to secondary dirty page storage before they are flushed to disk).
Second, I need to explain what is called "under pressure", which is called "emergency" in MySQL, is when MySQL is refreshed in the background, it needs to refresh some data in order for the new write operation to come in. Then, MySQL will use the Innodb_io_capacity_max.
So, what should you set innodb_io_capacity and Innodb_io_capacity_max for?
The best way to do this is to measure the random write throughput of your storage settings and then give Innodb_io_capacity_max the maximum IOPS your device can achieve. Innodb_io_capacity is set to its 50-75%, especially if your system is primarily write-operated.
Usually you can predict how much iops your system is. For example, a RAID10 composed of 8 15k hard drives can do about 1000 random writes per second, so you can set up innodb_io_capacity=600 and innodb_io_capacity_max=1000. Many cheap enterprise SSD can do 4,000-10,000 ioPS and so on.
This value is not set to perfect the problem is not very good. However, be aware that the default 200 and 400 limit your write throughput, so you may occasionally catch the refresh process. If this happens, you may have reached the write IO throughput on your hard drive, or the value is set too low to limit 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 because it is already set up correctly.
However, in MySQL 5.5 or 5.1, it is strongly recommended to close this variable – if it is turned on, like command Show table status will immediately query information_schema instead of waiting a few seconds to execute, which will use the additional IO operation.
Starting with the 5.1.32 version, this is a dynamic variable, meaning you don't need to reboot the MySQL server to shut it down.

12.innodb_buffer_pool_dump_at_shutdown & Innodb_buffer_pool_load_at_startup

The two variables innodb_buffer_pool_dump_at_shutdown and innodb_buffer_pool_load_at_startup are not performance-independent, but if you occasionally reboot the MySQL server (such as the effective configuration), Then it's about. When two are activated, the contents of the MySQL buffer pool (more specifically the cached page) are stored in a file when the MySQL is stopped. 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 to 3-5 times.
Two things:
First, it does not actually copy the buffer pool content to the file at shutdown, just copy the table space ID and the page ID enough information to locate the page on the hard drive. It can then be read in a lot of order to load those pages very quickly, 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 and then start accepting the request (so it does not seem to have any effect).
Starting with the MySQL 5.7.7, the default is that only 25% of the buffer pool page is stored in the file when MySQL closes, but you can control the value-use innodb_buffer_pool_dump_pct, recommendation 75-100.
This feature is only supported from MySQL 5.6.

13.innodb_adaptive_hash_index_parts

If you run a MySQL server with a large number of select queries (and are optimized as far as possible), then the Adaptive Hash index will be next to your next bottleneck. Adaptive Hash Index (INNODB) is a dynamic index for internal maintenance, which can improve the performance of the most commonly used query modes. This feature restarts the server shutdown, but is turned on by default in all versions of MySQL.
This technique is very complex, and in most cases it will be useful for most types of queries until they are accelerated. However, when you have too many queries to the database, at a certain point it spends too much time waiting for ahi locks and latches.
If you are MySQL 5.7, no this problem –innodb_adaptive_hash_index_parts the default setting is 8, so the adaptive hash index is cut to 8 partitions because there is no global mutex.
However, in the MySQL version before 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 can cause your select query to often bang against the wall.
So if you are running 5.1 or 5.6 and have a large number of select queries, the simplest solution is to switch to the same version of Percona server to activate the AHI partition.

14.query_cache_type

If a person thinks the query cache works well, it should be used. Well, sometimes it's useful. But this is only useful when you're at low load, especially in low loads, mostly read, small, or not.
If that's the case, it's good to set up Query_cache_type=on and query_cache_size=256m. Remember, however, that you cannot set the 256M to a higher value, or you will cause a serious server standstill as the query cache fails.
If your MySQL server is high load action, it is recommended that you set query_cache_size=0 and Query_cache_type=off and restart the server for effective. That way MySQL will stop using the query cache mutex in all queries.

15.table_open_cache_instances

The table cache can be split into multiple partitions, starting with the MySQL 5.6.6. The
Table cache is used to hold a list of currently open tables, and when each table is opened or closed the mutex is locked-even if it is an implicit temporary table. Using multiple partitions definitely reduces potential contention.
starting with the MySQL 5.7.8, table_open_cache_instances=16 is the default configuration.