返回 登录
0

大型数据集的MySQL优化

原文:MySQL Tuning for Large Datasets
作者: Rob Gravelle
译者:仲培艺,关注数据库领域,纠错、寻求报道或者投稿请致邮:zhongpy@csdn.net。

诸多知名大公司都在使用MySQL,其中包括Google、Yahoo、NASA和Walmart。此外,其中部分公司的表囊括数十亿行,却又性能极佳。虽然很难保持MySQL数据库高速运行,但面对数据堆积,可以通过一些性能调整,来使其继续工作。本文则将围绕这一问题展开讨论。

导论

设计数据库之前,有必要先了解一下表的使用方法。例如,对于需要频繁更新的数据,最好将其存入一个独立表中,而通过这样的分表,更新操作将更加快捷。同时,表的连接操作也会消耗时间,所以若要深入分析复杂数据,则最好选用大表。惯有认知下,归一化可通过清除冗余来减少数据。然而,归一化也有其负面作用:它会极大地增加索引查找量。考虑到其需要随机访问读取标准硬盘,故而有时也会选用反归一化。

虽然新加载的数据库能够很好地有序运行,但随着数据库进一步扩展,这种有序操作将难以保持,从而导致更多的随机I/O和性能问题。

尽管“反归一化”可能颠覆了一些传统认知,但随着“元数据”理念兴起,为求性能和扩展性的双重提升,包括Google、eBay和Amazon在内的众多主要参与者,都对其数据库进行了“反归一化”调整。更有甚者,传统思维上的这一转变,还在众多数据库设计人员中掀起了这样的言论:归一化是弱者的选择

用InnoDB取代MyISAM

InnoDB具有change buffering特性(5.5版本之前,又名insert buffer),它能减少磁盘I/O(要求保留二级索引),而名称变更则大大提升了其性能。它可通过多种途径提升插入性能,且受到默认支持。

在表尾插入方面,MyISAM速度更快,但在其和磁盘间的数据加载过程中,为了保护key buffer,MyISAM用到了表锁和一个single lock,从而导致争用。同时,它也不具备上面提到的change buffering特性。

相关阅读

压缩InnoDB表

InnoDB的另一大优势就是它支持表压缩(有助于提高其原始性能和扩展性),它还具有双重效用:减少磁盘和内存间的数据传送;增加磁盘和内存中的压缩存储。此外,因为索引数据本身就是压缩状态,所以对于包含二级索引的表来说,这些优势又将进一步扩大。不止如此,通过SSD(闪存盘)存储设备,压缩的益处会达到最大化,原因是其容量小于传统HDD(旋转硬盘驱动)设备。

按主键顺序批量导入数据

进行批量插入时,按照主键顺序插入行,速度会更快。但InnoDB按该顺序对行进行机械化排布时,如果需要在其他行中间再插入行,就会导致页面分割(极大损害内存外表的性能)。

硬件优化

很久之后才能开始变更MySQL的设置,但如果在次优硬件上操作,则不会造成什么影响。

内存

写入时采用16到32GB的RAM应当是效果最佳的。

处理能力

MySQL(5.5版本)全面采用多线程处理,因此在操作系统支持的情况下,可实现多处理器操作。尽管出于扩展性的需求,很多DBAs能支持更多处理器,但在这一点上,两个双核CPU已能满足需求。

操作系统

只要能支持64位进程,选用什么样的O/S并不重要。大多数DBAs都选择64位的LVM(逻辑卷管理器)来提高备份效率。

存储

存储的标准协议,是将其连接至数个spindle和RAID(独立磁盘冗余阵列)。新版2.5 SAS(串行连接SCSI接口)硬盘驱动器虽然很小,通常却比传统大型驱动器运行得更快。

如上所述,在某些情况下,可以使用SSD(特别当需要执行多项写入时)。假设驱动器转速为7200 RPM,则IPOS(每秒输入输出操作)不可能超过100,而SSD的速度则至少是它的五倍。若基于较为优质的SSD,则IOPS可达到20000甚至更高。由此看来,如果面对巨量内存,且只想清除其中20%的数据,可利用MySQL将其存入内存。

方法和技巧

为保持数据库高效运行,方法和技巧如下:

从大容量的insert/read表开始,分析所有表的索引:移除不必要的索引;特别关注唯一索引(禁用change buffering)。除非必要,否则不使用唯一索引,代之以普通索引。

每隔一至两周,查看一次慢查询日志,从中筛选出三项速度最慢的查询,并加以优化。

若加载一个备份从服务器,卸载大容量读取查询的备份,因为这也会导致信息冗余。

总结

论及数据库优化,所有方法归根结底都是泛型建议。因此,进一步评估之前,并不能保证这些方法就适用于某些特定的操作或模式。此外,还有许多本文未曾涉及的方法,可以用来优化MySQL服务器。例如,MySQL包含许多服务器变量,它们都可以进一步优化,且在不久的将来,这些发展就会实现。

更多Rob Gravelle的文章

2016年8月12日-13日,由CSDN重磅打造的互联网应用架构实战峰会、运维技术与实战峰会将在成都举行,目前18位讲师和议题已全部确认。两场峰会大牛讲师来自阿里、腾讯、百度、京东、小米、乐视、聚美优品、YY、华为、360等知名互联网公司,共同探讨高可用/高并发/高性能系统架构设计、电商架构、分布式架构、运维工具研发与实践、运维自动化系统的构建、大数据与运维、云上的运维案例分析、虚拟化技术、应用性能检测与管理、游戏行业的运维实践等,将和与会嘉宾共同探讨「构建更安全、更高性能、更稳定的架构和运维体系」等领域的话题与技术。【目前限时6折,点击这里抢票

7月15日24点前仍处于最低六折优惠票价阶段,单场峰会(含餐)门票只需499元,5人以上团购或者购买两场峰会通票更有特惠,限时折扣,预购从速。(票务详情链接)。

评论