github / github/gh-ost

Results of testing on multisource

Open
#225 2 comments 0 reactions 0 assignees View on GitHub
feedback
Dominant language
Go
Stars
13.6k
Forks
1.4k
Avg merge
2h 31m
Merged PRs (30d)
4

Description

I saw the note here about needing a test on a multisource setup: https://github.com/github/gh-ost/blob/master/doc/requirements-and-limitations.md

I've done a simple functional test, and have the following results.

In summary, no issues were found in running this from the master. It failed as predicted when running from the slave, because the code is looking for "show slave status" and that doesn't return a result if no default_master_connection is specified. The syntax would be "show all slaves status" but I believe that would result in either an error or a failure as well. Will test this with the --assume-master-host flag when that is merged.
#### setup

```
MariaDB [(none)]> show all slaves status\G
*************************** 1. row ***************************
Connection_name: d1
Slave_SQL_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.56.68
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: maria101-bin.000004
Read_Master_Log_Pos: 2176440
Relay_Log_File: maria101multislave-relay-bin-d1.000007
Relay_Log_Pos: 2176731
Relay_Master_Log_File: maria101-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
*************************** 2. row ***************************
Connection_name: d2
Slave_SQL_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.56.69
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: maria101master2-bin.000002
Read_Master_Log_Pos: 1731
Relay_Log_File: maria101multislave-relay-bin-d2.000003
Relay_Log_Pos: 2029
Relay_Master_Log_File: maria101master2-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
2 rows in set (0.00 sec)

MariaDB [(none)]> show create table d1.t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(200) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

MariaDB [(none)]> show create table d2.t2\G
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(200) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
```
#### run test from replica

```
./gh-ost \
--max-load=Threads_running=25 \
--critical-load=Threads_running=1000 \
--chunk-size=1000 \
--throttle-control-replicas="192.168.56.70" \
--max-lag-millis=1500 \
--user="ghost" \
--password="ghost" \
--host=192.168.56.70 \
--database="d1" \
--table="t1" \
--verbose \
--alter="add column whatever varchar(50)" \
--switch-to-rbr \
--allow-master-master \
--cut-over=default \
--default-retries=120 \
--panic-flag-file=/tmp/ghost.panic.flag \
--postpone-cut-over-flag-file=/tmp/ghost.postpone.flag \
--initially-drop-ghost-table \
--execute
```

Failed as expected:

```
2016-09-01 15:59:33 INFO starting gh-ost 1.0.14
2016-09-01 15:59:33 INFO Migrating `d1`.`t1`
2016-09-01 15:59:33 INFO connection validated on 192.168.56.70:3306
2016-09-01 15:59:33 INFO User has SUPER, REPLICATION SLAVE privileges, and has ALL privileges on `d1`.*
2016-09-01 15:59:33 INFO 192.168.56.70:3306 has STATEMENT binlog_format. I will change it to ROW, and will NOT change it back, even in the event of failure.
2016-09-01 15:59:33 INFO binary logs validated on 192.168.56.70:3306
2016-09-01 15:59:33 INFO Restarting replication on 192.168.56.70:3306 to make sure binlog settings apply to replication thread
2016-09-01 15:59:33 INFO Table found. Engine=MyISAM
2016-09-01 15:59:33 INFO Estimated number of rows via EXPLAIN: 0
2016-09-01 15:59:33 FATAL It seems like this migration attempt to run directly on master. Preferably it would be executed on a replica (and this reduces load from the master). To proceed please provide --allow-on-master
```

The syntax would be "show all slaves status" but that would probably also result in an error or proceed with possibly the wrong master. The --assume-master-host flag might allow for this.
#### run tests from master

```
./gh-ost \
--max-load=Threads_running=25 \
--critical-load=Threads_running=1000 \
--chunk-size=1000 \
--throttle-control-replicas="192.168.56.70" \
--max-lag-millis=1500 \
--user="ghost" \
--password="ghost" \
--host=192.168.56.68 \
--database="d1" \
--table="t1" \
--verbose \
--alter="add column whatever varchar(50)" \
--switch-to-rbr \
--allow-on-master \
--cut-over=default \
--default-retries=120 \
--panic-flag-file=/tmp/ghost.panic.flag \
--postpone-cut-over-flag-file=/tmp/ghost.postpone.flag \
--initially-drop-ghost-table \
--execute

./gh-ost \
--max-load=Threads_running=25 \
--critical-load=Threads_running=1000 \
--chunk-size=1000 \
--throttle-control-replicas="192.168.56.70" \
--max-lag-millis=1500 \
--user="ghost" \
--password="ghost" \
--host=192.168.56.69 \
--database="d2" \
--table="t2" \
--verbose \
--alter="add column whatever varchar(50)" \
--switch-to-rbr \
--allow-on-master \
--cut-over=default \
--default-retries=120 \
--panic-flag-file=/tmp/ghost.panic.flag \
--postpone-cut-over-flag-file=/tmp/ghost.postpone.flag \
--initially-drop-ghost-table \
--execute
```

After changes, on multisource slave, replication is healthy, and changes succeeded:

```
MariaDB [(none)]> show create table d1.t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(200) DEFAULT NULL,
`whatever` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

MariaDB [(none)]> show create table d2.t2\G
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(200) DEFAULT NULL,
`whatever` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
```

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.