技术分享 | 数据库产品选型测试 集中式与分布式

前文

数据库派别分为集中式和分布式，谁强谁弱，一直争论不休。什么样的场景用上集中式数据库，什么时候适合用分布式，**分库分表的契机在哪里？**纸上得终学浅，绝知此事要躬行。还是需要做下测试，测试之前在各个博客与官网上做了大量的调研，宏观上基于shardingproxy上面进行sysbench测试，后面证实我也走过各位博主的坑。整体而言，不敢说100%的科学严谨，比起一些博主的结论严谨很多。

测试环境

测试思路

• 数据量级从500万起、1000万级别、5000万级往上递进【后面直接用5000万】
• SQL语句采用sysbench的oltp_read_write.lua，
• 调整虚拟机CPU核数，逐步从1升到8，观察 sysbench的QPS和TPS以及CPU、内存、IO的消耗
• 针对MYSQL单机测试不用 数据量级别下的QPS和TPS、以及CPU、内存、IO的消耗
• 针对shardingproxy转发单个MYSQL，并观测QPS和TPS、以及CPU、内存、IO的消耗
• 针对shardingproxy转发两个MYSQL，并观测QPS和TPS、以及CPU、内存、IO的消耗
• 基于shardingproxy,基于原生语句DistSQL创建单表，创建分片规则，并通过 insert插 入大量数据,观测QPS和TPS、以及CPU、内存、IO的消耗
• 通过sysbench生成相关数据，测试数据、销毁数据

环境了解

• oltp_read_write.lua覆盖7个动作 execute_simple_ranges(),execute_sum_ranges()、 execute_order_ranges()、execute_distinct_ranges()、execute_index_updates()、execute_non_index_updates()、execute_delete_inserts()，某个程度，oltp_read_write.lua代表sysbench最有代表性的TPS和QPS的测试选项。

sum_ranges = {
"SELECT SUM(k) FROMsbtest%u WHERE id BETWEEN ? AND ?",
t.INT, t.INT},
order_ranges = {
"SELECT c FROMsbtest%u WHERE id BETWEEN ? AND ? ORDER BY c",
t.INT, t.INT},
distinct_ranges = {
"SELECT DISTINCT cFROM sbtest%u WHERE id BETWEEN ? AND ? ORDER BY c",
t.INT, t.INT},
index_updates = {
"UPDATE sbtest%uSET k=k+1 WHERE id=?",
t.INT},
non_index_updates = {
"UPDATE sbtest%uSET c=? WHERE id=?",
{t.CHAR, 120}, t.INT},
deletes = {
"DELETE FROMsbtest%u WHERE id=?",
t.INT},
inserts = {
"INSERT INTOsbtest%u (id, k, c, pad) VALUES (?, ?, ?, ?)",
t.INT, t.INT, {t.CHAR,120}, {t.CHAR, 60}}


• mysql5.7保留原有的内存管理参数不变，也会稍做调整，目前测试性能强弱结论与内存管理没有关系

• shardingproxy使用默认的配置参数，默认支持XA事务，没有任何变化。

执行操作

试了几个版本的shardingproxy,目前只有5.10的能够成功启动没有报错

测试记录

直接MYSQL压测

不同CPU核数、不同线程的500万的数据集，首先从默认的8核CPU，从1线程 开始一直到6线程，最后把CPU核数降为2核

直接shardingproxy压测

add resource ds_0(url="jdbc:mysql://192.168.153.128:3306/tpcc?serverTimezone=UTC&useSSL=false",user=henley,password=Gmcc@1234);

同样基于500万数据集，shardingproxy后面挂着一个数据源【MYSQL】，基于先前针对MYSQL的参数配置，发现TPQ和QPS都降低了，和其它网友的踩坑一样ShardingSphere实践（5）——性能测试

怀疑是数据量的问题和数据入口的方式不对【shardingproxy 有数据分片规则】，于是从单机mysql导出5000万数据，从shardingproxy 以insert 的方式插入，后端数据源是两个mysql，插入时间花了3天。这样遵从shardingproxy 的规则进行了5000万的插入。

