RDS MySQL空间优化最佳实践

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云原生数据库 PolarDB 分布式版,标准版 2核8GB
简介: 在前三期介绍了RDS for MySQL参数优化,锁问题以及延迟优化最佳实践之后,本期将介绍存储空间相关的最佳实践。
在前三期介绍了RDS for MySQL参数优化,锁问题以及延迟优化最佳实践之后,本期将介绍存储空间相关的最佳实践。

存储空间是RDS很重要的一个指标,在RDS的工单问题中,空间问题的咨询可以排在top 5,当RDS的实际使用空间超过了购买的空间后,实例就会被锁定了,这样就会导致应用无法再写入,更新数据,造成应用的报错。在RDS的控制台中可以设定空间的报警阀值,当实例空间到达报警阀值后用户就会收到报警短信,这个时候用户则需要对判断当前的空间增长是否合理。如果增长合理则需要对实例的进行弹性升级,这里需要指出的是弹性升级分为两种,一种是本地升降级,该类升级是实例所在的主机磁盘空间充足,足以容纳升级所需的空间,这个时候的升级非常迅速,而且对应用毫无影响;第二种是跨机升级,该类升级时实例所在的主机剩余磁盘空间不足以容纳升级所需的空间,那么就需要将实例迁移到其他磁盘资源足够的主机上,这时需要的时间可能会很长,取决于实例的总空间大小,因为迁移过程涉及将备份拷贝还原到新的主机上,同时还要考虑新实例与旧实例的数据同步问题,那么这一些因素都会导致升级时间较长,最后升级结束时候数据库中的已有连接会全部断开(备注:高安全访问链路没有此问题)。如果增长不合理,则需要进行快速的判断问题出现在那,

screenshot

则需要我们了解RDS的空间组成到底包括了哪些。在RDS控制台中可以看到空间的组成分为了5部分,分别为:磁盘总空间,数据空间,日志空间,临时文件空间,系统文件空间。
screenshot

接下来我们将一一介绍一下这些文件组成。
一. 数据文件:

顾名思义该文件空间则是指的存放用数据的文件,对应到数据库中就是一张张的表,表的组成主要包括:数据和索引两类,所以当你看到你的数据文件占用实例的空间非常多的时候,你需要看一下到底是哪一张表占用了我的空间,用户可以通过数据库的数据字典找到系统中占用最大的表:

select TABLE_SCHEMA,TABLE_NAME,INDEX_LENGTH/1024/1024 as index_M,DATA_LENGTH/1024/1024 as data_M from TABLES order by (INDEX_LENGTH+DATA_LENGTH) desc limit 10
凡事预则立,不预则废,我们可以未雨绸缪,在设计应用的初期就考虑好系统的存储:

  1. 未来数据的增长趋势,决定磁盘的空间大小;
  2. 数据的生命保留周期,决定是否进行数据删除或归档;
  3. 设计表选用合理的数据类型,字段大小,存储引擎,进行分区还是分表;
    下图的案例中,数据空间占用了实例大量的空间,那么可以通过上述方法定位数据库中到底是那些张表占用空间导致的问题:

screenshot
常见的空间问题:

  1. 对表进行数据删除后空间不会进行释放
    最佳实践:需要对表进行重建,重建的方法:Optimize table xxxxx,该方法在5.6以下会导致锁表,RDS 5.6支持在线重建。
  2. 大表索引占用的空间比数据空间还大
    最佳实践:需要将表中无用或者重复的索引删除,删除索引需要特别注意该索引是否还在使用。
  3. 大表主要用作日志型业务数据存储,基本都是插入,很少查询
    最佳实践:可以使用tokudb引擎将表中的数据进行压缩,通常压缩效率在3倍以上,注意使用tokudb引擎需要调整tokudb的buffer,可参考参数优化loose_tokudb_buffer_pool_ratio。

二. 日志文件:

