mariadb InnoDB:dict_sys.latch的innodb_fatal_信号量_wait_threshold已超出

ipakzgxi  于 5个月前  发布在  其他
关注(0)|答案(2)|浏览(53)

因为几天我有致命的错误“InnoDB:innodb_fatal_信号量_wait_threshold was exceeded for dict_sys.latch”。
Debian Bookworm和MariaDB服务器10.11.4都没有改变。
导致此问题的原因是什么?
日志显示:

mariadbd[324032]: 2023-12-22 11:50:54 0 [ERROR] [FATAL] InnoDB: 
innodb_fatal_semaphore_wait_threshold was exceeded for dict_sys.latch. 
Please refer to
https://mariadb.com/kb/en/how-to-produce-a-full-stack-trace-for-mysqld/

mariadbd[324032]: 231222 11:50:54 [ERROR] mysqld got signal 6 ;
mariadbd[324032]: This could be because you hit a bug. It is also possible that this binary
mariadbd[324032]: or one of the libraries it was linked against is corrupt, improperly built,
mariadbd[324032]: or misconfigured. This error can also be caused by malfunctioning hardware.
mariadbd[324032]: To report this bug, see https://mariadb.com/kb/en/reporting-bugs
mariadbd[324032]: We will try our best to scrape up some info that will hopefully help
mariadbd[324032]: diagnose the problem, but since we have already crashed,
mariadbd[324032]: something is definitely wrong and this may fail.
mariadbd[324032]: Server version: 10.11.4-MariaDB-1~deb12u1 source revision:
mariadbd[324032]: key_buffer_size=134217728
mariadbd[324032]: read_buffer_size=131072
mariadbd[324032]: max_used_connections=99
mariadbd[324032]: max_threads=1002
mariadbd[324032]: thread_count=99
mariadbd[324032]: It is possible that mysqld could use up to
mariadbd[324032]: key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 2337911 K  bytes of memory
mariadbd[324032]: Hope that's ok; if not, decrease some variables in the equation.
mariadbd[324032]: Thread pointer: 0x0
mariadbd[324032]: Attempting backtrace. You can use the following information to find out
mariadbd[324032]: where mysqld died. If you see no messages after this, something went
mariadbd[324032]: terribly wrong...
mariadbd[324032]: stack_bottom = 0x0 thread_stack 0x49000
mariadbd[324032]: 
/usr/sbin/mariadbd(my_print_stacktrace+0x2e)[0x55ea6dcb14ae]
mariadbd[324032]: 
/usr/sbin/mariadbd(handle_fatal_signal+0x409)[0x55ea6d81ef49]
mariadbd[324032]: /lib/x86_64-linux-gnu/libc.so.6(+0x3bfd0)[0x7f9bb5e5afd0]
mariadbd[324032]: /lib/x86_64-linux-gnu/libc.so.6(+0x8ad3c)[0x7f9bb5ea9d3c]
mariadbd[324032]: 
/lib/x86_64-linux-gnu/libc.so.6(gsignal+0x12)[0x7f9bb5e5af32]
mariadbd[324032]: 
/lib/x86_64-linux-gnu/libc.so.6(abort+0xd3)[0x7f9bb5e45472]
mariadbd[324032]: /usr/sbin/mariadbd(+0x68b8af)[0x55ea6d4818af]
mariadbd[324032]: /usr/sbin/mariadbd(+0x683b58)[0x55ea6d479b58]
mariadbd[324032]: 
/usr/sbin/mariadbd(_ZN5tpool19thread_pool_generic13timer_generic7executeEPv+0x38)[0x55ea6dc555a8]
mariadbd[324032]: 
/usr/sbin/mariadbd(_ZN5tpool4task7executeEv+0x2f)[0x55ea6dc563ef]
mariadbd[324032]: 
/usr/sbin/mariadbd(_ZN5tpool19thread_pool_generic11worker_mainEPNS_11worker_dataE+0x4f)[0x55ea6dc5466f]
mariadbd[324032]: 
/lib/x86_64-linux-gnu/libstdc++.so.6(+0xd44a3)[0x7f9bb60d44a3]
mariadbd[324032]: /lib/x86_64-linux-gnu/libc.so.6(+0x89044)[0x7f9bb5ea8044]
mariadbd[324032]: /lib/x86_64-linux-gnu/libc.so.6(+0x10961c)[0x7f9bb5f2861c]
mariadbd[324032]: The manual page at
https://mariadb.com/kb/en/how-to-produce-a-full-stack-trace-for-mysqld/
contains
mariadbd[324032]: information that should help you find out what is causing the crash.
mariadbd[324032]: Writing a core file...
mariadbd[324032]: Working directory at /var/lib/mysql
mariadbd[324032]: Resource Limits:
mariadbd[324032]: Limit                     Soft Limit Hard Limit           Units
mariadbd[324032]: Max cpu time              unlimited unlimited            seconds
mariadbd[324032]: Max file size             unlimited unlimited            bytes
mariadbd[324032]: Max data size             unlimited unlimited            bytes
mariadbd[324032]: Max stack size            8388608 unlimited bytes
mariadbd[324032]: Max core file size        0 unlimited            bytes
mariadbd[324032]: Max resident set          unlimited unlimited            bytes
mariadbd[324032]: Max processes             1030903 1030903 processes
mariadbd[324032]: Max open files            200000 200000 files
mariadbd[324032]: Max locked memory         524288 524288 bytes
mariadbd[324032]: Max address space         unlimited unlimited            bytes
mariadbd[324032]: Max file locks            unlimited unlimited            locks
mariadbd[324032]: Max pending signals       1030903 1030903 signals
mariadbd[324032]: Max msgqueue size         819200 819200 bytes
mariadbd[324032]: Max nice priority         0                    0
mariadbd[324032]: Max realtime priority     0                    0
mariadbd[324032]: Max realtime timeout      unlimited unlimited            us
mariadbd[324032]: Core pattern: core
mariadbd[324032]: Kernel version: Linux version 6.1.0-12-amd64
([email protected]) (gcc-12 (Debian 12.2.0-14) 12.2.0, GNU ld (GNU Binutils for Debian) 2.40) #1 SMP PREEMPT_DYNAMIC Debian
6.1.52-1 (2023-09-07)
systemd[1]: mariadb.service: Main process exited, code=killed, status=6/ABRT
systemd[1]: mariadb.service: Failed with result 'signal'.
systemd[1]: mariadb.service: Consumed 3h 50min 55.933s CPU time.
systemd[1]: mariadb.service: Scheduled restart job, restart counter is at 1.
systemd[1]: Stopped mariadb.service - MariaDB 10.11.4 database server.
systemd[1]: mariadb.service: Consumed 3h 50min 55.933s CPU time.
systemd[1]: Starting mariadb.service - MariaDB 10.11.4 database server...