导出单机5000万的数据
mysqldump -h127.0.0.1 -P3306 -u henley -pGmcc@1234 --no-create-info --skip-extended-insert --complete-insert tpcc3 sbtest1 >tpcc3_sbtest1.sql

再次通过sysbench进行压测试，发生性能依旧不理想，比直接mysql要慢。同时发现一个重要参数修改，Sharding-Proxy接入的分布式事务API支持LOCAL，XA，BASE类型的事务。我原来用的是XA，我把它更换成LOCAL，XA是标准的可靠的安全事务，但是性能低， 而LOCAL可能会有事务出错的风险。

**但是我把XA换成LOCAL，依然性能低下。我再次比较了直连mysql和 shardingproxy的IO读写数据对比，两者差异很大，Sharding-Proxy的IO根本进入繁忙状态 ** 。业务逻辑语句不变，数据底座的IO读写强弱就令人深思了。

过程记录

一个性能测试与SQL、数据量、结构表以及硬件状况相关， mysql5.7单机500万数据集如下。

• mysql5.7单机500万、1000万、5000万的数据集的oltp_read_write.lua测试，shardingproxy外挂都要比mysql5.7要弱，而且占用的CPU、内存、IO要比mysql5.7要搞
• 增加innodb_buffer可以明显提高mysql5.7单机的sysbench测试处理，但是对shardingproxy的提升表现不明显。
• sysbench内含语句是针对单表的随机增删查改，考验的是一个DBMS的处理能力。MYSQL默认InnoDB引擎，按照 sbtest1的表结构，估摸即使1亿多的数据也能在3次硬盘寻道的范围内返回。
• CPU个数和线程可以提高sysbench的处理能力，从虚拟机CPU个数据调整 ，从1到8，性能上升。sysbench的线程标准设置是6线程，6~8可以达到sysbench的性能高锋，即使把sysbench调整到50，由于没有相关的CPU核心支撑，所以不能发挥相应的作用。 CPU个数和sysbench线程 个数成正比。
• 通过聚合函数例count统计5000万的数据量，根据测试，假使数据量越来越大，直连shardingproxy的SQL执行性能要比直接MYSQL要快。
• 大量插入数据的时候，数据在剧烈增长变化过程中，这时聚合查询count(*)shardingproxy的查询响应速度相对 查询mysql要快。

结论

• 从500万上升到1000万、5000万，观察硬盘利用率没有使用100%，单机5.7的IO仍有空闲，随着CPU核数提高会进入过多的繁忙，提高innodb_buffer可以提高sysbench性能， 如果需要更大程度的提高，必须要提高CPU个数
• sysbench测试，数据量级上升从500万、1000万级别、5000级，mysql5.7单机处理性能TPS、QPS比同样的shardingproxy后带一个mysql5.7或者shardingproxy后带两个mysql5.7要强
• 直接点查询、范围查询、单表组合查询不推荐使用shardingproxy进行分库分表，聚合查询或者大笔数据可以借助shardingproxy进行加强。
• 解读两个对比IO读写集中问题，直连mysql代表的是集中式的架构，直连sharding-proxy是分布式的架构代码，在数据库管理系统支持的能力范围内，增删查改的选择【selection]和投影【projection】是数据库的强项。转入XA事务，除了逻辑链路增加，sharding-proxy还需要保障每个事务协同，这样的损耗就增多了。聚合函数几乎都是要对所有的数据进行全盘扫描的，索引的优越性没有体现出来，随着数据量的增大，B+树的体量大了，对于dbms造成必定的压力，这时候多个DBMS反而减轻了压力。这时候 sharding-proxy的好处用上来了。

最后想到的shardingproxy如此，其它分布式数据库必然因为2pc带来的单机性能影响下降，5000万的数据量覆盖了多少业务场景 ，除了常规的点查询语句，我需要进行什么样的分析需求才会选择一个分布式产品。选择的前提，除非MySQL各方面的调优都无法满足业务的需求 ！