RDS MySQL采用主从M-M的高可用架构,其主备之间的数据同步依靠binlog日志。为了减少binlog日志对用户的空间的占用,RDS会定时把日志备份到oss中,然后将本地的binlog清除。当日志空间出现异常的时候,如下图,由于应用写入数据压力过大,导致binlog日志增加的速度大于了RDS上传到oss的速度,造成了binlog日志增长迅猛,这时候需要用户对数据库进行优化,减小对数据库的变更操作。

screenshot

  1. 曾经看到这样的案例,应用频繁的对表进行更新,但是在该表上有较多的大字段,由于在row格式下,binlog会记录整行记录,这样就导致了binlog增长非常迅猛,详细可以参考Mysql大字段的频繁更新导致binlog暴增。所以在应用的设计初期,就要避免使用大字段:varchar(8000),text,blob,clob等。
  2. 还有一种情况可能是主备的复制卡主或者中断,则会导致主库的binlog没有传递到备库,那么这个时候binlog会一直在主库堆积,那么就需要提工单要求尽快处理了。
    三. 临时文件:

    临时文件通常可以理解为数据库做一个大的操作,由于内存不足,数据库需要将内存中的文件写到磁盘上,这样则有可能导致临时文件写的非常大,通常出现这种情况的时候,数据库在做大的排序操作(order by,group by,distinct)。下图的案例中,由于数据库中一条order by的语句频繁的执行,但是排序sql没有索引,导致了临时文件的频繁写操作:

    screenshot

  3. 当临时空间上涨原因是SQL排序导致的时候,可以通过show processlist快速找出排序的SQL,然后kill 掉SQL;
  4. 同时对排序的sql添加合适的索引,避免排序,这是治根的办法,避免数据库中出现排序的SQL;
  5. 为了避免排序消耗的空间过大,可以设置临时空间的大小,具体可以参考RDS参数优化loose_rds_max_tmp_disk_space;
    四. 系统文件:

    系统文件是每个数据库在安装的时候会初始化一些系统文件,这些系统文件是数据库正常运行的前提,mysql:ibdata1,ib_logfile0,下面的这幅图反映了 “其他文件”占用达到了非常多的问题,可以参考:ibdata1文件持续增加的问题定位 

    screenshot

  6. ibdata1文件中大量的都是undo_log,建议将版本升级到5.6以上有独立的purge线程可以很快的回收掉undo log,可以单独设置undo tablespace文件,避免与ibdata1混用在一起;
  7. 同时也可以采用逻辑迁移的方式,重建ibdata1文件;
  8. 数据库中要注意未提交的事务对undo的影响,监控数据库中的INNODB_TRX视图。

    综上所述,空间问题也是一个比较复杂的问题,但是如果我们能够在系统设计之初遵循一些最佳实践,那么我们还是能够很好的处理掉这些问题,祝你玩得开心,用得放心。
    
