[待回复] 官方MySQL8.0.16 报错A long semaphore wait，innodb主动crash

783 2 2025-3-13 14:07

本帖最后由 gavinlee 于 2025-3-13 14:32 编辑

日志：

--Thread 140467929577216 has waited at log0chkp.cc line 639 for 655.00 seconds the semaphore:
Mutex at 0x7fc66c4b3448, Mutex LOG_CHECKPOINTER created log0log.cc:521, lock var 1

--Thread 140489639945984 has waited at log0chkp.cc line 639 for 655.00 seconds the semaphore:
Mutex at 0x7fc66c4b3448, Mutex LOG_CHECKPOINTER created log0log.cc:521, lock var 1

--Thread 140468507637504 has waited at log0chkp.cc line 396 for 0.00 seconds the semaphore:
Mutex at 0x7fc66c4b3240, Mutex LOG_WRITER created log0log.cc:523, lock var 1

--Thread 140467922532096 has waited at log0chkp.cc line 639 for 655.00 seconds the semaphore:
Mutex at 0x7fc66c4b3448, Mutex LOG_CHECKPOINTER created log0log.cc:521, lock var 1

--Thread 140490168911616 has waited at log0chkp.cc line 639 for 655.00 seconds the semaphore:
Mutex at 0x7fc66c4b3448, Mutex LOG_CHECKPOINTER created log0log.cc:521, lock var 1

--Thread 140468199474944 has waited at log0chkp.cc line 639 for 655.00 seconds the semaphore:
Mutex at 0x7fc66c4b3448, Mutex LOG_CHECKPOINTER created log0log.cc:521, lock var 1

--Thread 140468186498816 has waited at log0chkp.cc line 639 for 655.00 seconds the semaphore:
Mutex at 0x7fc66c4b3448, Mutex LOG_CHECKPOINTER created log0log.cc:521, lock var 1

--Thread 140489596573440 has waited at log0chkp.cc line 639 for 655.00 seconds the semaphore:
Mutex at 0x7fc66c4b3448, Mutex LOG_CHECKPOINTER created log0log.cc:521, lock var 1

....

2025-03-12T01:12:41.657704+08:00 0 [ERROR] [MY-012872] [InnoDB] Semaphore wait has lasted > 37520568 seconds. We intentionally crash the server because it appears to be hung.[FATAL] Semaphore wait has lasted > 600 seconds. We intentionally crash the server because it appears to be hung.
2025-03-12T01:12:41.657773+08:00 0 [ERROR] [MY-013183] [InnoDB] Assertion failure: ut0ut.cc:629 thread 140489655318272
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/ ... nnodb-recovery.html
InnoDB: about forcing recovery.
17:12:41 UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
Thread pointer: 0x0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0 thread_stack 0x46000
/usr/local/mysql-8.0.16-linux-glibc2.12-x86_64/bin/mysqld(my_print_stacktrace(unsigned char*, unsigned long)+0x2e) [0x1d563fe]
/usr/local/mysql-8.0.16-linux-glibc2.12-x86_64/bin/mysqld(handle_fatal_signal+0x323) [0xee86f3]
/lib64/libpthread.so.0(+0xf630) [0x7fc686d7f630]
/lib64/libc.so.6(gsignal+0x37) [0x7fc6850bd387]
/lib64/libc.so.6(abort+0x148) [0x7fc6850bea78]
/usr/local/mysql-8.0.16-linux-glibc2.12-x86_64/bin/mysqld(ut_dbg_assertion_failed(char const*, char const*, unsigned long)+0x2b6) [0x1fdf586]
/usr/local/mysql-8.0.16-linux-glibc2.12-x86_64/bin/mysqld(ib::fatal::~fatal()+0x11a) [0x1fe0b3a]
/usr/local/mysql-8.0.16-linux-glibc2.12-x86_64/bin/mysqld(srv_error_monitor_thread()+0x8b6) [0x1f893d6]
/usr/local/mysql-8.0.16-linux-glibc2.12-x86_64/bin/mysqld(std::thread::_State_impl<std::thread::_Invoker<std::tuple<Runnable, void (*)()> > >::_M_run()+0x63) [0x1e0fcf3]
/usr/local/mysql-8.0.16-linux-glibc2.12-x86_64/bin/mysqld() [0x23c4caf]
/lib64/libpthread.so.0(+0x7ea5) [0x7fc686d77ea5]
/lib64/libc.so.6(clone+0x6d) [0x7fc685185b0d]
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

业务夜间大量的更新操作。初步看是checkpoint跟不上，redolog空间不够，然后主动crash掉了。

下面是redo相关配置

