Shulou(Shulou.com)06/01 Report--

Reprint: http://mysqllover.com/?p=1264

Reference: InnoDB Insert Buffer implementation details http://hedengcheng.com/?p=94

MySQL5.6.23: fix "UNABLE TO PURGE A RECORD"

This paper briefly describes the reasons for the frequent encounter of "UNABLE TO PURGE A RECORD" online before.

# # #

"UNABLE TO PURGE A RECORD" occasionally appears in the online instance error log, and according to the official bug system, many users have encountered similar problems.

When the change buffer module caches index operations in the following sequence:

The record is marked to be deleted (IBUF_OP_DELETE_MARK) and then inserted into the same record-the IBUF_OP_ insert thread needs to physically delete the secondary index record, and the operation is performed by buffer-IBUF_OP_DELETE

When reading a physical page, ibuf merge is required, and when executed to IBUF_OP_DELETE, it is found that the record has not been marked for deletion, resulting in an error log error.

Obviously, the above operation sequence is unreasonable, and the correct sequence should be IBUF_OP_DELETE_MARK,IBUF_OP_DELETE,IBUF_OP_INSERT.

In order to sort out the logic, let's simply sort out the relevant code.

Note that IBUF_OP_DELETE is triggered by the tag deletion operation in the first step and initiated by the Purge thread; in the control structure of each buffer pool, there is a member buf_pool- > watch [BUF _ POOL_WATCH_SIZE], and the value of BUF_POOL_WATCH_SIZE is the number of purge threads, which is used to assist the Purge operation.

Assume that there is no corresponding Page,Purge thread in memory to do the following:

First query buffer pool to see if page has been read into memory

If it is not in memory, information such as page no is stored in the watch array and inserted into page hash (buf_pool_watch_set). If the page is then read into memory, the watch tag is deleted.

Determine whether the secondary index record can be Purge (row_purge_poss_sec, when the clustered index record corresponding to the secondary index record does not have delete mark and its trx id is older than the current purge view, the Purge operation cannot be done). Then, when the IBUF_OP_DELETE type ibuf record is inserted, whether the page is set to sentinel (ibuf_insert_low,buf_pool_watch_occurred) under double check, if not set. Indicates that page has been read into memory, so you can do purge directly without caching. For normal operation types, such as IBUF_OP_INSERT and IBUF_OP_DELETE_MARK, it is also possible to double check page whether it is read into memory. Buf_page_hash_get is called in the function ibuf_insert to check. If the page is read into memory, the operation is not cached. If the requested Page is set to sentinel, NULL is returned from the buf_page_hash_get, so it is determined that the cache operation is required. This is where the problem lies: Mark to delete the record, write to the IBUF_OP_DELETE_ Mark purchase thread to set the sentinel corresponding to page, complete the legitimacy check, prepare to call ibuf_insert to insert the same record, write IBUF_OP_INSERTPurge thread to write IBUF_OP_DELETE

Solve

If the page where the record is located is set to a sentinel, then concurrent insertions to that page should not be cached in the change buffer, but instead try to read the physical page directly.

Https://github.com/mysql/mysql-server/commit/ec369cb4f363161dfbbbd662b20763b54808b7d1

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.