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 带宽与稳定性,是一个矛盾,两者不可兼得。

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

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

在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触发器逻辑。