mysql 서버를 운영중에 master , slave 간 동기화 속도때문에 문제가 발생되었다.
Seconds_Behind_Master 값이 증가하는 현상도 발생되었다.
아래의 명령어를 slave 서버에서 진행되며 처리프로세스를 늘려서 좀더 원활하게 적용하도록 하는것이다.
필자는 mysql 5.6.12 버전에서 적용하였다.
>show variables like 'slave%';
+------------------------------+-----------------------+
| Variable_name | Value |
+------------------------------+-----------------------+
| slave_allow_batching | OFF |
| slave_checkpoint_group | 512 |
| slave_checkpoint_period | 300 |
| slave_compressed_protocol | OFF |
| slave_exec_mode | STRICT |
| slave_load_tmpdir | /tmp |
| slave_max_allowed_packet | 1073741824 |
| slave_net_timeout | 3600 |
| slave_parallel_workers | 1 |
| slave_pending_jobs_size_max | 16777216 |
| slave_rows_search_algorithms | TABLE_SCAN,INDEX_SCAN |
| slave_skip_errors | OFF |
| slave_sql_verify_checksum | ON |
| slave_transaction_retries | 10 |
| slave_type_conversions | |
+------------------------------+-----------------------+
15 rows in set (0.00 sec)
>set global slave_parallel_workers=8;
+------------------------------+-----------------------+
| Variable_name | Value |
+------------------------------+-----------------------+
| slave_allow_batching | OFF |
| slave_checkpoint_group | 512 |
| slave_checkpoint_period | 300 |
| slave_compressed_protocol | OFF |
| slave_exec_mode | STRICT |
| slave_load_tmpdir | /tmp |
| slave_max_allowed_packet | 1073741824 |
| slave_net_timeout | 3600 |
| slave_parallel_workers | 4 |
| slave_pending_jobs_size_max | 16777216 |
| slave_rows_search_algorithms | TABLE_SCAN,INDEX_SCAN |
| slave_skip_errors | OFF |
| slave_sql_verify_checksum | ON |
| slave_transaction_retries | 10 |
| slave_type_conversions | |
+------------------------------+-----------------------+
15 rows in set (0.00 sec)
>show processlist;
+----------+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----------+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+------------------+
| 30978915 | system user | | NULL | Connect | 256408 | Waiting for master to send event | NULL |
| 30978916 | system user | | NULL | Connect | 0 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL |
| 30978917 | system user | | NULL | Connect | 0 | Waiting for an event from Coordinator | NULL |
| 30978918 | system user | | NULL | Connect | 843 | Waiting for an event from Coordinator | NULL |
| 30978919 | system user | | NULL | Connect | 0 | Waiting for an event from Coordinator | NULL |
| 30978920 | system user | | NULL | Connect | 0 | Waiting for an event from Coordinator | NULL |
| 32079307 | root | localhost | NULL | Query | 0 | init | show processlist |
+----------+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+------------------+
'리눅스 > mysql' 카테고리의 다른 글
| ERROR 1819 (HY000): Your password does not satisfy the current policy requirements (0) | 2021.01.15 |
|---|---|
| mysql 패스워드 분실, 재설정 (0) | 2021.01.11 |
| galera cluster 설치 (0) | 2019.06.25 |
| 세그멘테이션 오류 (0) | 2019.04.26 |
| mysql 패스워드 초기화 (0) | 2019.04.26 |