今年我司发布了一款基于MySQL的数据库管理平台-Mydata;通过MyData管理平台可以进行MySQL的自动化安装部署、性能监控、高可用、备份恢复等主要功能,极大的简化了用户对于MySQL的运维工作量,同时也降低了传统行业客户使用开源数据库的门槛。
对于安装部署目前支持单实例、主从、MGR、ProxySQL等环境的自动化安装部署、高可用切换,即将支持Percona Server和MariaDB。
通过Mydata进行一套MySQL group replication环境的部署,只需不到10分钟即可完成,同时自动化安装模板融合了我们多年的最佳实践经验,无需再进行参数优化。
这里我使用部署在华为云的ECS主机进行了简单的压力测试,发现性能还不错。
[root@enmodb2 lua]# sysbench oltp_read_write.lua --mysql-host=192.168.1.132 --mysql-port=3307 --mysql-db=enmotech --mysql-user=root --mysql-password=enmotech --table_size=1000000 --tables=20 --threads=120 --report-interval=10 --time=300 run
sysbench 1.0.17 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 120
Report intermediate results every 10 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 10s ] thds: 120 tps: 2907.03 qps: 58301.31 (r/w/o: 40823.15/11652.70/5825.45) lat (ms,95{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 74.46 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 120 tps: 2986.88 qps: 59750.97 (r/w/o: 41825.90/11950.81/5974.26) lat (ms,95{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 65.65 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 120 tps: 3263.08 qps: 65281.33 (r/w/o: 45699.44/13055.63/6526.26) lat (ms,95{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 57.87 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 120 tps: 3270.20 qps: 65416.04 (r/w/o: 45786.33/13089.51/6540.20) lat (ms,95{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 57.87 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 120 tps: 3325.52 qps: 66509.09 (r/w/o: 46561.38/13296.48/6651.24) lat (ms,95{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 57.87 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 120 tps: 3285.84 qps: 65687.12 (r/w/o: 45975.78/13140.26/6571.08) lat (ms,95{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 58.92 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 120 tps: 2927.94 qps: 58572.17 (r/w/o: 41003.84/11712.05/5856.28) lat (ms,95{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 71.83 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 120 tps: 2997.81 qps: 59965.17 (r/w/o: 41976.39/11993.15/5995.63) lat (ms,95{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 66.84 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 120 tps: 3345.99 qps: 66890.03 (r/w/o: 46824.61/13373.75/6691.67) lat (ms,95{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 56.84 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 120 tps: 3245.01 qps: 64913.19 (r/w/o: 45432.30/12990.76/6490.13) lat (ms,95{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 58.92 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 120 tps: 3307.79 qps: 66185.18 (r/w/o: 46331.94/13237.36/6615.88) lat (ms,95{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 56.84 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 120 tps: 3295.58 qps: 65899.83 (r/w/o: 46130.14/13178.63/6591.06) lat (ms,95{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 57.87 err/s: 0.00 reconn/s: 0.00
[ 130s ] thds: 120 tps: 2956.52 qps: 59127.17 (r/w/o: 41387.13/11827.19/5912.85) lat (ms,95{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 69.29 err/s: 0.00 reconn/s: 0.00
[ 140s ] thds: 120 tps: 3157.89 qps: 63167.63 (r/w/o: 44221.51/12630.05/6316.07) lat (ms,95{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 62.19 err/s: 0.00 reconn/s: 0.00
[ 150s ] thds: 120 tps: 3328.87 qps: 66539.91 (r/w/o: 46580.61/13301.76/6657.53) lat (ms,95{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 56.84 err/s: 0.00 reconn/s: 0.00
从上面测试数据来看,不难看出,TPS在3200左右,QPS在65000左右,还是不错的数据了(其实可以更好,因为内存配置相对较多,而且环境上面还部署了一套MySQL主从环境)。
由于这里我们使用了ProxySQL来进行读写分离(Mydata支持ProxySQL中间件的自动化部署),因此这里我们也开启读写分离,看看性能是否有较大的提升(关于ProxySQL的介绍就不再描述了).
[root@enmodb2 lua]# sysbench oltp_read_write.lua --mysql-host=192.168.1.10 --mysql-port=12301 --mysql-db=enmotech --mysql-user=root --mysql-password=enmotech --table_size=1000000 --tables=20 --threads=120 --report-interval=10 --time=300 run
sysbench 1.0.17 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 120
Report intermediate results every 10 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 10s ] thds: 120 tps: 3993.40 qps: 80059.22 (r/w/o: 56055.53/16004.89/7998.80) lat (ms,95{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 38.94 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 120 tps: 4222.63 qps: 84409.03 (r/w/o: 59088.27/16875.61/8445.15) lat (ms,95{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 38.25 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 120 tps: 4176.69 qps: 83446.22 (r/w/o: 58400.20/16692.74/8353.27) lat (ms,95{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 38.25 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 120 tps: 4198.90 qps: 84024.07 (r/w/o: 58825.85/16800.51/8397.71) lat (ms,95{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 37.56 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 120 tps: 3913.40 qps: 78288.71 (r/w/o: 54805.33/15659.18/7824.19) lat (ms,95{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 38.94 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 120 tps: 4164.90 qps: 83284.07 (r/w/o: 58297.05/16654.51/8332.51) lat (ms,95{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 38.25 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 120 tps: 4166.02 qps: 83399.76 (r/w/o: 58382.15/16685.37/8332.24) lat (ms,95{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 38.25 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 120 tps: 3979.17 qps: 79552.89 (r/w/o: 55695.44/15899.10/7958.35) lat (ms,95{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 38.94 err/s: 0.00 reconn/s: 0.00
我们可以看到,通过ProxySQL来进行读写分离后,由于可以降低运行sysbench操作的服务器的性能开销,因此整个sysbench压测结果更好一些。粗略估算一下,通过ProxySQL进行读写分离后,相比直连MySQL Group Replication集群主节点进行读写操作,其性能可以提升35{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}左右;显然这是非常之可观的。
同时,由于我这里所测试的是MySQL 8.0.16版本,即目前的最新版,我们可以看到,整个压测结果非常平稳,如下所示(我这里测试的是单主模式):
由此可见,MySQL 的MGR功能已经比较稳定了,我相信未来1-2年大量用户会开始使用MySQL 8.0. 我们拭目以待吧!