Shulou(Shulou.com)05/31 Report--

This article mainly explains "what is the reason for the decline in the performance of MySQL 5.7partition table". The content in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what is the reason for the decline in the performance of MySQL partition table".

Problem description

There are many performance-related improvements in MySQL version 5. 7. It includes performance improvement related to temporary table, optimization of connection establishment speed and performance improvement related to replication and distribution, and so on. Basically do not need to make configuration changes, only need to upgrade to version 5.7, can bring a lot of performance improvement.

We are in a test environment to upgrade the database to version 5.7.18 to verify that MySQL version 5.7.18 meets our expectations. After observing and running for a period of time, with development feedback, the performance of the database is lower than that of the previous version 5.6.21. The main performance feature is to encounter more lock timeouts. Develop additional feedback, performance degradation-related tables are partitioned tables. Updates are all primary keys. This feedback has attracted our attention. We made the following attempts:

The version of the database is 5.7.18. If you keep the partitioned table, the performance will be degraded.

The database version is 5.7.18, and the performance is normal by adjusting the table to a non-partitioned table.

Roll back the version of the database to version 5.6.21, keep the partition table, and the performance is normal.

Through the above tests, we roughly determined that this performance degradation is related to the upgrade of the MySQL5.7 version.

Problem recurrence

There are many database table structures in the test environment, and the invocation relationship is also complex. In order to further analyze and locate the problem, we peel off the cocoon and construct a simple reproduction process as follows

/ / create a test partition table T2: CREATE TABLE `t2` (`id` INT (11) NOT NULL, `dt` DATETIME NOT NULL, `data` VARCHAR (10) DEFAULT NULL, PRIMARYKEY (`id`, `dt`), KEY`idx _ dt` (`dt`) ENGINE=INNODB DEFAULTCHARSET=latin1 / *! 50100 PARTITION BY RANGE (to_days (dt)) (PARTITION p20170218 VALUES LESS THAN (736744) ENGINE= InnoDB, PARTITIONp20170219 VALUES LESS THAN (736745) ENGINE= InnoDB PARTITIONpMax VALUES LESS THAN MAXVALUE ENGINE = InnoDB) * / / insert test data INSERT INTO T2 VALUES (1, NOW (),'1') INSERT INTO T2 VALUES (2, NOW (),'2'); INSERT INTO T2 VALUES (3, NOW (),'3'); / / SESSION 1 does an update operation on the record with id = 1, and the transaction is not committed. BEGIN;UPDATE T2 SET DATA ='12 'WHERE id = 1; / / SESSION 2 makes an update to the record of id = 2. BEGIN;UPDATE T2 SET DATA = '21' WHERE id = 2

In SESSION 2, we found that this update operation has been waiting. ID is the primary key. According to reason, the record update of the primary key id = 1 will not affect the record update of the primary key id = 2.

Query the innodb_locks table under information_schema. This table is used to record locks that InnoDB transactions have attempted to request but have not yet acquired, as well as locks owned by transactions that block other transactions. There are two records:

Looking at the innodb_ locks table at this time, the transaction id=40021 locks the second row record on page 3, causing the transaction id=40022 to fail.

If we roll back the database to version 5.6.21, we cannot reproduce the above scenario.

Further analysis

Based on the information provided by the innodb_ locks table, we know that the problem is that InnoDB locks inappropriate rows. The table is the memory storage engine. We set a breakpoint on the insert interface of the memory storage engine to get the following stack information. Make sure it is the red box and write the lock information to the innodb_ locks table.

And it is confirmed in the function fill_innodb_locks_from_cache that each time the data written to the row is obtained from the Cache object in the following code.

We know that transaction lock information is stored in Cache, so we need to further find out how the data in Cache is added. Find the function add_lock_to_cache by searching for the location of the cache object in the innodb code. After setting a breakpoint for debugging, it is found that the content of this function is consistent with the data that filled in the innodb_locks table. Make sure that the lock object used by this function is the lock object we are looking for.

Troubleshoot where the lock_t type is used. After filtering and debugging, it is found that in the function RecLock::lock_add, the generated row lock is added to the transaction linked list where the lock is located.

The RecLock::lock_add function deduces the reason why the row lock was generated. Therefore, by setting a breakpoint for the function, look at the function stack and locate the function at the red box in the following stack:

Trace the following code of Partition_helper::handle_ordered_index_scan. According to the analysis of this code, m_part_spec.end_part determines the number of * * rows to lock. Here is the reason for abnormal row lock generation.

In the end, the problem boils down to the reason for the generation of m_part_spec.end_part. By troubleshooting the use of end_part, we finally locate the initial setting value of the variable before use in the get_partition_set function. As you can see from the code, each update operation of a single record locks the same number of rows with the same number of partitioned tables during index scan locking. This is the root cause.

Verify the conclusion

According to the previous analysis, each update operation of a single record locks the same number of rows in the partitioned table. We tried to verify our findings.

The following two records are added:

INSERT INTO T2 VALUES (4, NOW (),'4'); INSERT INTO T2 VALUES (5, NOW (),'5'); / / SESSION 1 does an update operation on the record with id = 1, and the transaction is not committed. BEGIN;UPDATE T2 SET DATA ='12 'WHERE id = 1; / / SESSION 2 now makes an update to the record of id = 4. BEGIN;UPDATE T2 SET DATA = '44' WHERE id = 4

We found that the update to id = 4 works normally. Will not be affected by the update of id = 1. This is because the id=4 record, which exceeds the number of partitions in the test case, will not be locked. In practical application, the number of partitions defined by the partition table will not be only 3 as in the test cases, but tens or even hundreds. The result of locking in this way will aggravate the lock conflict in the case of updates, causing the transaction to be in a lock waiting state. As shown in the following figure, each transaction has N row locks, so the possibility of these locked records overwriting each other is greatly improved, which leads to a decrease in concurrency and efficiency.

Thank you for your reading, the above is "what is the reason for the decline in the performance of MySQL 5.7partition table". After the study of this article, I believe you have a deeper understanding of what is the reason for the decline in the performance of MySQL 5.7. the specific use of the situation also needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.