Description:
Hi,
I have a three node InnoDB Cluster. mysql1,mysql2,mysql3
mysql2 is the Primary , mysql1 and mysql3 are the readers.
If we simulate a partial network outage example with iptables:
Running this on mysql3:
mysql3# iptables -A INPUT -s mysql2 \
-j DROP; iptables -A OUTPUT -s mysql2 -j DROP
mysql3 will still get all the changes made on mysql2 because mysql1 is
going act like a relay node and send all the changes to mysql3. You can
confirm this even with tcpdump.
However it has a huge Performance impact. Before I cut the network I was able to insert 60-80 rows per second after that only 1-3 roes per second, which is a huge degradation.
Also the cluster.status() on mysql2 reports that mysql3 is not reachable , but mysql2 reports that all the nodes are Online, which is also interesting in a cluster I would love to see if all the nodes are reporting the same cluster status, except if a node is totally isolated.
How to repeat:
How to repeat:
create a 3 node InnoDb cluster.
create a table on primary:
CREATE TABLE `lab` ( `id` int NOT NULL AUTO_INCREMENT, `hostname`
varchar(20) DEFAULT NULL, `created_at` datetime DEFAULT
CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY
`idx_created` (`created_at`) ) ENGINE=InnoDB;
Insert some data in a loop on mysql2:
while true;do mysql -usbtest -pxxxxx -P3306 -h127.0.0.1 -e "INSERT INTO
sysbench.lab (hostname) VALUES ( @@hostname)"; done 2>/dev/null
On mysql2 also start another loop to select roughly how many rows are inserted per second:
while true;do mysql -BN -usbtest --pxxxxx-P3306 -hmysql2 -e "select 'mysql2',count(*),now() from sysbench.lab where created_at BETWEEN now() - INTERVAL 1 second AND now()"; sleep 1; done 2>/dev/null
Cut network between a reader and primary:
mysql3# iptables -A INPUT -s mysql2 \
-j DROP; iptables -A OUTPUT -s mysql2 -j DROP
You will see the impact immediately:
mysql2 48 2020-03-31 12:27:15
mysql2 50 2020-03-31 12:27:16
mysql2 51 2020-03-31 12:27:17
mysql2 51 2020-03-31 12:27:18
mysql2 52 2020-03-31 12:27:19
mysql2 53 2020-03-31 12:27:20
mysql2 54 2020-03-31 12:27:21
mysql2 55 2020-03-31 12:27:22
mysql2 56 2020-03-31 12:27:23
mysql2 56 2020-03-31 12:27:24
mysql2 26 2020-03-31 12:27:25
mysql2 8 2020-03-31 12:27:26
mysql2 7 2020-03-31 12:27:27
mysql2 8 2020-03-31 12:27:28
mysql2 4 2020-03-31 12:27:29
mysql2 2 2020-03-31 12:27:30
mysql2 2 2020-03-31 12:27:31
mysql2 2 2020-03-31 12:27:32
mysql2 2 2020-03-31 12:27:33
Suggested fix:
I am not sure what is causing this degradation but a partial network failure should not impact the performance that badly.
Description: Hi, I have a three node InnoDB Cluster. mysql1,mysql2,mysql3 mysql2 is the Primary , mysql1 and mysql3 are the readers. If we simulate a partial network outage example with iptables: Running this on mysql3: mysql3# iptables -A INPUT -s mysql2 \ -j DROP; iptables -A OUTPUT -s mysql2 -j DROP mysql3 will still get all the changes made on mysql2 because mysql1 is going act like a relay node and send all the changes to mysql3. You can confirm this even with tcpdump. However it has a huge Performance impact. Before I cut the network I was able to insert 60-80 rows per second after that only 1-3 roes per second, which is a huge degradation. Also the cluster.status() on mysql2 reports that mysql3 is not reachable , but mysql2 reports that all the nodes are Online, which is also interesting in a cluster I would love to see if all the nodes are reporting the same cluster status, except if a node is totally isolated. How to repeat: How to repeat: create a 3 node InnoDb cluster. create a table on primary: CREATE TABLE `lab` ( `id` int NOT NULL AUTO_INCREMENT, `hostname` varchar(20) DEFAULT NULL, `created_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `idx_created` (`created_at`) ) ENGINE=InnoDB; Insert some data in a loop on mysql2: while true;do mysql -usbtest -pxxxxx -P3306 -h127.0.0.1 -e "INSERT INTO sysbench.lab (hostname) VALUES ( @@hostname)"; done 2>/dev/null On mysql2 also start another loop to select roughly how many rows are inserted per second: while true;do mysql -BN -usbtest --pxxxxx-P3306 -hmysql2 -e "select 'mysql2',count(*),now() from sysbench.lab where created_at BETWEEN now() - INTERVAL 1 second AND now()"; sleep 1; done 2>/dev/null Cut network between a reader and primary: mysql3# iptables -A INPUT -s mysql2 \ -j DROP; iptables -A OUTPUT -s mysql2 -j DROP You will see the impact immediately: mysql2 48 2020-03-31 12:27:15 mysql2 50 2020-03-31 12:27:16 mysql2 51 2020-03-31 12:27:17 mysql2 51 2020-03-31 12:27:18 mysql2 52 2020-03-31 12:27:19 mysql2 53 2020-03-31 12:27:20 mysql2 54 2020-03-31 12:27:21 mysql2 55 2020-03-31 12:27:22 mysql2 56 2020-03-31 12:27:23 mysql2 56 2020-03-31 12:27:24 mysql2 26 2020-03-31 12:27:25 mysql2 8 2020-03-31 12:27:26 mysql2 7 2020-03-31 12:27:27 mysql2 8 2020-03-31 12:27:28 mysql2 4 2020-03-31 12:27:29 mysql2 2 2020-03-31 12:27:30 mysql2 2 2020-03-31 12:27:31 mysql2 2 2020-03-31 12:27:32 mysql2 2 2020-03-31 12:27:33 Suggested fix: I am not sure what is causing this degradation but a partial network failure should not impact the performance that badly.