#### redo log #####
innodb_log_file_size = 4G
innodb_log_buffer_size = 16M       # 16777216
innodb_log_group_home_dir = /data/mysql/3306/redolog
innodb_flush_log_at_trx_commit = 2 # 建议1
# innodb_max_dirty_pages_pct=70 原来是默认值90 现已改成70

######## innodb ########
innodb_page_size = 16384
innodb_buffer_pool_size = 64G # 建议配置为物理内存的一半。另需遵循innodb_buffer_pool_size = (innodb_buffer_pool_chunk_size(default 128M) * innodb_buffer_pool_instances) * N.
innodb_buffer_pool_instances = 16 # 当前N=2。 64*1024/128/16=32
innodb_buffer_pool_dump_pct = 40 # default 25
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_lru_scan_depth = 4096
innodb_lock_wait_timeout = 5
innodb_io_capacity = 10000
innodb_io_capacity_max = 20000
innodb_file_per_table = 1
innodb_flush_method = O_DIRECT
innodb_flush_neighbors = 0  ##on ssd
innodb_purge_threads = 4
# innodb_large_prefix = 1 # 删除 8.0 已移除
innodb_thread_concurrency = 64
innodb_print_all_deadlocks = 1
innodb_print_ddl_logs = 1
innodb_strict_mode = 1
innodb_sort_buffer_size = 67108864
innodb_doublewrite = 0             # slave can close double write , ssd maybe ?
innodb_page_cleaners = 4
innodb_purge_rseg_truncate_frequency = 128
innodb_adaptive_hash_index = 0 # 新增 8.0新增默认开启。对高频等值查询有优化，建议关闭。
innodb_stats_persistent_sample_pages = 500

# innodb status

OS WAIT ARRAY INFO: signal count 3413880
RW-shared spins 3309372, rounds 3327325, OS waits 6550
RW-excl spins 112614, rounds 296181, OS waits 2803
RW-sx spins 4760, rounds 33219, OS waits 241
Spin rounds per wait: 1.01 RW-shared, 2.63 RW-excl, 6.98 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 20941991844
Purge done for trx's n

< 20937489869 undo n

< 0 state: running
History list length 2250704
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421965276710576, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421965276741936, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421965276775984, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421965276725808, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421965276751792, not started
0 lock struct(s), heap size 1136, 0 row lock(s)

