MySQL semaphore semaphore and innodb_adaptive_hash_index

I have recently encountered a case where the MySQL environment uses version 5.7.19~5.7.22 or lower, and I often encounter the following information:

InnoDB: Warning: a long semaphore wait:   813 --Thread 139957495039744 has waited at btr0cur.cc line 545 for 241.00 seconds the semaphore:  814 X-lock (wait_ex) on RW-latch at 0x7f4a60043da8 created in file dict0dict.cc line 2341

The rough meaning is to wait for the semaphore.

Use the following information to understand what semaphore is in MySQL?

Where to record semaphore information in MySQL

mysql> SHOW ENGINE INNODB STATUS\G;----------SEMAPHORES----------OS WAIT ARRAY INFO: reservation count 68581015, signal count 218437328 --Thread 140653057947392 has waited at btr0pcur.c line 437 for 0.00 seconds the semaphore:S-lock on RW-latch at 0x7ff536c7d3c0 created in file buf0buf.c line 916a writer (thread id 140653057947392) has reserved it in mode exclusiveMutex spin waits 1157217380, rounds 1783981614, OS waits 10610359RW-shared spins 103830012, rounds 1982690277, OS waits 52051891RW-excl spins 43730722, rounds 602114981, OS waits 3495769

If there is a high concurrent workload, SEMAPHORES records the semaphore information, which contains two kinds of data: event counter and optional list of currently waiting threads.

1) OS WAIT ARRAY INFO: reservation count 68581015, signal count 218437328 #This  
line gives information about the operating system waiting array. It is an array of slots. InnoDB reserves some slots for the semaphore in the array. The operating system Use these semaphores to send signals to the threads so that the threads can continue to run to complete what they are waiting to do. This line also shows how many times the operating system waits for InnoDB: the
reservation count (reservation count) shows the allocation of InnoDB. The frequency of the slot, the
signal count (signal count) measures the frequency with which the thread gets the signal through the array, and
the wait of the operating system is relative to the idle wait (spin wait).

2) –Thread 140653057947392 has waited at btr0pcur.c line 437 for 0.00 seconds the semaphore:
This part shows the innodb thread that is currently waiting for the mutex. Here you can see that there are two threads waiting, each of which is Start with -Thread <number> has waited... This section of content should be empty under normal circumstances (that is, there is no such content when viewing it), unless the server is running a high concurrent workload, prompting innodb to take the operating system to wait Measures,

3) Counter information
Mutex spin waits 1157217380, rounds 1783981614, OS waits 10610359 #This line shows several counters related to mutex
RW-shared spins 103830012, rounds 1982690277, OS waits 52051891 #This line shows the shared read and write Lock counter
RW-excl spins 43730722, rounds 602114981, OS waits 3495769 #This line shows the counter of exclusive lock for reading and writing

Innodb has a multi-stage waiting strategy. First, it will try to wait for the lock idle. If it has not succeeded after a preset idle waiting period (setting the innodb_sync_spin_loops configuration variable command), it will fall back to more expensive More complicated waiting in the array.

mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_sync_spin_loops%';+------------------------+-------+| Variable_name          | Value |+------------------------+-------+| innodb_sync_spin_loops | 30    |+------------------------+-------+
image.png


The number of times that the thread waited for an InnoDB mutex object to be released before this thread was suspended.

4) The cost of idle waiting is relatively low, but they have to constantly check whether a resource can be locked. This method consumes CPU cycles, because when the processor is waiting for IO, there are generally some idle CPU cycles available , Even if there are no idle CPU cycles, idle waiting is cheaper than other methods.
However, when another thread can do something, idle waiting will also monopolize the CPU.
The alternative to idle waiting is to let the operating system do context switching, so that when a thread is waiting, another thread can be run, and then, by waiting for the semaphore in the array to send a signal, wake up the sleeping thread, and pass the signal It is more effective to send a signal by a certain amount, but context switching is very expensive, which will quickly accumulate, and thousands of switching per second will cause a lot of system overhead.

mysql> show engine innodb mutex;+--------+------------------------+---------+| Type   | Name                   | Status  |+--------+------------------------+---------+| InnoDB | rwlock: log0log.cc:846 | waits=3 |+--------+------------------------+---------+1 row in set (0.01 sec)

From the above content, it can be understood that due to the MySQL lock mechanism, other events need to be waited for processing.

Solution

1. Expand the kernel parameters:

