本帖最后由 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 ![]() ![]() 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 |
yejr
2025-3-13 14:52:24
| ||
earl86
2025-3-13 17:45:26
| ||
合作电话:010-64087828
社区邮箱:greatsql@greatdb.com