Início / dba / Perguntas / 335304
Accepted
Anthony Benavidez
Asked: 2024-01-30 08:07:13 +0800 CST

Consultas fazendo com que o MariaDB receba o sinal 7

  • 772

Eu tenho uma caixa Redhat executando o MariaDB 10.3.39-MariaDB-log. Existem algumas consultas em execução que parecem fazer com que o MariaDB receba um sinal 7, o MariaDB irá travar, marcar um monte de tabelas como travadas e reiniciar. Abri um ticket com Redhat, pensando que talvez algo esteja errado com nosso servidor, porém chegamos à mesma conclusão de que algumas consultas pareciam estar causando problemas idênticos. Achei que talvez fosse um problema de espaço, pois o ponto de montagem estava acabando, dei mais 10 TB, apenas por segurança e o problema persiste.

Então isso me traz aqui, desesperado por algum tipo de pista.

  1. Não sei como interromper essas consultas e elas são executadas a cada 30 minutos.
  2. Não sei como ver de onde essas consultas se originam, então não consigo descobrir se é de uma fonte comum.

Eu apreciaria muito se alguém pudesse gentilmente me 'apontar' na direção certa.

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

E então continua assim por um bom tempo. Eu entendo se não obtiver respostas, mas se houver algo que seja flagrantemente óbvio, compartilhe ou indique-me a direção certa.

mariadb

2 respostas

  1. Abri um ticket com Redhat

    Você deveria ter aberto um ticket com MariaDB, como sugere a mensagem de erro. O servidor parece tentar acessar um local de memória inválido ao ler um índice na tabela chamada WEBT$VEP$AV$$2022_04_06, portanto, eliminar e recriar quaisquer índices existentes nessa tabela pode ajudar.

    Saber "como interromper essas consultas" ou "ver de onde essas consultas são originadas" não o ajudará com a causa raiz, mas se você quiser saber essas coisas, talvez faça uma pergunta separada, porque o fato de seu servidor travar é irrelevante para isso.

    • 0
  2. Best Answer

    sugestões a serem consideradas para sua seção my.cnf [mysqld]

    default-storage-engine = MYISAM
myisam_use_mmap = 1
table_open_cache = 2048
open_files_limit = 6144
# or REMOVE thread_concurrency=32
key_buffer_size = 128M
myisam_sort_buffer_size = 32M
query_cache_size = 0
query_cache_type = 0
read_buffer_size = 1M
sort_buffer_size = 2M
max_allowed_packet = 256M
slow_query_log = ON
long_query_time = .2
read_rnd_buffer_size = 16K
tmp_table_size = 128M
max_heap_table_size = 128M
# or REMOVE core-file

    para um sistema provavelmente mais estável que sobreviva aos seus testes.

    Por favor, publique os resultados do TEXTO da página 1 completa do htop, quando o tempo permitir.

    Veja o perfil, por favor.

    • 0

relate perguntas