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

This article will explain in detail the example analysis of MySQL slow check caused by a large number of deletions. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

I. background

A large number of alarms of slow check were received on the monitoring, and the business also reported that the query was very slow, and then turned on the computer to confirm the reason for the slow check.

II. Description of phenomena

After analyzing the slow check of the platform, we find that the slow check has the following characteristics:

The names of slow tables are all sbtest1, and there are no other tables.

Most slow queries look up the latest data, such as select * from sbtest1 limit 1.

Rows examined is 1 and no large amount of data is scanned.

III. Problem analysis

Through a rough analysis of the slow search, SQL itself did not find a problem. So is there something wrong with the host or the network?

After the analysis of the IO of the network and host disk, the load is normal and there is no packet loss.

Back to the database itself, the slow check is still there to confirm where the slow check is.

When slow check is being carried out, most of them are in the state of Sending data. We use profiling to confirm the time distribution of slow check:

From the figure, we can see that the time taken by sending data is 0.945 seconds, which basically accounts for 99% of the SQL execution time.

So what does sending data mean? let's learn about it from the official documents.

The thread is reading and processing rows for a SELECT statement, and sending data to the client. Because operations occurring during this state tend to perform large amounts of disk access (reads), it is often the longest-running state over the lifetime of a given query.

Sending data means that reading and processing row data and sending data to the client, because there is only one row of data, and the network is confirmed to be normal at that time, then time is spent reading and processing select data.

Then why is it that only limit 1 is taken, and SQL without where conditions is so slow to scan a row of data?

Turn on the monitor and see if there are any abnormal indicators.

We have noticed that the History list length of the database has been rising to tens of thousands. The execution time of slow check becomes slower with the increase of History list length. When History list length remains high, it shows that there are a large number of UNDO that have not been purge. Since the next door level of the current database is RR, if the transaction is not committed yet, you need to use UNDO to build the corresponding version history to ensure that the database can be read repeatedly (related to MVCC).

Since the History list length is so high, it may be an exception in a historical transaction that is not committed, or it may be a backup of a consistent snapshot. The corresponding transaction information can be confirmed through the information_schema.innodb_trx table. After query, it is found that a transaction has been executed for about 4 hours, has not been committed, and is not a backup user. Manually kill the thread, and the slow check disappears.

3.1talk about MVCC.

MySQL InnoDB supports multiple versions of MVCC, which can be left unlocked in normal SELECT. Using multiple versions to read row records of a specified version, reducing the number of locks, can greatly improve the concurrent read and write ability of the database.

Innodb records a list of all active transactions in MySQL at some point in the transaction and saves it to read view. In a subsequent query, we determine whether the record is visible by comparing the transaction list in the recorded transaction ID and read view.

3.1.1 Innodb line record

In the row structure of Innodb, there are also three system columns, namely DATA_ROW_ID, DATA_TRX_ID, and DATA_ROLL_PTR.

DATA_ROW_ID: if the table does not show a defined primary key, use the ROW_ID generated by MySQL itself, which is 48-bit, otherwise it represents the user-defined primary key value

DATA_TRX_ID: represents the transaction ID of this record. If it is a secondary index, only save trx_id in page

DATA_ROLL_PTR: a pointer to the corresponding rollback segment.

3.1.2 read view

The read view is requested before the execution of the SQL statement, where the RC isolation level is applied for every SELECT, and the read view of the RR isolation level is the first SQL request after the start of the transaction, and then other SQL within the transaction uses this read view.

There are three variables in read view that need to be focused on:

Low_limit_id: represents the largest transaction ID of the active transaction list at the moment of creation of read view

Up_limit_id: represents the smallest transaction ID that created the list of active transactions at the moment of read view

Trx_ids: represents a list of all active transactions at the moment the read view was created.

3.1.3 judgment record visible

When the DATA_TRX_ID of the record is less than the up_limit_id of the read vew, the record has been submitted before the read view is created, and the record is visible

If the DATA_TRX_ID of the record is the same as the TRX_ID of the transaction creator, the record is visible

When the DATA_TRX_ID of the record is greater than the up_limit_id of the read view, the new transaction modification made after the creation of the read view is committed and the record is not visible

At the RR isolation level, if the A transaction starts before the B transaction creates the read view, then the SQL in the B transaction cannot see the modifications performed by the A transaction. So there is another rule: if the corresponding DATA_TRX_ID of the record is in the trx_ids of the read view, then the record is not visible.

3.1.4 DATA_ROLL_PTR

The UNDO log is an important part of MVCC. When a piece of data is modified, the historical version of the record is kept in the UNDO log. When a transaction needs to query the historical version of a record, a specific version of the data can be built from the UNDO log.

Each row record has a pointer DATA_ROLL_PTR that points to the most recent UNDO record. At the same time, each UNDO record contains a pointer to the previous UNDO record, thus forming a linked list of all UNDO history of a record. When the UNDO record still exists, the historical version of the corresponding record can be constructed.

When the corresponding version of the record is found to be invisible through DATA_TRX_ID comparison, find the corresponding rollback segment record through the system column DATA_ROLL_PTR, and continue to judge through the above rules that the record is visible. If the record is still invisible, continue to find the previous version through the rollback segment until the corresponding visible version is found.

3.2 slow check problem recurrence

After communicating with the business side, I learned that the table has scheduled tasks every day and will delete historical data. After a rough understanding of the whole process, we set up a simulation environment for testing.

The test is divided into three session, of which the Sess1 CEO transaction is not committed. Sess2 cleans up the historical data of the table, cleaning up 20 million of the data. At this point, the query is executed in Sess3, as shown in the figure above. Select * from sbtest1 limit 1 is slow as expected. But select * from sbtest1 order by id desc limit 1 executes very quickly. Why?

The image above shows the record format of the primary key, with a delete flag bit in front of each primary key record, followed by the primary key ID, transaction ID, rollback segment pointer, and finally the row record.

When a record is deleted, MySQL simply marks the record as deleted and updates DATA_TRX_ID to the transaction ID of its delete session, not actually deleting the record. When the deleted record is no longer needed by other transactions, it will be deleted by the purge thread. The purge thread is responsible for cleaning up these actually deleted records and UNDO logs that are no longer needed.

Back to the slow check itself, let's take a look at the implementation process of the slow check.

SQL is select * from sbtest1 limit1.

Through the primary key, the record of ID=1 is scanned. According to the MVCC comparison, it is found that the transaction ID is larger than the DATA_TRX_ID of the record. Matching visible rule 1, the record is visible.

Because ID=1 has been marked as DELETED, the delete record is visible

Since the table data has not been scanned completely and limit 1 is not satisfied, continue to scan the next record

The record scanned to the ID=2, according to the MVCC comparison, found that the transaction ID is larger than the record DATA_TRX_ID, matching visible rule 1, the record visible

Because ID=2 has been marked as DELETED, the delete record is visible

Since the table data has not been scanned completely and limit 1 is not satisfied, continue to scan the next record

Repeat steps 4-6 until a record is found, or a full table scan is complete.

Since 20 million of the records were deleted, Innodb needs to scan 20 million of the records to find the first record that meets the criteria, and then return to the Server layer of MySQL.

When SQL is select * from sbtest1 order by id desc limit1.

Because the old data is deleted, when scanning from the largest direction of the ID, the MVCC determines that it is visible, and then determines whether the record is marked for deletion, the record is not deleted, so it can quickly return to the Server layer, and the execution efficiency of SQL will be very high.

This is the end of the article on "sample analysis of MySQL slow check caused by a large number of deletions". I hope the above content can be helpful to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

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.