文档章节

mysql数据库插入速度和读取速度的调整记录

j
 jessie_lyj1
发布于 2017/06/23 17:29
字数 1405
阅读 10
收藏 0

由于项目变态需求;需要在一个比较短时间段急剧增加数据库记录(两三天内,由于0增加至4亿)。在整个过程调优过程非常艰辛
-
(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亿数据正常处理。预计数据库瓶颈已经被巧妙解决,结果变成程序“猿”苦逼地向我埋怨,大哥不用这么狠啊。 
 

本文转载自:http://blog.csdn.net/ewili/article/details/8652743

j
粉丝 0
博文 54
码字总数 6933
作品 0
天津
私信 提问

暂无文章

CentOS7.6中安装使用fcitx框架

内容目录 一、为什么要使用fcitx?二、安装fcitx框架三、安装搜狗输入法 一、为什么要使用fcitx? Gnome3桌面自带的输入法框架为ibus,而在使用ibus时会时不时出现卡顿无法输入的现象。 搜狗和...

技术训练营
昨天
5
0
《Designing.Data-Intensive.Applications》笔记 四

第九章 一致性与共识 分布式系统最重要的的抽象之一是共识(consensus):让所有的节点对某件事达成一致。 最终一致性(eventual consistency)只提供较弱的保证,需要探索更高的一致性保证(stro...

丰田破产标志
昨天
8
0
docker 使用mysql

1, 进入容器 比如 myslq1 里面进行操作 docker exec -it mysql1 /bin/bash 2. 退出 容器 交互: exit 3. mysql 启动在容器里面,并且 可以本地连接mysql docker run --name mysql1 --env MY...

之渊
昨天
10
0
python数据结构

1、字符串及其方法(案例来自Python-100-Days) def main(): str1 = 'hello, world!' # 通过len函数计算字符串的长度 print(len(str1)) # 13 # 获得字符串首字母大写的...

huijue
昨天
6
0
PHP+Ajax微信手机端九宫格抽奖实例

PHP+Ajax结合lottery.js制作的一款微信手机端九宫格抽奖实例,抽奖完成后有收货地址添加表单出现。支持可以设置中奖概率等。 奖品列表 <div class="lottery_list clearfix" id="lottery"> ......

ymkjs1990
昨天
5
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部