MySql分区表性能测试及切换案例

  • 时间:
  • 浏览:0
  • 来源:彩神欢乐生肖_神彩欢乐生肖官方

互联网公司的业务变化变慢,数据库表形态设计相对比较直接,很少会在前期设计的很完善。当业务存活并发展起来后,就都能否在扩展性、安全性等方面进行改进。

比如,或多或少人 一张记录用户清况 的表,存储在RDS for MySql(InnoDB存储引擎)中。此业务表最近膨胀到1.5亿条记录,存储占用1000多G,且数据还在不断增长。

分区表是MySql 5.1引入的形态。根据官网alter-table-partition-operations的介绍,其本质是将分库分表直接集成到MySql中。或多或少人 知道,传统的分库分表功能,地处业务层、里边件、数据库三层:业务层通过调用里边件的API访问数据库,别问我具体的物理存储细节;里边件将一张很大的逻辑表映射到数据库中多张较小的物理表,并对业务层的访问请求进行分解后分别放入对应物理库中执行,再将执行结果在里边件合并后返回给业务层,从而对业务层屏蔽物理存储细节;数据库则提供实际的物理存储。

下面分别针对 插入、查询、DDL、存储空间 等十好多个 关键性能指标进行测试(更新和删除数据的性能表现与查询数据比较一致,不单独分析)。测试结果如下:

逻辑上分析,分区表的优点很明显:既能防止大数据量的性能哪几种的间题,又能对应用层无缝切换。或者,其真实性能表现和稳定性到底为社 在么在样? 还是得通过测试来验证。

通过里边的分析,或多或少人 都能否看出,InnoDB表数据量过大对各种操作都地处较大的性能影响。针对哪几种哪几种的间题,有以下并算是优化方案:

为方便说明,或多或少人 将此业务表逻辑形态简化为只涵盖以下4列:id(自增列),depart_id(部门ID),user_id(员工ID),mark(员工业绩)。

而MySql的分区表,借助MySql并算是的逻辑架构,将分库分表功能进行了下沉。MySql逻辑架构中的客户端即对应业务层,Server层对应里边件层,存储引擎层对应物理存储层。简单的说,分库表或者或多或少人 在数据库层面看一遍是一张表,但物理上是分成多个文件独立存储。

总体来看,切换分区表比较好的防止了或多或少人 当前对于数据量快速增长的数据库性能的担忧,离米 数据量再增长2、3倍应该是能扛住的。但它算是能如或多或少人 预期的在高并发下支持10倍数据量(即单表15亿记录)而性能表现依然稳定,仍有待实践证明。

于是,或多或少人 认为都能否通过将非分区表切换到分区表来降低该数据表地处的性能风险。

切换完成后,或多或少人 进行了一周的性能观察:CPU维持在10%,IOPS有8%左右的下降,存储空间有3%的上升。

单纯从整体的性能指标来看,切换前后变化并全是有点硬明显。但或者耗时没人 长的操作,耗时稳定了下来,锁表冲突事件也基本没人 再跳出 。通过PARTITIONS的分析,最大的一4个 分区也只有10000万行。即使数据量再扩大10倍,最大分区的数据量也才100000万,对于单个存储引擎文件来说,这完整版无压力,理论上性能表现或者会跳出 大幅下滑。但或者放入非分区表,估计业务高峰流量稍微一冲击,或者硬件性能跳出 波动(在资源共享的云计算环境中,较为常见),全是崩溃的风险。

针对大数据表,分区表的插入性能、存储空间算是分区表基本一致,查询性能在分区键上比非分区键好,DDL执行时间比非分区表短。

都能否看出:

下面或多或少人 深入分析MySql InnoDB表数据量大小对CRUD及DDL操作的性能影响:

具体步骤如下:

MySql在5.5及或者的版本,对Online DDL支持不太好,会由于锁表。或者,percona推出pt-online-schema-change,利用触发器实现在DDL过程中不要再造成读写阻塞。

目前,分区表或者稳定在生产环境运行了近一4个 月。

此方案最简单直接。但或多或少人 的业务中,只有此表数据量较大且都能否查询完整版单据。仅为了一张表就引入并算是存储机制,考虑到运维和经济成本,总人太好不划算。另外,此表还与或多或少表有一定的联合查询操作,分离出去总要增加应用层的简化度;

通过以上的分析,或多或少人 得到以下的结论:

人太好目前整体性能表现尚可,但偏离 操作耗时没人 长,锁表冲突事件也结束了了英语 跳出 。考虑到数据量的快速增长,以及数据库并算是的雪崩特点,或多或少人 认为这张表地处很大的性能风险,急需优化。

分库分表是防止大数据表的利器。但或多或少人 数据库系统的CPU和IO资源很富余(CPU仅10%,IOPS仅1000多),完整版没人 分库的必要。而分表会使得应用层的修改工作量巨大,代码的可读性也会变差。或者为了业务层的逻辑清晰再引入里边件进行代理访问,又有杀鸡用牛刀之感;

为了防止业务中断,或多或少人 参考pt-online-schema-change的模式进行切换。