Estou executando o MariaDB 11.4.4 com um site movimentado (Debian12+nginx+php-fpm).
Uma vez a cada poucos dias, os threads do MariaDB parecem congelados até que a conexão máxima seja atingida. Mesmo matando a consulta, o thread não é liberado. O MariaDB não pode ser desligado por systemctl stop mariadbFinalmente, preciso killall -9 mariadbdretomar as operações. Não há nada inesperado em mysql/error.log ou slow_query.log
Aqui está SHOW FULL PROCESSLIST, SHOW GLOBAL STATUS e SHOW ENGINE INNODB STATUS tirados logo antes do bloqueio total do MariaDB:
https://pastejustit.com/mariadb-debug-info
MOSTRAR VARIÁVEIS está aqui:
Houve relatos semelhantes no passado, porém eles estavam relacionados a uma versão mais antiga do MariaDB, sem solução clara e provavelmente foram corrigidos no MariaDB 10.6.*
Alguma ideia de como evitar esse problema?
ADD : fully_pingdefinição de tabela
CREATE TABLE `fully_ping` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`devid` varchar(255) NOT NULL,
`appid` int(11) NOT NULL DEFAULT 1,
`firstping_time` datetime DEFAULT NULL COMMENT 'UTC',
`lastping_time` datetime NOT NULL COMMENT 'UTC',
`message` mediumtext NOT NULL,
`pass` varchar(255) DEFAULT NULL,
`ip` varchar(255) DEFAULT NULL,
`serial` varchar(255) DEFAULT NULL,
`counter` int(11) NOT NULL DEFAULT 0,
`version` int(11) DEFAULT NULL,
`tls_version` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `devid` (`devid`(191)),
KEY `appid` (`appid`),
KEY `lastping_time` (`lastping_time`),
KEY `counter` (`counter`),
KEY `serial` (`serial`),
CONSTRAINT `fully_ping_ibfk_1` FOREIGN KEY (`appid`) REFERENCES `fully_apps` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=180169 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci
Estou tendo os mesmos problemas no Debian. Provavelmente isso está acionando meu problema: https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=1093243
Por favor forneça
SHOW CREATE TABLE fully_pingTalvez isso ajude:
Você pode alterar a declaração de
devidparaVARCHAR(191)? ou algumas delas são muito longas?E, substitua
por
junto com
Hmmmm.... Algumas coisas interessantes no STATUS / VARIÁVEIS:
Observações:
As questões mais importantes:
Alterações de configurações recomendadas:
Altere os clientes para serem menos agressivos na conexão com o MariaDB. Quando você tem 600 conexões (cf Max_used_connections), cada uma está tropeçando em todas as outras. Se você estiver usando o Apache, diminua o número de filhos lá e deixe-o criar um backlog.
Por que tantos comandos SET? (987/seg) Verifique o pool de conexões (re 987 conexões/seg.)
Vamos ver o slowlog — pode haver pistas adicionais lá:( https://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog ) Isso pode ajudar com o alto número de tmp_tables sendo criadas.
Detalhes e outras observações:
( Key_blocks_used * 1024 / key_buffer_size ) = 0 * 1024 / 128M = 0-- Porcentagem de key_buffer usada. Marca d'água alta. -- Key_buffer_size menor (agora 134217728) para evitar uso desnecessário de memória.( Table_open_cache_misses ) = 115349902 / 120277 = 959 /sec-- Pode ser necessário aumentar table_open_cache (agora 2000)( Table_open_cache_misses / (Table_open_cache_hits + Table_open_cache_misses) ) = 115,349,902 / (1508587798 + 115349902) = 7.1%-- Eficácia do table_open_cache. -- Aumente o table_open_cache (agora 2000) e verifique o table_open_cache_instances (agora 8).( binlog_cache_size * max_connections ) = (16M * 700) / 16384M = 68.4%-- RAM usada para armazenar transações em cache no caminho para o binlog. -- Diminuir binlog_cache_size (agora 16777216) e/ou max_connections (agora 700)( innodb_lru_scan_depth ) = 1,536-- innodb_lru_scan_depth é uma variável com um nome muito ruim. Um nome melhor seria innodb_free_page_target_per_buffer_pool. É um número de páginas que o InnoDB tenta manter livres em cada instância do buffer pool para acelerar as operações de leitura e criação de páginas. -- "InnoDB: page_cleaner: 1000ms intended loop took ..." pode ser corrigido diminuindo lru_scan_depth( innodb_io_capacity ) = 200-- Ao liberar, use esta quantidade de IOPs. -- As leituras podem ser lentas ou irregulares. Use 2000 se estiver usando uma unidade SSD.( Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total ) = 418,553 / 648960 = 64.5%-- Pct de buffer_pool atualmente não está em uso -- innodb_buffer_pool_size (agora 10737418240) é maior que o necessário?( innodb_io_capacity_max / innodb_io_capacity ) = 2,000 / 200 = 10-- Capacidade: máx./simples -- Recomendado 2. O máximo deve ser aproximadamente igual aos IOPs que seu subsistema de E/S pode manipular. (Se o tipo de unidade for desconhecido, 2000/200 pode ser um par razoável.)( Innodb_buffer_pool_bytes_data / innodb_buffer_pool_size ) = 3,774,693,376 / 10240M = 35.2%-- Porcentagem do buffer pool ocupada por dados -- Uma porcentagem pequena pode indicar que o buffer_pool é desnecessariamente grande.( Innodb_os_log_written ) = 505660239032 / 120277 = 4204130 /sec-- Este é um indicador de quão ocupado o InnoDB está. -- InnoDB muito ocioso ou muito ocupado.( Innodb_log_writes ) = 40547610 / 120277 = 337 /sec( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 120,277 / 60 * 2048M / 505660239032 = 8.51-- Minutos entre rotações de log do InnoDB A partir da versão 5.6.8, innodb_log_file_size pode ser alterado dinamicamente; não sei sobre o MariaDB. Certifique-se de alterar também my.cnf -- (A recomendação de 60 minutos entre rotações é um tanto arbitrária.) Ajuste innodb_log_file_size (agora 2147483648). (Não é possível alterar no AWS.)( Innodb_num_open_files/innodb_open_files ) = 76/2000 = 3.8%-- Porcentagem do cache de arquivos do InnoDB atualmente em uso(?) -- innodb_open_files (agora 2000) muito pequeno = desperdício de RAM; muito grande: sobrecarga.( default_tmp_storage_engine ) = default_tmp_storage_engine =( Innodb_row_lock_waits ) = 13945 / 120277 = 0.12 /sec-- Com que frequência há um atraso na obtenção de um bloqueio de linha. -- Pode ser causado por consultas complexas que podem ser otimizadas.( innodb_flush_neighbors ) = innodb_flush_neighbors = 1-- Uma pequena otimização ao gravar blocos no disco. -- Use 0 para unidades SSD; 1 para HDD.( innodb_io_capacity ) = 200-- Operações de E/S por segundo capazes em disco. 100 para unidades lentas; 200 para unidades giratórias; 1000-2000 para SSDs; multiplique pelo fator RAID. Limita solicitações de E/S de gravação por segundo (IOPS). -- Para começar: HDD: 200; SSD: 2000.( innodb_flush_log_at_trx_commit ) = 1-- 1 = seguro; 2 = mais rápido -- (Você decide) Use 1, junto com sync_binlog (agora 0)=1 para o maior nível de tolerância a falhas. 0 é melhor para velocidade. 2 é um meio-termo entre 0 e 1.( sync_binlog ) = 0-- Use 1 para maior segurança, com algum custo de E/S =1 pode levar a muitos "fim de consulta"; =0 pode levar a "binlog em posição impossível" e perder transações em uma falha, mas é mais rápido. 0 é aceitável para Galera.( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF-- Se deve registrar todos os Deadlocks. -- Se você for atormentado por Deadlocks, ative isso. Cuidado: Se você tiver muitos deadlocks, isso pode gravar muita coisa no disco.( min( tmp_table_size, max_heap_table_size ) ) = (min( 256M, 256M )) / 16384M = 1.6%-- Porcentagem de RAM a ser alocada quando for necessária uma tabela MEMORY (por tabela) ou uma tabela temporária dentro de um SELECT (por tabela temporária para alguns SELECTs). Muito alto pode levar à troca. -- Diminua tmp_table_size (agora 268435456) e max_heap_table_size (agora 268435456) para, digamos, 1% de ram.( innodb_ft_result_cache_limit ) = 2,000,000,000 / 16384M = 11.6%-- Limite de bytes no conjunto de resultados FULLTEXT. (Ele aumenta conforme necessário.) -- Reduza a configuração.( character_set_client ) = character_set_client = utf8mb3-- -- Se você for usar texto de outros lugares além da Europa Ocidental, considere mudar para utf8mb4. (Além do escopo desta discussão.)( character_set_connection ) = character_set_connection = utf8mb3--( character_set_results ) = character_set_results = utf8mb3--( log_warnings ) = log_warnings = 4( Max_statement_time_exceeded ) = 594 / 120277 = 18 /HR-- Identificar instruções de longa duração que foram abortadas.( local_infile ) = local_infile = ON-- local_infile (agora ON) = ON é um potencial problema de segurança( Com_set_option ) = 118817521 / 120277 = 987 /sec-- Frequência de uso da opção SET -- Por que tão alta?( Questions ) = 937764234 / 120277 = 7796 /sec-- Consultas (fora do SP) -- "qps" -- >2000 pode estar estressando o servidor( Queries ) = 937764234 / 120277 = 7796 /sec-- Consultas (incluindo dentro do SP) -- >3000 podem estar estressando o servidor( Created_tmp_tables ) = 4922791 / 120277 = 41 /sec-- Frequência de criação de tabelas "temporárias" como parte de SELECTs complexos.( Created_tmp_disk_tables ) = 3193850 / 120277 = 27 /sec-- Frequência de criação de tabelas "temp" de disco como parte de SELECTs complexos -- aumente tmp_table_size (agora 268435456) e max_heap_table_size (agora 268435456). Verifique as regras para tabelas temporárias sobre quando MEMORY é usado em vez de MyISAM. Talvez pequenas alterações de esquema ou consulta possam evitar MyISAM. Melhores índices e reformulação de consultas provavelmente ajudarão.( Created_tmp_disk_tables / Created_tmp_tables ) = 3,193,850 / 4922791 = 64.9%-- Porcentagem de tabelas temporárias que foram transferidas para o disco -- Talvez aumentar tmp_table_size (agora 268435456) e max_heap_table_size (agora 268435456); melhorar índices; evitar blobs, etc.( tmp_table_size ) = 256M-- Limite no tamanho das tabelas temporárias de MEMÓRIA usadas para suportar um SELECT -- Diminua tmp_table_size (agora 268435456) para evitar ficar sem RAM. Talvez não mais que 64M.( Com_show_variables ) = 240531 / 120277 = 2 /sec-- MOSTRAR VARIÁVEIS ... -- Por que você está solicitando as VARIÁVEIS com tanta frequência?( Com_insert + Com_delete + Com_delete_multi + Com_replace + Com_update + Com_update_multi ) = (673370 + 23336 + 8020 + 5 + 117459591 + 0) / 120277 = 982 /sec-- gravações/seg -- 50 gravações/seg + descargas de log provavelmente atingirão o máximo da capacidade de gravação de E/S de unidades normais( binlog_format ) = binlog_format = MIXED-- DECLARAÇÃO/LINHA/MISTO. -- LINHA é preferido por 5,7 (10,3)( expire_logs_days ) = 1.9867897033691E-8M = 0.0208-- Quão cedo para purgar binlog automaticamente (após tantos dias). Sendo substituído por binlog_expire_logs_seconds. -- Muito grande (ou zero) = consome espaço em disco; muito pequeno = precisa responder rapidamente a falhas de rede/máquina. (Não relevante se log_bin (agora OFF) = OFF)( Max_used_connections ) = 604-- Marca d'água alta para conexões -- Muitas conexões inativas são aceitáveis; mais de 100 conexões ativas provavelmente serão um problema. Max_used_connections (agora 604) não as distingue; Threads_running (agora 603) é instantâneo.( Connections ) = 118801097 / 120277 = 987 /sec-- Conexões -- Aumentar wait_timeout (agora 28800); usar pooling?( Threads_connected / max_connections ) = 604 / 700 = 86.3%-- Threads, como uma % do máximo permitido -- Aumentar max_connection, diminuir MaxClients do Apache, diminuir wait_timeout (agora 28800)( Threads_running - 1 ) = 603 - 1 = 602-- Threads ativos (simultaneidade quando dados são coletados) -- Otimize consultas e/ou esquemas. Um valor maior que, digamos, o dobro do número de núcleos de CPU provavelmente indica sobrecarga.( Threads_running / thread_cache_size ) = 603 / 256 = 2.36-- Threads: atuais / em cache (não relevante ao usar pool de threads) -- Otimizar consultas( thread_pool_max_threads ) = 65,536-- Uma das muitas configurações para o pool de threads do MariaDB -- Reduza o valor.Você tem o Query Cache pela metade. Você deve definir query_cache_type = OFF e query_cache_size = 0 . Há (de acordo com um rumor) um 'bug' no código QC que deixa algum código ligado a menos que você desative ambas as configurações.
Anormalmente pequeno:
Anormalmente grande: