New replication features in MySQL 5.6

This post was thought of as an attempt to make some performance test with new multi-threaded replication in 5.6, at least that was my initial intention. Based on Luis Soarez – Replication Team Leader in Oracle – post. I wanted to play with new set of variables and features in order to understand how new multi-threaded replication works and make some performance tests.

First impressions:
=================
I will start with my negative impressions about how to setup new replication features:

– Lack of documentation: I know this is a labs release and most of new features are not documented yet, I’ve just found the Luis blog which has some definitions but is mostly an overview than a manual.

– Variables not present: mts% variables are no longer present in last release.

Good impressions:
– Easy to set up replication.
– Replication looks very stable.

Installation:
============
This was the worst and one of the harder task I’ve done here, it’s a nightmare. First of all, lack of documentation only turns this trivial task into an issue. I was expecting to have this working from source code, just compiling the proper version but this didn’t work as expected.
I’ve downloaded different sources from https://labs.mysql.com/ for my VM (Ubuntu 11 32bit), but after compiling and finally getting it working I realized about the lack of multi-threaded support (don’t ask why)

After all I get a functional instance working:

mysql> status
--------------
mysql Ver 14.14 Distrib 5.1.49, for debian-linux-gnu (i686) using readline 6.1

 

Connection id: 6
Current database:
Current user: root@pancho-VirtualBox.local
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.6.3-labs-multi-threaded-slave MySQL Community Server (GPL)
Protocol version: 10
Connection: 192.168.56.102 via TCP/IP
Server characterset: latin1
Db characterset: latin1
Client characterset: latin1
Conn. characterset: latin1
TCP port: 3306
Uptime: 4 min 1 sec

 

Threads: 2 Questions: 13 Slow queries: 0 Opens: 52 Flush tables: 1 Open tables: 45 Queries per second avg: 0.053
--------------

 

To check if everything was working as expected I’ve followed Luis instructions. First trying to identify mts mts variables:
mysql> show global variables like 'mts%';
Empty set (0.00 sec)

Hey!!! where are those variables? Let’s try another approach:
mysql> show global variables like '%slave%';
+-----------------------------+----------+
| Variable_name | Value |
+-----------------------------+----------+
| init_slave | |
| log_slave_updates | OFF |
| slave_checkpoint_group | 512 |
| slave_checkpoint_period | 300 |
| slave_compressed_protocol | OFF |
| slave_exec_mode | STRICT |
| slave_load_tmpdir | /tmp |
| slave_net_timeout | 3600 |
| slave_parallel_workers | 0 |
| slave_pending_jobs_size_max | 16777216 |
| slave_skip_errors | OFF |
| slave_sql_verify_checksum | ON |
| slave_transaction_retries | 10 |
| slave_type_conversions | |
| sql_slave_skip_counter | 0 |
+-----------------------------+----------+
15 rows in set (0.00 sec)

Weird, they are no longer present and lack of documentation makes this harder to continue, let’s try to see what to configure based in blog:
1- Added some configuration lines:
root@slave:/usr/local/mysql# cat /etc/my.cnf |grep repo
relay_log_info_repository=TABLE
master_info_repository=TABLE

This 2 magical lines finally fixed an old security issue of storing replication credentials in a plain text file, now this information is stored in 2 tables inside mysql db:
mysql> select * from slave_relay_log_info\G
*************************** 1. row ***************************
Master_id: 2
Number_of_lines: 6
Relay_log_name: ./slave-relay-bin.000001
Relay_log_pos: 4
Master_log_name: master-bin.000002
Master_log_pos: 114
Sql_delay: 0
Number_of_workers: 1
1 row in set (0.00 sec)

 

mysql> select * from slave_master_info\G
*************************** 1. row ***************************
Master_id: 2
Number_of_lines: 20
Master_log_name: mysql-bin.000008
Master_log_pos: 605454111
Host: 192.168.56.101
User_name: root
User_password: admin
Port: 3306
Connect_retry: 60
Enabled_ssl: 0
Ssl_ca:
Ssl_capath:
Ssl_cert:
Ssl_cipher:
Ssl_key:
Ssl_verify_server_cert: 0
Heartbeat: 1800
Bind:
Ignored_server_ids: 0
Uuid: 9f5b7139-daf5-11e0-86a8-080027fcf84b
Retry_count: 86400
1 row in set (0.00 sec)
Cool right :-) ?
I know I know, it could be encrypted but is better than nothing, at least a hacker needs to gain access to db first.