相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
5天前
|
关系型数据库 MySQL Linux
MySQL原理简介—6.简单的生产优化案例
本文介绍了数据库和存储系统的几个主题: 1. **MySQL日志的顺序写和数据文件的随机读指标**:解释了磁盘随机读和顺序写的原理及对数据库性能的影响。 2. **Linux存储系统软件层原理及IO调度优化原理**:解析了Linux存储系统的分层架构,包括VFS、Page Cache、IO调度等,并推荐使用deadline算法优化IO调度。 3. **数据库服务器使用的RAID存储架构**:介绍了RAID技术的基本概念及其如何通过多磁盘阵列提高存储容量和数据冗余性。 4. **数据库Too many connections故障定位**:分析了MySQL连接数限制问题的原因及解决方法。
|
8天前
|
SQL 关系型数据库 MySQL
MySQL进阶突击系列(07) 她气鼓鼓递来一条SQL | 怎么看执行计划、SQL怎么优化?
在日常研发工作当中,系统性能优化,从大的方面来看主要涉及基础平台优化、业务系统性能优化、数据库优化。面对数据库优化,除了DBA在集群性能、服务器调优需要投入精力,我们研发需要负责业务SQL执行优化。当业务数据量达到一定规模后,SQL执行效率可能就会出现瓶颈,影响系统业务响应。掌握如何判断SQL执行慢、以及如何分析SQL执行计划、优化SQL的技能,在工作中解决SQL性能问题显得非常关键。
|
1天前
|
存储 缓存 关系型数据库
MySQL底层概述—5.InnoDB参数优化
本文介绍了MySQL数据库中与内存、日志和IO线程相关的参数优化,旨在提升数据库性能。主要内容包括: 1. 内存相关参数优化:缓冲池内存大小配置、配置多个Buffer Pool实例、Chunk大小配置、InnoDB缓存性能评估、Page管理相关参数、Change Buffer相关参数优化。 2. 日志相关参数优化:日志缓冲区配置、日志文件参数优化。 3. IO线程相关参数优化: 查询缓存参数、脏页刷盘参数、LRU链表参数、脏页刷盘相关参数。
MySQL底层概述—5.InnoDB参数优化
|
3天前
|
关系型数据库 MySQL 数据库
从MySQL优化到脑力健康:技术人与效率的双重提升
聊到效率这个事,大家应该都挺有感触的吧。 不管是技术优化还是个人状态调整,怎么能更快、更省力地完成事情,都是我们每天要琢磨的事。
50 23
|
3天前
|
SQL 关系型数据库 MySQL
MySQL原理简介—11.优化案例介绍
本文介绍了四个SQL性能优化案例,涵盖不同场景下的问题分析与解决方案: 1. 禁止或改写SQL避免自动半连接优化。 2. 指定索引避免按聚簇索引全表扫描大表。 3. 按聚簇索引扫描小表减少回表次数。 4. 避免产生长事务长时间执行。
|
20天前
|
监控 关系型数据库 MySQL
Aurora MySQL负载突增应对策略与优化方案
通过以上策略,企业可以有效应对 Aurora MySQL 的负载突增,确保数据库在高负载情况下依然保持高性能和稳定性。这些优化方案涵盖了从架构设计到具体配置和监控的各个方面,能够全面提升数据库的响应速度和处理能力。在实际应用中,应根据具体的业务需求和负载特征,灵活调整和应用这些优化策略。
49 22
|
25天前
|
缓存 关系型数据库 MySQL
【深入了解MySQL】优化查询性能与数据库设计的深度总结
本文详细介绍了MySQL查询优化和数据库设计技巧,涵盖基础优化、高级技巧及性能监控。
184 0
|
20天前
|
关系型数据库 MySQL 数据库连接
数据库连接工具连接mysql提示:“Host ‘172.23.0.1‘ is not allowed to connect to this MySQL server“
docker-compose部署mysql8服务后,连接时提示不允许连接问题解决
|
7天前
|
关系型数据库 MySQL 数据库
Docker Compose V2 安装常用数据库MySQL+Mongo
以上内容涵盖了使用 Docker Compose 安装和管理 MySQL 和 MongoDB 的详细步骤,希望对您有所帮助。
75 42
|
2月前
|
存储 Oracle 关系型数据库
数据库传奇:MySQL创世之父的两千金My、Maria
《数据库传奇:MySQL创世之父的两千金My、Maria》介绍了MySQL的发展历程及其分支MariaDB。MySQL由Michael Widenius等人于1994年创建,现归Oracle所有,广泛应用于阿里巴巴、腾讯等企业。2009年,Widenius因担心Oracle收购影响MySQL的开源性,创建了MariaDB,提供额外功能和改进。维基百科、Google等已逐步替换为MariaDB,以确保更好的性能和社区支持。掌握MariaDB作为备用方案,对未来发展至关重要。
71 3

相关产品

  • 云数据库 RDS MySQL 版
  • 云数据库 RDS