Shulou(Shulou.com)06/01 Report--

This article mainly introduces whether MYSQL innodb cluster will affect the performance because of the network. It is very detailed and has a certain reference value. Interested friends must finish reading it!

1 need to build the innodb cluster environment of mysql 8.019, and need three machines

192.168.198.100

192.168.198.101 (Primary)

192.168.198.102

Then we create a lab test table on the test library on 192.168.198.101 (this test is relatively simple. I originally wanted to use sysbench for a comprehensive test, but version 0.5 of sysbench cannot have barrier-free access to the user password plug-in of mysql 8, so we can only use the following way. In addition, if it is also the case of python, you need to upgrade the python-API of your connection to mysql, otherwise you will also report an error)

I can't help complaining about what a good product design is.

1 version is different, reduce the learning cost of users as much as possible

2 version is different, try to reduce the cost of non-essential users

3 different versions, try to reduce the changes in key parts, or there is a compatible way to overdo it.

So mysql in the upgrade road, the cost is not low, of course, can not stop people's enthusiasm, but if it can be done more thoughtfully and properly, the upgrade path will be better.

Then, in the normal state, start inserting data into the lab table

While true;do mysql-uroot-ppassword-- socket=/data/mysql/mysql.sock-e "INSERT INTO test.lab (hostname,create_time) VALUES (@ @ hostname,now ())"; done 2 > / dev/null

According to the following information, the number of inserted data per second is 89 per second.

Then directly on the / etc/hosts on mgr3's machine, change the address corresponding to mgr2 to an address that does not exist.

Unit 3 can no longer connect to the mgr2 to get the status information of the cluster, but from the system table, the cluster is still stable.

Then the old trick is repeated to see if the data insertion will be affected after MGR3 cannot communicate with MGR1. Finally, you can see that it will not, and the insertion speed is even faster (only modified / etc/hosts).

Insert approximately every second at

As you can see, MGR3 can no longer communicate with MGR1, but unlike the master node, at least the status can be displayed. The impact of the inability to connect with the master node and the slave node is greater.

We continued to do the same trick again, and it turned out to be a little interesting.

Summary info:

In the normal state of the system, 89 pieces of data are inserted per second

The system loses one (actually a little inaccurate) node, inserting 126 data per second

The slave node and one slave node are lost, and 85 data are inserted per second.

In fact, this test can be done many times, for example, 10 times, and then take the average (if anyone has time to try)

So there is actually a problem with that text, because it lacks a test between the slave node and the slave node, only testing the network problem between the master node and the slave node.

The above is all the contents of the article "will MYSQL innodb cluster affect performance because of the network?" Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.