2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 80174, OS thread handle 140467955070720, query id 1333464566 *.*.*.* db1 updating
...
---TRANSACTION 20937489870, ACTIVE 1185 sec updating or deleting, thread declared inside InnoDB 4999
mysql tables in use 1, locked 1
2 lock struct(s), heap size 1136, 1 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 (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 18446744073709550316
88317620 OS file reads, 2232116937 OS file writes, 20615652 OS fsyncs
0 pending preads, 1 pending pwrites
21.10 reads/s, 16384 avg bytes/read, 9291.59 writes/s, 14.30 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 56027, seg size 56029, 2753782 merges
merged operations:
insert 2693670, delete mark 184237, delete 15397
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 8850487, node heap has 0 buffer(s)
Hash table size 8850487, node heap has 0 buffer(s)
Hash table size 8850487, node heap has 0 buffer(s)
Hash table size 8850487, node heap has 0 buffer(s)
Hash table size 8850487, node heap has 0 buffer(s)
Hash table size 8850487, node heap has 0 buffer(s)
Hash table size 8850487, node heap has 0 buffer(s)
Hash table size 8850487, node heap has 0 buffer(s)
0.00 hash searches/s, 3739.86 non-hash searches/s
---
LOG
---
Log sequence number       54513294701071
Log buffer assigned up to 54513294701071
Log buffer completed up to 54513294701071
Log written up to          54513294701071
Log flushed up to          54513294093807
Added dirty pages up to    54513294692043
Pages flushed up to       54511517453119
Last checkpoint at          54511248724426
2188835533 log i/o's done, 9244.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 35165044736
Dictionary memory allocated 10719805
Buffer pool size 2097152
Free buffers    65524
Database pages    2031628
Old database pages 749638
Modified db pages  74828
Pending reads    0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 12679679, not young 715930634
9.05 youngs/s, 56.80 non-youngs/s
Pages read 88380641, created 354030, written 42851826
21.10 reads/s, 36.05 creates/s, 48.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 2 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 2031628, unzip_LRU len: 0
I/O sum[1738560]:cur[12224], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size 131072
Free buffers    4094
Database pages    126978
Old database pages 46854
Modified db pages  5137
Pending reads    0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 791441, not young 44724070
0.45 youngs/s, 4.05 non-youngs/s
Pages read 5510746, created 19136, written 4332994
1.45 reads/s, 0.00 creates/s, 3.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 1 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 126978, unzip_LRU len: 0
I/O sum[108660]:cur[764], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size 131072
Free buffers    4096
Database pages    126976
Old database pages 46852
Modified db pages  4720
Pending reads    0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 801942, not young 45155398
0.75 youngs/s, 4.30 non-youngs/s
Pages read 5567121, created 18759, written 2623561
1.70 reads/s, 0.00 creates/s, 3.00 writes/s
Buffer pool hit rate 997 / 1000, young-making rate 1 / 1000 not 7 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 126976, unzip_LRU len: 0
I/O sum[108660]:cur[764], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size 131072
Free buffers    4095
Database pages    126977
Old database pages 46853
Modified db pages  4676
Pending reads    0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 815293, not young 44916945
1.05 youngs/s, 3.80 non-youngs/s
Pages read 5518420, created 71000, written 2505286
1.15 reads/s, 0.00 creates/s, 3.00 writes/s
Buffer pool hit rate 998 / 1000, young-making rate 2 / 1000 not 9 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 126977, unzip_LRU len: 0
I/O sum[108660]:cur[764], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size 131072
Free buffers    4096
Database pages    126976
Old database pages 46852
Modified db pages  4602
Pending reads    0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 798809, not young 45091318
0.65 youngs/s, 3.45 non-youngs/s
Pages read 5548868, created 18528, written 2616464
1.35 reads/s, 0.00 creates/s, 3.00 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 2 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 126976, unzip_LRU len: 0
I/O sum[108660]:cur[764], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size 131072
Free buffers    4096
Database pages    126976
Old database pages 46852
Modified db pages  4898
Pending reads    0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 787525, not young 46516437
0.50 youngs/s, 1.85 non-youngs/s
Pages read 5534304, created 19838, written 3818197
0.80 reads/s, 0.55 creates/s, 3.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 126976, unzip_LRU len: 0
I/O sum[108660]:cur[764], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size 131072
Free buffers    4096
Database pages    126976
Old database pages 46852
Modified db pages  4726
Pending reads    0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 792010, not young 44598627
0.40 youngs/s, 3.45 non-youngs/s
Pages read 5541334, created 17932, written 2249054
1.30 reads/s, 4.15 creates/s, 3.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 1 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 126976, unzip_LRU len: 0
I/O sum[108660]:cur[764], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size 131072
Free buffers    4096
Database pages    126976
Old database pages 46852
Modified db pages  4588
Pending reads    0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 786260, not young 44671556
0.20 youngs/s, 3.15 non-youngs/s
Pages read 5519342, created 17990, written 1906511
1.15 reads/s, 6.40 creates/s, 3.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 1 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 126976, unzip_LRU len: 0
I/O sum[108660]:cur[764], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size 131072
Free buffers    4096
Database pages    126976
Old database pages 46852
Modified db pages  4239
Pending reads    0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 783940, not young 43067656
0.50 youngs/s, 3.80 non-youngs/s
Pages read 5507751, created 18816, written 2552624
1.45 reads/s, 6.40 creates/s, 3.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 126976, unzip_LRU len: 0
I/O sum[108660]:cur[764], unzip sum[0]:cur[0]
---BUFFER POOL 8
Buffer pool size 131072
Free buffers    4096
Database pages    126976
Old database pages 46852
Modified db pages  4536
Pending reads    0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 788727, not young 44945988
0.50 youngs/s, 2.50 non-youngs/s
Pages read 5476701, created 20220, written 2610705
1.05 reads/s, 6.40 creates/s, 3.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 1 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 126976, unzip_LRU len: 0
I/O sum[108660]:cur[764], unzip sum[0]:cur[0]
---BUFFER POOL 9
Buffer pool size 131072
Free buffers    4096
Database pages    126976
Old database pages 46852
Modified db pages  4737
Pending reads    0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 789871, not young 43072688
0.50 youngs/s, 3.30 non-youngs/s
Pages read 5525778, created 19694, written 2248292
1.30 reads/s, 6.40 creates/s, 3.00 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 2 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 126976, unzip_LRU len: 0
I/O sum[108660]:cur[764], unzip sum[0]:cur[0]
---BUFFER POOL 10
Buffer pool size 131072
Free buffers    4093
Database pages    126979
Old database pages 46853
Modified db pages  4537
Pending reads    0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 787433, not young 43847743
0.65 youngs/s, 3.30 non-youngs/s
Pages read 5550524, created 17840, written 1743323
1.40 reads/s, 4.50 creates/s, 3.00 writes/s
Buffer pool hit rate 998 / 1000, young-making rate 1 / 1000 not 5 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 126979, unzip_LRU len: 0
I/O sum[108660]:cur[764], unzip sum[0]:cur[0]
---BUFFER POOL 11
Buffer pool size 131072
Free buffers    4091
Database pages    126981
Old database pages 46853
Modified db pages  4433
Pending reads    0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 795490, not young 44020899
0.35 youngs/s, 3.55 non-youngs/s
Pages read 5460703, created 20174, written 2049816
1.25 reads/s, 1.20 creates/s, 3.00 writes/s
Buffer pool hit rate 998 / 1000, young-making rate 0 / 1000 not 6 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 126981, unzip_LRU len: 0
I/O sum[108660]:cur[764], unzip sum[0]:cur[0]
---BUFFER POOL 12
Buffer pool size 131072
Free buffers    4096
Database pages    126976
Old database pages 46852
Modified db pages  4784
Pending reads    0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 792198, not young 47237667
0.60 youngs/s, 3.35 non-youngs/s
Pages read 5511091, created 18183, written 1827772
1.35 reads/s, 0.00 creates/s, 3.00 writes/s
Buffer pool hit rate 997 / 1000, young-making rate 1 / 1000 not 9 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 126976, unzip_LRU len: 0
I/O sum[108660]:cur[764], unzip sum[0]:cur[0]
---BUFFER POOL 13
Buffer pool size 131072
Free buffers    4096
Database pages    126976
Old database pages 46852
Modified db pages  4661
Pending reads    0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 788974, not young 43808210
0.45 youngs/s, 5.60 non-youngs/s
Pages read 5544798, created 19755, written 2206061
1.60 reads/s, 0.00 creates/s, 3.00 writes/s
Buffer pool hit rate 996 / 1000, young-making rate 1 / 1000 not 14 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 126976, unzip_LRU len: 0
I/O sum[108660]:cur[764], unzip sum[0]:cur[0]
---BUFFER POOL 14
Buffer pool size 131072
Free buffers    4096
Database pages    126976
Old database pages 46852
Modified db pages  4622
Pending reads    0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 787427, not young 46611666
0.85 youngs/s, 4.10 non-youngs/s
Pages read 5553891, created 17807, written 3372207
1.30 reads/s, 0.05 creates/s, 3.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 126976, unzip_LRU len: 0
I/O sum[108660]:cur[764], unzip sum[0]:cur[0]
---BUFFER POOL 15
Buffer pool size 131072
Free buffers    4095
Database pages    126977
Old database pages 46853
Modified db pages  4932
Pending reads    0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 792339, not young 43643766
0.65 youngs/s, 3.25 non-youngs/s
Pages read 5509269, created 18358, written 4188959
1.50 reads/s, 0.00 creates/s, 3.00 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 2 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 126977, unzip_LRU len: 0
I/O sum[108660]:cur[764], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
39 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=7752, Main thread ID=140468465673984 , state=sleeping
Number of rows inserted 7530566, updated 570006799, deleted 39044, read 221031031864
1.70 inserts/s, 1834.21 updates/s, 0.00 deletes/s, 3695.92 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

请帮忙看看是否哪里配置不正确导致无法及时checkpint

全部回复(2)

只看楼主

yejr 2025-3-13 14:52:24

History list length 2250704
===
又是有大量待purge的undo，和你上次发生的问题也类似，抓紧做好监控提前介入吧

回复采纳

earl86 2025-3-13 17:45:26

trx_sql="select it.*, p.*, (unix_timestamp(now())-unix_timestamp(trx_started)) as trx_runing_time from information_schema.INNODB_TRX it, information_schema.processlist p where it.trx_mysql_thread_id=p.ID and p.COMMAND='Sleep' and p.TIME>60 having (unix_timestamp(now())-unix_timestamp(it.trx_started))>60;"
这两结合起来,很好用.
thread_execded_sql="select '==========INNODB_TRX==========', it.*,'==========processlist==========', p.*, '==========threads==========', t.*, '==========events_statements_history==========', es.*, '==========trx_runing_time==========', (unix_timestamp(now())-unix_timestamp(trx_started)) as trx_runing_time from information_schema.INNODB_TRX it, information_schema.processlist p, performance_schema.events_statements_history es, performance_schema.threads t where es.THREAD_ID=t.THREAD_ID and t.PROCESSLIST_ID=it.trx_mysql_thread_id and it.trx_mysql_thread_id=p.ID and p.COMMAND='Sleep' having (unix_timestamp(now())-unix_timestamp(it.trx_started))>60 order by es.TIMER_START asc\G;"

复制代码