A more comprehensive MySQL optimization reference

 

This article has compiled some common methods of MySQL optimization, to do a simple summary sharing, to help those who do not have full-time MySQL DBA to do the basic optimization work, as for the specific SQL optimization, most by adding the appropriate index to achieve the results, more complex needs to be specifically analyzed.

1, hardware layer related optimization 1.1, CPU-related

In the server's BIOS setup, you can adjust the following configurations to maximize CPU performance or avoid classic NUMA issues:

1, choose Performance Per Watt Optimized (DAPC) mode, play CPU maximum performance, run DB This usually requires a high computational capacity of the service should not consider power-saving;

2, close c1e and C states and other options, the purpose is to improve CPU efficiency;

3. Frequency (Memory frequency) Select Maximum performance (best performance);

4. In the Memory Settings menu, enable node interleaving to avoid NUMA issues;

1.2. Disk I/O related

The following are some of the measures that can be optimized for disk I/O, based on the magnitude of the IOPS performance boost:

1, the use of SSD or PCIe SSD devices, at least hundreds of times times or even tens of times the IOPS increase;

2, the acquisition of the array card with the cache and the Bbu module, can significantly increase the IOPS (mainly refers to mechanical disk, SSD or PCIe SSD except. At the same time, the health of the cache and the Bbu module should be checked periodically to ensure that the data is not lost during the accident;

3, when there is an array card, set the array write policy is WB, even Force WB (if there is double protection, or the data security requirements are not particularly high), the use of WT policy is strictly forbidden. and closed array pre-reading strategy, is basically a chicken, the use of little;

4, as far as possible to choose RAID-10, rather than RAID-5;

5, the use of mechanical disk, as far as possible to choose high-speed, such as the use of 15KRPM, rather than 7.2KRPM disk, a few money;

2, System layer related optimization 2.1, File system layer optimization

At the file system level, the following measures can significantly improve IOPS performance:

1, use Deadline/noop These two I/O scheduler, do not use CFQ (it is not suitable for running DB class service);

2, the use of XFS file system, do not use EXT3;EXT4 reluctantly available, but the volume of business is very large, you must use XFS;

3, File system mount parameter added: Noatime, Nodiratime, nobarrier several options (Nobarrier is the XFS file system special);

2.2. Optimization of other kernel parameters

The appropriate values are set for key kernel parameters in order to reduce the propensity for swap and to allow no significant fluctuations in memory and disk I/O, resulting in an instantaneous peak load:

1, set the vm.swappiness to about 5-10, or even set to 0 (RHEL 7 is carefully set to 0, unless you allow oom kill to occur), to reduce the chance of using swap;

2, set the Vm.dirty_background_ratio to 5-10, set the Vm.dirty_ratio to twice times its size to ensure that the dirty data can be continuously flushed to the disk, to avoid the instantaneous I/O write, resulting in a serious wait (and MySQL innodb_ max_dirty_pages_pct similar);

3, the net.ipv4.tcp_tw_recycle, Net.ipv4.tcp_tw_reuse are set to 1, reduce time_wait, improve TCP efficiency;

4, as for the network transmission of READ_AHEAD_KB, nr_requests These two parameters, I have been tested, found that the read-write mixed-based OLTP environment impact is not large (should be more effective for reading sensitive scenes), but it may be my test methods have problems, you can adjust the discretion;

3. mysql Layer related optimization 3.1, about version selection

The official version we call Oracle MySQL, this is nothing to say, I believe most people will choose it.

I personally strongly recommend that you choose the Percona branch version, which is a relatively mature, excellent MySQL branch version that has improved in terms of performance improvements, reliability, and management. It is basically fully compatible with the official Oracle MySQL version and has a performance improvement of about 20%, so I recommend it first, and I've been focusing on it myself since 2008.

Another important branch version is MARIADB, which says that MARIADB is a branch version that is not quite appropriate because its goal is to replace Oracle MySQL. It is mainly in the original MySQL server layer made a lot of source-level improvements, but also a very reliable, excellent branch version. However, there are new features that are not compatible with Gtid and the official version (MySQL 5.7, which also supports Gtid mode on or off), and that most people will follow the official version, so there is no priority to recommend MARIADB.

3.2, about the most important parameter options adjustment recommendations

It is recommended to adjust the following key parameters for better performance (use the MY.CNF generator provided on this site to generate profile templates):

1, select the Percona or MARIADB version, it is strongly recommended to enable the thread pool feature, so that in high concurrency, performance will not be significantly reduced. In addition, there are extra_port functions, very practical, the key moment can help. There is another important feature is the Query_response_time function, but also allows us to the overall SQL response time distribution has a visual experience;

2, set Default-storage-engine=innodb, also is the default use InnoDB engine, it is strongly recommended not to use MyISAM engine, InnoDB engine can meet more than 99% of business scenarios;

3, adjust the size of the innodb_buffer_pool_size, if it is a single instance and the vast majority of InnoDB engine table, you can consider setting the physical memory of 50% ~ 70% or so;

4, according to the actual need to set Innodb_flush_log_at_trx_commit, Sync_binlog value. If the requirement data cannot be lost, then two are set to 1. If you allow the loss of a bit of data, you can set it to 2 and 10, respectively. If the care data is not lost at all (for example, on slave, it's a big deal to redo), it can be set to 0. These three sets of values cause the performance of the database to be affected by: high, medium, low, that is, the first one will be the slowest database, the last one is the opposite;

5, set innodb_file_per_table = 1, using a separate table space, I really do not think of the use of shared table space has any advantage;

6, set Innodb_data_file_path = Ibdata1:1g:autoextend, do not use the default 10M, otherwise, when there is high concurrency transactions, will be a small impact;

7, set innodb_log_file_size=256m, set innodb_log_files_in_group=2, basic can meet more than 90% of the scene;

8, set long_query_time = 1, and above the 5.5 version, can be set to less than 1, it is recommended to set to 0.05 (50 milliseconds), record those who perform slower SQL for subsequent analysis and troubleshooting;

9, according to the actual needs of business, appropriate adjustment max_connection (maximum number of connections), Max_connection_error (maximum number of errors, the recommended setting is more than 100,000, and Open_files_limit, Innodb_open_files , Table_open_cache, Table_definition_cache These parameters can be set to about 10 times times the size of max_connection;

10, the common misunderstanding is the Tmp_table_size and max_heap_table_size set the relatively large, once seen set to 1G, the 2 options are each connection session will be assigned, so do not set too large, otherwise easy to cause oom to occur Some other connection session-level options such as: Sort_buffer_size, Join_buffer_size, read_buffer_size, read_rnd_buffer_size, etc., also need to be careful not to set too large;

11, because it is recommended no longer use the MyISAM engine, so the key_buffer_size can be set to about 32M, and strongly recommend to close the query cache function;

3.3, about the schema design code and SQL use suggestions

Here are a few common schema design specifications and SQL usage recommendations that will help improve MySQL efficiency:

1, all the InnoDB table design a non-business use of the self-increment of the key, for the vast majority of scenes are so, really pure only read with the InnoDB table is not much, really so it is better to use TOKUDB to come cost-effective;

2, the field length to meet the requirements of the premise, as far as possible to choose a small length. In addition, the field properties are as far as possible with NOT NULL constraints, can improve performance to some extent;

3, if possible, do not use the Text/blob type, it is necessary to split the sub-table, do not put together with the main table, avoid select * When reading performance is too poor.

4, when reading data, only select the required columns, do not select * Every time, avoid serious random reading problems, especially read some text/blob columns;

5. When creating an index on a varchar (N) column, it is usually 50% (or even smaller) to create the prefix index to meet more than 80% of the query requirements, no need to create an entire column full-length index;

6, under normal circumstances, sub-query performance is poor, proposed to change the join notation;

7, multi-table joins the query, the related field type is as consistent as possible, and must have the index;

8, multi-table connection query, the result set of small table (note, here refers to the filtered result set, not necessarily the whole table data volume is small) as the driver table;

9, multi-table joins and has the sort, the sort field must be the driver table, otherwise the row sequence cannot use the index;

10, multi-use composite index, less use of multiple independent indexes, especially some cardinality (cardinality) is too small (for example, the column of the total number of unique values less than 255) do not create a separate index;

11, similar to paging function of SQL, it is recommended to first use the primary Key association, and then return the result set, the efficiency is much higher;

3.4. Other suggestions

Other recommendations for management maintenance of MySQL are:

1, usually, the single table physical size does not exceed 10GB, the number of single-table rows of not more than 100 million, the average length of the line is not more than 8KB, if the machine performance is sufficient, the amount of data MySQL is fully capable of processing over, do not worry about performance issues, so the proposal is mainly to consider the cost of online DDL higher;

2, do not worry too much memory mysqld process, as long as not occur oom kill and use a lot of swap is OK;

3, in the past, the purpose of running multiple instances on a single machine is to maximize the use of computing resources, if the single instance has been able to exhaust most of the computational resources, there is no need to run more instances;

4. Periodically use Pt-duplicate-key-checker to check and remove duplicate indexes. Periodically use the Pt-index-usage tool to check and remove low-frequency indexes;

5, the regular collection slow query log, with the Pt-query-digest tool analysis, can be combined with anemometer system slow query management in order to analyze slow query and follow-up optimization work;

6, can use Pt-kill kill long time of SQL request, Percona version has an option innodb_kill_idle_transaction also can realize this function;

7, using Pt-online-schema-change to complete the large table online DDL requirements;

8, regular use Pt-table-checksum, pt-table-sync to check and repair MySQL master-slave replication data differences;

Written at the end: This time the optimization reference, in most cases I have introduced the applicable scenario, if your application scenario and this article describes the different, then it is recommended to adjust according to the actual situation, rather than mechanically. Welcome to question the shooting of bricks, but refuse to pass through the habitual resistance of the brain.

A more comprehensive MySQL optimization reference