Latch (latch) in MySQL--easy-to-generate problems and cause analysis

Latch

What is latch:

locks are a key feature of database system differences and file systems. The lock mechanism is used to manage concurrent access to shared resources. the Innodb storage engine locks the table data at the row level, which is certainly a good idea. But Innodb also uses locks in multiple places, allowing concurrent access to a variety of different resources. For example, manipulating the LRU list of the buffer pool rollup , deleting, adding, moving elements in the LRU list, and in order to ensure consistency, there must be a lock involved, which is latch Lock.

the difference between latch and lock

Latch are generally referred to as latches (lightweight locks) because they require a very short time to lock. If it lasts for a long time, the performance of the application will be very poor. In the Innodb storage engine,latch can be divided into mutexes(mutexes) and Rw-lock (read-write lock).

The lock object is a transaction, which is used to lock objects in the database, such as tables, rows, and pages. And the general lock Object is released only after the transaction commits or rollback (the time at which different transaction isolation levels are freed may be different). In addition,lock, as in most databases, has a deadlock mechanism.

view latch with show engine InnoDB Mutex

The name column displays information about the latch and where the source code is located (number of lines).

categories of latch

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

Rw-latch: read/write lock

Latch 's understanding:

An example:

when we execute a select , the data is cached in the buffer pool , and multiple threads concurrently accessing or modifying the data necessarily require 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 disk space, and the data buffer uses a hash table to record whether the data page is in memory. the corresponding rw-latch in MySQL is clearly stated in ErrLog , the rw-latch is in buf0sea.cc of the 658 rows are created Rw-latch .

See errorlog for a sample of the pressure test:

According to the log we can parse the thread 140140355766016 to add an x lock to the record , but wait for the thread to 0x4c407b8 the thread's Rw-latch the release.

Latch Contention Process

1)a with x access linked list

2)b is queued for x to unlock the CPU, but the CPU finds you waiting, so The CPU kicks b out

3) The time of the chain is the time to find the data.

4 b know very a fast So, b Don't go in line, this is after go to spin cpu a Is it unlocked

5)b after a round, in the time period of Bspin,C came in, successive times of spin after, produced a OS waits

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

Memory Structure very small database requested from the operating system, does not occupy buffer pool, completely exclusive

The holding process of a mutex lock:　

A thread holds a data structure that wants a mutex to write a 1

b thread see the memory data structure there are numbers, then go to spin

Confirm Latch contention type:

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

the process of latch contention

There is a chain protection mechanism on the listLatch, small memory structure, this time there are read threadsaup to read the chain, this time this management becomesR, read locks, when data is found on the chain(Read), release the read lock as soon as you find it,bcome up also to read, at this time a look isR, read locks can be shared, she is also access to the chain read,Cup to modify the contents of the two blocks in the chain, a look isR,Rand theWis mutually exclusive, cannot be carried out at the same time, or

1, the active request to quit the CPU

2, empty occupy theCPUresource (Execute an empty code,Loop, take a look at it at intervalsaand thebhave you finished using(spin), but in the process becauseCThere is no waiting line, so there may be other threads in the waiting process to grab the chain, if executed many times still, it may beSleep, ExitCPU) Why the empty Occupy (afraid of the operating system to see her busy dragging him away), etc (because he knewaand thebtakes up resource time is short, is to traverse a chain of time very short).

the phenomenon of latch contention:

1,latch contention will be shown as CPU busy

2,latch contention not queued, wait a random time to come back to see

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 means that each time the parameter of the rotation is queried

OS waits: Indicates sleep, when suddenly growing faster, indicating that latch contention is more serious

number of rw-shared spin

number of rw-excl spin

reasons for the occurrence of latch contention

1, memory access is too frequent (keep looking)

2, thelist chain is too long (the chain hangs 10000 Fast, is held the probability too big)

so sometimes you increase the number of instance, cut the big pool into small pool, and let the list The chain becomes shorter

how to reduce latch contention:

If there is a latch contention is more serious

1. optimize SQL, reduce the amount of memory read - - The effect is more obvious

2. increase The number of instances

how to find the solution latch accurately

1.show Engine Innodb Mutex

2. See what type of latch

3. locating SQL

Latch (latch) in MySQL--easy-to-generate problems and cause analysis