2- Checking slave status and setting number of workers:
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.56.101
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000008
Read_Master_Log_Pos: 114
Relay_Log_File: slave-relay-bin.000014
Relay_Log_Pos: 267
Relay_Master_Log_File: mysql-bin.000008
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 114
Relay_Log_Space: 583
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: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 9f5b7139-daf5-11e0-86a8-080027fcf84b
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
1 row in set (0.00 sec)

 

mysql> STOP SLAVE;
Query OK, 0 rows affected (0.00 sec)
mysql> SET GLOBAL slave_parallel_workers=2;
Query OK,
0 rows affected (0.00 sec)
mysql> START SLAVE;
Query OK, 0 rows affected (0.09 sec)
mysql> SELECT USER,STATE FROM INFORMATION_SCHEMA.PROCESSLIST WHERE SER='system user';
+-------------+------------------------------------------------------------------+
| USER | STATE | +-------------+------------------------------------------------------------------+
| system user | Slave has read all relay log; waiting for the slave I/O thread t |
| system user | Waiting for an event from sql thread |
| system user | Waiting for an event from sql thread |
| system user | Waiting for master to send event |
+-------------+------------------------------------------------------------------+
4 rows in set (0.00 sec)

 

mysql> select * from mysql.slave_worker_info\G
*************************** 1. row ***************************
Master_id: 2
Worker_id: 0
Relay_log_name:
Relay_log_pos: 0
Master_log_name:
Master_log_pos: 0
Checkpoint_relay_log_name:
Checkpoint_relay_log_pos: 0
Checkpoint_master_log_name:
Checkpoint_master_log_pos: 0
Checkpoint_seqno: 0
Checkpoint_group_size: 64
Checkpoint_group_bitmap:
*************************** 2. row ***************************
Master_id: 2
Worker_id: 1
Relay_log_name:
Relay_log_pos: 0
Master_log_name:
Master_log_pos: 0
Checkpoint_relay_log_name:
Checkpoint_relay_log_pos: 0
Checkpoint_master_log_name:
Checkpoint_master_log_pos: 0
Checkpoint_seqno: 0
Checkpoint_group_size: 64
Checkpoint_group_bitmap:
2 rows in set (0.00 sec)

 

mysql>

So, all looks good now. Will start some tests, for this task I will run mysqlslap tool in master and check how slave is working with different configurations:

1- Running test with 2 parallel threads and 2 replication workers:
root@pancho-VirtualBox:/usr/local/mysql/bin# mysqlslap --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-execute-number=1000 --auto-generate-sql-load-type=mixed --auto-generate-sql-secondary-indexes=2 -c 2 --create-schema='replication' -T -e InnoDB -i 10 --number-char-cols=10 -S/tmp/mysql.sock
Benchmark
Running for engine InnoDB
Average number of seconds to run all queries: 26.334 seconds
Minimum number of seconds to run all queries: 18.528 seconds
Maximum number of seconds to run all queries: 39.485 seconds
Number of clients running queries: 2
Average number of queries per client: 1000

 

User time 0.78, System time 0.92
Maximum resident set size 1828, Integral resident set size 0
Non-physical pagefaults 560, Physical pagefaults 1, Swaps 0
Blocks in 240 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 40345, Involuntary context switches 190
root@pancho-VirtualBox:/usr/local/mysql/bin#

 

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.56.101
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000008
Read_Master_Log_Pos: 9327097
Relay_Log_File: slave-relay-bin.000014
Relay_Log_Pos: 9325682
Relay_Master_Log_File: mysql-bin.000008
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 9325529
Relay_Log_Space: 9327566
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: 2
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 9f5b7139-daf5-11e0-86a8-080027fcf84b
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Reading event from the relay log
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
1 row in set (0.00 sec)

Max delay detected: 2 secs. Pretty good if we keep in mind this is working on a couple of VMs inside my laptop.

2- Running test with 2 parallel threads and 1 replication worker:
root@pancho-VirtualBox:/usr/local/mysql/bin# mysqlslap --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-execute-number=1000 --auto-generate-sql-load-type=mixed --auto-generate-sql-secondary-indexes=2 -c 2 --create-schema='replication' -T -e InnoDB -i 10 --number-char-cols=10 -S/tmp/mysql.sock
Benchmark
Running for engine InnoDB
Average number of seconds to run all queries: 18.909 seconds
Minimum number of seconds to run all queries: 16.928 seconds
Maximum number of seconds to run all queries: 20.675 seconds
Number of clients running queries: 2
Average number of queries per client: 1000

 

