MySQL Database Specification-Tuning chapter (end article)

Objective

This is the last of the MySQL Database Specification-tuning section, which is designed to provide precise positioning and tuning methods that we find are weak in system performance, parameter tuning in MySQL systems, and problems with SQL scripts.

Directory

1.MySQL Tuning Pyramid theory
2.MySQL Slow Query Analysis--mysqldumpslow, pt_query_digest tool usage (SQL scripting plane)
3. Select the appropriate data type
4. Removing useless indexes--pt_duplicate_key_checker the use of tools (index plane)
5. Inverse Paradigm Design (table structure)
6. Vertical Level Sub-table
7.MySQL Important parameter tuning (System configuration)

1.MySQL Tuning Pyramid theory

As shown in the following:

MySQL tuning pyramid theory. png
as shown in the following:

There are four database optimization dimensions :
hardware , system configuration , database table structure ,sql, and Indexes
Optimize costs :
hardware > system Configuration > database table Structure >SQL and Indexes
Optimization effect :
Hardware < system configuration < database table structure <SQL and Indexes

2.MySQL Slow Query analysis

The analysis of slow query in the system can help us locate the problem more efficiently and analyze the problem.
Mysqldumpslow, Pt_query_digest is a tool for slow query analysis.

Front-facing conditions

1. Check if the native MySQL Server slow query is open

‘slow%‘; 

The slow query opens as shown below:

Slow query status

If the slow query is not open, set a slow query with the following script:

set global slow_query_log = on;即set global [中选项] = [你要设置的参数值]注意 slow_query_log_file 路径要加单引号，因为路径varchar  类型的。

2.1 Mysqldumpslow Analysis Slow Query

Mysqldumpslow is a native tool for MySQL's own analysis database slow query, using the following methods:

3 /data/mysql/log/mysql_slow_query.log | more \G;-t  3 显示前3条慢查询。

Slow Query information and analysis

Slow query information. png

However, Mysqldumpslow displays less information, such as the percentage of the number of times that this SQL execution takes in the overall execution count. Similar to the above information is not present in the analysis results of Mysqldumpslow.

Then we'll introduce another tool pt_query_digest

2.2 Pt_query_digest Analysis Slow query

The reason why using the Pt_query_digest tool to analyze the slow query log is that the above tool analysis is more informative and more convenient for us to analyze the slow query.
Front-facing conditions
Install pt_query_digest, Google search should be a lot.

Ensure that the Pt_query_digest installation successfully performs the following actions:

pt-query-digest /data/mysql/log/mysql_slow_query.log > slow_log.report

The above command indicates parsing the native slow query and outputting the report (file)
Next analyze the resulting report:

tail slow_log.report

Press the information as shown:

Pt_query_digest report analysis. png

We have detailed descriptions of the report information in the Red block diagram above, in fact this is the key point we need to grasp:

1.pct : SQL statement An execution property is a percentage of the execution properties of all slow query statements
1.total: SQL statement all attribute time of an execution property.
2.Count: The number of times the SQL statement was executed, the corresponding PCT indicates that this SQL statement performed as% of the number of execution times of all slow query statements. For 25%,total: Indicates a total of 1 executions.
3.Exectime: SQL execution times
4.Locktime: When SQL is locked during execution
5.Rows sent: valid data transferred, only value in select query statement
6.Rows Examine: Total queried data, non-target data.
7.query_time Distribution: Query time distribution
8.SQL Statement : SELECT * from payment limit 10\g;

For example, adding a SQL statement with a higher number of executions (count), a long execution time, a small value for rows sent, and a large value for rows examine indicate (I/O is large). That means it's possible that the SQL query statement went through a full-table scan, or a full-index scan. Then it is necessary to establish the appropriate index or optimize the SQL statement.
The following is a good demonstration of the three points we need to focus on when analyzing slow queries:

Three datum points for slow query analysis. Png3. Select the appropriate data type

You can refer to the MySQL development specification- 1.6 Data sheet design and planning in the design chapter

As the characters commonly used segment type of selection suggestions:

Select the appropriate data type 4. Removing useless indexes--pt_duplicate_key_checker use of tools (index plane)

This tool can analyze indexes that may be duplicated in the index established in all tables in the selected database, and gives suggestions for deletion.

5. Inverse Paradigm Design (table structure)

For a paradigm understanding, refer to the--MYSQL Database specification-Design paradigm for 1.1 database tables (tri-paradigm & inverse paradigm)
First look at a data table design that does not meet the third paradigm:

Data table design that does not meet the third paradigm. png

