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

Dong Hongyu, a senior database expert of Walk Science and Technology, an overview of the problems.

Recently, we encountered a problem of MySQL, which is very representative after analysis, and specially wrote it for your reference.

The problem is that the database is first set to read-only, and then after a period of time, MySQL is directly Crash.

The following is printed in the error log of MySQL when Crash occurs:

-SEMAPHORES-OS WAIT ARRAY INFO: reservation count 1246555--Thread 140363572082432 has waited at row0upd.cc line 2354 for 253.00 seconds the semaphore:X-lock (wait_ex) on RW-latch at 0x7fa949340740 created in file buf0buf.cc line 1069a writer (thread id 140363572082432) has reserved it in mode wait exclusivenumber of readers 1, waiters flag 1 Lock_word: ffffffffffffffffLast time read locked in file btr0sea.cc line 931Last time write locked in file / export/home/pb2/build/sb_0-17068951-1447697721.44/mysql-5.6.28/storage/innobase/row/row0upd.cc line 2354

According to the log, we can see that thread 140363572082432 wants to put an X lock on the record, but waits for the release of the RW-latch of the 0x7fa949340740 thread.

We look down and find the following information (xxx is used instead of the user information predicate):

173 lock struct (s), heap size 30248, 7925 row lock (s), undo log entries 7924MySQL thread id 5709783, OS thread handle 0x7fa8f0da7700, query id 92213034 10.23.163.54 citicqyh updatingupdate TB_DEPARTMENT_INFO set TOTAL_USER=1 where ID='ac84f17e-82d3-4519-a1da-0d5a5a835d44'---TRANSACTION 53065242, ACTIVE 313 sec fetching rows, thread declared inside InnoDB 2081mysql tables in use 2, locked 0MySQL thread id 5428690, OS thread handle 0x7fa8f0136700 Query id 92213061 10.23.163.55 citicqyh Sending dataSELECT COUNT (DISTINCT r.user_id) FROM TB_DEPARTMENT_INFO d left join tb_qy_user_department_ref r on r.department_id = d.id WHERE d.org_id = 'xxx' AND (d.dept_full_name LIKE' xxx%' or d.dept_full_name = 'xxxTrx read view will not see trx with id > = xxx, sees

< xxx......还有很多select语句省略 根据上面信息我们去数据库中查看了这些select语句，发现执行计划都是全表扫描。 首先数据库变成了只读，最后数据库crash了，crash输出的信息如下: ----------------------------END OF INNODB MONITOR OUTPUT============================InnoDB: ###### Diagnostic info printed to the standard error streamInnoDB: Error: semaphore wait has lasted >

600 secondsInnoDB: We intentionally crash the server, because it appears to be hung.7fa8f9580700 InnoDB: Assertion failure in thread 140363714529024 in file srv0srv.cc line 1754InnoDB: We intentionally generate a memory trap.InnoDB: Submit a detailed bug report to http://bugs.mysql.com.InnoDB: If you get repeated assertion failures or crashes, evenInnoDB: immediately after the mysqld startup, there may beInnoDB: corruption in the InnoDB tablespace. Please refer toInnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.htmlInnoDB: about forcing recovery.

InnoDB: Error: semaphore wait has lasted > 600 seconds prompt did not respond for 600 seconds. The database chose Crash to force restart.

Judging from the error message:

The update statement needs to add an X lock on the record, but must wait for the release of RW-Latch. Because there are a large number of select statements that are full table scans, the latch has not been released, and the update cannot compete with RW-latchInnodb 's diagnostics thread to check that the RW-Latch has waited for more than 600 seconds and has not returned, thinking that there is a serious problem with the system, so the Crash of the MySQL service is triggered. Second, further analysis

First of all, we need to supplement the concept of Latch: Latch is used to protect shared data in high-speed buffers in MySQL, for example:

When we execute select, the data is cached in buffer pool. It is necessary for multiple threads to access or modify this data concurrently, which is called Latch.

As we all know, the data to be accessed by the database must be stored in the cache first, and the cache is generally smaller than the disk space. The data cache uses the hash table to record whether the data page is in memory. Concurrency control in Oracle is fine: first, latch is added to the hash bucket, and the corresponding data is found and pin is added according to the hash bucket, and then the Latch is released. While MySQL is relatively not so finely controlled, the corresponding RW-Latch is clearly stated in errlog. The RW-Latch is created on line 1069 of buf0buf.cc.

RW-latch at 0x7fa949340740 created in file buf0buf.cc line 1069

The corresponding code is excerpted as follows:

Rw_lock_create (PFS_NOT_INSTRUMENTED, & block- > lock, SYNC_LEVEL_VARYING)

Track the source code and know that the Latch is created by MySQL when initializing innodb_buffer_pool in the database. The corresponding function call procedure:

Buf_pool_init_instance ()-> buf_chunk_init ()-> buf_block_init ()

It is precisely because this RW-Latch has been occupied for a long time that other threads have been unable to compete, which leads to this problem.

III. Suggestions for restoration

Most of these problems occur because SQL is not well written. A large number of full table scans on the table take up a lot of Latch. The solution is to prevent SQL from occupying latch for a long time:

Modify select query to avoid full table scan, prevent latch from being occupied for a long time and properly indexed, make select execute faster, and avoid increasing buffer pool instance appropriately with less data locked by select. Each buffer pool has its own independent Latch to avoid latch competition.

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.