User time 0.84, System time 0.95
Maximum resident set size 1828, Integral resident set size 0
Non-physical pagefaults 560, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 40088, Involuntary context switches 136
root@pancho-VirtualBox:/usr/local/mysql/bin#

 

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.56.101
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000008
Read_Master_Log_Pos: 42351370
Relay_Log_File: slave-relay-bin.000023
Relay_Log_Pos: 18649832
Relay_Master_Log_File: mysql-bin.000008
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 42351370
Relay_Log_Space: 18649995
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: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 9f5b7139-daf5-11e0-86a8-080027fcf84b
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
1 row in set (0.00 sec)

 

mysql>

No delay detected, looks a little bit better with a single replication worker.

3- Running test with 4 parallel threads and 1 replication worker:
root@pancho-VirtualBox:/usr/local/mysql/bin# mysqlslap --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-execute-number=1000 --auto-generate-sql-load-type=mixed --auto-generate-sql-secondary-indexes=2 -c 4 --create-schema='replication' -T -e InnoDB -i 10 --number-char-cols=10 -S/tmp/mysql.sock
Benchmark
Running for engine InnoDB
Average number of seconds to run all queries: 21.709 seconds
Minimum number of seconds to run all queries: 20.720 seconds
Maximum number of seconds to run all queries: 23.031 seconds
Number of clients running queries: 4
Average number of queries per client: 1000

 

User time 0.34, System time 2.77
Maximum resident set size 1856, Integral resident set size 0
Non-physical pagefaults 578, Physical pagefaults 1, Swaps 0
Blocks in 16 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 65917, Involuntary context switches 366
root@pancho-VirtualBox:/usr/local/mysql/bin#

 

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.56.101
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000008
Read_Master_Log_Pos: 78092135
Relay_Log_File: slave-relay-bin.000023
Relay_Log_Pos: 54390597
Relay_Master_Log_File: mysql-bin.000008
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 78092135
Relay_Log_Space: 54390760
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: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 9f5b7139-daf5-11e0-86a8-080027fcf84b
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
1 row in set (0.00 sec)

No delay detected, seems the delay was caused by parallel workers.

After these tests things looks good, no replication issues detected beyond some delay which is expected because of the environment conditions.
Given this, let’s try make this a little bit fun:
3 mysqlslap instance running with 8 threads each in master, this means 24 concurrent threads performing INSERTS, UPDATES and SELECTS.
4 slave_replication_workers configured:

In master:
root@master:/usr/local/mysql/bin# mysqlslap --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-execute-number=1000 --auto-generate-sql-load-type=mixed --auto-generate-sql-secondary-indexes=2 -c 8 --create-schema='replication2' -T -e InnoDB -i 10 --number-char-cols=10 -S/tmp/mysql.sock
Benchmark
Running for engine InnoDB
Average number of seconds to run all queries: 64.774 seconds
Minimum number of seconds to run all queries: 17.968 seconds
Maximum number of seconds to run all queries: 438.106 seconds
Number of clients running queries: 8
Average number of queries per client: 1000

 

User time 2.72, System time 1.94
Maximum resident set size 2428, Integral resident set size 0
Non-physical pagefaults 799, Physical pagefaults 1, Swaps 0
Blocks in 168 out 24, Messages in 0 out 0, Signals 0
Voluntary context switches 162633, Involuntary context switches 150

 

root@master:/usr/local/bin# mysqlslap --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-execute-number=1000 --auto-generate-sql-load-type=mixed --auto-generate-sql-secondary-indexes=2 -c 8 --create-schema='replication3' -T -e InnoDB -i 10 --number-char-cols=10 -S/tmp/mysql.sock
Benchmark
Running for engine InnoDB
Average number of seconds to run all queries: 64.689 seconds
Minimum number of seconds to run all queries: 17.625 seconds
Maximum number of seconds to run all queries: 437.981 seconds
Number of clients running queries: 8
Average number of queries per client: 1000

User time 2.68, System time 1.94
Maximum resident set size 2428, Integral resident set size 0
Non-physical pagefaults 813, Physical pagefaults 0, Swaps 0
Blocks in 0 out 696, Messages in 0 out 0, Signals 0

root@master:/usr/local/mysql/bin# mysqlslap –auto-generate-sql –auto-generate-sql-add-autoincrement –auto-generate-sql-execute-number=1000 –auto-generate-sql-load-type=mixed –auto-generate-sql-secondary-indexes=2 -c 8 –create-schema=’replication4′ -T -e InnoDB -i 10 –number-char-cols=10 -S/tmp/mysql.sock
Benchmark
Running for engine InnoDB
Average number of seconds to run all queries: 64.520 seconds
Minimum number of seconds to run all queries: 17.157 seconds
Maximum number of seconds to run all queries: 437.966 seconds
Number of clients running queries: 8
Average number of queries per client: 1000

 

