Пару дней назад я настроил подчиненный сервер MySQL (Mysql Community Server 8.0.21). Пока мы используем его только для резервного копирования с помощью mysqldump. Я заметил, что он использует слишком много памяти, но меня это не смутило, потому что я использовал несколько параметров конфигурации, которые автоматически регулируют объем используемой памяти, а VPS был выделен для сервера MySQL (если я плачу за всю память, почему бы не использовать ее, верно?).
Сегодня первым делом с утра я захожу в свой Zabbix и замечаю, что подчиненный сервер не работает, поэтому я подключаюсь к нему по ssh, перезапускаю MySQL и пытаюсь запустить подчиненный сервер с помощьюНАЧАТЬ РАБОЧИЙ. И вот что у меня получилось:
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
Не уверен, что изначально вызвало проблему (это мой 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
Я понятия не имею, как это исправить. Мне что, снова делать весь дамп? Как предотвратить повторение этого?
ОБНОВЛЯТЬ:
Это результат того, чтоПОКАЗАТЬ СТАТУС РАБОЧЕГО ПОЛОЖЕНИЯ:
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)
ОБНОВЛЯТЬ
Как и просили, вот переменные с обоих серверов.
ВЛАДЕЛЕЦ
GLOBAL STATUS:
https://gist.github.com/IamRichter/ef4993bbf65883baa366ff30d73b9644
GLOBAL VARIABLES:
https://gist.github.com/IamRichter/dbf08facd364548529b07bc0cbd6b2e6
РАБ
GLOBAL STATUS:
https://gist.github.com/IamRichter/fcdff1111ed52fc859ebac46a2dbfc27
GLOBAL VARIABLES:
https://gist.github.com/IamRichter/53c5638fb3c6064fd51eab332660f07f
решение1
Увы, ПЕРЕМЕННЫЕ/СТАТУСЫ не показывают никаких очевидных причин возникшей у вас проблемы. Может бытьвысокий buffer_pool_size слишком сильно сжимает оперативную память, но я сомневаюсь в этом. Вот мои анализы (первичный, затем реплика):
Наблюдения:
- Версия: 8.0.20
- 16 ГБ оперативной памяти
- Время безотказной работы = 2д 02:30:41
- Вы не работаете на Windows.
- Запуск 64-битной версии
- Судя по всему, вы используете полностью (или в основном) InnoDB.
Более важные вопросы:
Увеличьте table_open_cache, если ОС позволит. Зачем так много таблиц??
Я считаю (без каких-либо доказательств), что неразумно использовать что-либо, кроме «2», поскольку innodb_log_files_in_group MariaDB уже игнорирует это; об Oracle я не слышал.
Есть некоторые признаки плохо сформулированных запросов. Смотретьhttp://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlogдля анализа slowlog.
У вас была какая-то причина для этого binlog_format=MIXED?
Подробности и другие наблюдения:
( Opened_tables ) = 452,365 / 181841 = 2.5 /sec-- Частота открытия таблиц -- увеличить table_open_cache (теперь 3995)
( Table_open_cache_overflows ) = 448,306 / 181841 = 2.5 /sec
-- Возможно, потребуется увеличить table_open_cache (сейчас 3995)
( Table_open_cache_misses ) = 452,365 / 181841 = 2.5 /sec
-- Возможно, потребуется увеличить table_open_cache (сейчас 3995)
( innodb_lru_scan_depth * innodb_page_cleaners ) = 1,024 * 4 = 4,096-- Объем работы для очистки страниц каждую секунду. -- "InnoDB: page_cleaner: 1000ms expected loop took ..." можно исправить, уменьшив lru_scan_depth: рассмотрите 1000 / innodb_page_cleaners (теперь 4). Также проверьте подкачку.
( innodb_page_cleaners / innodb_buffer_pool_instances ) = 4 / 8 = 0.5-- innodb_page_cleaners -- Рекомендуется установить innodb_page_cleaners (теперь 4) на innodb_buffer_pool_instances (теперь 8)
( innodb_lru_scan_depth ) = 1,024
-- "InnoDB: page_cleaner: предполагаемый цикл занял 1000 мс ..." можно исправить, уменьшив lru_scan_depth
( Innodb_pages_written / Innodb_buffer_pool_write_requests ) = 2,102,149 / 7377368 = 28.5%-- Запросы на запись, которые должны были попасть на диск -- Проверьте innodb_buffer_pool_size (теперь 12884901888)
( innodb_log_files_in_group ) = 9-- Количество файлов журнала InnoDB -- 2, вероятно, единственное разумное значение. Большое количество может вызвать проблемы с производительностью.
( 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-- Соотношение -- (см. протокол)
( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 181,841 / 60 * 1024M / 1773668352 = 1,834-- Минуты между ротациями журнала InnoDB Начиная с версии 5.6.8, это можно изменять динамически; не забудьте также изменить my.cnf. -- (Рекомендация 60 минут между ротациями несколько произвольна.) Отрегулируйте innodb_log_file_size (теперь 1073741824). (Нельзя изменить в AWS.)
( innodb_flush_method ) = innodb_flush_method = O_DIRECT_NO_FSYNC-- Как InnoDB должна просить ОС записывать блоки. Предложите O_DIRECT или O_ALL_DIRECT (Percona), чтобы избежать двойной буферизации. (По крайней мере для Unix.) См. chrischandler для предупреждения об O_ALL_DIRECT
( Innodb_row_lock_time_avg ) = 1,886-- Среднее время блокировки строки (миллисекунды) -- Возможно, конфликтующие запросы; возможно, сканирование таблиц.
( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF-- Регистрировать ли все взаимоблокировки. -- Если вас мучают взаимоблокировки, включите это. Внимание: если у вас много взаимоблокировок, это может привести к записи большого объема данных на диск.
( max_connections ) = 2,000-- Максимальное количество подключений (потоков). Влияет на различные распределения. -- Если max_connections (теперь 2000) слишком велико и различные настройки памяти высоки, у вас может закончиться ОЗУ.
( Handler_read_rnd_next / Com_select ) = 46,219,107,293 / 7120289 = 6,491-- Среднее количество строк, сканируемых за SELECT. (приблизительно) -- Рассмотрите возможность увеличения read_buffer_size (сейчас 131072)
( (Com_insert + Com_update + Com_delete + Com_replace) / Com_commit ) = (358564 + 419704 + 462 + 0) / 2006877 = 0.388-- Операторов на фиксацию (предполагается, что все InnoDB) -- Низкий: может помочь сгруппировать запросы в транзакции; Высокий: длинные транзакции нагружают различные компоненты.
( Select_scan ) = 4,386,291 / 181841 = 24 /sec-- полное сканирование таблиц -- добавление индексов / оптимизация запросов (если только это не маленькие таблицы)
( Select_scan / Com_select ) = 4,386,291 / 7120289 = 61.6%-- % выборок, выполняющих полное сканирование таблицы. (Могут быть обмануты хранимыми процедурами.) -- Добавить индексы / оптимизировать запросы
( binlog_format ) = binlog_format = MIXED-- ЗАЯВЛЕНИЕ/СТРОКА/СМЕШАННАЯ. -- СТРОКА предпочитается 5.7 (10.3)
( Aborted_clients ) = 175,398 / 181841 = 0.96 /sec-- Потоки, поднятые из-за wait_timeout -- Увеличьте wait_timeout (теперь 28800); будьте вежливы, используйте отключение
( Connections ) = 3,544,963 / 181841 = 19 /sec-- Подключения -- Увеличить wait_timeout (теперь 28800); использовать пул?
Аномально маленький: Открытые_файлы = 3
Аномально большой: Com_do = 0,02 /HR Com_release_savepoint = 2,3 /HR Com_rollback_to_savepoint = 0,11 /сек 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 /сек Com_show_triggers = 0,11 /сек Created_tmp_files = 20 /сек Handler_read_key = 139335 /сек Handler_read_next = 316713 / сек Handler_read_rnd = 46465 / сек Handler_savepoint = 2,3 / HR Handler_savepoint_rollback = 0,11 / сек Innodb_buffer_pool_read_requests = 1095747 / сек Innodb_num_open_files = 3996 Innodb_system_rows_inserted = 0,97 / HR Innodb_system_rows_read = 1,99e+7 Innodb_system_rows_updated = 0,095 / сек Open_table_definitions = 2000 Select_full_range_join = 2,1 / сек Select_full_range_join / Com_select = 5,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
Ненормальные строки: default_authentication_plugin = caching_sha2_password event_scheduler = ON innodb_dedicated_server = ON innodb_fast_shutdown = 1 optimizer_trace = enabled=off,one_line=off optimizer_trace_features = greedy_search=on, range_optimizer=on, dynamic_range=on, repeat_subselect=on slave_rows_search_algorithms = INDEX_SCAN, HASH_SCAN
sf1030756-M
Наблюдения:
- Версия: 8.0.21
- 6 ГБ оперативной памяти
- Время безотказной работы = 5дн 23:59:45
- Вы уверены, что это был ШОУ ГЛОБАЛЬНЫЙ СТАТУС? -- Или эта реплика просто не занята??
- Вы не работаете на Windows.
- Запуск 64-битной версии
- Судя по всему, вы используете полностью (или в основном) InnoDB.
Более важные вопросы:
Как и в случае с первичным -- table_open_cache, innodb_log_files_in_group,binlog_format
Проверьте наличие подкачки. innodb_buffer_pool_sizeдовольно большой для 6 ГБ оперативной памяти.
Уменьшите max_connectionsдо 100 — до сих пор вы не использовали больше 5; текущие 2000 — это нагрузка на оперативную память.
Подробности и другие наблюдения:
( table_open_cache ) = 3,995-- Количество дескрипторов таблиц для кэширования. Обычно достаточно нескольких сотен.
( Table_open_cache_misses / (Table_open_cache_hits + Table_open_cache_misses) ) = 56,436 / (1107115 + 56436) = 4.9%-- Эффективность table_open_cache. -- Увеличьте table_open_cache (теперь 3995) и проверьте table_open_cache_instances (теперь 16).
( innodb_lru_scan_depth * innodb_page_cleaners ) = 1,024 * 4 = 4,096-- Объем работы для очистки страниц каждую секунду. -- "InnoDB: page_cleaner: 1000ms expected loop took ..." можно исправить, уменьшив lru_scan_depth: рассмотрите 1000 / innodb_page_cleaners (теперь 4). Также проверьте подкачку.
( innodb_page_cleaners / innodb_buffer_pool_instances ) = 4 / 8 = 0.5-- innodb_page_cleaners -- Рекомендуется установить innodb_page_cleaners (теперь 4) на innodb_buffer_pool_instances (теперь 8)
( innodb_lru_scan_depth ) = 1,024
-- "InnoDB: page_cleaner: предполагаемый цикл занял 1000 мс ..." можно исправить, уменьшив lru_scan_depth
( Innodb_pages_written / Innodb_buffer_pool_write_requests ) = 25,132 / 57003 = 44.1%-- Запросы на запись, которые должны были попасть на диск -- Проверьте innodb_buffer_pool_size (теперь 5368709120)
( innodb_log_files_in_group ) = 5-- Количество файлов журнала InnoDB -- 2, вероятно, единственное разумное значение. Большое количество может вызвать проблемы с производительностью.
( 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-- Соотношение -- (см. протокол)
( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 518,385 / 60 * 512M / 22224896 = 208,704-- Минуты между ротациями журнала InnoDB Начиная с версии 5.6.8, это можно изменять динамически; не забудьте также изменить my.cnf. -- (Рекомендация 60 минут между ротациями несколько произвольна.) Отрегулируйте innodb_log_file_size (теперь 536870912). (Нельзя изменить в AWS.)
( innodb_flush_method ) = innodb_flush_method = O_DIRECT_NO_FSYNC-- Как InnoDB должна просить ОС записывать блоки. Предложите O_DIRECT или O_ALL_DIRECT (Percona), чтобы избежать двойной буферизации. (По крайней мере для Unix.) См. chrischandler для предупреждения об O_ALL_DIRECT
( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF-- Регистрировать ли все взаимоблокировки. -- Если вас мучают взаимоблокировки, включите это. Внимание: если у вас много взаимоблокировок, это может привести к записи большого объема данных на диск.
( max_connections ) = 2,000-- Максимальное количество подключений (потоков). Влияет на различные распределения. -- Если max_connections (теперь 2000) слишком велико и различные настройки памяти высоки, у вас может закончиться ОЗУ.
( (Com_show_create_table + Com_show_fields) / Questions ) = (8605 + 17405) / 313218 = 8.3%-- Непослушный фреймворк -- трата больших усилий на повторное открытие схемы. -- Пожалуйтесь стороннему поставщику.
( tmp_table_size ) = 64M-- Ограничение по размеруОБЪЕМ ПАМЯТИвременные таблицы, используемые для поддержки SELECT -- Уменьшите tmp_table_size (теперь 67108864), чтобы избежать нехватки оперативной памяти. Возможно, не более 64M.
( Select_full_join / Com_select ) = 8,884 / 123363 = 7.2%-- % выборок, которые являются безиндексными соединениями -- Добавьте подходящий(ие) индекс(ы) к таблицам, используемым в соединениях.
( Select_scan / Com_select ) = 107,352 / 123363 = 87.0%-- % выборок, выполняющих полное сканирование таблицы. (Могут быть обмануты хранимыми процедурами.) -- Добавить индексы / оптимизировать запросы
( Com_admin_commands / Queries ) = 6,755 / 320422 = 2.1%-- Процент запросов, являющихся командами «администратора». -- Что происходит?
( binlog_format ) = binlog_format = MIXED-- ЗАЯВЛЕНИЕ/СТРОКА/СМЕШАННАЯ. -- СТРОКА предпочитается 5.7 (10.3)
( log_slow_slave_statements ) = log_slow_slave_statements = OFF-- (5.6.11, 5.7.1) По умолчанию реплицированные операторы не отображаются в slowlog; это приводит к их отображению. -- В slowlog может быть полезно увидеть записи, которые могут мешать чтению реплики.
( thread_cache_size / Max_used_connections ) = 28 / 5 = 560.0%
-- Нет никакого преимущества в том, чтобы кэш потоков был больше, чем вероятное количество соединений. Недостаток — пустая трата места.
( thread_stack * max_connections ) = (286720 * 2000) / 6144M = 8.9%-- Минимальное выделение памяти для max_connections. -- Уменьшение max_connections (теперь 2000)
Аномально маленький: Получено байт = 58 / сек Com_insert = 0 Com_update = 0 Создано_tmp_files = 0,035 / HR Обновление_обработчика = 2,2 / HR Запросы_записи_в_буфере_пула = 0,11 / сек Запросы_записи_в_буфере_пула / Сброшенные_страницы_буфера_пула = 2,27 Записанные_страницы_в_д_д_в_д_в_записанные / Записанные_страницы_в_д_в_д_в_записанные = 1 Создано_страниц = 1 / HR Удалено_строк + Удалено_строк = 0 Удалено_строк = 0 Удалено_строк = 0 Удалено_строк = 0 Удалено_строк / Удалено_строк = 0 Диапазон_выбора_строк / Удалено_строк = 0 Диапазон_выбора_строк
Аномально большой: Com_do = 0,014 /HR Com_show_create_func = 0,15 /HR Com_slave_start = 0,028 /HR Innodb_num_open_files = 3996 Innodb_system_rows_read = 1,91e+7 Innodb_system_rows_updated = 2,2 /HR Open_table_definitions = 2000 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 = 1024 max_length_for_sort_data = 4096 optimizer_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
Ненормальные строки: default_authentication_plugin = caching_sha2_password event_scheduler = ON innodb_dedicated_server = ON innodb_fast_shutdown = 1 optimizer_trace = enabled=off,one_line=off optimizer_trace_features = greedy_search=on, range_optimizer=on, dynamic_range=on, repeat_subselect=on read_only = ON slave_rows_search_algorithms = INDEX_SCAN, HASH_SCAN
sf1030756-S