(1)提高数据库插入性能中心思想:尽量将数据一次性写入到Data File和减少数据库的checkpoint 操作。这次修改了下面四个配置项:
1)将 innodb_flush_log_at_trx_commit 配置设定为0;按过往经验设定为0,插入速度会有很大提高。
0: Write the log buffer to the log file and flush the log file every second, but do nothing at transaction commit.
1:the log buffer is written out to the log file at each transaction commit and the flush to disk operation is performed on the log file
2:the log buffer is written out to the file at each commit, but the flush to disk operation is not performed on it
2)将 innodb_autoextend_increment 配置由于默认8M 调整到 128M
此配置项作用主要是当tablespace 空间已经满了后,需要MySQL系统需要自动扩展多少空间,每次tablespace 扩展都会让各个SQL 处于等待状态。增加自动扩展Size可以减少tablespace自动扩展次数。
3)将 innodb_log_buffer_size 配置由于默认1M 调整到 16M
此配置项作用设定innodb 数据库引擎写日志缓存区;将此缓存段增大可以减少数据库写数据文件次数。
4)将 innodb_log_file_size 配置由于默认 8M 调整到 128M
此配置项作用设定innodb 数据库引擎UNDO日志的大小;从而减少数据库checkpoint操作。
经过以上调整,系统插入速度由于原来10分钟几万条提升至1秒1W左右;注:以上参数调整,需要根据不同机器来进行实际调整。特别是 innodb_flush_log_at_trx_commit、innodb_log_buffer_size和 innodb_log_file_size 需要谨慎调整;因为涉及MySQL本身的容灾处理。
(2)提升数据库读取速度,重数据库层面上读取速度提升主要由于几点:简化SQL、加索引和分区; 经过检查程序SQL已经是最简单,查询条件上已经增加索引。我们只能用武器:表分区。
数据库 MySQL分区前准备:在MySQL中,表空间就是存储数据和索引的数据文件。
将S11数据库由于同享tablespace 修改为支持多个tablespace;
将wb_user_info_sina 和 wb_user_info_tx 两个表修改为各自独立表空间;(Sina:1700W数据,2.6G 大数据文件,Tencent 1400W,2.3G大数据文件);
分区操作:
将现有的主键和索引先删除
重现建立id,uid 的联合主键
再以 uid 为键值进行分区。这时候到/var/data/mysql 查看数据文件,可以看到两个大表各自独立表空间已经分割成若干个较少独立分区空间。(这时候若以uid 为检索条件进行查询,并不提升速度;因为键值只是安排数据存储的分区并不会建立分区索引。我非常郁闷这点比Oracle 差得不是一点半点。)
再以 uid 字段上进行建立索引。再次到/var/data/mysql 文件夹查看数据文件,非常郁闷地发现各个分区Size竟然大了。MySQL还是老样子将索引与数据存储在同一个tablespace里面。若能index 与 数据分离能够更加好管理。
经过以上调整,暂时没能体现出系统读取速度提升;基本都是在 2~3秒完成5K数据更新。
MySQL数据库插入速度调整补充资料:
MySQL 从最开始的时候 1000条/分钟的插入速度调高至 10000条/秒。 相信大家都已经等急了相关介绍,下面我做调优时候的整个过程。提高数据库插入性能中心思想:
1、尽量使数据库一次性写入Data File
2、减少数据库的checkpoint 操作
3、程序上尽量缓冲数据,进行批量式插入与提交
4、减少系统的IO冲突
根据以上四点内容,作为一个业余DBA对MySQL服务进行了下面调整:
修改负责收录记录MySQL服务器配置,提升MySQL整体写速度;具体为下面三个数据库变量值:innodb_autoextend_increment、innodb_log_buffer_size、innodb_log_file_size;此三个变量默认值分别为 5M、8M、8M,根据服务器内存大小与具体使用情况,将此三只分别修改为:128M、16M、128M。同时,也将原来2个 Log File 变更为 8 个Log File。此次修改主要满足第一和第二点,如:增加innodb_autoextend_increment就是为了避免由于频繁自动扩展Data File而导致 MySQL 的checkpoint 操作;
将大表转变为独立表空并且进行分区,然后将不同分区下挂在多个不同硬盘阵列中。
完成了以上修改操作后;我看到下面幸福结果:
获取测试结果:
Query OK, 2500000 rows affected (4 min 4.85 sec)
Records: 2500000 Duplicates: 0 Warnings: 0
Query OK, 2500000 rows affected (4 min 58.89 sec)
Records: 2500000 Duplicates: 0 Warnings: 0
Query OK, 2500000 rows affected (5 min 25.91 sec)
Records: 2500000 Duplicates: 0 Warnings: 0
Query OK, 2500000 rows affected (5 min 22.32 sec)
Records: 2500000 Duplicates: 0 Warnings: 0
最后表的数据量:
+------------+
| count(*) |
+------------+
| 10000000|
+------------+
从上面结果来看,数据量增加会对插入性能有一定影响。不过,整体速度还是非常面议。一天不到时间,就可以完成4亿数据正常处理。预计数据库瓶颈已经被巧妙解决,结果变成程序“猿”苦逼地向我埋怨,大哥不用这么狠啊。
mysql数据库插入速度和读取速度的调整记录
2019-12-06 15:50MYSQL教程网 Mysql
由于项目变态需求;需要在一个比较短时间段急剧增加数据库记录(两三天内,由于0增加至4亿)。在整个过程调优过程非常艰辛
延伸 · 阅读
- 2022-06-24实战搭建MySQL高可用架构(手残党表示都会了!)
- 2022-06-24MySQL 数据库查询慢(除了索引问题还可能是什么原
- 2022-06-24面试官:线上MySQL的自增id用尽怎么办?
- 2022-06-24Python连接数据库并批量插入包含日期记录的操作
- 2022-06-07MongoDB创建与删除数据库
- 2022-06-06NoSQL优缺点与mongoDB数据库简介
- Mysql
清理Mysql general_log的方法总结
在本篇文章里小编给大家分享的是一篇关于清理Mysql general_log的相关知识点,需要的朋友们学习下。...
- Mysql
MySQL教程彻底学懂存储过程
这篇文章主要为大家介绍了MySQL系列的存储过程,文中详细的为大家解释存储过程的相关概念及用法语法,以及对存储过程的理解解析,有需要的朋友可以...
- Mysql
MySQL启动报错问题InnoDB:Unable to lock/ibdata1 error
这篇文章主要介绍了MySQL启动报错问题InnoDB:Unable to lock/ibdata1 error,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要...
- Mysql
win10 64位 MySQL8.0下载和安装教程图解
这篇文章主要介绍了MySQL下载和安装 win10 64位 MySQL8.0的教程图解,本文图文并茂给大家介绍的非常详细,具有一定的参考借鉴价值,需要的朋友可以参考下...
- Mysql
幻读为什么会被 MySQL 单独拎出来解决?
所谓幻读,即一个事务在前后两次查询同一个范围的时候,后一次查询看到了前一次查询没有看到的行,这个回答估计大伙儿已经背烂了,但是它具体有什...
- Mysql
mysql数据库备份及恢复命令 mysqldump,source的用法
mysql数据库备份及恢复命令 mysqldump,source的用法,需要的朋友可以参考下。 ...
- Mysql
mysql 5.6.17 绿色版(免安装)安装配置教程
这篇文章主要为大家详细介绍了mysql 5.6.17 绿色版(免安装)安装配置教程,感兴趣的小伙伴们可以参考一下...
- Mysql
数据库管理,不能没有这5个MySQL GUI工具
MySQL的数据库管理工具非常多,有哪些优秀的GUI工具可以帮助提高工作效率?不妨看一看这5个MySQL GUI工具...