Does not meet the problems arising from the third paradigm:
If the table belongs to the beverage classification of the data are all deleted, then the beverage classification will not exist, the beverage classification description will be gone, the query is not. This is obviously unreasonable.

Focus: satisfying the third paradigm requires that there is no dependency between the non-key attributes , and that the classification and classification describe a direct dependency relationship. So it does not conform to the requirements of the third paradigm, so what does it take to make the table conform to the third paradigm?

Table that satisfies the third normal form after splitting:

Table that satisfies the third normal form. png

We use a classification-the Commodity Name intermediate table to serve as the intermediate bridge after the table.

Of course, if you always follow the paradigm of design, what design is moving towards the third paradigm, when the query needs to connect many tables, the index has played no role, because the fields are not in the same table, so the index is useless, then we should consider the design of anti-normalization.

6. Vertical and horizontal sub-table

In principle, when the number of data records in the table more than 30 million, and the good index can not improve the speed of data query, it is necessary to split the table into more small tables, to query.
There are two mechanisms for table-splitting:

Vertical Sub-table : That is, part of the column is separated from the data placed in the new set of tables, the preferred field value length is longer, the type of the heavier fields are vertically detached.
Horizontal sub-table : The table in the horizontal segmentation of data, you can follow the range, modulo operations, hash operations for data cutting, each table structure information is the same.

7.MySQL Important parameter tuning (System configuration)

7.1 Operating System Configuration optimizations

Operating system configuration optimizations open operating system file restrictions. png

Brief introduction:

1.tcp连接配置，超时时间配置2.linux上文件打开数量限制3.除此之外，最好在MySQL 服务器上关闭iptables,selinux 等防火墙软件。

7.2 MySQL configuration file optimization

MySQL can be configured by setting configuration parameters at startup and using configuration files in two ways, in most cases the configuration file is located in the/etc/my.cnf or/etc/mysql/my.cnf MySQL lookup configuration file order can be obtained in the following ways:

$ /usr/sbin/mysqld --verbose --help | grep -A 1 ‘Default options‘

Note: If a configuration file exists in more than one location, the following will overwrite the previous

7.2.1 Innodb_buffer_pool_size

Innodb_buffer_pool_size is a very important parameter for the user to configure the InnoDB buffer pool size. If there are only InnoDB tables in the database, the recommended configuration is 75% of the total memory.
In general, run the following command to get the best value for configuring the Innodb_buffer_pool_size parameter:

select engine round(sum(data_length+index_length)/1024/1024,1) as ‘total MB‘ from information_schema.tables where table_schema not in ("information_schema","performance_schema") group by engine;Innodb_buffer_pool_size > Total MB;

7.2.2 Innodb_buffer_pool_instance

There are some resources in the MySQL system that need to be used exclusively, such as buffering is such a resource, so if there is only one buffer pool in the system, it will increase the chance of blocking. The more we divide, the more concurrent performance can be added.

7.2.3 Innodb_log_buffer_size

The size of the InnoDB log buffer can only be set to the size of the transaction log generated in 1s.

7.2.4 Innodb_flush_log_at_trx_commit

Critical parameters that have a significant impact on the I/O to InnoDB. The default value is 1, you can go to 0,1,2 three values, the general recommendation is 2, but if the data security requirements are high, 1 is used by default.

• 0: A transaction commit change record is flushed to disk every 1s
• 1: every transaction commit flushes the change log to disk (the safest way)
• 2: Each commit flushes the log to the buffer and flushes the log to disk after 1s.

7.2.5 innodb_read_io_threads && innodb_write_io_threads

These two parameters determine the number of I/O processes that the InnoDB reads and writes, and defaults to 4.
There are also two factors that determine the value of these two parameters: cpu核数 应用场景中读写事务比例 .

7.2.6 innodb_file_per_table

Key parameters, which are configured to off by default.
Control InnoDB Each table uses a separate table space, and by default all tables are built into the shared table space.
What is the problem with using shared tablespace:

 1.多个表对共享表空间的操作，是顺序进行的，这样的话操作效率在并发情况下回降低。2.如果现在要删除一张表，会导致共享表空间先要将数据导出来，再重组。

7.2.7 Innodb_stats_on_metadata

Role: Determines when MySQL will refresh the statistics of the InnoDB table.
Ensure that the database optimizer can use the latest indexes, but not too frequently, and generally set to off.

Fxliutao
Links: Https://www.jianshu.com/p/55020afb5eba
Source: Pinterest
The copyright of the book is owned by the author, and any form of reprint should be contacted by the author for authorization and attribution.

MySQL Database Specification-Tuning chapter (end article)