字符串
我将innodb_buffer_pool_size从4294967296增加到42949672960,但没有任何效果。

mrfwxfqh

mrfwxfqh1#

意见,
1.您的SHOW GLOBAL STATUS数据缺少innodb_rows_deleted,innodb_rows_inserted,innodb_rows_read,innodb_rows_updated有用信息。

  1. opened_files在你的44分钟的浏览中平均每连接148次。以前从未见过这种极端的负载。
  2. opened_table_definitions在你的44分钟的浏览中平均有147个PER连接。以前从未见过如此极端的负载。
    建议考虑您的my.cnf [mysqld]节,以提高性能
net_buffer_length=96K  # from 16K to reduce packets sent/received count.
innodb_lru_scan_depth=100  # from 1536 to reduce about 95% cpu cycles used for function.
innodb_io_capacity=500  # from 200 to utilize more available IOPSecond on SSD.
thread_pool_size=102  # from 128 for 80% mysql use and allow other apps access to limited cores.
log_error=sql_host_error_log  # for single purpose error log - not mixed in syslog.
table_definition_cache=96000  # from 10304 to reduce openec_table_definitions RPS of 62.

字符串
在操作系统命令提示符下,ulimit -n 180000
以避免饥饿MySQL的需要打开许多并发文件句柄。
这是对操作系统的动态更改,仅会影响新连接。
https://glassonionblog.wordpress.com/2013/01/27/increase-ulimit-and-file-descriptors-limit/此示例显示500000作为目标。对于您的需求,此时180000将足够。ulimit -显示的值应始终比open_files_limit多至少10%,以支持服务器上的其他活动。
还有其他机会来改善您的配置。请查看个人资料。

gorkyyrv

gorkyyrv2#

我们从现有数据库的SQL转储中创建了一个新的数据库,这似乎有助于解决问题。感谢您的所有提示!

相关问题