51CTO下载-MySQL性能调优最佳实践

2012/4/61MySQL性能优化最佳实践简朝阳(sky000)OracleACE(Expertise:MySQL)技术保障部@麦包包Blog：：@sky000Weibo：@简朝阳AboutmeDTCC2012DTCC20122012/4/62找出瓶颈设定目标实施优化确认结果优化MySQL性能优化最佳实践优化过程DTCC2012DTCC20122012/4/63瓶颈存储容量容量白菜价2TB很普及了MySQL性能优化最佳实践找出瓶颈DTCC2012DTCC20122012/4/64瓶颈Network(IOPS/吞吐量)存储容量一般很难跑满万兆已经很多MySQL性能优化最佳实践找出瓶颈DTCC2012DTCC20122012/4/65瓶颈DRAMNetwork(IOPS/吞吐量)存储容量Linux单机支持过百G价格较之过去已大降MySQL性能优化最佳实践找出瓶颈DTCC2012DTCC20122012/4/66瓶颈CPUDRAMNetwork(IOPS/吞吐量)存储容量X86Nehalem,SMP,NUMA4路PCServer32核30%MySQL性能优化最佳实践找出瓶颈DTCC2012DTCC20122012/4/67瓶颈IO(IOPS/吞吐量)CPUDRAMNetwork(IOPS/吞吐量)存储容量OLTP：iopsOLAP：吞吐量60%瓶颈在IOSSD?MySQL性能优化最佳实践找出瓶颈DTCC2012DTCC20122012/4/68目标设备能力极限不可能突破MySQL性能优化最佳实践设定目标DTCC2012DTCC20122012/4/69目标设备能力业务需求极限不可能突破一切以需求为导向MySQL性能优化最佳实践设定目标DTCC2012DTCC20122012/4/610目标设备能力业务需求应用环境极限不可能突破一切以需求为导向环境影响可行性MySQL性能优化最佳实践设定目标DTCC2012DTCC20122012/4/611对象ParamsEngineSchemaIndexSQL实施MySQL性能优化最佳实践实施优化OSHardwareMySQLDTCC2012DTCC20122012/4/612对象实施MySQL性能优化最佳实践实施优化方法„方法ParamsEngineSchemaIndexSQLOSHardwareMySQLDTCC2012DTCC20122012/4/613对象实施MySQL性能优化最佳实践实施优化方法„方法误区„误区ParamsEngineSchemaIndexSQLOSHardwareMySQLDTCC2012DTCC20122012/4/614对象实施MySQL性能优化最佳实践实施优化方法„方法误区„误区经验„最佳实践ParamsEngineSchemaIndexSQLOSHardwareMySQLDTCC2012DTCC20122012/4/615背景磁盘转速，容量，接口HDD:~150iops,200MBSSD:10x~1000x,400MBMySQL性能优化最佳实践实施优化DTCC2012DTCC20122012/4/616磁盘MySQL性能优化最佳实践实施优化CPU主频，多核，超线程SMP,NUMA,MPP背景DTCC2012DTCC20122012/4/617磁盘索引~BalanceTree缩短检索路径有序MySQL性能优化最佳实践实施优化CPU背景DTCC2012DTCC20122012/4/618磁盘索引MySQL性能优化最佳实践实施优化CPUSQL执行计划如何获得：explain如何分析：Docs背景DTCC2012DTCC20122012/4/619磁盘索引MySQL性能优化最佳实践实施优化CPUSQL背景MySQL简单，轻型，开放多线程，插件式SQL+StorageEngine…DTCC2012DTCC20122012/4/620磁盘索引MySQL性能优化最佳实践实施优化CPUSQL背景MySQL存储引擎插件式，可自由更换开放型，可自行开发多样性，特性不一并存性，可并存使用DTCC2012DTCC20122012/4/621Hardware方法DiskRaidCardCPU…MySQL性能优化最佳实践•提高磁盘转速•增加磁盘数量•选好磁盘接口DTCC2012DTCC20122012/4/622HardwareDiskRaidCardCPU…MySQL性能优化最佳实践•容量越大越好？•FC磁盘一定比SAS盘快？•磁盘Cache越大越好？误区DTCC2012DTCC20122012/4/623HardwareDiskRaidCardCPU…MySQL性能优化最佳实践•OLTP:小容量”高”转速•OLAP:大容量”低”转速(钱多可以高转速)•磁盘数量尽可能多•有钱可以上SSD(IO瓶颈场景下)最佳实践DTCC2012DTCC20122012/4/624HardwareDiskRaidCardCPU…MySQL性能优化最佳实践•OLTP:小容量”高”转速•OLAP:大容量”低”转速(钱多可以高转速)•磁盘数量尽可能多•有钱可以上SSD(IO瓶颈场景下)•增加Raid卡Cache容量•提升Cache利用率•确保数据安全方法DTCC2012DTCC20122012/4/625HardwareDiskRaidCardCPU…MySQL性能优化最佳实践•OLTP:小容量”高”转速•OLAP:大容量”低”转速(钱多可以高转速)•磁盘数量尽可能多•有钱可以上SSD(IO瓶颈场景下)•读写都是用Cache提升效率？•Raid10一定比Raid5快？•带电池的Raid卡数据一定安全？误区DTCC2012DTCC20122012/4/626HardwareDiskRaidCardCPU…MySQL性能优化最佳实践•OLTP:小容量”高”转速•OLAP:大容量”低”转速(钱多可以高转速)•磁盘数量尽可能多•有钱可以上SSD(IO瓶颈场景下)•Cache只供写使用，Direct读取•OLTPRaid10,StripSize参考DB•OLAPRaid5•关注Raid卡充放电带来的Cache失效•预读只对连续读有效，OLTP关闭预读最佳实践DTCC2012DTCC20122012/4/627HardwareDiskRaidCardCPU…MySQL性能优化最佳实践•OLTP:小容量”高”转速•OLAP:大容量”低”转速(钱多可以高转速)•磁盘数量尽可能多•有钱可以上SSD(IO瓶颈场景下)•Cache只供写使用，Direct读取•OLTPRaid10,StripSize参考DB•OLAPRaid5•关注Raid卡充放电带来的Cache失效•预读只对连续读有效，OLTP关闭预读方法•提高CPU运算能力（频率？）•缩短CPU访问数据的路径（缓存？）DTCC2012DTCC20122012/4/628HardwareDiskRaidCardCPU…MySQL性能优化最佳实践•OLTP:小容量”高”转速•OLAP:大容量”低”转速(钱多可以高转速)•磁盘数量尽可能多•有钱可以上SSD(IO瓶颈场景下)•Cache只供写使用，Direct读取•OLTPRaid10,StripSize参考DB•OLAPRaid5•关注Raid卡充放电带来的Cache失效•预读只对连续读有效，OLTP关闭预读•CPU越多越好？•Core越多越好？误区DTCC2012DTCC20122012/4/629HardwareDiskRaidCardCPU…MySQL性能优化最佳实践•OLTP:小容量”高”转速•OLAP:大容量”低”转速(钱多可以高转速)•磁盘数量尽可能多•有钱可以上SSD(IO瓶颈场景下)•Cache只供写使用，Direct读取•OLTPRaid10,StripSize参考DB•OLAPRaid5•关注Raid卡充放电带来的Cache失效•预读只对连续读有效，OLTP关闭预读•使用主频更高的CPU•使用缓存更大的CPU•8个Core比较合适，不超过16Core•Core比较多可以单机多实例最佳实践DTCC2012DTCC20122012/4/630OS方法FileSystemI/OScheduler…MySQL性能优化最佳实践•确保安全：有日志，能恢复•OLTP:提高大文件下随机I/O性能•OLAP:提高大文件下连续I/O性能•降低管理成本CPU/DRAMDTCC2012DTCC20122012/4/631OSFileSystemI/OScheduler…MySQL性能优化最佳实践•OS默认自带的就是最好的？•功能最强的才是最好的？•性能最高的才是最好的？误区CPU/DRAMDTCC2012DTCC20122012/4/632OSFileSystemI/OScheduler…MySQL性能优化最佳实践•XFS非常适合MySQL•XFS要注意su(stripesize)和sw(stripewidth)•ZFS非常适合备份管理最佳实践CPU/DRAMDTCC2012DTCC20122012/4/633OSFileSystemI/OScheduler…MySQL性能优化最佳实践•XFS非常适合MySQL•XFS要注意su(stripesize)和sw(stripewidth)•ZFS非常适合备份管理方法•尽量减少不必要阻塞•尽量降低随机I/O访问的延时•CFQ,Deadline,NOOP和Anticipatory差异CPU/DRAMDTCC2012DTCC20122012/4/634OSFileSystemI/OScheduler…MySQL性能优化最佳实践•XFS非常适合MySQL•XFS要注意su(stripesize)和sw(stripewidth)•ZFS非常适合备份管理•公平的才是最合适的？•智能的就是合适的？误区CPU/DRAMDTCC2012DTCC20122012/4/635OSFileSystemI/OScheduler…MySQL性能优化最佳实践•XFS非常适合MySQL•XFS要注意su(stripesize)和sw(stripewidth)•ZFS非常适合备份管理•CFQ适用于io大小非常均匀的场景•稍微复杂点的OLTP最好更换为Deadline•I/O性能不是瓶颈的时候使用NOOP•Anticipatory不适用数据库场景最佳实践CPU/DRAMDTCC2012DTCC20122012/4/636OSFileSystemI/OScheduler…MySQL性能优化最佳实践•XFS非常适合MySQL•XFS要注意su(stripesize)和sw(stripewidth)•ZFS非常适合备份管理•CFQ适用于io大小非常均匀的场景•稍微复杂点的OLTP最好更换为Deadline•I/O性能不是瓶颈的时候使用NOOP•Anticipatory不适用数据库场景方法•提升CPU利用率•均衡CPU资源•提高内存利用率CPU/DRAMDTCC2012DTCC20122012/4/637OSFileSystemI/OScheduler…MySQL性能优化最佳实践•XFS非常适合MySQL•XFS要注意su(stripesize)和sw(stripewidth)•ZFS非常适合备份管理•CFQ适用于io大小非常均匀的场景•稍微复杂点的OLTP最好更换为Deadline•I/O性能不是瓶颈的时候使用NOOP•Anticipatory不适用数据库场景•CPU越多越好？•NUMA一定能提高性能？•内存越大越好？误区CPU/DRAMDTCC2012DTCC20122012/4/638OSFileSystemI/OSchedulerCPU/DRAM…MySQL性能优化最佳实践•XFS非常适合MySQL•XFS要注意su(stripesize)和sw(stripewidth)•ZFS非常适合备份管理•CFQ适用于io大小非常均匀的场景•稍微复杂点的OLT