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

Learn from the great god's http://mp.weixin.qq.com/s/hXtCzSnlVfo9Cq92538ipw and sort out some ideas by yourself.

1.0top looks at cpu consumption and finds that sys is much higher than usr, which is very abnormal.

1.1 use pstack to see the call stack of all MySQL threads:

InnoDB thread synchronization mechanism

We know that Linux thread synchronization has a variety of synchronization mechanisms, such as Mutex,spin lock, condition variables, rw lock and so on. InnoDB does not directly use the synchronization mechanism of the system, but defines its own mutually exclusive data structure kernel_mutex, which combines the system's spin lock,mutex with condition variables.

As shown in the figure, the important structural members of the kernel_mutexmutex object are os_event and lock_word.

Kernel_mutex mainly involves the mutex_create,mutex_enter,mutex_exit function, which dynamically allocates and frees memory using malloc () and free () calls of glibc, respectively.

Encapsulating mutex and condition variables, the green box area in the figure sends asynchronous signals between MySQL threads to synchronize mainly through the os_mutex (pthread_mutex_t encapsulating os) and cond_var (pthread_cond_t encapsulating os) member objects in the os_event_struct structure. When InnoDB is acquiring the lock, first try to spin for a period of time, if the threshold of innodb_sync_spin_loops is exceeded, it will wait on os_event_struct- > cond_var through the function os_event_wait_low (). As shown in the figure, when a thread releases the lock, it attempts to send a broadcast through os_cond_broadcast to wake up all threads waiting for the os_event_struct- > cond_var condition variable. After being awakened, these threads continue to compete for os_event_struct- > os_mutex

Spin lock, the yellow box area in the picture is spin wait through lock_word. The thread determines this value when it wants to scramble for the lock, and continues to spin and wait if lock_word is 1. If you find that another thread has released the lock and the value is changed to 0, change it to 1 through test_and_set, and "tell" the other thread that the lock is held.

InnoDB is designed to slow down the speed at which a thread is suspended and enters the os wait, because every time it enters the os wait and wakes up or wakes up, there is a context switch. But it can only be delayed, can not stop, if the continuous deterioration of the environment, and finally will still enter the os waiting queue, there will be a large number of context switching. But there are two problems:

Kernel_mutex is a global lock, protection transaction, buffer pool, lock, and most of the objects implemented by the InnoDB storage engine. When MySQL suddenly has a large number of visits, and once the concurrency is very high, the mutex conflict is very intense, and the critical area becomes very large, and a lot of cpu time is wasted idling. So this also explains why sys cpu is heavily consumed in optional idling.

And when the concurrency is high, malloc () will be called frequently to apply for memory, while the malloc () of glibc itself will frequently call the system mutex_lock () and unlock (). In the scenario of multi-thread and high concurrency and insufficient resources, it will also cause serious competition among all kinds of mutex in the system. A large number of threads are suspended waiting for os pthread_cond_broadcast broadcasts to wake up, followed by a large number of context switches

Through dstat, we can see that cpu has nearly 20W context switches per second. To sum up, the high sys cpu load is mainly as follows:

(1) cpu kernel state spin, a large number of threads cpu kernel state spin wait

(2) system context switching, which is divided into:

If spin still fails, it finally enters os wait and calls pthread_cond_wait to be awakened when the condition is met.

Malloc () frequently adds or subtracts os mutex, and the system memory is tight.

1.3 continue to observe the use of memory allocation.

Sar-B looks at memory page recycling, where pgscank/s, corresponding to kswapd, is recycled directly by the MySQL thread corresponding to pgscand/s

Pgscand/s is not 0, which means that memory resources are tight, and MySQL reclaims memory directly.

However, the free command shows that the memory free is not used up. After some investigation, it was found that numa was responsible for using the numactl-- hardware command to see:

Available: 2 nodes (0-1) node 0 cpus: 0 12 3node 0 size: 32733 MBnode 0 free: 1900 MBnode 1 cpus: 4 56 7node 1 size: 32768 MBnode 1 free: 20 MBnode distances:node 0 10: 10 20 1: 20 10

Although the overall free of memory is not used up, under the default memory allocation mechanism of numa, the use of memory is seriously uneven and node0 is still very sufficient, but node1 is almost used up.

You can also use strace to diagnose!

Https://huoding.com/2015/10/16/474

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.