Alguns dias atrás eu configurei um servidor MySQL escravo (Mysql Community Server 8.0.21). Até agora só o usamos para backup usando mysqldump. Notei que ele estava usando muita memória, mas não me importei porque usei alguns parâmetros conf que regulam automaticamente a quantidade de memória que ele usaria, e o VPS era dedicado ao servidor MySQL (se eu pagar pelo memória inteira, por que não usar, certo?).
Hoje, logo pela manhã eu procuro meu Zabbix e percebo que o escravo não estava funcionando, então eu faço ssh nele, reinicio o MySQL e tento iniciar o escravo usandoINICIAR ESCRAVO. E foi isso que consegui:
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
Não tenho certeza do que causou o problema em primeiro lugar (este é o meu my.cnf)
[client]
# Usado apenas para casos especificos
port = 3306 # Porta parada
socket = /var/run/mysqld/mysqld.sock
[mysql]
# Configurações do cliente
auto-rehash # Auto completar
[mysqld]
# Configuração do servidor
pid_file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
log_error = /var/log/mysql/error.log
user = mysql
bind_address = 0.0.0.0 # Ouve todos os endereços
# Configurações genericas do servidor
max_allowed_packet = 32M # Tamanho maximo do pacote.
max_connections = 2000 # Maximo de coneções
open_files_limit = 10000 # Maximo de arquivos abertos
tmp_table_size = 64M # Limite tamanho tabela em mem
max_heap_table_size = 64M # Limite tamanho tabela em mem
tmpdir = /tmp # Diretorio /tmp/
default_storage_engine = InnoDB # Engine default
skip_name_resolve # Desabilita resolução DNS
# Configurações de log binario
log_bin = mysql-bin # Arquivo de log binario
relay-log = mysql-relay-bin
log-slave-updates = 1 # Log de update no slave
read-only = 1 # Apenas leitura
binlog-format = mixed # Formato
server_id = 2 # Identifica servidor para log
max_binlog_size = 256M # Tamanho maximo log binario
binlog_expire_logs_seconds = 604800 # Max tempo log binario
innodb_flush_log_at_trx_commit = 1 # Proteção de dados
sync_binlog = 1 # Somente pra replicação
# Configurações especificas do InnoDB
innodb_dedicated_server = ON # Autoconf InnoDB
innodb_io_capacity = 2000 # Quantas escritas por segundo
innodb_read_io_threads = 64 # Threads de leitura
innodb_write_io_threads = 64 # Threads de escrita
innodb_thread_concurrency = 0 # Auto dectecta threads
# Slow query log
slow_query_log = 1 # Guarda queries lentas
long_query_time = 1.0 # Tempo query
# Funções dentro do Mysql
log_bin_trust_function_creators = 1; # Permite funções criadas
Não tenho ideia de como consertar isso. Devo fazer todo o despejo de novo? Como posso evitar que isso aconteça novamente?
ATUALIZAR:
Este é o resultado doMOSTRAR STATUS DE ESCRAVO:
mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: IP_ADDR
Master_User: USER
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000011
Read_Master_Log_Pos: 178887867
Relay_Log_File: pergamum-relay-bin.000029
Relay_Log_Pos: 178888082
Relay_Master_Log_File: mysql-bin.000011
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 13124
Last_Error: Slave failed to initialize relay log info structure from the repository
Skip_Counter: 0
Exec_Master_Log_Pos: 178887867
Relay_Log_Space: 0
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 13124
Last_SQL_Error: Slave failed to initialize relay log info structure from the repository
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_UUID: bef45e1b-99d6-11ea-a355-3e2547e4f083
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 200819 09:58:32
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)
ATUALIZAR
Conforme solicitado, aqui estão as variáveis de ambos os servidores.
MESTRE
GLOBAL STATUS:
https://gist.github.com/IamRichter/ef4993bbf65883baa366ff30d73b9644
GLOBAL VARIABLES:
https://gist.github.com/IamRichter/dbf08facd364548529b07bc0cbd6b2e6
ESCRAVO
GLOBAL STATUS:
https://gist.github.com/IamRichter/fcdff1111ed52fc859ebac46a2dbfc27
GLOBAL VARIABLES:
https://gist.github.com/IamRichter/53c5638fb3c6064fd51eab332660f07f
Responder1
Infelizmente, as VARIÁVEIS/STATUS não mostram nenhuma razão óbvia para o problema que você está tendo. Talvezo buffer_pool_size alto está comprimindo muito a RAM, mas duvido. Aqui estão minhas análises (primária e depois réplica):
Observações:
- Versão: 8.0.20
- 16 GB de RAM
- Tempo de atividade = 2d 02:30:41
- Você não está executando no Windows.
- Executando a versão de 64 bits
- Você parece estar executando inteiramente (ou principalmente) o InnoDB.
As questões mais importantes:
Aumente table_open_cachese o sistema operacional permitir. Por que tantas mesas?
Eu acho (sem qualquer prova) que não é aconselhável usar qualquer coisa, mas "2" para innodb_log_files_in_group MariaDB já está ignorando; Eu não ouvi falar da Oracle.
Existem alguns indícios de consultas mal formuladas. Verhttp://mysql.rjweb.org/doc.php/mysql_análise#slow_queries_and_slowlogpara analisar o slowlog.
Você teve algum motivo para ter binlog_format=MIXED?
Detalhes e outras observações:
( Opened_tables ) = 452,365 / 181841 = 2.5 /sec-- Frequência de abertura de tabelas -- aumenta table_open_cache (agora 3995)
( Table_open_cache_overflows ) = 448,306 / 181841 = 2.5 /sec
-- Pode ser necessário aumentar table_open_cache (agora 3995)
( Table_open_cache_misses ) = 452,365 / 181841 = 2.5 /sec
-- Pode ser necessário aumentar table_open_cache (agora 3995)
( innodb_lru_scan_depth * innodb_page_cleaners ) = 1,024 * 4 = 4,096-- Quantidade de trabalho para limpadores de páginas a cada segundo. - "InnoDB: page_cleaner: loop pretendido de 1000ms levado ..." pode ser corrigido diminuindo lru_scan_profundidade: Considere 1000 / innodb_page_cleaners (agora 4). Verifique também se há troca.
( innodb_page_cleaners / innodb_buffer_pool_instances ) = 4 / 8 = 0.5-- innodb_page_cleaners -- Recomendo configurar innodb_page_cleaners (agora 4) para innodb_buffer_pool_instances (agora 8)
( innodb_lru_scan_depth ) = 1,024
- "InnoDB: page_cleaner: loop pretendido de 1000ms levado ..." pode ser corrigido diminuindo lru_scan_profundidade
( Innodb_pages_written / Innodb_buffer_pool_write_requests ) = 2,102,149 / 7377368 = 28.5%-- Escreva solicitações que tiveram que chegar ao disco -- Verifique innodb_buffer_pool_size (agora 12884901888)
( innodb_log_files_in_group ) = 9-- Número de arquivos de log do InnoDB -- 2 é provavelmente o único valor razoável. Um grande número pode causar problemas de desempenho.
( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 1,773,668,352 / (181841 / 3600) / 9 / 1024M = 0.00363-- Proporção -- (ver ata)
( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 181,841 / 60 * 1024M / 1773668352 = 1,834-- Minutos entre rotações de log do InnoDB A partir da versão 5.6.8, isso pode ser alterado dinamicamente; 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 1073741824). (Não é possível alterar na AWS.)
( innodb_flush_method ) = innodb_flush_method = O_DIRECT_NO_FSYNC-- Como o InnoDB deve solicitar ao sistema operacional para escrever blocos. Sugira O_DIRECT ou O_ALL_DIRECT (Percona) para evitar buffer duplo. (Pelo menos para Unix.) Consulte chrischandler para advertências sobre O_ALL_DIRECT
( Innodb_row_lock_time_avg ) = 1,886-- Tempo médio para bloquear uma linha (milissegundos) -- Consultas possivelmente conflitantes; possivelmente varreduras de tabela.
( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF-- Se deseja registrar todos os impasses. - Se você está atormentado com Deadlocks, ative esta opção. Cuidado: Se você tiver muitos deadlocks, isso poderá gravar muito no disco.
( max_connections ) = 2,000-- Número máximo de conexões (threads). Impacta diversas alocações. -- Se max_connections (agora 2000) for muito alto e várias configurações de memória estiverem altas, você poderá ficar sem RAM.
( Handler_read_rnd_next / Com_select ) = 46,219,107,293 / 7120289 = 6,491-- Média de linhas verificadas por SELECT. (aproximadamente) -- Considere aumentar read_buffer_size (agora 131072)
( (Com_insert + Com_update + Com_delete + Com_replace) / Com_commit ) = (358564 + 419704 + 462 + 0) / 2006877 = 0.388-- Instruções por Commit (assumindo todo o InnoDB) -- Baixo: Pode ajudar a agrupar consultas em transações; Alto: transações longas sobrecarregam várias coisas.
( Select_scan ) = 4,386,291 / 181841 = 24 /sec-- varreduras completas de tabelas -- Adicionar índices/otimizar consultas (a menos que sejam tabelas pequenas)
( Select_scan / Com_select ) = 4,386,291 / 7120289 = 61.6%--% de seleções fazendo varredura completa da tabela. (Pode ser enganado por rotinas armazenadas.) - Adicionar índices/otimizar consultas
( binlog_format ) = binlog_format = MIXED-- DECLARAÇÃO/LINHA/MISTA. - ROW é preferido por 5,7 (10,3)
( Aborted_clients ) = 175,398 / 181841 = 0.96 /sec-- Threads interrompidos devido a wait_timeout -- Aumenta wait_timeout (agora 28800); seja legal, use desconectar
( Connections ) = 3,544,963 / 181841 = 19 /sec-- Conexões -- Aumenta wait_timeout (agora 28800); usar pooling?
Anormalmente pequeno: Arquivos_abertos = 3
Anormalmente grande: Com_do = 0,02 /HR Com_release_savepoint = 2,3 /HR Com_rollback_to_savepoint = 0,11 /seg Com_savepoint = 2,3 /HR Com_show_charsets = 0,44 /HR Com_show_create_db = 2,3 /HR Com_show_create_func = 2,5 /HR Com_show_create_proc = 1,5 /HR Com_show_create_trigger = 0,12 /HR Com_show_function_status = 2,5 /HR Com_show_procedure_status = 2,5 /HR Com_show_table_status = 0,11 /seg Com_show_triggers = 0,11 /seg Criados_tmp_files = 20 /seg Handler_read_key = 139335 /seg Handler_read_next = 316713 /seg Handler_read_rnd = 46465 /seg Handler_savepoint = 2,3 /HR Handler_savepoint_rollback = 0,11 /seg Innodb_buffer_pool_read_requests = 1095747 /seg Innodb_num_open_files = 3.996 Innodb_system_rows_inserted = 0,97 /HR Innodb_system_rows_read = 1,99e+7 Innodb_system_rows_updated = 0,095 /seg Open_table_definitions = 2.000 Select_full_range_join = 2,1 /seg Select_full_range_join / Com_select = 5 0,5% back_log / max_connections = 100,0% innodb_max_dirty_pages_pct_lwm = 10 innodb_read_io_threads = 64 innodb_undo_tablespaces = 2 innodb_write_io_threads = 64 max_error_count = 1.024 max_length_for_sort_data = 4.096
Sequências anormais: default_authentication_plugin = caching_sha2_password event_scheduler = ON innodb_dedicated_server = ON innodb_fast_shutdown = 1 otimizador_trace = ativado = desativado, one_line = desativado otimizador_trace_features = guloso_search = ativado, range_optimizer = ativado, intervalo dinâmico = ativado, repetido_subselect = ativado escravo_rows_search_algorithms = INDEX_SCAN, HASH _SCAN
sf1030756-M
Observações:
- Versão: 8.0.21
- 6 GB de RAM
- Tempo de atividade = 5d 23:59:45
- Tem certeza de que este foi um SHOW GLOBAL STATUS? -- Ou esta réplica simplesmente não está ocupada?
- Você não está executando no Windows.
- Executando a versão de 64 bits
- Você parece estar executando inteiramente (ou principalmente) o InnoDB.
As questões mais importantes:
Tal como acontece com o Primário - table_open_cache, innodb_log_files_in_group,binlog_format
Verifique se há troca. innodb_buffer_pool_sizeé bastante grande para os 6 GB de RAM.
Diminuir max_connectionspara 100 – Você não usou mais de 5 até agora; o atual 2000 é um fardo para a RAM.
Detalhes e outras observações:
( table_open_cache ) = 3,995-- Número de descritores de tabela para armazenar em cache -- Várias centenas geralmente é bom.
( Table_open_cache_misses / (Table_open_cache_hits + Table_open_cache_misses) ) = 56,436 / (1107115 + 56436) = 4.9%-- Eficácia de table_open_cache. -- Aumente table_open_cache (agora 3995) e verifique table_open_cache_instances (agora 16).
( innodb_lru_scan_depth * innodb_page_cleaners ) = 1,024 * 4 = 4,096-- Quantidade de trabalho para limpadores de páginas a cada segundo. - "InnoDB: page_cleaner: loop pretendido de 1000ms levado ..." pode ser corrigido diminuindo lru_scan_profundidade: Considere 1000 / innodb_page_cleaners (agora 4). Verifique também se há troca.
( innodb_page_cleaners / innodb_buffer_pool_instances ) = 4 / 8 = 0.5-- innodb_page_cleaners -- Recomendo configurar innodb_page_cleaners (agora 4) para innodb_buffer_pool_instances (agora 8)
( innodb_lru_scan_depth ) = 1,024
- "InnoDB: page_cleaner: loop pretendido de 1000ms levado ..." pode ser corrigido diminuindo lru_scan_profundidade
( Innodb_pages_written / Innodb_buffer_pool_write_requests ) = 25,132 / 57003 = 44.1%-- Escreva solicitações que tiveram que chegar ao disco -- Verifique innodb_buffer_pool_size (agora 5368709120)
( innodb_log_files_in_group ) = 5-- Número de arquivos de log do InnoDB -- 2 é provavelmente o único valor razoável. Um grande número pode causar problemas de desempenho.
( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 22,224,896 / (518385 / 3600) / 5 / 512M = 5.7e-5-- Proporção -- (ver ata)
( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 518,385 / 60 * 512M / 22224896 = 208,704-- Minutos entre rotações de log do InnoDB A partir da versão 5.6.8, isso pode ser alterado dinamicamente; 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 536870912). (Não é possível alterar na AWS.)
( innodb_flush_method ) = innodb_flush_method = O_DIRECT_NO_FSYNC-- Como o InnoDB deve solicitar ao sistema operacional para escrever blocos. Sugira O_DIRECT ou O_ALL_DIRECT (Percona) para evitar buffer duplo. (Pelo menos para Unix.) Consulte chrischandler para advertências sobre O_ALL_DIRECT
( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF-- Se deseja registrar todos os impasses. - Se você está atormentado com Deadlocks, ative esta opção. Cuidado: Se você tiver muitos deadlocks, isso poderá gravar muito no disco.
( max_connections ) = 2,000-- Número máximo de conexões (threads). Impacta diversas alocações. -- Se max_connections (agora 2000) for muito alto e várias configurações de memória estiverem altas, você poderá ficar sem RAM.
( (Com_show_create_table + Com_show_fields) / Questions ) = (8605 + 17405) / 313218 = 8.3%- Estrutura impertinente - gastando muito esforço redescobrindo o esquema. - Reclame com o fornecedor terceirizado.
( tmp_table_size ) = 64M- Limite no tamanho deMEMÓRIAtabelas temporárias usadas para suportar um SELECT - Diminuir tmp_table_size (agora 67108864) para evitar ficar sem RAM. Talvez não mais que 64 milhões.
( Select_full_join / Com_select ) = 8,884 / 123363 = 7.2%-- % de seleções que são junção sem índice -- Adiciona índice(s) adequado(s) às tabelas usadas em JOINs.
( Select_scan / Com_select ) = 107,352 / 123363 = 87.0%--% de seleções fazendo varredura completa da tabela. (Pode ser enganado por rotinas armazenadas.) - Adicionar índices/otimizar consultas
( Com_admin_commands / Queries ) = 6,755 / 320422 = 2.1%-- Porcentagem de consultas que são comandos "admin". -- O que está acontecendo?
( binlog_format ) = binlog_format = MIXED-- DECLARAÇÃO/LINHA/MISTA. - ROW é preferido por 5,7 (10,3)
( log_slow_slave_statements ) = log_slow_slave_statements = OFF-- (5.6.11, 5.7.1) Por padrão, as instruções replicadas não aparecerão no slowlog; isso faz com que eles apareçam. -- Pode ser útil no slowlog ver gravações que podem estar interferindo nas leituras da réplica.
( thread_cache_size / Max_used_connections ) = 28 / 5 = 560.0%
- Não há vantagem em ter o cache do thread maior que o número provável de conexões. Desperdiçar espaço é a desvantagem.
( thread_stack * max_connections ) = (286720 * 2000) / 6144M = 8.9%-- Alocação mínima de memória para max_connections. -- Menor max_connections (agora 2000)
Anormalmente pequeno: Bytes_recebidos = 58 /seg Com_insert = 0 Com_update = 0 Criados_tmp_files = 0,035 /HR Handler_update = 2,2 /HR Innodb_buffer_pool_write_requests = 0,11 /seg Innodb_buffer_pool_write_requests / Innodb_buffer_pool_pages_flushed = 2,27 Innodb_dblwr_pages_ escrito / Innodb_dblwr_writes = 1 Innodb_pages_created = 1 /HR Innodb_rows_deleted + Innodb_rows_inserted = 0 Innodb_rows_inserted = 0 Innodb_rows_updated = 0 Select_range = 0 Select_range / Com_select = 0
Anormalmente grande: Com_do = 0,014 /HR Com_show_create_func = 0,15 /HR Com_slave_start = 0,028 /HR Innodb_num_open_files = 3.996 Innodb_system_rows_read = 1.91e+7 Innodb_system_rows_updated = 2,2 /HR Open_table_definitions = 2.000 back_log / max_connections = 10 0,0% innodb_max_dirty_pages_pct_lwm = 10 innodb_read_io_threads = 64 innodb_undo_tablespaces = 2 innodb_write_io_threads = 64 max_error_count = 1.024 max_length_for_sort_data = 4.096 optimizador_trace_offset = --1 performance_schema_error_size = 4.772 performance_schema_max_cond_classes = 100 performance_schema_max_mutex_classes = 300 performance_schema_max_rwlock_classes = 60 performance_schema_max_stage _classes = 175 performance_schema_max_statement_classes = 218 performance_schema_max_thread_classes = 100
Sequências anormais: default_authentication_plugin = caching_sha2_password event_scheduler = ON innodb_dedicated_server = ON innodb_fast_shutdown = 1 otimizador_trace = ativado = desativado, one_line = desativado otimizador_trace_features = guloso_search = ativado, range_optimizer = ativado, intervalo_dinâmico = ativado, repetido_subselect = ativado somente leitura = ativado escravo_rows_search_algorithms = VERIFICAR, HASH_SCAN
sf1030756-S