使用Mydata进行MySQL的自动化运维

本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客

今年我司发布了一款基于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%): 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%): 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%): 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%): 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%): 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%): 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%): 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%): 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%): 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%): 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%): 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%): 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%): 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%): 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%): 56.84 err/s: 0.00 reconn/s: 0.00

[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%): 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%): 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%): 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%): 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%): 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%): 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%): 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%): 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%): 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%): 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%): 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%): 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%): 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%): 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%): 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%): 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%): 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%): 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%): 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%): 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%): 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%): 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%): 38.94 err/s: 0.00 reconn/s: 0.00

[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%): 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%): 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%): 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%): 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%): 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%): 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%): 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%): 38.94 err/s: 0.00 reconn/s: 0.00

我们可以看到，通过ProxySQL来进行读写分离后，由于可以降低运行sysbench操作的服务器的性能开销，因此整个sysbench压测结果更好一些。粗略估算一下，通过ProxySQL进行读写分离后，相比直连MySQL Group Replication集群主节点进行读写操作，其性能可以提升35%左右；显然这是非常之可观的。

同时，由于我这里所测试的是MySQL 8.0.16版本，即目前的最新版，我们可以看到，整个压测结果非常平稳,如下所示（我这里测试的是单主模式)：

由此可见，MySQL 的MGR功能已经比较稳定了，我相信未来1-2年大量用户会开始使用MySQL 8.0. 我们拭目以待吧！

Tags: group replication, mysql 8.0.16
Posted in MySQL, MySQL安装配置 on 5月 26, 2019

You must be logged in to post a comment.

love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客

Categories

Archives

最新评论

国内圈子

oracle security

使用Mydata进行MySQL的自动化运维

Leave a Reply