MySQL插入性能优化
MySQL插入性能优化
标签: 博客
[TOC]
可以从如下几个方面优化MySQL的插入性能。
代码优化
values 多个
即拼接成一个insert values sql, 例如
INSERT INTO MyTable ( Column1, Column2, Column3 ) VALUES ('John', 123, 'Lloyds Office'), ('Jane', 124, 'Lloyds Office'), ('Billy', 125, 'London Office'), ('Miranda', 126, 'Bristol Office');
一个事务
开启一个事务,批量操作完了才提交事务,而不是,操作一次就提交一次,这样io太高,插入太慢。
插入字段尽量少,尽量用默认值
注意事项: max_allowed_packet 默认是1M,如何insert values sql 太大需要上调这个值
关闭 unique_checks
优化效果不是很明显,下面截图 选自 《MySQL 数据库开发、优化与管理维护 第2版》书籍
bulk_insert_buffer_size
这个参数只能对 MyISAM使用,innodb无效
配置优化
innodb_buffer_pool_size 缓冲区配置
什么是 innodb_buffer_pool_size
MySQL 缓存表数据,索引数据的地方。增加它的值可以减少 磁盘 io ,提升 读写性能。
提升读的原理:因为 buffer_pool_size 设置的比较大, 很多表数据和索引已缓存到 buffer pool , 要查询的数据在缓存中找到了,就不需要访问磁盘了。读性能就得到了提升。
提升写的原理:因为 buffer_pool_size 设置的比较大, 写的数据,暂时以脏页的方式放在内存,然后慢慢落到磁盘,如果buffer_pool_size 太小就没办法缓存写操作,写一次访问一次磁盘 ,写入性能就比较慢。(实际自测增大buffer_pool_size后,并未带来纯写操作的性能提升, 这块有待进一步研究)
设置多大的 innodb_buffer_pool_size 合适?
通常将innodb_buffer_pool_size其配置为物理内存的50%到75%
相关参数设置
innodb_buffer_pool_instances
一般将 innodb_buffer_pool_size 值增大后,需要增加配置 innodb_buffer_pool_instances 的值。
innodb_buffer_pool_instances 是 buffer_pool 实例数量,默认为1。增加它的值,可以减少数据库内部的资源竞争,增加并发处理能力。
如何设置innodb_buffer_pool_instances? innodb_buffer_pool_instances 的范围是 1 (the default) up to 64 (the maximum). 可以将 innodb_buffer_pool_instances 的个数设置为 buffer pool size 的 十分之一, 比如 innodb_buffer_pool_size 是 30g ,那 innodb_buffer_pool_instances 就设置为 3;
innodb_buffer_pool_size 注意事项
因为有额外内存的使用,如果指定 innodb_buffer_pool_size 为 12g 实际占用内存可能是 14g +
事务日志配置
innodb_log_file_size
默认值 48MB 设置的太小:比如用默认值48MB,当一个日志文件写满后,innodb会自动切换到另外一个日志文件,而且会触发数据库的检查点(Checkpoint),这会导致innodb缓存脏页的小批量刷新,会明显降低innodb的性能。
设置的太大:设置很大以后减少了checkpoint,并且由于redo log是顺序I/O,大大提高了I/O性能。但是如果数据库意外出现了问题,比如意外宕机,那么需要重放日志并且恢复已经提交的事务,如果日志很大,那么将会导致恢复时间很长。甚至到我们不能接受的程度。
总结: innodb_log_file_size设置得太小无法释放数据库性能,设置得太大,会增加宕机后日志重放恢复的时间。
innodb_log_files_in_group
重做日志组中的日志数量,默认值是2 ,一般用默认值也可以。
innodb_log_buffer_size
将日志写入磁盘日志文件前的缓冲大小,默认值 8MB,一般用默认值也可以。
读写线程增加
合理增加 innodb_write_io_threads,innodb_read_io_threads 两个配置的值即可。
实践比较
环境:centos 7, MySQL 6.7 , 8G,i5 操作:jmeter 64个并发插入数据,每个并发插入320条数据,每条数据插入前都会随机查询一次数据库。
优化前的配置
innodb_buffer_pool_size = 134217728 //128MB innodb_buffer_pool_instances = 1
优化后的配置
innodb_buffer_pool_size = 1G innodb_buffer_pool_instances = 1
性能结果
- buffer_pool_size 128MB:耗时172s
- buffer_pool_size 1GB:耗时58s
结论
buffer pool 缓冲区增加8倍内存, 换来 3 倍 读性能提升,3 倍 含读的写性能提升(含读的写,指的是在insert 前,进行了数据库查询,将查得的数据赋值给了 insert 字段), 对于纯写几乎没有性能提升。同理,如果缓冲区增加5.3倍内存,理论可以提升2倍性能提升。
硬件优化
最影响数据库性能的是磁盘 io,上 ssd 可以大大提升性能,其次是 cpu,内存 。
架构优化
读写分离,降低单机io的压力。
配置优化参考文章
https://mp.weixin.qq.com/s/DjM4jl5v0IleXLl_QFoyOw https://github.com/jdaaaaaavid/mysql_best_configuration

低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。
持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。
转载内容版权归作者及来源网站所有,本站原创内容转载请注明来源。
- 上一篇
使用 ale.js 制作一个小而美的表格编辑器(3)
今天来教大家如何使用 ale.js 制作一个小而美的表格编辑器,首先先上 gif: 是不是还是有一点非常 cool 的感觉的?那么我们现在开始吧! 这是我们这篇文章结束后完成的效果(如果想继续完成请访问第四篇文章): ok,那继续开始吧(本篇文章是表格编辑器系列的第三篇文章,如果您还没有看过第一篇,请访问第一篇文章(开源中国)): 首先让我们把每一个列表项都添加一个他们的行数和列数作为 dataset 数据吧! 先创建一个 rowId 变量: //在 handleTemplateRender 函数里,我们把: var returnVal = "<table><thead><tr>", getSortSign = this.methods.getSortSign, sortBy = this.staticData.sortBy; //改为 var returnVal = "<table><thead><tr>", getSortSign = this.methods.getSortSign, sor...
- 下一篇
RocketMQ 源码解析 —— 调试环境搭建
摘要: 原创出处 http://www.iocoder.cn/RocketMQ/build-debugging-environment/ 「芋道源码」欢迎转载,保留摘要,谢谢! 0. 友情提示 1. 依赖工具 2. 源码拉取 3. 启动 RocketMQ Namesrv 4. 启动 RocketMQ Broker 5. 启动 RocketMQ Producer 6. 启动 RocketMQ Consumer 666. 彩蛋 《Dubbo 实现原理与源码解析 —— 精品合集》 《Netty 实现原理与源码解析 —— 精品合集》 《Spring 实现原理与源码解析 —— 精品合集》 《MyBatis 实现原理与源码解析 —— 精品合集》 《Spring MVC 实现原理与源码解析 —— 精品合集》 《数据库实体设计合集》 《Java 面试题 —— 精品合集》 《Java 学习指南 —— 精品合集》 0. 友情提示 阅读源码之前,建议胖友对 RocketMQ 的文档已经熟读。目前 RocketMQ 4 的中文文档很少,所以英文不太好的胖友,推荐看看如下资料: 《RocketMQ 用户指南》 ...
相关文章
文章评论
共有0条评论来说两句吧...