MySql并发读取性能测试

评价:MySql 的并发读取性能受到带宽与CPU制约影响比较大。平均每m可以支持100个并发连接,且处理速度为10个每m。随着并发连接数增多,延迟增加,处理速度降低,这些连接会保持,消耗服务器资源。

建议: 架构设计时,应考虑:有大量并发需求时,最好避免跨网络连接数据库;应用与数据库最好位于同一内网。

背景概要:使用办公内网,连接到生产内网的数据库服务器,中间使用ngrok进行数据转发,也可直接通过动态域名连接。
测试目标:搜集收下几种场景下的MySql连接响应时间,得到最大并发经验数据。
测试指标:分别测试1,10,100,500,1000,3000,这些并发连接的:平均读取完成时间;总任务时间,并计算每秒处理并发数量。
测试方法:使用命令行程序进行连接,并输出测试数据到文本文件。
测试场景
1 本机MySql。
2 本机与虚拟机MySql。
3 局域网MySql。
4 跨网络MySql(使用动态域名直接连接,与使用ngrok中转服务器连接)。

测试结论

场景 最大安全并发数 最大并发时响应时间 最大并发时每秒处理连接数 CPU占用 峰值带宽

(mbps)

带宽总量 结论
本机 >10000 <20ms 2000~2500 100% 2000~2500 是单机并发上限,受CPU限制。
虚拟机 >10000 约20ms 1500~2000 100% 虚拟机的并发上限是2000,受CPU限制。
内网跨机 >10000 <100ms 1400~1700 50~80 100m 生产库的并发上限受制于带宽,也可能与CPU有关。
外网直连 8000~1000 <2s 800~1000 30~40 100m 外网通过动态域名直连并发数受限于带宽,100M带宽的实际上传速度约为30m
服务器中转 80~100 2~3s 7~10 0.8~1.2 1m 带宽与稳定性,是一个矛盾,两者不可兼得。

模块化编程概述

本文讨论复杂应用场景下,应用层的水平模块化设计思路,也即桌面客户端应用层的模块化开发方法。
服务器,比如WebService服务器,从架构上来说相比于客户端,处于系统的更底层,如果负载压力不大WebService可以设计得很简单,单服务器就解决。而如果负载压力比较大的话,则需要在服务器集群中设计负载均衡方案,不过这属于另外一个话题了。
在具有许多关联业务需求的场景下,我们把这个场景叫做“应用上下文”。用户需要使用很多界面来解决他们的需求,这些需求之间具有一定的相关性,需要共享某些数据,在操作的过程中经常切换。在构建方面,不同的应用会共用某些基础设施,比如跟同一服务器通信,或者使用相同或相似的接口来访问不同的服务器,这时的操作逻辑会相当复杂,如果不够小心的话,一个错误的决定,会让开发过程会变得混乱,造成时间和成本方面的损失。
所以,当软件的规模越来越大,应用逻辑越来越复杂,为了简轻代码的维护负担,简化开发流程,模块化开发方式就成为一种必然。
截至目前本文作者已经全职工作了五年,专业主要集中在CS架构方面,因此对桌面客户端的开发较为熟悉。五年并不算太长的时间,所以不敢保证我的理解是正确的,欢迎一起交流探讨。 模块化解决方案有很多种思路,比如:

应用内模块化

在应用开发时将不同的功能代码放在不同的目录中,使之自然形成应用模块。在用户看来,这些功能是一个整体,并没有体现模块化的思想。然而这毕竟是一种不错的开始,现在用这种方法的人已经不多了,因为它的代码组织形式不利于多人同时维护,只适合于单人开发。 也许你要说,怎么就不能多人维护了,我用Git不行吗?Git本来就是用来维护代码版本的。这样说当然也可以,在笔者看来,开源软件经常存在多人同时编译同一份代码文件的情况,虽然会有合并冲突,然而这都不是事儿,只要足够的小心,抱着开放的心态,问题总能够解决。但在生产环境之下,赶进度才是大事,每人只想管好自己那一块,别人的东西不想插手,插手也不合适,那是别人的工作边界,因此多人共同编辑并不必要,最多也就是浏览下别人的代码。
类库模块化
不同的模块开发成对应的类库工程,然后分别编译,或者与主程序一起编译,相当于把应用内模块化的功能目录从主工程中拿出来了,这又是一个进步,但是这种方法仅仅是提供了一种分布式开发的可能性,然而类库的调试还要依赖从主工程启动,因此在工程组织上,它与应用内模块化相差不大,同样属于一种比较原始的方法。

进程隔离模块化

把不同的功能开发成不同的进程,在进程间使用管道,共享内存,或者本地端口来共享数据。主进程可以直接向操作系统发送程序启动命令来启动另一个模块,这种开发方式的好处是进程隔离使得不同模块之间具有一定的安全性:如果某个模块崩溃,则其他的模块仍然可以正常工作。意味着不同的模块可以由不同的人或团队独立开发与测试,然而这仍然存在着一种可能性,那就是通信接口的调试要依赖于另一个模块,如果对方处于运行中,才可以得到回复。不过可以使用对方模块的一个原型,或者自己写一个Mock来替代,然而这需要建立一个完整的可运行工程,成本稍微有点高了。