[[email protected] bak]# cat /proc/sys/kernel/sem250   32000   32   128[[email protected] bak]# echo "kernel.sem=250 32000 100 128" >> /etc/sysctl.conf[[email protected] bak]# sysctl -p vm.max_map_count = 262144kernel.sem = 250 32000 100 128

#cat /proc/sys/kernel/sem  
250 32000 32 128  
Description: The  
first column indicates the maximum number of semaphores in each signal set.
The second column indicates the total number of maximum semaphores within the system.
The third column indicates the maximum number of system operations when each signal occurs.
The fourth column indicates the maximum total number of signals in the system.

  1. MySQL closes the adaptive hash index function.
    Official instructions and bugs are combined
mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_adaptive_hash_index';+----------------------------+-------+| Variable_name              | Value |+----------------------------+-------+| innodb_adaptive_hash_index | ON    |+----------------------------+-------+1 row in set (0.01 sec) mysql> SET GLOBAL innodb_adaptive_hash_index=OFF;Query OK, 0 rows affected (0.00 sec) mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_adaptive_hash_index';+----------------------------+-------+| Variable_name              | Value |+----------------------------+-------+| innodb_adaptive_hash_index | OFF   |+----------------------------+-------+1 row in set (0.01 sec)

There has never been a similar problem since.
Reference materials:
https://bugs.mysql.com/bug.php?id=50641
http://stackoverflow.com/questions/24860111/warning-a-long-semaphore-wait

Introduction to adaptive hash index

The Innodb storage engine monitors the lookup of the secondary index on the table. If it is found that a secondary index is frequently accessed, the secondary index becomes hot data, and the establishment of a hash index can bring speed improvements.

image.png

It can be seen from the above figure that the hash index of the secondary index improves the performance in terms of query. If there is a back-to-table operation in the statement, it is better to directly query the primary key. Therefore, it is very important to use the primary key in MySQL usage scenarios.

Related code implementation:
storage\innobase\btr\btr0sea.cc is
basically the implementation of the adaptive hash index to add, move or delete the hash bar of the moved record.
Especially use RW_LOCK_X in page and block units

image.png

Adaptive hash index features:

  • Hash adaptive index will occupy the innodb buffer pool;
  • Adaptive hash index is only suitable for searching equivalent queries, but for other search types, such as range search, it cannot be used;
    such as: where index_col='xxx';
  • Unable to sort;
  • MySQL is automatically managed and cannot be intervened by humans;

For some workloads, the acceleration of hash index lookup greatly exceeds the extra work of monitoring index lookup and maintaining the hash index structure. Under heavy workloads (such as multiple concurrent connections), access to the adaptive hash index can sometimes become a source of contention. The query with the LIKE operator and% wildcard is not good.

The architecture changes of MySQL 5.6 make it more suitable than previous versions to disable the adaptive hash index feature. Because it is difficult to predict in advance whether the adaptive hash index feature is suitable for a specific system and workload, you can consider enabling and disabling it to run benchmark tests.

MySQL 5.7 partitions the adaptive hash index feature. Each index is bound to a specific partition, and each partition is protected by a separate latch. The partition is controlled by the innodb_adaptive_hash_index_parts variable. In earlier versions, the adaptive hash index feature was protected by a latch. Under high workloads, this latch may become a point of contention

image.png


. It is divided into 8 parts by default. Maximum setting is 512

The use and contention of the adaptive hash index can be monitored in the semaphore part of the output of SHOW ENGINE INNODB STATUS.

image.png


There are 8 areas by default, and each area contains 69257 size. This memory overhead has to be recorded in the innodb buffer pool size.

to sum up

Under certain loads, innodb_adaptive_hash_index is not suitable for opening, and closing innodb_adaptive_hash_index can avoid additional maintenance overhead. Of course, this depends on the performance test for the specific load.

As stated on the official website:
If there are many threads waiting to be created in btr0sea, many threads are waiting for RW-latch competition. Consider increasing the number of adaptive hash index partitions or disabling the adaptive hash index feature.

8.0 adds skip-innodb-adaptive-hash_index to disable it by default.

Version 8.0.18 has more hash joins. Both are hashes. Is there any connection? Look at the explanation below.
Hash join can only be effective on fields that are not indexed.
Innodb_adaptive_hash_index takes effect on the secondary index.

Personal suggestion is to simply close it at the initial stage of deployment. In the case of a lightweight MySQL database, the performance improvement cannot be evaluated, and there are hidden dangers.