Shulou(Shulou.com)06/01 Report--

Foreword:

MYSQL should be the most popular WEB back-end database. Although NOSQL has been mentioned more and more recently, it is believed that most architects will choose MYSQL for data storage. In this paper, the author summarizes and combs the 15 important variables of MySQL performance tuning, but also hopes to point out the deficiencies that need to be added.

1.DEFAULT_STORAGE_ENGINE

If you are already using MySQL 5.6or 5.7and your datasheets are all InnoDB, then you have set it up. If not, be 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, has high concurrency, and has 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 main storage engine is InnoDB, then for you, this variable is the most important for 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 the hard disk), and various internal structures such as adaptive hash indexes.

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

3.INNODB_LOG_FILE_SIZE

The setting of InnoDB redo log files is also called transaction logging in the MySQL community. Until MySQL 5.6.8 transaction log default innodb_log_file_size=5M is the single biggest InnoDB performance killer. Starting with MySQL 5.6.8, the default value has been raised to 48m, but for many slightly busier systems, it is much lower.

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 be so troublesome, setting the size of 1-2G will give you a good performance. This variable is also very important, so please see here for a more detailed description.

Before moving on to the next variable, let's make a quick mention of innodb_log_buffer_size. "quick mention" is because it is often difficult to understand and often overpaid. In fact, in most cases you only 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 a larger value than the default innodb log buffer size will improve your performance, but if you are using autocommit, or your transaction changes are less than a few k, keep the default value.

4.INNODB_FLUSH_LOG_AT_TRX_COMMIT

By default, an innodb_flush_log_at_trx_commit setting of 1 means that InnoDB refreshes synchronization data to the hard disk immediately after each transaction is committed. If you use autocommit, then every INSERT, UPDATE, or DELETE statement you make is a transaction commit.

Synchronization is an expensive operation (especially if you don't write back to the cache) because it involves actual synchronous physical writes to the hard disk. So if possible, it is not recommended to use default values.

The two optional values are 0 and 2:

* 0 means flushing to the hard disk, but not synchronized (there is no actual IO operation when the transaction is committed)

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

So if you set it to 0 or 2, the synchronization is performed once a second. So the obvious disadvantage is that you may lose the last second of submitted data. Specifically, your transaction has been committed, but the server immediately lost power, so your commit is equivalent to never happened.

It shows that for financial institutions, such as banks, this is intolerable. However, for most websites, it can be set to innodb_flush_log_at_trx_commit=0 | 2, even if the server eventually crashes. After all, only a few years ago, many websites used MyISAM, losing 30 seconds of data when they crashed (not to mention the maddeningly slow repair process).

So what is the actual difference between 0 and 2? The obvious difference in performance is negligible because flushing to the operating system cache is very fast. So obviously it should be set to 0, in case the MySQL crashes (not the entire machine), you won't lose any data, because the data is already in the OS cache and will eventually be synchronized to the hard drive.

5.SYNC_BINLOG

A lot of documents have been written about sync_binlog and its relationship with innodb_flush_log_at_trx_commit, so let's briefly introduce it:

A) if your server is not set up from the slave server and you do not make backups, then setting sync_binlog=0 will be good for performance.

B) if you have a slave server and do a backup, but you don't mind losing some events in the binary log when the master server crashes, set it to sync_binlog=0 for better performance.

C) if you have a slave server and backup, you are very concerned about the consistency from the server and the ability to recover to a point in time (the ability to restore the database to a specific point in time by using the latest consistent backup and binary logs), then you should set up innodb_flush_log_at_trx_commit=1 and seriously consider using sync_binlog=1.

The problem is that sync_binlog=1 is expensive-now every transaction has to be synchronized to the hard disk once. You might wonder why you don't merge two synchronizations into one, and that's the right idea-the new version of MySQL (5.6 and 5.7 sync_binlog=1 DB and Percona Server) has been able to merge and commit, so the operation of MariaDB is not so expensive in this case, but it will still have a big impact on performance in the old version of mysql.

6.INNODB_FLUSH_METHOD

Set 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 does not 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 synchronized to the hard disk-so the InnoDB buffer pool and the OS cache hold the same data at the same time. Especially if your buffer pool is limited to 50% of the total memory, that means you can waste 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 the high pressure of OS cache.

Simply put, set it to innodb_flush_method=O_DIRECT.

7.INNODB_BUFFER_POOL_INSTANCES

MySQL 5.5 introduces buffered instances as a means to reduce internal lock contention and improve MySQL throughput.

This is of little help in improving throughput in version 5.5, but it is very big in MySQL version 5.6, so you may set innodb_buffer_pool_instances=4 conservatively in MySQL5.5, and you can set it to 8-16 buffer pool instances in MySQL 5.6 and 5.7.

After you set it up, you will feel that the performance improvement is not great, but it should perform well in most high load situations.

By the way, don't expect this setting to reduce the response time of your single query. 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 innodb_thread_concurrency=0 should be set and then leave it alone. However, this is only true when using low-load servers. Then, if your server's CPU or IO usage is saturated, especially if there are occasional spikes, and the system wants to handle queries properly in case of overload, it is strongly recommended to pay attention to innodb_thread_concurrency.

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

There will be no problem when you have a 32CPU core and only have 4 requests. But imagine when you only have the 4CPU core and 32 requests-if you let 32 requests be processed at the same time, you're asking for trouble. Because these 32 requests have only a 4 CPU core, they are obviously at least eight times slower than usual (actually more than eight times), while each of these requests has its own external and internal locks, which is likely to pile up requests.

