mysql数据库性能以及多线程插入的初步研究

研究目的和背景
研究目的和背景 -众所周知，数据库是限制应用系统发挥其最大处理能力的瓶颈所在， 比如一个数据库读写操作所耗费的总时间有相当大一部分是在数据 持久层的执行； 例如河北配网数据处理项目上通过计时应用层读取并解析一条数据 的时间大约3-5毫秒，但是花费在插入或者更新数据库的时间大约是 大约20-30毫秒。实际执行一个全省数据的导入需要10分钟以上的时 间。 -虽然河北这个项目不是一个多用户高并发的场景，但是在每次数据 台账导入的时候要顺序读取解析,执行少则1-2万多则4-5万条数据的 insert或者update操作。即便尝试了所有数据库级别的优化，除非 切换到不支持事务的myISAM存储引擎使用insert into delayed…提 高插入效率 -从这个意义上讲：可以理解为在程序导入数据的时候会发生很高的 数据量插入或者更新的业务
研究范畴
-首先比较一下同等条件下不同数据库之间的插入性能比较 在这个基础上我们推测在一个数据库上的其他条件下的插入 性能可以反映到其他数据库上。 -其次专门研究目前使用最广泛的社区版数据库mySQL的插入 性能问题，结果推广到其他数据库。 - 与有关文献做一个比较
使用工具和方法
-插入程序的开发是在myEclipse中实现的；数据库使用mySQL5.7 windows64位版本; 开发jdk=1.6.45; 数据库连接文件使用的稳定版的 jdbc包，mySQL使用的最新的mysql-connectort-5.1.40.jar; 数据库比较分析中使用了oracle11gR2, 达梦7数据库，mySQL分支开 源项目mariaDB10稳定版和mySQL5.6 -无论哪种数据库都注意了使用批处理提交sql的编程方法，由于 mysql默认是事务开启的因此每次执行一些sql就会提交commit，为 了最大优化插入性能，在程序循环插入之前 setAutoCommit(false); 并且循环插入之后执行commit(); -在单线程案例中根据测试插入量的不同，一般取插入量20%的sql提 交一次commit 比如10000条插入的测试会在循环2000条的时候 commit一次。 -Oracle数据库和达梦好像没有mysql这种要求，直接执行batch处理 就可以
未来研究方向
-目前的测试都是瞬态的测试，既数据库的某一个随机时刻的性能，在 这个特定时刻有很多因素可能会干扰其性能，合理的测试应该是持续一 点时间的插值后取平均值；但是在日常业务中是否有这样持续高并发的 数据插入有待考证；持续大量数据插入也许在科研工作中才存在。 -目前只执行了mysql数据库的多线程和字段线程关系的测试，尚未在 其他数据库进行测试，但是根据数据库插入性能之间的比较可以推广认 为多线程的方法在其他数据库上有很大可能取得更高的表现。 -本测试使用的是uuid作为字段或者主键，规避了冲突问题，所以多线 程读写数据的逻辑还没有在真实业务场景中得到检验，需要在真实现场 实现后观察。 -本研究仅仅尝试了在单例的数据库上实现多线程的插入，是否多线程 方法匹配多实例分布式数据库跨库读写会取得更好表现令人着迷。 -更多搜索和比较国外网友的研究成果
典型mySQL商业测试
从服务器每秒中返回的页面数，用户数是并发的用户数测试方式。所 有测试都部署在同一种硬件上（HP NetServerLT6000 4 Xeon CPU 2GB 内存和千兆以太网卡）；从测试结论上看：只有oracle和mysql 可以长时间运行客户的应用程序而没有问题；oracle & mysql数据 库驱动程序是最稳定的；
文献研究
2.插入速度和插入数据量的大小，建立 16个线程，但是都是串行每个线程定时 插入600秒
中科院物理研究所的同志为了满足中国散 裂源项目的数据监测和分析任务，进行了C 语言读写mySQL数据库服务在特定条件下 事务处理能力研究，内容与本研究相似.研 究包括查询速度的内容，但是这里只关注 插入速度的研究部分内容
数据库插入性能以及 多线程插入的初步研究
报告人：电网业务中心工程师 张凌飞
报告大纲
• • • • • • • • • 研究目的和背景 文献研究 研究范畴 使用工具和研究方法 MySQL与不同数据库插入性能之间的比较 MySQL数据库多线程插入性能研究 数据库插入字段数和性能之间的关系 未来研究方向 建议和互动
数据库插入字段数和性能之间的关系
• 在同等条件下，增加多线程插入的单个表的字段数对于插入速度影响并不显著 • 每次测试的结果其实都不一样，是否应该长时间插入并且去平均值和误差 • 但是总体趋势是 单线程到多线程插入 随着字段数超过16， 数据插入效率会比较明显的下降。但是2线程的情 况有所例外。 • 总体来看，虽然规律不规则，但是随着线程数提高，同样字段的表插入速度会有提高，这种特征在从2线程到 4,8线程最明显，但是在不同字段数=96的时候从4线程以上提升不太明显 • 我推测，在字段数-线程数-CPU核心数之间有一个最优化组合，需要根据具体情况选择最优。
1.插入速度和线程之间关系：建立16个 线程，但是都是串行每个பைடு நூலகம்程定时插入 600秒
该研究使用了多达800个以上的数据库连 接线程，在其my.in配置文件中 max_connection设置为mySQL最大默认 值16384; 每次提交50个作业，每个作业 16个线程，这样的话一个作业每秒插入速 度22k/50=440; 16个线程每秒
MySQL与不同数据库 插入性能之间的比较
Mysql: 1000/1.652=605 insert/s
测试存在的问题？执行时间太短，不是长循环， 理想测试是长时间循环插值然后去平均
MySQL数据库多线程插入性能研究
• 左图插入1000条数据40个线程需要15K毫秒左右，而在右图中 实际需要不超过1400毫秒。这原因是左图是在家用VPN连接公 司mysql数据库，所以延时部分主要来自网络的延时。 • 另外发现当数据量很小的=1000的时候，多线程并不会带来巨 大的收益，除非是8线程或者4线程，而100线程反而还会低于 单线程。考虑到系统运行在8核心电脑上，推测是线程数与核 心数匹配问题 • 上述案例中当线程数超高跟40后，其实没测20；效率几乎没有 提高。看似线程数与核心数呈现2n次方关系的时候效率提高最 多，因此猜测如果机器从8核心升级16核心32线程甚至更高的 硬件配置，也许才有可能从提高线程数获得更高收益。
文献：mysql性能白皮书
几个典型mySQL商业测试
2005年mySQL实验室在SunFire 96GB内存Solaris操作系统上测试了性能。该系 统拥有可以开关的单核或双核模式 CPU； sysBench软件用于模拟OLTP负载