数日前、スレーブ MySQL サーバー (Mysql Community Server 8.0.21) をセットアップしました。これまでのところ、mysqldump を使用したバックアップにのみ使用しています。メモリが多すぎることに気付きましたが、メモリ使用量を自動調整するいくつかの conf パラメータを使用しており、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 が大きいと RAM が圧迫されすぎていると思われますが、そうではないと思います。私の分析結果は次のとおりです (プライマリ、レプリカ):
観察:
- バージョン: 8.0.20
- 16 GBのRAM
- 稼働時間 = 2日 02:30:41
- Windows 上で実行されていません。
- 64ビット版を実行
- 完全に（または大部分）InnoDB を実行しているようです。
より重要な問題:
table_open_cacheOS が許可する場合は増やしてください。なぜこんなに多くのテーブルがあるのでしょうか?
私は (証拠はありませんが)、innodb_log_files_in_group MariaDB では既に無視されている「2」以外の値を使用するのは賢明ではないと思います。Oracle については聞いたことがありません。
クエリが適切に作成されていない兆候がいくつかあります。http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_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-- ページ クリーナーの 1 秒あたりの作業量。 -- 「InnoDB: page_cleaner: 意図したループに 1000 ミリ秒かかりました...」は、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: 1000ms の意図したループに要した時間...」は、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 が OS にブロックの書き込みを要求する方法。ダブル バッファリングを回避するには、O_DIRECT または O_ALL_DIRECT (Percona) をお勧めします。(少なくとも Unix の場合) O_ALL_DIRECT に関する注意事項については、chrischandler を参照してください。
( Innodb_row_lock_time_avg ) = 1,886-- 行をロックするのにかかる平均時間 (ミリ秒) -- 競合するクエリやテーブル スキャンの可能性があります。
( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF-- すべてのデッドロックをログに記録するかどうか。 -- デッドロックに悩まされている場合は、これをオンにします。注意: デッドロックが多数ある場合、ディスクに大量のデータが書き込まれる可能性があります。
( max_connections ) = 2,000-- 接続 (スレッド) の最大数。さまざまな割り当てに影響します。 -- max_connections (現在は 2000) が高すぎ、さまざまなメモリ設定が高い場合、RAM が不足する可能性があります。
( 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-- STATEMENT/ROW/MIXED。-- ROWは5.7（10.3）で優先されます。
( Aborted_clients ) = 175,398 / 181841 = 0.96 /sec-- wait_timeout によりスレッドがバンプされました -- wait_timeout を増やします (現在 28800)。親切に、disconnect を使用してください。
( 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 = 3,996 Innodb_system_rows_inserted = 0.97 /HR Innodb_system_rows_read = 1.99e+7 Innodb_system_rows_updated = 0.095 /秒 Open_table_definitions = 2,000 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 最大エラー数 = 1,024 ソートデータの最大長 = 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, repeated_subselect=on slave_rows_search_algorithms = INDEX_SCAN,HASH_SCAN
sf1030756-M
観察:
- バージョン: 8.0.21
- 6 GB の RAM
- 稼働時間 = 5日 23:59:45
- これは本当に SHOW GLOBAL STATUS でしたか? -- それとも、このレプリカは単にビジーではないのでしょうか??
- Windows 上で実行されていません。
- 64ビット版を実行
- 完全に（または大部分）InnoDB を実行しているようです。
より重要な問題:
プライマリーと同様にtable_open_cache、、、innodb_log_files_in_groupbinlog_format
スワッピングをチェックします。6GB innodb_buffer_pool_sizeの RAM に対してはかなり大きいです。
100 に下げるmax_connections- これまで 5 個以上使用していません。現在の 2000 個は RAM に負担をかけています。
詳細およびその他の観察事項:
( 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-- ページ クリーナーの 1 秒あたりの作業量。 -- 「InnoDB: page_cleaner: 意図したループに 1000 ミリ秒かかりました...」は、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: 1000ms の意図したループに要した時間...」は、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 が OS にブロックの書き込みを要求する方法。ダブル バッファリングを回避するには、O_DIRECT または O_ALL_DIRECT (Percona) をお勧めします。(少なくとも Unix の場合) O_ALL_DIRECT に関する注意事項については、chrischandler を参照してください。
( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF-- すべてのデッドロックをログに記録するかどうか。 -- デッドロックに悩まされている場合は、これをオンにします。注意: デッドロックが多数ある場合、ディスクに大量のデータが書き込まれる可能性があります。
( max_connections ) = 2,000-- 接続 (スレッド) の最大数。さまざまな割り当てに影響します。 -- max_connections (現在は 2000) が高すぎ、さまざまなメモリ設定が高い場合、RAM が不足する可能性があります。
( (Com_show_create_table + Com_show_fields) / Questions ) = (8605 + 17405) / 313218 = 8.3%-- 不適切なフレームワーク -- スキーマの再発見に多大な労力を費やしています。 -- サードパーティ ベンダーに苦情を申し立てます。
( tmp_table_size ) = 64M-- サイズの制限メモリSELECT をサポートするために使用される一時テーブル - RAM 不足を回避するために tmp_table_size (現在は 67108864) を減らします。おそらく 64 MB を超えないようにしてください。
( Select_full_join / Com_select ) = 8,884 / 123363 = 7.2%-- インデックスなし結合である選択の割合 -- JOIN で使用されるテーブルに適切なインデックスを追加します。
( Select_scan / Com_select ) = 107,352 / 123363 = 87.0%-- 選択の % が完全なテーブルスキャンを実行します。(ストアド ルーチンによって誤認される可能性があります。) -- インデックスを追加し、クエリを最適化します。
( Com_admin_commands / Queries ) = 6,755 / 320422 = 2.1%-- 「admin」コマンドであるクエリの割合。 -- 何が起こっているのでしょうか?
( binlog_format ) = binlog_format = MIXED-- STATEMENT/ROW/MIXED。-- ROWは5.7（10.3）で優先されます。
( log_slow_slave_statements ) = log_slow_slave_statements = OFF-- (5.6.11、5.7.1) デフォルトでは、レプリケートされたステートメントはスローログに表示されませんが、これにより表示されます。 -- スローログで、レプリカの読み取りに干渉する可能性のある書き込みを確認すると役立ちます。
( thread_cache_size / Max_used_connections ) = 28 / 5 = 560.0%
-- スレッド キャッシュを予想される接続数よりも大きくしても利点はありません。スペースを無駄にするのが欠点です。
( thread_stack * max_connections ) = (286720 * 2000) / 6144M = 8.9%-- max_connections のメモリ割り当ては最小限です。 -- max_connections を低くします (現在は 2000)
異常に小さい: Bytes_received = 58 /sec Com_insert = 0 Com_update = 0 Created_tmp_files = 0.035 /HR Handler_update = 2.2 /HR Innodb_buffer_pool_write_requests = 0.11 /sec Innodb_buffer_pool_write_requests / Innodb_buffer_pool_pages_flushed = 2.27 Innodb_dblwr_pages_written / 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
異常に大きい: 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 = 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 optimizer_trace_offset = --1 performance_schema_error_size = 4,772 パフォーマンス スキーマ最大接続クラス = 100 パフォーマンス スキーマ最大ミューテックスクラス = 300 パフォーマンス スキーマ最大 rwlock クラス = 60 パフォーマンス スキーマ最大ステージクラス = 175 パフォーマンス スキーマ最大ステートメントクラス = 218 パフォーマンス スキーマ最大スレッドクラス = 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, repeated_subselect=on read_only = ON slave_rows_search_algorithms = INDEX_SCAN,HASH_SCAN
sf1030756-S