静态插件式模块化

插件式模块化是真正的模块化,其基本原理是利用反射技术来加载实现了特定接口的模块,并调用此接口所实现的方法来访问插件所实现的功能。之所以叫做静态插件,是因为主程序在启动时检测与加载插件,当主程序启动完毕,所有的插件都被加载到内存中等待调用,它的内存代码是静态的。在代码组织方面,主工程暴露了一些必要的全局接口,像菜单,配置文件,工作区信息等,这样插件可以使用这些全局接口来访问主工程的一些功能,从而实现交互。不同的插件之间,也可以通过主工程的通信接口来通信。相比于进程隔离模块化来说,插件的调试依赖于主工程提供的接口,而不依赖于其他的插件,这就实现了插件之间的相互隔离。如果一个插件依赖于另一个插件的插口,可以简单地Mock一个接口而不是Mock一个进程,调试起来就更加方便了。 静态插件的应用十分广泛,比如为知笔记,再比如著名的Visual Studio提供的插件叫做“扩展”,也是这种静态方式。还有eclipse这款开源界的明星产品也是如此。

动态插件式模块化

动态插件与静态插件的不同之处在于,在安装了插件之后就可以使用插件提供的功能,而不必重启主程序。这是由于主程序把插件的代码动态加载到内存中执行,因此我把它叫做“动态插件”。 比如NotePad++是一款常用的文本编译器,相信读者一定不对对它感到陌生。它提供了一个插件库来扩展,就是这种方法。
接下来,我将单独写一篇文章来详细介绍插件式模块化的原理以及实现方法。

子表被级联删除时触发器失效

先说结论:如题,子表被级联删除时触发器失效。详细描述如下:

在MySql(5.7.18)中,如果一个表t2中的某列c2为外键,关联了另一表 t1中的列c1,外链关系为级联,则如果t1中某记录删除,会级联删除t2表中相关数据,但是如果 t2表有触发器,则不会触发。这里,t1是父表,t2是子表。

这可能是MySql的Bug,也有可能是MySql的一种机制,不管怎么说,都是个比较简单的问题,设计数据库时需要注意。

血泪教训暂不细表,只说测试过程如下:

先建父表t1,具有两列:c1, c2,其中,c1为主键,

  1. CREATE TABLE `t1` (
  2.   `c1` int(11) NOT NULL,
  3.   `c2` varchar(45) DEFAULT NULL,
  4.   PRIMARY KEY (`c1`)
  5. ) ENGINE=InnoDB DEFAULT CHARSET=latin1

创建子表t2,也有两列:c1是主键,c2是外键,引用了t1.c1:

  1. CREATE TABLE `t2` (
  2.   `c1` int(11) NOT NULL,
  3.   `c2` int(11) NOT NULL,
  4.   PRIMARY KEY (`c2`,`c1`),
  5.   KEY `fk_t12_idx` (`c1`),
  6.   CONSTRAINT `fk_t12` FOREIGN KEY (`c2`) REFERENCES `t1` (`c1`) ON DELETE CASCADE ON UPDATE NO ACTION
  7. )

注意,这里的外键有 ON DELETE CASCADE 约束。

创建日志表:

  1. CREATE TABLE `t2log` (
  2.   `time` datetime DEFAULT CURRENT_TIMESTAMP,
  3.   `content` varchar(225) DEFAULT NULL
  4. )

日志表存在的作用是,当t2数据被删除时,在触发器中向日志表中插入记录,就知道触发器生效了。

  1. DELIMITER $$
  2. CREATE TRIGGER `Trigger_SubstationBox_Deleted` AFTER DELETE ON `t2` FOR EACH ROW
  3. BEGIN
  4.     insert into t2log (content) values (CONCAT(OLD.c1, OLD.c2));
  5. END;
  6. $$ DELIMITER ;

以上就是 脚本清单,插入数据 如下:

现在,来删除t2表中的任意一条数据,比如(2,6):DELETE FROM t2 WHERE c1 = 2 AND c2 = 6;

第二行,就是本次删除数据插入的日志,证明触发器有效。

然后,删除t1表中的数据 c1 = 2,可以预见,t2表中的数据 (1,2)(2,2)都会删除,但是会不会在日志表 中留下记录呢?结论是不会。

再看日志表,仍与上次一样:

实际上,第一次遇到这种事情之后,我并没有太在意,因为线上项目很复杂,偶尔有不可知因素丢失一两条数据,或者,删除时没有删干净,都是低概率事件,一般都不可重复。但是当用户反复提交同一个错误时,我意识到这下麻烦大了。

最后在测试环境中重现了错误,解决方案是:在父表AFTER DELETE时,遍历一遍子表数据,执行子表的AFTER DELETE触发器逻辑。