Mysql Common low-level optimization operation tutorials and related recommendations _mysql

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), select Performance per Watt optimized (DAPC) mode, play CPU maximum performance, run DB This usually requires high computational services do not consider power-saving;
(2), turn off C1E and C states and other options, the purpose is to enhance CPU efficiency;
(3), Memory 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 range of IOPS performance improvements:

(1), using SSD or PCIe SSD equipment, obtain at least hundreds of times times or even million times IOPS upgrade;
(2), the acquisition of array card with both cache and BBU module, can significantly enhance IOPS (mainly refers to mechanical disk, SSD or PCIe SSD except.) At the same time need to regularly check the cache and BBU module health status, to ensure that the accident does not lose data;

(3), there are array cards, set the array write strategy for the WB, or even Force WB (if there is dual-power protection, or the data security requirements are not particularly high), the use of WT strategy is strictly prohibited. and closed array pre-read strategy, is basically chicken ribs, not very useful;

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

(5), the use of mechanical disk, as far as possible to choose a high speed, such as the choice of 15KRPM, rather than 7.2KRPM of the plate, not a few money;
2. System-Level correlation optimization

2.1, File system layer optimization

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

(1), use deadline/noop These two kind of 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 barely available, but the business volume is very large, you must use XFS;

(3), file system mount parameter increase: Noatime, Nodiratime, nobarrier several options (Nobarrier is XFS file system special);
2.2. Other Kernel parameter optimization

Set appropriate values for key kernel parameters to reduce the tendency of swap and allow no significant fluctuations in memory and disk I/O, resulting in instantaneous peak load:

(1), set the vm.swappiness to 5-10 or so, or even set to 0 (RHEL 7 or more carefully set to 0 unless you allow Oom kill), in order to reduce the chance to use swap;
(2), set the Vm.dirty_background_ratio to 5-10, set the vm.dirty_ratio to its twice times to ensure that the dirty data can be continuously flushed to disk, to avoid instantaneous I/O write, resulting in serious waiting (and MySQL in the innodb_ max_dirty_pages_pct similar);

(3), will 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 of READ_AHEAD_KB, nr_requests These two parameters, I have been tested, I found that the mixed-reading of the OLTP environment impact is not large (should be read-sensitive scenes more effective), but I may be the test method has problems, can be discretionary adjustments;

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 the choice of Percona branch version, it is a relatively mature, excellent MySQL branch version, in performance improvement, reliability, management has done a lot of improvements. It's basically completely compatible with the official Oracle MySQL version, and it's about 20% more performance, so I recommend it first, and I've been using it since 2008.

Another important branch version is mariadb, saying that MARIADB is a branching version that is not quite appropriate because it aims to replace Oracle MySQL. It is mainly in the original MySQL server layer to do a lot of source-level improvement, but also a very reliable, excellent branch version. But it has also resulted in new features that are not compatible with the official version of Gtid (MySQL 5.7, which also supports the Gtid mode online dynamic open or close), as well as the vast majority of people will follow the official version, so there is no priority to recommend MARIADB.

3.2, about the most important parameter options to adjust the recommendations

It is recommended that you adjust the following key parameters to achieve better performance:
(1), select Percona or MARIADB version, it is strongly recommended that the thread pool feature be enabled so that performance does not drop significantly in high concurrency. In addition, there are extra_port functions, very practical, the key moment can save lives. Another important feature is the Query_response_time function, which enables us to have an intuitive sense of the overall SQL response time distribution;

(2), set Default-storage-engine=innodb, also is the default to use the InnoDB engine, strongly recommends that the MyISAM engine should not be used again, InnoDB engine can meet more than 99% of the business scene;

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

(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 a bit of data is allowed to be lost, it can be set to 2 and 10, respectively. And if you don't need to care the data (for example, on Slave, anyway), you can set it to 0. These three sets of values cause the performance of the database to be affected by: high, Medium, Low, which is the first one will be the slowest database, the last one is the opposite;

(5), Set innodb_file_per_table = 1, using independent table space, I really do not want to use the shared table space has any advantage;

(6), set Innodb_data_file_path = Ibdata1:1g:autoextend, do not use the default 10M, otherwise in the case of high concurrent transactions, will be affected by no small;

(7), set innodb_log_file_size=256m, set innodb_log_files_in_group=2, basically can meet more than 90% scene;

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

(9), according to the actual needs of the business, the appropriate adjustment max_connection (maximum number of connections), Max_connection_error (maximum number of errors, recommended set to 100,000 or more, 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 the max_connection;

(10), the common misconception is that the tmp_table_size and max_heap_table_size settings are relatively large, have seen set to 1G, these 2 options are each connection session will be allocated, so do not set too large, or easily lead to oom 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 pay attention to not set too large;

(11), because it has been recommended no longer use the MyISAM engine, so you can set the key_buffer_size to around 32M, and strongly recommend to turn off query cache function;
3.3, about schema design specifications and SQL use recommendations

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

(1), all the InnoDB table design a No business use of the self-add the key, for most of the scene is so, really pure only read with InnoDB table is not much, really so not as good as the tokudb come cost-effective;

(2), the length of the field 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 plus not NULL constraints, to a certain extent improve performance;

(3), as far as possible do not use the Text/blob type, if necessary, it is recommended to split into the child table, do not put together with the main table, to avoid SELECT * When read performance is too poor.

(4), read the data, only select the required columns, not every time select *, to avoid serious random reading problems, especially read some text/blob columns;

(5) When creating an index on a varchar (N) column, it is generally necessary to create a prefix index of about 50% (or even smaller) of the left and right lengths to meet more than 80% of the query requirements, and it is unnecessary to create a full length index of the entire column;

(6), under normal circumstances, the performance of the subquery is relatively poor, the proposed change to create join writing;

(7), multiple table join query, the associated field type as much as possible, and must be indexed;

(8), multiple table connection query, the result set small table (note, here refers to the filtered result set, not necessarily the whole table data volume small) as a driving table;

(9), multiple 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 than multiple independent indexes, especially some cardinality (cardinality) is too small (for example, the column's total number of unique values less than 255), do not create a separate index;

(11), similar to the paging function of SQL, it is recommended to use Primary Key association, and then return the result set, the efficiency will be much higher;
3.4. Other suggestions

Other recommendations regarding the management maintenance of MySQL are:

(1), generally, the Tanku physical size is not more than 10GB, the number of single table rows no more than 100 million, the line average length of not more than 8KB, if the machine performance is sufficient, the amount of data MySQL is fully capable of processing, do not worry about performance problems, so the proposal is mainly to consider the cost of online DDL

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

(3), in the past, a single running multiple instances of the goal is to maximize the use of computing resources, if the single instance has been able to run out of most of the computational resources, there is no need to run more instances;

(4) Periodically use Pt-duplicate-key-checker to check and delete duplicate indexes. Periodically use the Pt-index-usage tool to check for and remove indexes with very low frequency;

(5), regular collection of slow query log, with pt-query-digest tools for 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 a very long time SQL request, Percona version has an option innodb_kill_idle_transaction also can implement this function;

(7), using Pt-online-schema-change to complete the online DDL requirements of large tables;

(8), regular use of pt-table-checksum, pt-table-sync to check and repair MySQL master and slave copying data differences;