MySQL 信号量semaphore 和 innodb_adaptive_hash_index

最近碰见案例，MySQL环境使用5.7.19~5.7.22版本 或 更低版本，经常会碰到以下信息：

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

大致意思就是等待信号量。

通过以下的一些信息了解一下MySQL里信号量是指什么？

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 916
a writer (thread id 140653057947392) has reserved it in mode exclusive
Mutex spin waits 1157217380, rounds 1783981614, OS waits 10610359
RW-shared spins 103830012, rounds 1982690277, OS waits 52051891
RW-excl spins 43730722, rounds 602114981, OS waits 3495769

如果有高并发的工作负载，SEMAPHORES记录了信号量信息,它包含了两种数据：事件计数器以及可选的当前等待线程的列表。

1）OS WAIT ARRAY INFO: reservation count 68581015, signal count 218437328 
#这行给出了关于操作系统等待数组的信息，它是一个插槽数组，innodb在数组里为信号量保留了一些插槽，操作系统用这些信号量给线程发送信号，使线程可以继续运行，以完成它们等着做的事情，这一行还显示出innodb使用了多少次操作系统的等待：
保留统计（reservation count）显示了innodb分配插槽的频度，
信号计数（signal count）衡量的是线程通过数组得到信号的频度，
操作系统的等待相对于空转等待（spin wait）。

2）–Thread 140653057947392 has waited at btr0pcur.c line 437 for 0.00 seconds the semaphore:
这部分显示的是当前正在等待互斥量的innodb线程，在这里可以看到有两个线程正在等待，每一个都是以–Thread <数字> has waited…开始，这一段内容在正常情况下应该是空的（即查看的时候没有这部分内容），除非服务器运行着高并发的工作负载，促使innodb采取让操作系统等待的措施，

3）计数器信息
Mutex spin waits 1157217380, rounds 1783981614, OS waits 10610359 #这行显示的是跟互斥量相关的几个计数器
RW-shared spins 103830012, rounds 1982690277, OS waits 52051891 #这行显示读写的共享锁的计数器
RW-excl spins 43730722, rounds 602114981, OS waits 3495769 #这行显示读写的排他锁的计数器

innodb有着一个多阶段等待的策略，首先，它会试着对锁进行空转等待，如果经历了一个预设的空转等待周期（设置innodb_sync_spin_loops配置变量命令）之后还没有成功，那就会退到更昂贵更复杂的等待数组中。

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

在这个线程被挂起之前，线程等待一个InnoDB互斥对象被释放的次数。

4）空转等待的成本相对较低，但是它们要不停地检查一个资源能否被锁定，这种方式会消耗CPU周期，因为当处理器在等待IO时，一般都有一些空闲的CPU周期可用，即使是没有空闲的CPU周期，空等也要比其他方式更加廉价一些。
然而，当另外一个线程能做一些事情的时候，空转等待也还会把CPU独占着。
空转等待的替代方案就是让操作系统做上下文切换，这样，当一个线程在等待时，另外一个线程就可以被运行，然后，通过等待数组里的信号量发出信号，唤醒那个沉睡的线程，通过信号量来发送信号是比较有效的，但是上下文切换就很昂贵，这很快就会积少成多，每秒钟几千次的切换会引发大量的系统开销。

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

经上述内容可以理解到因MySQL锁机制，其他事件需要进行等待处理。

解决方式

1.扩大内核参数：

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

#cat /proc/sys/kernel/sem 
250     32000   32      128 
说明： 
第一列，表示每个信号集中的最大信号量数目。 
第二列，表示系统范围内的最大信号量总数目。 
第三列，表示每个信号发生时的最大系统操作数目。 
第四列，表示系统范围内的最大信号集总数目。

2. MySQL关闭 adaptive hash index功能
   官方说明 和 bug 结合

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)

之后再也没有出现过，类似问题。
参考资料：
https://bugs.mysql.com/bug.php?id=50641
http://stackoverflow.com/questions/24860111/warning-a-long-semaphore-wait

adaptive hash index介绍

Innodb存储引擎会监控对表上二级索引的查找，如果发现某二级索引被频繁访问，二级索引成为热数据，建立哈希索引可以带来速度的提升。

从上述图上可以看出二级索引的哈希索引，在查询方面提升了性能。 要是语句里有回表操作，不如直接主键进行查询。所以在MySQL使用场景里使用主键是非常重要的。

相关的代码实现里：
storage\innobase\btr\btr0sea.cc
基本都是adaptive hash index添加，移动或删除已移动记录的哈希条实现。
特别是用到page，block 单位的RW_LOCK_X

adaptive hash index特性：

• hash自适应索引会占用innodb buffer pool；
• 自适应hash索引只适合搜索等值的查询，而对于其他查找类型，如范围查找，是不能使用的；
  如: where index_col=‘xxx’；
• 无法进行排序；
• MySQL自动管理，人为无法干预；

对于某些工作负载，哈希索引查找的加速大大超过监视索引查找和维护哈希索引结构的额外工作。在繁重的工作负载(如多个并发连接)下，对adaptive hash索引的访问有时会成为争用的来源。带有LIKE操作符和%通配符的查询也没有什么好处。

MySQL 5.6的架构变化使得它比以前的版本更适合禁用adaptive hash index特性。因为很难预先预测adaptive hash index特性是否适合特定的系统和工作负载，所以可以考虑启用和禁用它来运行基准测试。

MySQL 5.7，对自适应哈希索引特性进行了分区。每个索引都绑定到一个特定的分区，每个分区都由一个单独的锁存器保护。分区由innodb_adaptive_hash_index_parts变量控制。在早期版本中，自适应哈希索引特性由一个锁存器保护，在高工作负载下，这个锁存器可能成为争用点

默认情况下被划分为8个部分。最大设置为512

可以在SHOW ENGINE INNODB STATUS输出的信号量部分监视自适应哈希索引的使用和争用。

默认8个区，每个区包含69257 大小，这个内存开销还得记在innodb buffer pool size里。

总结

• 在某些负载下innodb_adaptive_hash_index并不适合打开，关闭innodb_adaptive_hash_index可以避免额外的维护开销。当然这取决于针对具体负载的性能测试。

• 如官网所说：
  如果有许多线程等待在btr0sea中创建很多线程在等待 RW-latch竞争。考虑增加自适应哈希索引分区的数量或禁用自适应哈希索引特性。

• 8.0多加了 skip-innodb-adaptive-hash_index进行默认关闭。

• 8.0.18版本多了hash join 两个都是hash ，是否有关联？看下下面解释。
  hash join只能在没有索引的字段上有效。
  Innodb_adaptive_hash_index二级索引上生效。

个人建议，部署初期直接关闭掉即可，在轻量级MySQL数据库来说，性能方面提升无法评估，又存在隐患。