User time 2.69, System time 2.00
Maximum resident set size 2424, Integral resident set size 0
Non-physical pagefaults 811, Physical pagefaults 0, Swaps 0
Blocks in 0 out 456, Messages in 0 out 0, Signals 0
Voluntary context switches 162597, Involuntary context switches 156


In slave:
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

 

mysql> set global slave_parallel_workers=4;
Query OK, 0 rows affected (0.00 sec)

 

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> show processlist;
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
| 1 | root | localhost | NULL | Query | 0 | init | show processlist |
| 11 | system user | | NULL | Connect | 3 | Waiting for master to send event | NULL |
| 12 | system user | | NULL | Connect | 3 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL |
| 13 | system user | | NULL | Connect | 3 | Waiting for an event from sql thread | NULL |
| 14 | system user | | NULL | Connect | 3 | Waiting for an event from sql thread | NULL |
| 15 | system user | | NULL | Connect | 3 | Waiting for an event from sql thread | NULL |
| 16 | system user | | NULL | Connect | 3 | Waiting for an event from sql thread | NULL |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
7 rows in set (0.00 sec)

mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| db1 |
| db2 |
| mysql |
| performance_schema |
| rep_test |
| replication |
| replication1 |
| replication2 |
| replication3 |
| replication4 |
| test |
+——————–+
12 rows in set (0.42 sec)

mysql> show processlist;
+—-+————-+———–+——+———+——+———————————————–+——————+
| Id | User | Host | db | Command | Time | State | Info |
+—-+————-+———–+——+———+——+———————————————–+——————+
| 1 | root | localhost | NULL | Query | 0 | init | show processlist |
| 11 | system user | | NULL | Connect | 416 | Waiting for master to send event | NULL |
| 12 | system user | | NULL | Connect | 0 | Waiting for Slave Worker to release partition | NULL |
| 13 | system user | | NULL | Connect | 35 | Executing event | NULL |
| 14 | system user | | NULL | Connect | 416 | Waiting for an event from sql thread | NULL |
| 15 | system user | | NULL | Connect | 416 | Waiting for an event from sql thread | NULL |
| 16 | system user | | NULL | Connect | 416 | Waiting for an event from sql thread | NULL |
+—-+————-+———–+——+———+——+———————————————–+——————+
7 rows in set (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.56.101
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000004
Read_Master_Log_Pos: 216185850
Relay_Log_File: slave-relay-bin.000010
Relay_Log_Pos: 174327889
Relay_Master_Log_File: master-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 206947624
Relay_Log_Space: 216186321
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: 107
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 9f5b7139-daf5-11e0-86a8-080027fcf84b
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Waiting for Slave Worker to release partition
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
1 row in set (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.56.101
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000004
Read_Master_Log_Pos: 216185850
Relay_Log_File: slave-relay-bin.000010
Relay_Log_Pos: 183566115
Relay_Master_Log_File: master-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 216185850
Relay_Log_Space: 216186321
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: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 9f5b7139-daf5-11e0-86a8-080027fcf84b
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
1 row in set (0.02 sec)

 

mysql>

Max delay detected 100 secs.
As I mentioned before this is not a performance test, this was only an approach of new features and how things work in this release.

Some tips to keep in mind to configure replication in 5.6:
– Configure replication to store the information in tables instead of files by adding two lines to my.cnf. This is a good security improvement and I expect this to become a default in next releases:
relay_log_info_repository=TABLE
master_info_repository=TABLE

– There are new slave% variables (called mts% in Luis blog) which we could configure, in this blog I’ve only used slave_parallel_workers, which indicates the number of threads that will read an apply changes from binary logs.
This is the list of additional variables:
slave_checkpoint_group
slave_checkpoint_period
slave_parallel_workers
slave_pending_jobs_size_max
slave_sql_verify_checksum

We still need some extra investigation of what each of them does and it exceeds the purpose of this blog, as I mentioned before documentation is not something we can find easily.

Conclusions:
=========
Multi-threaded replication is here and seems it will be a part of 5.6 release. This means we will not have the need of using third part tools or strange configurations to improve replication performance.
Regarding performance test results were not too promising, this can’t be evaluated in this environment because tests were performed using a laptop running Windows with only 3GB of RAM and 2 VMs running Ubuntu 11-32 bit with 512M RAM each.
Parallel replication works as expected, I didn’t detect data inconsistencies so I’m very optimistic about new performance test by using a better environment.

Next steps:
=========
We didn’t make performance tests so the next obvious steps are to setup a new good environment (probably something in ECS), and push replication to see how good this works.
Stay tuned!!