Foreword
Recently, we have communicated and investigated with many database users and learned that a considerable number of MySQL high-availability or failover solutions currently in production use the read-write separation function or the business access VIP (Virtual IP Address) method. to shield the backend database architecture.
In MySQL, the VIP mechanism is generally completed with the help of two components, namely HaProxy and Keepalived.
HaProxy is a high-performance load balancer that supports proxying and forwarding of TCP/HTTP traffic. Keepalived is an open source software that implements virtual IP address allocation and failover, and supports active-standby mode to ensure high availability. Many production environments use this method as one of the high-availability solutions for database failover.
As the development process of localization accelerates, a number of excellent domestic database high-availability solutions have also emerged. Wanli Database’s core product security database GreatDB provides external read-write separation and failover in the form of a built-in VIP plugin. Function.
The secure database GreatDB combines the numerous deployment and production practices of customers, combines the capabilities of the above-mentioned HaProxy + Keepalived software with the paxos protocol master selection and switching mechanism of group replication, and integrates the VIP plug-in in the form of a database pluggable plug-in.
Users can install plugin greatdb_ha soname ‘greatdb_ha.so’; to provide VIP to the outside world in a fast, flexible and convenient way, shielding the architectural complexity of the back-end database.
On the other hand, in 2017, MySQL launched the MySQL InnoDB Cluster (MIC) high-availability architecture, which has been continuously updated and iterated after the release of MySQL 8.0. Through the lightweight mysql-router routing component and mysql-shell management tool, it is paired with MySQL Group Replicaton group replication forms a supporting high-availability solution architecture to provide read-write separation and automatic failover capabilities.
Today, we will compare the performance of the two solutions horizontally, as well as applicable scenarios, advantages and disadvantages.
01/Introduction to two high-availability architectures
1. MySQL Innodb Cluster architecture
MySQL Innodb Cluster (MIC for short) is a MySQL-based group_replication group replication plug-in that provides automatic membership management, fault tolerance, automatic failover and other functions.
Based on MGR group replication, two component tools are expanded, namely mysql-shell and mysql-router.
Among them, mysql-shell expands the management capabilities of the entire high-availability architecture. Through the AdminAPI of mysql-shell, you can quickly switch between SQL, JavaScript, and Python languages. It is very suitable for scripting and automated deployment of high-availability replication of MySQL, and adds database instances. Expanded flexibility. By using the AdminAPI of MySQL Shell, the synchronization of inventory data between multiple instances of the original manual configuration has been improved, as well as many configuration links such as node configuration checking, user creation, permission allocation, and plug-in loading.
mysql-router provides business-transparent failover and read-write separation capabilities. Officially positioned as a lightweight routing plug-in, running inside MGR, combined with the mysql_innodb_cluster_metadata library created by mysql-shell, mysql-router automatically identifies the primary and secondary nodes inside MGR by obtaining cluster metadata, and reads and writes through the default 6446 , 6447 read-only port to provide read-write separation support.
(Image source: MySQL Open Source Community 8.0 Product Documentation)
Architecture Description:
MGR group replication supports single-master mode and multi-master mode. Generally, single-master mode is used in MIC usage scenarios, that is, partitioned primary master node and secondary standby node (slave node). This article mainly focuses on the test and comparison of group replication performance in single-master mode.
2. GreatDB Paxos architecture of Wanli Database
GreatDB VIP Plugin provides access to replication groups via virtual IP addresses (single-master mode).
When the plug-in is enabled, the VIP is bound to the primary node network card in group replication by default. When the primary node is switched manually or automatically, the VIP will drift to other GreatDB instances to ensure the continuous read and write capabilities of the business, thereby achieving transparent failover and achieving high-performance access with nearly zero loss in performance. Technically Achieve the goal of efficiency and ease of use.
(Image source: GreatDB official product architecture diagram simplified)
Architecture Description:
The business side only needs to care about the externally exposed VIP address and the original group replication PORT. It does not matter who is the primary master node. VIP will automatically determine the network card where the IP corresponding to group_replication_group_seeds in the group replication is located, and bind the network card subport, for example, the network card is eth0: 0 192.168.1.100/24.
At the same time, it supports cross-network segments, provided that VIP and IP routing are forwarded normally (both are in the network whitelist or have the same gateway address).
02/Test environment and test methods
1. Test object
1) MySQL official MySQL Innodb Cluster architecture
2) GreatDB Paxos + VIP high-availability architecture of Wanli Database
Through sysbench, we stress tested the native privately delivered group replication architecture and added the component mysql-router and enabled the VIP plug-in to compare the performance impact and changes brought about by the differences between the two high-availability architectures.
Software version used:
1.MySQL8.0.32
2. The latest version of GreatDB
3. Stress measurement tool sysbench-master 1.1.0
2. Introduction to test methods
? Stress testing tool: sysbench
? Stress test environment: 50 tables, each table has 1 million rows of data, the stress test duration is no less than 5 minutes, and the performance change differences of 16, 32, and 64 multiples are tested respectively. After each concurrent test is completed, the stress test data is cleared, the database is restarted, the cache is cleared, and the data is reinitialized before the next round of concurrent stress tests is performed. Parameter configuration: my.cnf
? Database: 3-node group replication scheme
? Active/standby mode: single active mode
? Data consistency: all set to eventual consistency
sysbench oltp_read_write.lua --mysql-user=$username --mysql-password=$pwd --mysql-host=$host --mysql-port=$port --mysql-db=sysbench --threads= 16/32/64 --report-interval=1 --tables=50 --table-size=100000 --time=300 run
Host environment:
The test host uses three 16c 16g 200g KVM hosts, and the operating systems are all CentOS 8.5. The installation and deployment use the GreatADM database management platform to uniformly and graphically install the latest version of MySQL8.0.32 and GreatDB group replication architecture.
The two plug-ins for MIC, mysql-router and mysql-shell, are based on the group replication configured in GreatADM. Mysql-router is manually configured and initialized, and the plug-ins are started. Mysql-router performs auxiliary MGR command management.
03/GreatADM Installation and Deployment of Paxos (mgr) High Availability Architecture
1. Deploy 2 sets of databases
Please refer to the GreatSQL community’s previous sharing here [Exploring MGR Graphical Deployment], and click to view the original text of the article.
2. Configure the MySQL InnoDB Cluster component mysql-router
1) MySQL InnoDB Cluster enables the mysql-router plug-in
First
Use mysql-shell (after decompressing, you can directly call mysqlsh under mysql-shell/bin) to create a cluster name for the configured MGR, as follows:
[root@gip bin]# cd /root/mysql-shell/bin;./mysqlsh -ugreatdb -p'!QAZ2wsx' -P3311 -h172.17.138.161</code><code> MySQL 172.17 .138.161:3311 ssl JS > </code><code> MySQL 172.17.138.161:3311 ssl JS > dba.createCluster('mycluster')</code><code> MySQL 172.17.138.161:3311 ssl JS > dba .getCluster();</code><code><Cluster:mycluster></code><code> MySQL 172.17.138.161:3311 ssl JS > \sql</code><code>Switching to SQL mode... Commands end with ;</code><code> MySQL 172.17.138.161:3311 ssl SQL > show databases;</code><code> + ------------------- ------------ + </code><code>Database |</code><code> + -------------------- ----------- + </code><code>information_schema |</code><code>mysql |</code><code>mysql_innodb_cluster_metadata |</code><code>performance_schema |</code> code><code>sys |</code><code>sysbench |</code><code> + -------------------------- ----- + </code><code> MySQL 172.17.138.161:3311 ssl JS > \q</code><code>Bye!
Secondly
Start initializing mysql-router and use the most direct bootstrap method to guide it. The directory and conf configuration file of mysqlrouter will be automatically generated, as follows.
This time, configure a mysql-router on the primary node of mgr:
[root@gip mysql-router]# cd /root/mysql-router/bin;./mysqlrouter --bootstrap greatdb@localhost:3311 --directory /root/mysqlrouter --conf-use-sockets --user =root --conf-bind-address=172.17.138.161 --force</code><code>Please enter MySQL password for greatdb: </code><code>Bootstrapping MySQL Router instance at '/root/mysqlrouter' ...</code><code>Creating account(s) (only those that are needed, if any)</code><code>Verifying account (using it to run SQL queries that would be run by Router)</code><code> code><code>Storing account in keyring</code><code>Adjusting permissions of generated files</code><code>Creating configuration /root/mysqlrouter/mysqlrouter.conf</code> <code>MySQL Router configured for the InnoDB Cluster 'mycluster'</code><code>After this MySQL Router has been started with the generated configuration</code><code> $ ./mysqlrouter -c /root/mysqlrouter /mysqlrouter.conf</code><code>InnoDB Cluster 'mycluster' can be reached by connecting to:</code><code>MySQL Classic protocol</code><code>Read/Write Connections: localhost:6446 , /root/mysqlrouter/mysql.sock #router’s read-write port</code><code>Read/Only Connections: localhost:6447, /root/mysqlrouter/mysqlro.sock #router’s read-only port</code><code>MySQL X protocol</code> <code>Read/Write Connections: localhost:6448, /root/mysqlrouter/mysqlx.sock</code><code>Read/Only Connections: localhost:6449, /root/mysqlrouter/mysqlxro.sock
Finally
Just start mysql-router
[root@gip bin]# </code><code>[root@gip mysqlrouter]# pwd</code><code>/root/mysqlrouter</code><code>[root@gip mysqlrouter]# ls</code><code>data log mysqlrouter.conf mysqlrouter.key run start.sh stop.sh</code><code>[root@gip mysqlrouter]# sh start.sh </code><code>[root @gip mysqlrouter]# PID 307283 written to '/root/mysqlrouter/mysqlrouter.pid'</code><code>stopping to log to the console. Continuing to log to filelog</code><code>[root@ gip mysqlrouter]# mysql -ugreatdb -p'!QAZ2wsx' -h172.17.138.161 -P6446 #Test passes 6446 to connect to MGR
3. GreatDB enables VIP PLUGIN and configures VIP
Select [Configure Database VIP] through the topology interface
Fill in [VIP address configuration]–[Pre-check] to check whether the corresponding VIP is occupied, and the operating system permissions required to configure the VIP and the lib library used for operation are loaded normally.
After [Submit], the database will add the greatdb_ha.so plug-in to the my.cnf configuration file to complete the loading of VIP parameter variables. At this stage, the database instance needs to be restarted. Do not configure VIP during business peaks or when business is running, otherwise it may cause business interruption.
After the restart is complete, the topology is as follows:
[Login to database]: Check the VIP variable configuration information of the database instance. The three database instances have the same configuration and automatically identify the network card name to be bound.
Configuration through the GreatADM graphical interface is relatively simple and efficient, shielding the complexity of parameter configuration and lib loading.
04/Perform split testing
1. MySQL MGR with and without mysql-router performance loss test
Database version: MySQL Community Server 8.0.32
mysql-shell: ver 8.0.34 for Linux on x86_64
mysql-router: ver 8.0.34 for Linux on x86_64
Stress test command:
sysbench oltp_read_write.lua \ #Use oltp_read_write.lua mixed reading and writing script</code><code>--mysql-user=username \ </code><code>--mysql-password='pwd ' \</code><code>--mysql-host=172.17.138.161 \</code><code> --mysql-port=3311 \ #MGR port 3311, MIC mysql-router is 6446 </code><code>--mysql-db=sysbench \</code><code>--threads=64 \ #Test 16, 32, 64 concurrency respectively</code><code>--report-interval =1 \</code><code>--tables=50 \ </code><code>--table-size=100000 \</code><code>--time=300 run</pre > <p></p> <p><strong>Test results:</strong></p> <p></p> <p><img alt="Picture" height="94" src="//i2.wp.com/img-blog.csdnimg.cn/img_convert/37a360b23ea7b6ac79634c069a7503cb.jpeg" width="758" ></p> <p></p> <p><strong>Performance curve comparison:</strong></p> <p></p> <p><img alt="Picture" height="579" src="//i2.wp.com/img-blog.csdnimg.cn/img_convert/f629ebd3123df8ea95a260e5aa525f0c.png" width="1080" ></p> <p></p> <p><strong>Conclusion:</strong> In the actual MGR single-master mode, through the mysql-router stress test, under 16, 32, and 64 concurrency, the performance dropped by -14.2% and -24.7% respectively compared with the direct stress test of the primary node. , -17.3%, the average performance loss of 3 concurrent runs is 18.7%.</p> <p></p> <p></p> <p><strong><strong>2. GreatDB performance loss test with and without VIP Plugin enabled</strong></strong></p> <p></p> <p>Database version: Server version:8.0.32-23-GreatDB latest version</p> <p></p> <p><img alt="Picture" height="372" src="//i2.wp.com/img-blog.csdnimg.cn/img_convert/b435d59f31ef313dae1cbc5da1653282.png" width="828" ></p> <p>Stress test command:</p> <pre>sysbench oltp_read_write.lua \ #Use oltp_read_write.lua mixed reading and writing script</code><code>--mysql-user=username \ </code><code>--mysql-password='pwd ' \</code><code>--mysql-host=172.17.138.161 \ #vip address 172.17.138.100</code><code> --mysql-port=3322 \ #Database access port 3322, vip and physical ip access the same port</code><code>--mysql-db=sysbench \</code><code>--threads=64 \ #Test 16, 32, 64 concurrency respectively</code> <code>--report-interval=1 \</code><code>--tables=50 \ </code><code>--table-size=100000 \</code><code>- -time=300 run
Test results:
Performance curve comparison:
Conclusion: In the actual GreatDB paxos architecture, in single master mode, compared to the VIP stress test configured through the built-in VIP plugin, the performance of the direct stress test of the primary node dropped by -2 under 16, 32, and 64 concurrency conditions respectively. %, + 0.9%, -1.4%, the average performance loss for three times under three concurrencies is 0.83%.
05/Comprehensive comparison
Horizontal comparison of results: By comparing the latest version of GreatDB and the original community MySQL8.0.32 version, on the same three configuration hosts, using the same my.cnf parameters, 50 100,000-row table data were used respectively. Conducting 16, 32, and 64 concurrent read and write stress tests, GreatDB’s paxos optimization improvement solution improved the performance by 12.6%, 18.4%, and 26.8% respectively compared with the native MySQL MGR. Three concurrent tests were conducted, and the average performance increased by about 19.3%.
Horizontal comparison of results: The latest version of GreatDB provides the VIP failover capability of the enterprise version. Compared with MySQL Innodb Cluster, the performance improvements are 26.1%, 49%, and 46.7% respectively under 16, 32, and 64 concurrency conditions. , 3 concurrent tests, the average performance improvement is about 40.6%.
06/Summary
Based on the above comparative analysis, we understand the difference in the performance of the group replication scheme between the two.
Both solutions are based on the group_replication group replication plug-in to establish multiple data copies and achieve high-availability master selection and failover through the paxos protocol.
Although the positioning of mysql-router is as simple and lightweight as possible, after adding the route forwarding of mysql-router, the performance of port-based read and write routing and weighted polling-based load balancing is unsatisfactory.
GreatDB uses integrated greatdb_ha.so plug-in support. Through the built-in plug-in method, failover is realized without external awareness, the configuration is simple, and the performance consumption is compared with mysql-router. TPS performance is improved by nearly 40%, and can Flexibly install and uninstall plugin, making it truly efficient and easy to use.
GreatDB Paxos + VIP High Availability Architecture Solution Features
1.Better compatibility
The GreatDB database has a built-in integrated plugin, no additional components are needed, and the database itself has greater plug-in compatibility;
2. Better architecture stability
Combined with GreatDB’s network layer optimization of group_replication, transaction authentication flow control code layer improvements, and better architecture stability;
3. Performance improvement
GreatDB has enhanced the development of the optimizer and supports cost-based parallel queries and other capabilities. Compared with the community version MGR, the performance has improved by about 19.3%;
4. Improve operation and maintenance efficiency
In terms of maintainability, the AWR automatic load information database and ASH active session history analysis report supported by GreatDB development allow users to track performance change trends in operation and maintenance, and enhance the efficiency of problem location and troubleshooting.
GreatDB Paxos + VIP plan applicable scenarios
1. It can be used in the original dual-master + keepalived based on MySQL5.7 or with Haproxy and other solution architectures, and in upgrade and replacement scenarios where the number of failover times and failure recovery costs are high;
2. It can be used in scenarios that are troubled by performance and business stability with the help of the official MySQL InnoDB Cluster architecture. In such scenarios, you can consider upgrading to the GreatDB Paxos + VIP solution;
3. It can be used in business scenarios where read-write separation, manual maintenance, and read-write scalability are limited with the help of Sqlproxy, etc. In such scenarios, you can consider upgrading the GreatDB Paxos + VIP solution, which will help enhance scalability and read-write performance.
Just this Saturday, MySQL version 5.7 will also usher in the end of its life cycle. The GreatDB Paxos + VIP plan can also meet the needs of enterprise users who want to obtain long-term maintenance support or want to migrate in parallel to an enterprise-level commercial database. .
In addition, if you want to find a second open source version with long-term community update support, you can choose the GreatSQL open source database in the GreatSQL open source community established under the leadership of Wanli Database. The GreatSQL open source database can also simultaneously support VIP plugin high Available plans.
The knowledge points of the article match the official knowledge files, and you can further learn relevant knowledge. MySQL entry skill treeSQL advanced skillsCTE and recursive query 76311 people are learning the system