我有一个运行 MariaDB 10.3.39-MariaDB-log 的 Redhat 机器。有一些正在运行的查询似乎导致 MariaDB 收到信号 7,MariaDB 会将一堆表崩溃标记为崩溃并重新启动。我向 Redhat 开了一张票,认为我们的服务器可能出了问题,但我们得出了相同的结论,即一些查询似乎导致了相同的问题。我认为这可能是一个空间问题,因为挂载点已不足,为了安全起见,我又给了它 10TB,但问题仍然存在。
所以这让我来到这里,迫切需要某种线索。
- 我不知道如何停止这些查询,它们每 30 分钟运行一次。
- 我不知道如何查看这些查询的来源,因此我无法确定它是否来自共同来源。
如果有人能好心地“指出”我正确的方向,我将不胜感激。
default-storage-engine = MYISAM
myisam_use_mmap=1
table_open_cache = 2048
open_files_limit = 6144
thread_concurrency=32
#key_buffer_size = 4096M
key_buffer_size = 21474836480
##myisam_sort_buffer_size = 32M
myisam_sort_buffer_size = 64M
##query_cache_size= 32M
query_cache_size= 0k
read_buffer_size = 32M
sort_buffer_size = 32M
max_allowed_packet = 256M
slow-query-log = ON
long_query_time = .2
read_rnd_buffer_size = 64K
tmp_table_size = 128M
core-file
Jan 29 22:35:45 mysqld[1021462]: 240129 22:35:45 [ERROR] mysqld got signal 7 ;
Jan 29 22:35:45 mysqld[1021462]: This could be because you hit a bug. It is also possible that this binary
Jan 29 22:35:45 mysqld[1021462]: or one of the libraries it was linked against is corrupt, improperly built,
Jan 29 22:35:45 mysqld[1021462]: or misconfigured. This error can also be caused by malfunctioning hardware.
Jan 29 22:35:45 mysqld[1021462]: To report this bug, see https://mariadb.com/kb/en/reporting-bugs
Jan 29 22:35:45 mysqld[1021462]: We will try our best to scrape up some info that will hopefully help
Jan 29 22:35:45 mysqld[1021462]: diagnose the problem, but since we have already crashed,
Jan 29 22:35:45 mysqld[1021462]: something is definitely wrong and this may fail.
Jan 29 22:35:45 mysqld[1021462]: Server version: 10.3.39-MariaDB-log source revision: ca001cf2048f0152689e1895e2dc15486dd0b1a
f
Jan 29 22:35:45 mysqld[1021462]: key_buffer_size=21474836480
Jan 29 22:35:45 mysqld[1021462]: read_buffer_size=33554432
Jan 29 22:35:45 mysqld[1021462]: max_used_connections=12
Jan 29 22:35:45 mysqld[1021462]: max_threads=153
Jan 29 22:35:45 mysqld[1021462]: thread_count=17
Jan 29 22:35:45 mysqld[1021462]: It is possible that mysqld could use up to
Jan 29 22:35:45 mysqld[1021462]: key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 31001963 K bytes of m
Jan 29 22:35:45 mysqld[1021462]: This could be because you hit a bug. It is also possible that this binary
Jan 29 22:35:45 mysqld[1021462]: or one of the libraries it was linked against is corrupt, improperly built,
Jan 29 22:35:45 mysqld[1021462]: or misconfigured. This error can also be caused by malfunctioning hardware.
Jan 29 22:35:45 mysqld[1021462]: To report this bug, see https://mariadb.com/kb/en/reporting-bugs
Jan 29 22:35:45 mysqld[1021462]: We will try our best to scrape up some info that will hopefully help
Jan 29 22:35:45 mysqld[1021462]: diagnose the problem, but since we have already crashed,
Jan 29 22:35:45 mysqld[1021462]: something is definitely wrong and this may fail.
Jan 29 22:35:45 mysqld[1021462]: Server version: 10.3.39-MariaDB-log source revision: ca001cf2048f0152689e1895e2dc15486dd0b1a
f
Jan 29 22:35:45 mysqld[1021462]: key_buffer_size=21474836480
Jan 29 22:35:45 mysqld[1021462]: read_buffer_size=33554432
Jan 29 22:35:45 mysqld[1021462]: max_used_connections=12
Jan 29 22:35:45 mysqld[1021462]: max_threads=153
Jan 29 22:35:45 mysqld[1021462]: thread_count=17
Jan 29 22:35:45 mysqld[1021462]: It is possible that mysqld could use up to
Jan 29 22:35:45 mysqld[1021462]: key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 31001963 K bytes of m
emory
Jan 29 22:35:45 mysqld[1021462]: Hope that's ok; if not, decrease some variables in the equation.
Jan 29 22:35:45 mysqld[1021462]: Thread pointer: 0x7fb5b8000c48
Jan 29 22:35:45 mysqld[1021462]: Attempting backtrace. You can use the following information to find out
Jan 29 22:35:45 mysqld[1021462]: where mysqld died. If you see no messages after this, something went
Jan 29 22:35:45 mysqld[1021462]: terribly wrong...
Jan 29 22:35:45 mysqld[1021462]: stack_bottom = 0x7fb5bca02c48 thread_stack 0x49000
Jan 29 22:35:45 mysqld[1021462]: /usr/libexec/mysqld(my_print_stacktrace+0x41)[0x55b55d773eb1]
Jan 29 22:35:45 mysqld[1021462]: /usr/libexec/mysqld(handle_fatal_signal+0x4f5)[0x55b55d299c05]
Jan 29 22:35:45 mysqld[1021462]: /lib64/libpthread.so.0(+0x12cf0)[0x7fbda6861cf0]
Jan 29 22:35:46 mysqld[1021462]: :0(__memmove_avx_unaligned_erms)[0x7fbda6558e73]
Jan 29 22:35:46 mysqld[1021462]: /usr/libexec/mysqld(+0xc00475)[0x55b55d6de475]
Jan 29 22:35:46 mysqld[1021462]: /usr/libexec/mysqld(+0xc030e9)[0x55b55d6e10e9]
Jan 29 22:35:46 mysqld[1021462]: /usr/libexec/mysqld(+0xc2309a)[0x55b55d70109a]
Jan 29 22:35:46 mysqld[1021462]: /usr/libexec/mysqld(_ZN7handler17ha_index_read_mapEPhPKhm16ha_rkey_function+0x148)[0x55b55d29f448]
Jan 29 22:35:46 mysqld[1021462]: /usr/libexec/mysqld(_ZN7handler16read_range_firstEPK12st_key_rangeS2_bb+0x66)[0x55b55d2a38c6]
Jan 29 22:35:46 mysqld[1021462]: /usr/libexec/mysqld(_ZN7handler21multi_range_read_nextEPPv+0xbf)[0x55b55d1c5d6f]
Jan 29 22:35:46 mysqld[1021462]: /usr/libexec/mysqld(_ZN23Mrr_simple_index_reader8get_nextEPPv+0x52)[0x55b55d1c5df2]
Jan 29 22:35:46 mysqld[1021462]: /usr/libexec/mysqld(_ZN10DsMrr_impl10dsmrr_nextEPPv+0x4a)[0x55b55d1c722a]
Jan 29 22:35:46 mysqld[1021462]: /usr/libexec/mysqld(_ZN18QUICK_RANGE_SELECT8get_nextEv+0x3c)[0x55b55d3987dc]
Jan 29 22:35:46 mysqld[1021462]: /usr/libexec/mysqld(+0x8d581d)[0x55b55d3b381d]
Jan 29 22:35:46 mysqld[1021462]: /usr/libexec/mysqld(_Z10sub_selectP4JOINP13st_join_tableb+0x18e)[0x55b55d0ed90e]
Jan 29 22:35:46 mysqld[1021462]: /usr/libexec/mysqld(_ZN4JOIN10exec_innerEv+0xa9a)[0x55b55d10fc2a]
Jan 29 22:35:46 mysqld[1021462]: /usr/libexec/mysqld(_ZN4JOIN4execEv+0x37)[0x55b55d10fed7]
Jan 29 22:35:46 mysqld[1021462]: /usr/libexec/mysqld(_Z12mysql_selectP3THDP10TABLE_LISTjR4ListI4ItemEPS4_jP8st_orderS9_S7_S9_yP13select_resultP18st_select_lex_unitP13st_select_lex+0xff)[0x55b55d11002f]
Jan 29 22:35:46 mysqld[1021462]: /usr/libexec/mysqld(_Z13handle_selectP3THDP3LEXP13select_resultm+0x165)[0x55b55d110955]
Jan 29 22:35:46 mysqld[1021462]: /usr/libexec/mysqld(+0x5d035c)[0x55b55d0ae35c]
Jan 29 22:35:46 mysqld[1021462]: /usr/libexec/mysqld(_Z21mysql_execute_commandP3THD+0x5373)[0x55b55d0baeb3]
Jan 29 22:35:46 mysqld[1021462]: /usr/libexec/mysqld(_Z11mysql_parseP3THDPcjP12Parser_statebb+0x215)[0x55b55d0bd835]
Jan 29 22:35:46 mysqld[1021462]: /usr/libexec/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcjbb+0x1304)[0x55b55d0bf8f4]
Jan 29 22:35:46 mysqld[1021462]: /usr/libexec/mysqld(_Z10do_commandP3THD+0x126)[0x55b55d0c0f26]
Jan 29 22:35:46 mysqld[1021462]: /usr/libexec/mysqld(_Z24do_handle_one_connectionP7CONNECT+0x252)[0x55b55d19a212]
Jan 29 22:35:46 mysqld[1021462]: /usr/libexec/mysqld(handle_one_connection+0x41)[0x55b55d19a3b1]
Jan 29 22:35:46 mysqld[1021462]: /lib64/libpthread.so.0(+0x81ca)[0x7fbda68571ca]
Jan 29 22:35:46 mysqld[1021462]: :0(__GI___clone)[0x7fbda64c3e73]
Jan 29 22:35:46 mysqld[1021462]: Trying to get some variables.
Jan 29 22:35:46 mysqld[1021462]: Some pointers may be invalid and cause the dump to abort.
Jan 29 22:35:46 mysqld[1021462]: Query (0x7fb5b800f680): SELECT st, et, sr, datatype, tracebuf FROM `WEBT$VEP$AV$$2022_04_06` WHERE st>=7.025538E8 AND st<=7.025544E8 ORDER BY st ASC
Jan 29 22:35:46 mysqld[1021462]: Connection ID (thread ID): 29
Jan 29 22:35:46 mysqld[1021462]: Status: NOT_KILLED
Jan 29 22:35:46 mysqld[1021462]: Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on
Jan 29 22:35:46 mysqld[1021462]: The manual page at https://mariadb.com/kb/en/how-to-produce-a-full-stack-trace-for-mysqld/ contains
Jan 29 22:35:46 mysqld[1021462]: information that should help you find out what is causing the crash.
Jan 29 22:35:46 mysqld[1021462]: Writing a core file...
Jan 29 22:35:46 mysqld[1021462]: Working directory at /mnt/winston
Jan 29 22:35:46 mysqld[1021462]: Resource Limits:
Jan 29 22:35:46 mysqld[1021462]: Limit Soft Limit Hard Limit Units
Jan 29 22:35:46 mysqld[1021462]: Max cpu time unlimited unlimited seconds
Jan 29 22:35:46 mysqld[1021462]: Max file size unlimited unlimited bytes
Jan 29 22:35:46 mysqld[1021462]: Max data size unlimited unlimited bytes
Jan 29 22:35:46 mysqld[1021462]: Max stack size 8388608 unlimited bytes
Jan 29 22:35:46 mysqld[1021462]: Max core file size unlimited unlimited bytes
Jan 29 22:35:46 mysqld[1021462]: Max resident set unlimited unlimited bytes
Jan 29 22:35:46 mysqld[1021462]: Max processes 319785 319785 processes
Jan 29 22:35:46 mysqld[1021462]: Max open files 1048576 1048576 files
Jan 29 22:35:46 mysqld[1021462]: Max locked memory 65536 65536 bytes
Jan 29 22:35:46 mysqld[1021462]: Max address space unlimited unlimited bytes
Jan 29 22:35:46 mysqld[1021462]: Max file locks unlimited unlimited locks
Jan 29 22:35:46 mysqld[1021462]: Max pending signals 319785 319785 signals
Jan 29 22:35:46 mysqld[1021462]: Max msgqueue size 819200 819200 bytes
Jan 29 22:35:46 mysqld[1021462]: Max nice priority 0 0
Jan 29 22:35:46 mysqld[1021462]: Max realtime priority 0 0
Jan 29 22:35:46 mysqld[1021462]: Max realtime timeout unlimited unlimited us
Jan 29 22:35:46 mysqld[1021462]: Core pattern: /tmp/corefiles/core
Jan 29 22:35:46 mysqld[1021462]: Kernel version: Linux version 4.18.0-477.27.1.el8_8.x86_64 ([email protected]) (gcc version 8.5.0 20210514 (Red Hat 8.5.0-18) (GCC)) #1 SMP Thu Aug 31 10:29:22 EDT 2023
Jan 29 22:35:47 systemd[1]: mariadb.service: Main process exited, code=killed, status=7/BUS
Jan 29 22:35:47 systemd[1]: mariadb.service: Failed with result 'signal'.
Jan 29 22:35:52 systemd[1]: mariadb.service: Service RestartSec=5s expired, scheduling restart.
Jan 29 22:35:52 systemd[1]: mariadb.service: Scheduled restart job, restart counter is at 608.
Jan 29 22:35:52 systemd[1]: Stopped MariaDB 10.3 database server.
Jan 29 22:35:52 systemd[1]: Starting MariaDB 10.3 database server...
Jan 29 22:35:52 mysql-check-socket[1021701]: Socket file /var/lib/mysql/mysql.sock exists.
Jan 29 22:35:52 mysql-check-socket[1021701]: No process is using /var/lib/mysql/mysql.sock, which means it is a garbage, so it will be removed automatically.
Jan 29 22:35:52 mysql-prepare-db-dir[1021730]: Database MariaDB is probably initialized in /mnt/winston already, nothing is done.
Jan 29 22:35:52 mysql-prepare-db-dir[1021730]: If this is not the case, make sure the /mnt/winston is empty before running mysql-prepare-db-dir.
Jan 29 22:35:52 mysqld[1021768]: 2024-01-29 22:35:52 0 [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release.
Jan 29 22:35:52 mysqld[1021768]: 2024-01-29 22:35:52 0 [Note] Starting MariaDB 10.3.39-MariaDB-log source revision ca001cf2048f0152689e1895e2dc15486dd0b1af as process 1021768
Jan 29 22:35:55 mysqld[1021768]: 2024-01-29 22:35:55 0 [Note] InnoDB: Using Linux native AIO
Jan 29 22:35:55 mysqld[1021768]: 2024-01-29 22:35:55 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
Jan 29 22:35:55 mysqld[1021768]: 2024-01-29 22:35:55 0 [Note] InnoDB: Uses event mutexes
Jan 29 22:35:55 mysqld[1021768]: 2024-01-29 22:35:55 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
Jan 29 22:35:55 mysqld[1021768]: 2024-01-29 22:35:55 0 [Note] InnoDB: Number of pools: 1
Jan 29 22:35:55 mysqld[1021768]: 2024-01-29 22:35:55 0 [Note] InnoDB: Using SSE2 crc32 instructions
Jan 29 22:35:55 mysqld[1021768]: 2024-01-29 22:35:55 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
Jan 29 22:35:55 mysqld[1021768]: 2024-01-29 22:35:55 0 [Note] InnoDB: Completed initialization of buffer pool
Jan 29 22:35:55 mysqld[1021768]: 2024-01-29 22:35:55 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
Jan 29 22:35:55 mysqld[1021768]: 2024-01-29 22:35:55 0 [Note] InnoDB: The log sequence number 1650206 in the system tablespace does not match the log sequence number 1654796 in the ib_logfiles!
Jan 29 22:35:55 mysqld[1021768]: 2024-01-29 22:35:55 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
Jan 29 22:35:55 mysqld[1021768]: 2024-01-29 22:35:55 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
Jan 29 22:35:55 mysqld[1021768]: 2024-01-29 22:35:55 0 [Note] InnoDB: Creating shared tablespace for temporary tables
Jan 29 22:35:55 mysqld[1021768]: 2024-01-29 22:35:55 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
Jan 29 22:35:55 mysqld[1021768]: 2024-01-29 22:35:55 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
Jan 29 22:35:55 mysqld[1021768]: 2024-01-29 22:35:55 0 [Note] InnoDB: Waiting for purge to start
Jan 29 22:35:55 mysqld[1021768]: 2024-01-29 22:35:55 0 [Note] InnoDB: 10.3.39 started; log sequence number 1654796; transaction id 38
Jan 29 22:35:55 mysqld[1021768]: 2024-01-29 22:35:55 0 [Note] InnoDB: Loading buffer pool(s) from /mnt/winston/ib_buffer_pool
Jan 29 22:35:55 mysqld[1021768]: 2024-01-29 22:35:55 0 [Note] InnoDB: Buffer pool(s) load completed at 240129 22:35:55
Jan 29 22:35:55 mysqld[1021768]: 2024-01-29 22:35:55 0 [Note] Plugin 'FEEDBACK' is disabled.
Jan 29 22:35:55 mysqld[1021768]: 2024-01-29 22:35:55 0 [Note] Recovering after a crash using tc.log
Jan 29 22:35:55 mysqld[1021768]: 2024-01-29 22:35:55 0 [Note] Starting crash recovery...
Jan 29 22:35:55 mysqld[1021768]: 2024-01-29 22:35:55 0 [Note] Crash recovery finished.
Jan 29 22:35:55 mysqld[1021768]: 2024-01-29 22:35:55 0 [Note] Server socket created on IP: '::'.
Jan 29 22:35:55 mysqld[1021768]: 2024-01-29 22:35:55 0 [Note] Reading of all Master_info entries succeeded
Jan 29 22:35:55 mysqld[1021768]: 2024-01-29 22:35:55 0 [Note] Added new Master_info '' to hash table
Jan 29 22:35:55 mysqld[1021768]: 2024-01-29 22:35:55 0 [Note] /usr/libexec/mysqld: ready for connections.
Jan 29 22:35:55 mysqld[1021768]: Version: '10.3.39-MariaDB-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MariaDB Server
Jan 29 22:35:55 systemd[1]: Started MariaDB 10.3 database server.
Jan 29 22:35:55 mysqld[1021768]: 2024-01-29 22:35:55 8 [ERROR] mysqld: Table './WWS_HAG@0024SHE@0024AV/HAG@0024SHE@0024AV@0024@00242024_01_29' is marked as crashed and should be repaired
然后就这样持续了很长一段时间。如果我没有得到答案,我会明白,但如果有一些明显明显的东西,请分享或给我指出正确的方向。