因为几天我有致命的错误“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,但没有任何效果。
2条答案
按热度按时间mrfwxfqh1#
意见,
1.您的SHOW GLOBAL STATUS数据缺少innodb_rows_deleted,innodb_rows_inserted,innodb_rows_read,innodb_rows_updated有用信息。
建议考虑您的my.cnf [mysqld]节,以提高性能
字符串
在操作系统命令提示符下,ulimit -n 180000
以避免饥饿MySQL的需要打开许多并发文件句柄。
这是对操作系统的动态更改,仅会影响新连接。
https://glassonionblog.wordpress.com/2013/01/27/increase-ulimit-and-file-descriptors-limit/此示例显示500000作为目标。对于您的需求,此时180000将足够。ulimit -显示的值应始终比open_files_limit多至少10%,以支持服务器上的其他活动。
还有其他机会来改善您的配置。请查看个人资料。
gorkyyrv2#
我们从现有数据库的SQL转储中创建了一个新的数据库,这似乎有助于解决问题。感谢您的所有提示!