MySQLのレプリケーション構成において、スレーブの処理を並列にして処理を
高速化する事が可能。但し、5.6までは並列化の単位はスキーマ(データベース)
単位になります。5.7からは、同じスキーマ単位でも並列処理が可能になる予定だそうです。
OS側に余裕があれば、処理を高速化し時間を短縮化する事で他の業務も出来るようになるので、
MySQLのレプリケーションがスレッド不足で遅延している環境では、
有益な選択肢になるかと思います。もちろん、自分でツール開発やシェルを工夫して
対応する事も可能ですが、MySQL UtilityやMySQL Enterprise Monitorなどで管理していくと、
更に管理効率が上がり、工数削減とサービス開発等の業務に力を注ぐ事が出来るようになりますね。

slave_parallel_workers MySQL5.6.3~
Sets the number of slave worker threads for executing replication events (transactions) in parallel.
Setting this variable to 0 (the default) disables parallel execution. The maximum is 1024.

http://dev.mysql.com/doc/refman/5.6/en/replication-options-slave.html#sysvar_slave_parallel_workers

Default設定(slave_parallel_workers=0)

root@CentOS03 [(none)]> select @@hostname;
+------------+
| @@hostname |
+------------+
| CentOS03   |
+------------+
1 row in set (0.00 sec)

root@CentOS03 [(none)]> system cat /etc/my.cnf | grep slave_parallel_workers
# slave_parallel_workers       = 0 ~ 1024   

root@CentOS03 [(none)]> show variables like 'slave_parallel_workers';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| slave_parallel_workers | 0     |
+------------------------+-------+
1 row in set (0.00 sec)
 
root@CentOS03 [(none)]> show processlist;
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
| Id | User        | Host      | db   | Command | Time | State                                                                       | Info             |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
|  1 | system user |           | NULL | Connect |  421 | Waiting for master to send event                                            | NULL             |
|  2 | system user |           | NULL | Connect |  412 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL             |
|  3 | root        | localhost | NULL | Query   |    0 | init                                                                        | show processlist |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)

root@CentOS03 [(none)]>

設定変更(slave_parallel_workers=3)

root@CentOS02 [(none)]> select @@hostname;
+------------+
| @@hostname |
+------------+
| CentOS02   |
+------------+
1 row in set (0.00 sec)

root@CentOS02 [(none)]> system cat /etc/my.cnf | grep slave_parallel_workers
slave_parallel_workers          = 3                                  #Can be 0 ~ 1024

root@CentOS02 [(none)]> show variables like 'slave_parallel_workers';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| slave_parallel_workers | 3     |
+------------------------+-------+
1 row in set (0.00 sec)

root@CentOS02 [(none)]> show processlist;
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
| Id | User        | Host      | db   | Command | Time | State                                                                       | Info             |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
|  1 | system user |           | NULL | Connect |  120 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL             |
|  2 | system user |           | NULL | Connect |  120 | Waiting for master to send event                                            | NULL             |
|  3 | system user |           | NULL | Connect |  120 | Waiting for an event from Coordinator                                       | NULL             |
|  4 | system user |           | NULL | Connect |  120 | Waiting for an event from Coordinator                                       | NULL             |
|  5 | system user |           | NULL | Connect |  120 | Waiting for an event from Coordinator                                       | NULL             |
|  6 | root        | localhost | NULL | Query   |    0 | init                                                                        | show processlist |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
6 rows in set (0.00 sec)

root@CentOS02 [(none)]> 


マスター側へそれぞれのスレーブから接続に来ている事が確認出来る。
スレーブ側のスレッドは設定によって変化している事も確認出来る。

slave_parallel_workers

その他、5.6におけるReplicationに関しての資料
http://www.congresoeducacion.unach.mx/sistema_congeducacion/ponencias/replicacion.pdf

Comments are closed.

Post Navigation