Here's how to change this variable 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 then you can gradually increase when the server reaches this limit and resource utilization is underutilized. You can view the current query processing through show engine innodb status\ G, and find something like the following line:

22 queries inside InnoDB, 104 queries in queue

9.SKIP_NAME_RESOLVE

This item has to be mentioned because there are still many people who have not added it. You should add skip_name_resolve to avoid DNS parsing when connecting.

In most cases you will have no feeling to change this, because in most cases the DNS server parsing will be very fast. But when the DNS server fails, it appears "unauthenticated connections" on your server, which is why all requests suddenly start to slow down.

So don't wait for this to happen. Now add this variable and avoid authorization based on the hostname.

10.INNODB_IO_CAPACITY, INNODB_IO_CAPACITY_MAX

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

* innodb_io_capacity_max: under pressure, control the amount of write IO performed by MySQL per second when refreshing dirty data.

First of all, this has nothing to do with reading-the operation performed by the SELECT query. For read operations, MySQL processes and returns the results as much as possible. As for write operations, MySQL refreshes in a loop in the background, checks how much data needs to be refreshed in each loop, and does not refresh with more than the number specified by innodb_io_capacity. This also includes changing the buffer merge (changing the buffer is the key to auxiliary dirty page storage before they are flushed to disk).

Second, I need to explain what "under pressure", called "emergency" in MySQL, is that when MySQL refreshes in the background, it needs to refresh some data in order to allow new writes to come in. Then, MySQL uses innodb_io_capacity_max.

So, why should innodb_io_capacity and innodb_io_capacity_max be set?

The best way is to measure the random write throughput of your storage settings and set innodb_io_capacity_max to the maximum IOPS your device can achieve. Innodb_io_capacity is set to 50-75% of it, especially if your system is mainly write.

Usually you can predict what the IOPS of your system is. For example, RAID10 made up of 815k 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 low-cost enterprises SSD can do 4000-10000 IOPS and so on.

It's not a problem if this value is not set perfectly. However, be aware that the default values of 200 and 400 limit your write throughput, so you may occasionally catch the refresh process. If this happens, it may be that you have reached the write IO throughput of your hard drive, or that this value is set too low to limit the throughput.

11.INNODB_STATS_ON_METADATA

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

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

Starting with version 5.1.32, this is a dynamic variable, which means you don't need to restart the MySQL server to shut it down.

12.INNODB_BUFFER_POOL_DUMP_AT_SHUTDOWN & INNODB_BUFFER_POOL_LOAD_AT_STARTUP

The innodb_buffer_pool_dump_at_shutdown and innodb_buffer_pool_load_at_startup variables have nothing to do with performance, but if you occasionally restart the mysql server (such as an active configuration), it does. When both are activated, the contents of the MySQL buffer pool (more specifically, cache pages) are stored in a file when MySQL is stopped. The next time you start MySQL, it starts 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 doesn't actually copy the buffer pool contents to the file on shutdown, just copy the tablespace ID and page ID-enough information to locate the pages on the hard disk. Then it can read those pages in a large order and load those pages very quickly, instead of requiring thousands of small random reads.

Second, the content is loaded in the background at startup, because MySQL doesn't have to wait until the buffer pool content is loaded before accepting requests (so it doesn't seem to make a difference).

Starting with MySQL 5.7.7, by default only 25% of buffer pool pages are stored in files when mysql is turned off, but you can control this value-using innodb_buffer_pool_dump_pct, recommended 75-100.

This feature has not been supported since MySQL 5.6.

13.INNODB_ADAPTIVE_HASH_INDEX_PARTS

If you are running a MySQL server with a large number of SELECT queries (and have been optimized as much as possible), adaptive hash indexing will be your next bottleneck. Adaptive hash indexes are dynamic indexes maintained internally by InnoDB that can improve the performance of the most commonly used query modes. This feature can be restarted and the server is turned off, but it is enabled by default in all versions of mysql.

This technique is very complex and in most cases it will work on most types of queries until it is accelerated. However, when you have too many queries to the database, at some point it will spend too much time waiting for AHI locks and latches.

If you have MySQL 5. 7, there is no 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, prior to mysql 5.7, there was no control over the number of AHI partitions. In other words, having a global mutex to protect AHI may cause your select queries to hit the wall frequently.

So if you are running 5.1or5.6and have a large number of select queries, the easiest solution is to switch to the same version of Percona Server to activate the AHI partition.

14.QUERY_CACHE_TYPE

If people think query caching works well, they should definitely use it. Well, sometimes it works. However, this is only useful when you are under low load, especially when it is mostly read, small write or none.

If that's the case, just set up query_cache_type=ON and query_cache_size=256M. Keep in mind, however, that 256m cannot be set to a higher value, or it will cause serious server outages when the query cache expires.

If your MySQL server has a high load, it is recommended to set query_cache_size=0 and query_cache_type=OFF, and restart the server to take effect. Then Mysql will stop using query cache mutexes on all queries.

15.TABLE_OPEN_CACHE_INSTANCES

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

The table cache is used to hold a list of currently open tables and is locked when each table opens or closes the mutex-even if it is an implicit temporary table. The use of multiple partitions definitely reduces potential contention.

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

The above is the whole content of this article, I hope it will be helpful to your study, and I also hope that you will support it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.