MySQL5.7 慢查询+DDL操作堵塞查询
2024-09-05 10:27:24
数据库版本：
mysql> select @@version;
+------------+
| @@version |
+------------+
| 5.7.26-log |
+------------+
事务隔离级别：
mysql> show variables like '%iso%';
+-----------------------+----------------+
| Variable_name | Value |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
| tx_isolation | READ-COMMITTED |
+-----------------------+----------------+
会话一：关闭自动提交，锁定表中一行数据
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec) mysql> select * from sbtest1 where id=1 lock in share mode;
+----+------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+------+-------+-------+-------+-------+-------+-------+--------+--------+------+------+------+
| id | k | c | pad | name | name2 | name5 | name6 | name7 | name8 | name9 | name10 | name11 | c1 | c2 | c3 |
+----+------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+------+-------+-------+-------+-------+-------+-------+--------+--------+------+------+------+
| 1 | 5015 | 68487932199-96439406143-93774651418-41631865787-96406072701-20604855487-25459966574-28203206787-41238978918-19503783441 | 22195207048-70116052123-74140395089-76317954521-98694025897 | aa | bb | a | a | a | a | a | a | a | NULL | NULL | 0 |
+----+------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+------+-------+-------+-------+-------+-------+-------+--------+--------+------+------+------+
1 row in set (0.00 sec)
会话二：对该表做DDL操作
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> alter table sbtest1 add cc1 int;
会话三：显示事务在等待数据字典锁，但在innodb的锁信息表中并未给出锁堵塞信息
mysql> show processlist;
+-------+-----------------+--------------------+---------------+---------+---------+---------------------------------+---------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+-----------------+--------------------+---------------+---------+---------+---------------------------------+---------------------------------+
| 1 | event_scheduler | localhost | NULL | Daemon | 1549348 | Waiting on empty queue | NULL |
| 39327 | root | 10.200.21.13:39600 | canal_manager | Sleep | 884 | | NULL |
| 39436 | root | localhost | sysbench_db | Query | 51 | Waiting for table metadata lock | alter table sbtest1 add cc1 int |
| 39465 | root | localhost | NULL | Query | 0 | starting | show processlist |
| 39506 | root | localhost | sysbench_db | Query | 6 | Waiting for table metadata lock | select * from sbtest1 |
| 39511 | root | localhost | sysbench_db | Sleep | 137 | | NULL |
+-------+-----------------+--------------------+---------------+---------+---------+---------------------------------+---------------------------------+
6 rows in set (0.00 sec)
mysql> select * from INNODB_TRX;
+-----------------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
| trx_id | trx_state | trx_started | trx_requested_lock_id | trx_wait_started | trx_weight | trx_mysql_thread_id | trx_query | trx_operation_state | trx_tables_in_use | trx_tables_locked | trx_lock_structs | trx_lock_memory_bytes | trx_rows_locked | trx_rows_modified | trx_concurrency_tickets | trx_isolation_level | trx_unique_checks | trx_foreign_key_checks | trx_last_foreign_key_error | trx_adaptive_hash_latched | trx_adaptive_hash_timeout | trx_is_read_only | trx_autocommit_non_locking |
+-----------------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
| 421422174436976 | RUNNING | 2019-11-15 09:38:10 | NULL | NULL | 2 | 39511 | NULL | NULL | 0 | 1 | 2 | 1136 | 1 | 0 | 0 | READ COMMITTED | 1 | 1 | NULL | 0 | 0 | 0 | 0 |
+-----------------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
1 row in set (0.00 sec) mysql> select * from INNODB_locks;
Empty set, 1 warning (0.00 sec) mysql> select * from INNODB_LOCK_WAITS;
Empty set, 1 warning (0.00 sec)
mysql> show engine innodb status \G;
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2019-11-15 09:45:19 0x7f47c8d38700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 11 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 3581 srv_active, 0 srv_shutdown, 1542568 srv_idle
srv_master_thread log flush and writes: 1546144
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 30450
OS WAIT ARRAY INFO: signal count 19118
RW-shared spins 0, rounds 15075, OS waits 6862
RW-excl spins 0, rounds 433241, OS waits 12516
RW-sx spins 1169, rounds 35046, OS waits 873
Spin rounds per wait: 15075.00 RW-shared, 433241.00 RW-excl, 29.98 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-11-14 09:55:27 0x7f47c8a20700
*** (1) TRANSACTION:
TRANSACTION 8801, ACTIVE 1 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 2
MySQL thread id 16018, OS thread handle 139946286548736, query id 99910 10.138.61.12 root updating
DELETE FROM sbtest1 WHERE id=4999
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space not gap waiting
Record lock, heap no 73 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80001387; asc ;;
1: len 6; hex 00000000225b; asc "[;;
2: len 7; hex 2e000000201fdb; asc . ;;
3: len 4; hex 80001375; asc u;;
4: len 30; hex 36323536333834383835322d34313830333137393631382d383536313634; asc 62563848852-41803179618-856164; (total 120 bytes);
5: len 30; hex 33303032343434313836392d36363935353436363130382d323939353039; asc 30024441869-66955466108-299509; (total 60 bytes); *** (2) TRANSACTION:
TRANSACTION 8795, ACTIVE 1 sec starting index read, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 2
MySQL thread id 16017, OS thread handle 139946285467392, query id 99920 10.138.61.12 root updating
DELETE FROM sbtest1 WHERE id=4979
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 57 page no 100 n bits 144 index PRIMARY of table `sysbench_db`.`sbtest1` trx id 8795 lock_mode X locks rec but not gap
Record lock, heap no 73 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80001387; asc ;;
1: len 6; hex 00000000225b; asc "[;;
2: len 7; hex 2e000000201fdb; asc . ;;
3: len 4; hex 80001375; asc u;;
4: len 30; hex 36323536333834383835322d34313830333137393631382d383536313634; asc 62563848852-41803179618-856164; (total 120 bytes);
5: len 30; hex 33303032343434313836392d36363935353436363130382d323939353039; asc 30024441869-66955466108-299509; (total 60 bytes); *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 57 page no 100 n bits 144 index PRIMARY of table `sysbench_db`.`sbtest1` trx id 8795 lock_mode X locks rec but not gap waiting
Record lock, heap no 53 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80001373; asc s;;
1: len 6; hex 000000002261; asc "a;;
2: len 7; hex 310000001f2239; asc 1 "9;;
3: len 4; hex 800013aa; asc ;;
4: len 30; hex 31333735393532343036322d37383433393531343532342d383137393338; asc 13759524062-78439514524-817938; (total 120 bytes);
5: len 30; hex 34353737383233343435352d38303336313338353035392d383834323633; asc 45778234455-80361385059-884263; (total 60 bytes); *** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 2258851
Purge done for trx's n:o < 2258850 undo n:o < 0 state: running but idle
History list length 49
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421422174438800, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421422174437888, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421422174436064, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421422174435152, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (read thread)
I/O thread 7 state: waiting for completed aio requests (read thread)
I/O thread 8 state: waiting for completed aio requests (read thread)
I/O thread 9 state: waiting for completed aio requests (read thread)
I/O thread 10 state: waiting for completed aio requests (write thread)
I/O thread 11 state: waiting for completed aio requests (write thread)
I/O thread 12 state: waiting for completed aio requests (write thread)
I/O thread 13 state: waiting for completed aio requests (write thread)
I/O thread 14 state: waiting for completed aio requests (write thread)
I/O thread 15 state: waiting for completed aio requests (write thread)
I/O thread 16 state: waiting for completed aio requests (write thread)
I/O thread 17 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0, 0, 0, 0, 0] , aio writes: [0, 0, 0, 0, 0, 0, 0, 0] ,
ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
613084 OS file reads, 1249482 OS file writes, 643052 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 126, seg size 128, 20513 merges
merged operations:
insert 124338, delete mark 486, delete 0
discarded operations:
insert 35606, delete mark 359, delete 0
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 231 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 1625718660
Log flushed up to 1625718660
Pages flushed up to 1625718660
Last checkpoint at 1625718651
0 pending log flushes, 0 pending chkp writes
585541 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137428992
Dictionary memory allocated 264434
Buffer pool size 8192
Free buffers 4096
Database pages 3858
Old database pages 1404
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 51385, not young 13979456
0.00 youngs/s, 0.00 non-youngs/s
Pages read 612973, created 365347, written 614329
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 3858, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=2589, Main thread ID=139946660714240, state: sleeping
Number of rows inserted 8673974, updated 7522, deleted 3147, read 45773008
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================ 1 row in set (0.09 sec) ERROR:
No query specified
最新文章
- System.Linq.Dynamic的使用
- Linux:-杀进程的技巧
- 谁能完全搞懂Visual Studio的安装项？
- MVC _ aspx视图引擎登录及状态保持
- Android 设置VPN(pptp连接方式)
- Ubuntu与Ubuntu系统之间的mount挂载
- Android EditText小结
- Python的控制结构(转)
- RGB转MIPI CSI芯片方案TC358746XBG
- redis 简单安装使用
- Electron 发生错误 "Cannot find module app"的解决方案
- PAT A1111 Online Map （30 分）——最短路径，dijkstra
- Linux - 网络检测
- Python open 读写小栗子
- Resharper插件的使用
- 浅谈js设计模式 — 享元模式
- MVC confirm提示
- 编写高质量代码改善C#程序的157个建议——建议138：事件和委托变量使用动词或形容词短语命名
- VPS性能综合测试（6）：UnixBench跑分工具测试
- oc 阿拉伯数字转中文数字