热门标签 | HotTags
当前位置:  开发笔记 > 后端 > 正文

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

由于项目变态需求;需要在一个比较短时间段急剧增加数据库记录(两三天内,由于0增加至4亿)。在整个过程调优过程非常艰辛

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

推荐阅读
  • 本文探讨了如何在PHP与MySQL环境中实现高效的分页查询,包括基本的分页实现、性能优化技巧以及高级的分页策略。 ... [详细]
  • 本文详细介绍了MySQL InnoDB存储引擎中的Redo Log和Undo Log,探讨了它们的工作原理、存储方式及其在事务处理中的关键作用。 ... [详细]
  • 本文探讨了MySQL中的死锁现象及其监控方法,并介绍了如何通过配置和SQL语句调整来优化数据库性能。同时,还讲解了慢查询日志的配置与分析技巧。 ... [详细]
  • MVC模式下的电子取证技术初探
    本文探讨了在MVC(模型-视图-控制器)架构下进行电子取证的技术方法,通过实际案例分析,提供了详细的取证步骤和技术要点。 ... [详细]
  • 本文探讨了使用Python实现监控信息收集的方法,涵盖从基础的日志记录到复杂的系统运维解决方案,旨在帮助开发者和运维人员提升工作效率。 ... [详细]
  • 【MySQL】frm文件解析
    官网说明:http:dev.mysql.comdocinternalsenfrm-file-format.htmlfrm是MySQL表结构定义文件,通常frm文件是不会损坏的,但是如果 ... [详细]
  • 本文介绍了MySQL窗口函数的基本概念、应用场景及常见函数的使用方法。窗口函数在处理复杂查询时非常有用,例如计算每个用户的订单排名、环比增长率、以及动态聚合等。 ... [详细]
  • 本文回顾了作者在求职阿里和腾讯实习生过程中,从最初的迷茫到最后成功获得Offer的心路历程。文中不仅分享了个人的面试经历,还提供了宝贵的面试准备建议和技巧。 ... [详细]
  • 解决ADODB连接Access时出现80004005错误的方法
    本文详细介绍了如何解决在使用ADODB连接Access数据库时遇到的80004005错误,包括错误原因分析和具体的解决步骤。 ... [详细]
  • 本文详细解析了MySQL中常见的几种错误,并提供了具体的解决方法,帮助开发者快速定位和解决问题。 ... [详细]
  • 搭建个人博客:WordPress安装详解
    计划建立个人博客来分享生活与工作的见解和经验,选择WordPress是因为它专为博客设计,功能强大且易于使用。 ... [详细]
  • 七大策略降低云上MySQL成本
    在全球经济放缓和通胀压力下,降低云环境中MySQL数据库的运行成本成为企业关注的重点。本文提供了一系列实用技巧,旨在帮助企业有效控制成本,同时保持高效运作。 ... [详细]
  • 本文介绍了如何通过安装 sqlacodegen 和 pymysql 来根据现有的 MySQL 数据库自动生成 ORM 的模型文件(model.py)。此方法适用于需要快速搭建项目模型层的情况。 ... [详细]
  • H5技术实现经典游戏《贪吃蛇》
    本文将分享一个使用HTML5技术实现的经典小游戏——《贪吃蛇》。通过H5技术,我们将探讨如何构建这款游戏的两种主要玩法:积分闯关和无尽模式。 ... [详细]
  • 本文详细介绍了Oracle 11g中的创建表空间的方法,以及如何设置客户端和服务端的基本配置,包括用户管理、环境变量配置等。 ... [详细]
author-avatar
D萳飝赝_870
这个家伙很懒,什么也没留下!
PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有