|
依旧为简单记录，因为如果每个问题都详细的写，问题太多耗用的时间也长，因此把我认为有意思的case快速简单的记录一下。仅供参考。
本处感谢康壮/李端峰提供的案例和共同分析
首先我们来看一个提交小BUG
当前已经确认，官方定义为一个小BUGphp指针，查看慢查询可能需要注意一下。
一、几个奇怪state
这里的state就是我们show processlist出来看到的值，当前来看这个值有点类似Oracle的等待事件，但是实际上它是一个stage，也就是代码跑到某处时会置为一个合适的值，可以作为判定问题的一个初始化方向。但是这个值某些时候也会让人迷惑，比如5.7主从中的system lock，详细参考下文：
1.1 preparing和statistics状态的特例
先来看下面的state的值，我们可以看到：
我们可以发现里面有一个delete in语句处于preparing状态下面，其他两个select for update语句处于statistics状态下面，这个时候如果根据常规判定，preparing和statistics状态都处于语句执行计划生成阶段，语句还没有开始执行，如果按照这样去判断就出现了误判。
版本8.0.28，隔离级别RC，实际上在8.0.28触发这个问题已经很难了，需要设置优化选项，关闭半连接和物化，但是在8.0.19及一下环境很容易重现，因为DML使用半连接优化是8.0.21的特性，另外对于RC模式下面delete in select 加S锁的情况也有优化，会提前调用lock_rec_unlock解锁，老版本不会解锁。准备环境如下：
mysql>?select?version();
+-----------+
|?version()?|
+-----------+
|?8.0.28????|
+-----------+
1?row?in?set?(0.00?sec)
mysql>?show?variables?like?'%transaction_isolation%';
+-----------------------+----------------+
|?Variable_name?????????|?Value??????????|
+-----------------------+----------------+
|?transaction_isolation?|?READ-COMMITTED?|
+-----------------------+----------------+
1?row?in?set?(0.00?sec)
mysql>?create?table?testout(id?int?auto_increment?unique?key,name?varchar(20));
Query?OK,?0?rows?affected?(0.01?sec)
mysql>?insert?into?testout(name)?values('g');
Query?OK,?1?row?affected?(0.01?sec)
mysql>?insert?into?testout(name)?select?name?from?testout;
Query?OK,?1?row?affected?(0.00?sec)
Records:?1??Duplicates:?0??Warnings:?0
...
mysql>?insert?into?testout(name)?select?name?from?testout;
Query?OK,?1048576?rows?affected?(8.38?sec)
Records:?1048576??Duplicates:?0??Warnings:?0
mysql>?create?table?testin?like?testout;
Query?OK,?0?rows?affected?(0.01?sec)
mysql>?insert?into?testin?select?*?from?testout;
Query?OK,?2097152?rows?affected?(17.42?sec)
Records:?2097152??Duplicates:?0??Warnings:?0
mysql>??set??optimizer_switch='semijoin=off';
Query?OK,?0?rows?affected?(0.00?sec)
mysql>?set??optimizer_switch='materialization=off';
Query?OK,?0?rows?affected?(0.00?sec)
mysql>?desc?delete?from?testout?where?name?in?(?select?name?from?testin?);
+----+--------------------+---------+------------+----------------+---------------+------+---------+------+---------+----------+-------------------------------------------------+
|?id?|?select_type????????|?table???|?partitions?|?type???????????|?possible_keys?|?key??|?key_len?|?ref??|?rows????|?filtered?|?Extra???????????????????????????????????????????|
+----+--------------------+---------+------------+----------------+---------------+------+---------+------+---------+----------+-------------------------------------------------+
|??1?|?DELETE?????????????|?testout?|?NULL???????|?ALL????????????|?NULL??????????|?NULL?|?NULL????|?NULL?|?3602700?|???100.00?|?Using?where?????????????????????????????????????|
|??2?|?DEPENDENT?SUBQUERY?|?testin??|?NULL???????|?index_subquery?|?name??????????|?name?|?83??????|?func?|?8372870?|???100.00?|?Using?where;?Using?index;?Full?scan?on?NULL?key?|
+----+--------------------+---------+------------+----------------+---------------+------+---------+------+---------+----------+-------------------------------------------------+
2?rows?in?set,?1?warning?(0.00?sec)
接下来，我们跑如下流程，因为数据量不大，需要稍微快一点执行。
TRX1TRX2TRX3
begin
delete from testout where id not in ( select id from testin );(本语句执行10秒左右)
select * from testin where id=1 for update;（堵塞）
select * from testin where id=2 for update;（堵塞）
这样就出现了上面的情况，实际上delete not in 语句和select for update语句都是在执行状态如下：
实际上这里就是由于delete not in 对子查询的数据加了S锁，然后堵塞了select for update语句，就是row lock 堵塞，但是这里的状态有一定的迷惑性，常规的堵塞应该是updating/insert/executing/sending data 等，需要特别注意。
1.2 AHI维护的特例
当进行DDL的时候经常会触发AHI维护，如果本表中的数据大量的生成AHI，则可能导致DDL异常缓慢，AHI本质上是指向行记录的HASH结构，包含如下：
在这个简图中，AHI查找表被根据参数innodb_adaptive_hash_index_parts参数分为了0-7 8个子表定义为hash_table_t,每个hash_table_t子表包含一个hash结构其中hash_cell_t就是一个hash桶（cell）。每个hash桶（cell）包含了一个ha_node_t指针的链表，每个元素都是一个ha_node_t指针类型，代表一行数据，这就是在show engine中看到的（Hash table size 1106407）中的size的单位。每行数据包含了3个元素，其中key为fold，value为rec_t* data，fold为每行数据算出来的hash值，data为实际指向记录在innodb buffer中的地址，另外一个元素ha_node_t* next则为链表结构的指针。除此之外在每个子表hash_table_t中还包含了n_cells和heap内存信息，其中n_cells为hash桶（cell）的个数，heap内存信息为ha_node_t结构的实际存储的位置，分配内存的时候以buffer为单位每个buffer 16K，也就是我们在show engine中看到的（node heap has 0 buffer(s)）中的buffer。当构建某行数据的AHI的信息的时候，首先通过这行数据所在的space_id和index_no等信息算一个hash值，然后根据innodb_adaptive_hash_index_parts参数取余，得到hash_table_t子表的位置。然后通过本行数据每个字段的值计算出一个hash值叫做fold，继而根据fold和n_cells取余计算出在哪个hash桶（cell）里面，最后加入到hash桶（cell）的链表中。
实际上每个hash结构元素就是24个字节（1个long类型，2个指针）。当DDL进行的时候会大量的清理本表相关的AHI，如下：
伪代码如下：
循环每个分区调用函数
->btr_drop_ahi_for_table
??循环表（或者分区）中的每个索引，如果索引都没有用到AHI，
则退出
??循环innodb?buffer中的每个实例，根据LRU链表循环每个page
如果page建立了AHI信息，且是要删除表（或者分区）的相关索引
??则放入drop?vector容器中
如果page没有建立AHI信息?
则跳过
如果drop?verctor容器中填满1000个page
则清理一次，循环每个page，调用函数
->btr_search_drop_page_hash_index
??计算page所在AHI结构的slot信息，以便找到对应的hash_table_t结构
??循环page中所有的行
????循环行中访问到的索引字段（访问模式），计算出fold信息填入到fold[]数组中
????本循环中会通过函数rec_get_offsets进行字段偏移量的获取，为耗用CPU的函数
??????循环fold[]数组，一个fold代表一行数据,调用函数
???????->ha_remove_all_nodes_to_page,为耗用CPU的函数
?????????->ha_chain_get_fist
???????????根据fold信息找到hash结构的cell
?????????循环本cell中的链表信息
???????????如果行的地址在本要删除的page上，调用函数
???????????->ha_delete_hash_node，为消耗CPU的函数
?????????????进行链表和hash结构的维护
每次处理完1000个page后，yeild线程主动放弃CPU，避免长期占用CPU，醒来后继续处理
这种时间复杂度极高。
这里描述2个特殊的案例，
image.png
这里我们看到状态是committing alter table to storage engine和waiting for handler commit，实际上它们都是在DDL快要结束的时候进行的，这也是特殊的例子。
1.3 关于本类问题的方法论
这类问题，所幸的是语句都在执行，那么他们耗用的CPU很高比如：
那么我们可以通过抓取perf信息来分析，对于paratition操作的AHI维护的火焰图如下：
实际上这已经很明显了，不过多描述。
二、主从延迟瞬间跳动问题
这个问题其实很简单，大概为有1主2从，其中1个从库的延迟，一下涨到1W多秒然后又恢复，循环往复，而另外一个从库确没什么问题。那么简单分析，既然另外一个从库没有问题，且分析postition和gtid后发现没什么延迟，那么最大的可能就是这个问题从库的时间做过修改，因为延迟的计算很简单如下：
这个也算老生常谈吧，因为主从服务器之间的差是在启动从库的时候记录到内存的，以后就不会更改，如果修改了服务器时间它是不知道的。
而延迟降为0，那么说明应用完了嘛，因为主从延迟的伪代码如下：
一旦没有event，直接print 0。当然这部分也可以参考我的书籍。
最后，世界杯冷门真多啊，绝代双骄要谢幕了啊。
以上。。。
（编辑：好传媒网）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
