Latch (latch) in MySQL-analysis of problems and causes, and mysqllatch

Latch (latch) in MySQL-analysis of problems and causes, and mysqllatch

Latch

What is latch:

Locks are a key feature of the differences between database systems and file systems. The lock mechanism is used to manage concurrent access to shared resources. The Innodb Storage engine locks table data at the row level, which is certainly good. However, Innodb also uses locks in multiple locations to allow concurrent access to multiple resources. For example, to delete, add, and move elements in the LRU list in the list of Operation buffer pools, lock intervention is required to ensure consistency. This is the latch Lock.

Latch and lock

Latch is generally referred to as a latch (lightweight lock) because it requires a very short lock time. If the duration is long, the application performance will be very poor. In the Innodb Storage engine, latch can be divided into mutex and RW-Lock ).

The lock object is a transaction used to lock objects in the database, such as tables, rows, and pages. Generally, lock objects are released only after the transaction commit or rollback (the release time may vary depending on the transaction isolation level ). In addition, locks, as in most databases, have a deadlock mechanism.

View latch with show engine innodb mutex

 

The name column displays latch information and the location of the source code (number of rows ).

Latch Classification

Divided into: mutex; sometimes some resources need to be shared and concurrent, but not frequently divided, so apply for a mutex from the operating system, mutex is exclusive.

RW-LATCH: read/write lock

Latch understanding:

Example:

When we execute the select statement, the data is cached in the buffer pool. concurrent access or modification to the data by multiple threads requires a concurrency control mechanism, which is latch.

The data to be accessed by the database must first exist in the cache, And the cache is generally smaller than the disk space. The data buffer uses the hash table to record whether the data page is in the memory. The RW-Latch corresponding to MySQL is clearly stated in errlog. This RW-Latch is the RW-Latch created in row 658 of buf0sea. cc.

 

See the errorlog pressure test example:

 

According to the log, we can analyze that thread 140140355766016 requires an x lock for the record, but waits for the release of RW-Latch of thread 0x4c407b8.

 

Latch contention Process

1) a accesses the linked list with x

2) B waits in queue for x to unlock the cpu, but the cpu finds that you are waiting, so the cpu kicks B out

3) The time of the chain is the time for data searching.

4) B knows that a is fast, so B does not go to the queue. This is to go to spin, that is, idling the cpu, and then check the memory data structure. Is a unlocked?

5) After B turns around, c comes in during the bspin time period. After several consecutive spin times, the OS waits is generated.

6) The operating system kicks B out of the cpu.

Latch Lock features:

• 1. Do not queue
• 2. spin
• 3. OS waits
• 4. cpu busy

Mutex:

A database with a small memory structure applied by the operating system does not occupy the buffer pool.

Mutex lock Holding Process:　

Thread a writes a 1 string in the mutex Memory Data Structure

If the data structure in the memory has a number in line B, go to spin.

Confirm latch contention type:

(This is the 173 statement in the source code) obtained by errorlog

 

Latch contention Process

The linked list has a chain protection mechanism latch, with a small memory structure. At this time, there is a read thread a to read the chain. At this time, this management will become r, read lock, when data is found on the chain (read), The read lock is released as soon as it is found, and B also needs to read it. At this time, it is r, and the read lock can be shared, she also accesses and reads the chain. c will modify the content of the two blocks in the chain. r and w are mutually exclusive and cannot be performed at the same time.

1. voluntarily ask to exit the cpu

2. Empty occupies cpu resources (execute an empty code, loop, and check whether a and B are used up (spin) after a while, but in this process, c is not waiting in queue, so there may be other threads occupying the chain while waiting. If the execution is still like this many times, it may sleep and quit the cpu) why is it empty? (afraid that the operating system will forcibly drag him when she is idle). (because he knows that a and B are occupying resources for a short time, is to traverse a chain for a very short time ).

Latch contention:

1. latch contention may result in cpu busy

2. There is no queue for latch contention. Wait for a random period of time to come back and have a look.

Monitoring metrics

----------

SEMAPHORES

----------

OS WAIT ARRAY INFO: reservation count 2

OS WAIT ARRAY INFO: signal count 2

RW-shared spins 0, rounds 4, OS waits 2

RW-excl spins 0, rounds 0, OS waits 0

RW-sx spins 0, rounds 0, OS waits 0

Spin rounds per wait: 4.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx

Rounds indicates the parameters for each query

OS waits: sleep. When the sudden increase is fast, it indicates that latch is competing for more resources.

Rw-shared spin count

Rw-excl spin count

Reasons for latch contention

1. Memory Access is too frequent (keep searching)

2. The list chain is too long (the chain is up to 10000 fast, and the probability of being held is too high)

So sometimes the number of instances is increased, and the large pool is cut into small pools to shorten the list chain.

How to Reduce latch contention:

If latch contention is serious

1. Optimize SQL statements to reduce the number of read operations in the memory. The effect is obvious.

2. Increase the number of instances

How to accurately find and solve latch

1. show engine Innodb mutex

2. Check what type of latch

3. Locate SQL