Debian Question posted in
The oficial documentation can be found here.

Debian – InnoDB: innodb_fatal_semaphore_wait_threshold was exceeded for dict_sys.latch

Since a few days I have fatal errors
"InnoDB: innodb_fatal_semaphore_wait_threshold was exceeded for dict_sys.latch".

Neither the underlying Debian Bookworm nor the MariaDB Server 10.11.4 was changed before.

What could cause this issue?

The log is showing:

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...

I increased innodb_buffer_pool_size from 4294967296 to to 42949672960 with no effect.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    We created a new database from an SQL dump of the exiosting one which seems to have helped the problem. Thanks for all your hints!


  2. 0

    Observations,

    1. Your SHOW GLOBAL STATUS data was missing innodb_rows_deleted, innodb_rows_inserted, innodb_rows_read, innodb_rows_updated helpful information.

    2. opened_files averaged 148 PER connection for your 44 Minutes of uptime.
      Never seen this extreme load before.

    3. opened_table_definitions averaged 147 PER connection for your 44 Minutes of uptime.
      Never seen this extreme load before.

    Suggestions to consider for your my.cnf [mysqld} section to improve performance

    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.

    From your OS command prompt,
    ulimit -n 180000
    to avoid starving MySQL’s need to open many concurrent file handles.

    This is a dynamic change to the OS and will affect NEW CONNECTIONS only.

    To make this change persistent across OS stop/start, refer to this URL for an overview only. https://glassonionblog.wordpress.com/2013/01/27/increase-ulimit-and-file-descriptors-limit/
    This example shows 500000 as the goal. For your needs 180000 will be adequate at this time.
    ulimit -a displayed value should always be more than open_files_limit by at least 10% to support the other activites on your server.

    There are other opportunities to improve your configuration. Please view profile.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search