商品库MySQL优化实践

淘宝商品库MySQL优化实践QCon2011Beijing核心系统数据库组余锋（褚霸）商品库项目背景介绍以及约束技术要求和方案性能保证安全性保证运维保证优化成果交流时间商品库(单机，测试)情况•无复杂查询，离散度高•记录数：1亿条键值对•记录大小：100字节•数据文件：170G•访问热点情况：20%的键占用55％的访问量•键读写比例：10：1硬件选择•主机：Dell;PowerEdgeC2100;•处理器：physical=2,cores=12,virtual=24•内存：96G•RAID卡：LSIMegaSAS9260/512MBMemory•PCI-EFlash卡:Fusion-ioioDrive320GB/MLC•硬盘:SEAGATEST3300657SS300Gx12软件选择•发行版：RedHatEnterpriseLinuxServerrelease5.4•内核：Kernel|2.6.18-164.el5•文件系统：Ext3•Flashcache:FB内部版本•MySQL版本：5.1.48-logSourceAgenda商品库项目背景介绍以及约束技术要求和方案性能保证安全性保证运维保证优化成果交流时间商品库技术要求•高可用，安全第一•高性能，性能平稳，性价比高•控制运维风险技术方案•MySQL数据库集群，数据水平切割，主从备份•采用高性价比PC服务器，大内存，强劲CPU•采用高性能PCI-EFlash卡作为cache,提高系统的IO性能•充分利用系统各部件的cache,大胆采用新技术•充分考虑容灾，在各个层面考虑数据的安全性系统资源规划内存分配：•MySQL•InnoDBbufferpool•OSpagecache•驱动程序IO能力分配：•读能力，零散读，提高IOPS•写能力，集中写，提高吞吐量Cache分配：•MySQL内部cache•匿名页面/文件页面•Flashcache混合存储•Raid卡内部cache调优指导思想•杜绝拍脑袋，理论（源码）指导+精确测量+效果验证•内存为王•数据访问规律导向，随机数据和顺序数据尽量分离•尽量提高IO的利用率，减少无谓的IO能力浪费•在安全性的前提下，尽可能的利用好系统各个层次cache调优工具•源码+emacs+大脑•必备工具osystemtapooprofileolatencytopoblktrace/btt/seekwatcheroaspersaotcprstatosarogdb•自制工具obash脚本ognuplot脚本Agenda商品库项目背景介绍以及约束技术要求和方案性能保证安全性保证运维保证优化成果交流时间MySQL数据库考虑因素：•主从备份带来的性能影响•复杂数据查询操作是否需要预留内存以及上限•数据备份dump对系统的影响，避免系统swap•开启binlog带来的性能开销•限制最大链接数#############################max_binlog_cache_size=2Gmax_binlog_size=500Mmax_connections=1020max_user_connections=1000query_cache_size=30MInnoDB引擎考虑因素：•尽可能大的BP(bufferpool)•日志和数据分设备存储•离散数据走direct-IO，顺序日志走buffered-IO•减少脏页的同步，提高命中率•减少锁对多核CPU性能的影响•提高底层存储默认的IO能力#############################innodb_buffer_pool_size=72Ginnodb_flush_method=O_DIRECTinnodb_sync_spin_loops=0innodb_log_group_home_dir=/u02/innodb_io_capacity=2000innodb_thread_concurrency=64高速页缓存考虑因素：•page资源倾斜给数据库,尽量不浪费，兼顾临时内存申请•避免NUMA架构带来的zone内存分配不均而导致的swap现象•cache大部分由InnoDB日志产生，适时清除，限制page数量##############################numactl--interleave=allmysqld#sysctlvm.drop_caches=1vm.swappiness=0vm.dirty_ratio=？vm.dirty_background_ratio=？vm.pagecache=?文件系统考虑因素(选择)：•Ext3/4•Xfs考虑因素(配置)：•减少元数据变化产生的IO•对混合存储系统友好•关闭barrier#############################/dev/mapper/cachedev(rw,noatime,nodiratime,barrier=0)/u01/dev/sda12(rw,barrier=0)/u02IO调度考虑因素：•调度算法对减少磁头移动的效果•关闭预读•设备队列长度#############################sda|[deadline]128sdb|[deadline]128混合存储（Flashcache）考虑因素•结合磁盘的大容量，PCI-EFlash卡的高随机读写性能优点•数据尽可能多停留在PCI-EFlash卡上,提高读写命中率•减少同步次数，保留磁盘的IO能力•适时同步数据，减少安全风险#############################dev.flashcache.dirty_thresh_pct=90dev.flashcache.cache_all=0dev.flashcache.fast_remove=1dev.flashcache.reclaim_policy=1Raid卡考虑因素：•逻辑分卷•Cache使用写优先，读少分配（数据无相关性效果不好）•数据安全和raidlevel•少预读#############################Controller|LSILogic/SymbiosLogicLSIMegaSAS9260(rev03)Model|LSIMegaRAIDSAS9260-8i,PCIEinterface,8portsCache|512MBMemory,BBUPresentBBU|95%Charged,Temperature28C,isSOHGood=VirtualDevSizeRAIDLevelDisksSpnDpthStripeStatusCache0(noname)278.875GB1(1-0-0)21-164OptimalWB,RA1(noname)1.361TB1(1-0-0)25-564OptimalWB,RA存储设备驱动考虑因素：•减少IO的抖动，提高IOPS•提高寿命•关闭或减少预读#############################PCI-EFlash卡驱动：$cat/etc/modprobe.d/iomemory-vsl.confoptionsiomemory-vsluse_workqueue=0optionsiomemory-vsldisable-msi=0optionsiomemory-vsluse_large_pcie_rx_buffer=1性能保证小结解决IO瓶颈:•高速PCI-EFlash卡做Cache，读写速度可达800/500M•10xSAS300G存放离散度高数据文件•2xSAS300G存放顺序binlog和trx日志•控制数据库脏页面的刷新频率和强度•优化操作系统的pagecache，资源倾斜,杜绝swap发生•优化文件系统减少meta数据的产生，以及写入延迟•优化IO调度器和预读•开启raid卡的读写cache•优化设备驱动，适应高强度的读写请求，减少jitter解决CPU瓶颈:•业务上优化掉复杂查询•优化自旋锁Agenda商品库项目背景介绍以及约束技术要求和方案性能保证安全性保证运维保证优化成果交流时间安全性保证概要•Raid卡带Flash，掉电保护，raidlevel10防止磁盘损害•PCI-E卡自身有日志系统，恢复时间最差10分钟•Ext3文件系统带日志保护•Flashcache上的cache数据最多24小时都会同步到SAS盘•数据库Innodb引擎本身有redo日志，数据安全校验，高级别日志同步•MySQL主从备份•商品库应用方有事务日志Agenda商品库项目背景介绍以及约束技术要求和方案性能保证安全性保证运维保证优化成果交流时间运维保证概要数据预热：•支持热点数据每秒150M从磁盘直接加载到混合存储•数据库重新启动，无需重新预热数据库DDL操作：•控制数据表的大小，让DDL时间可接受•减少DDL对性能的冲击混合存储cache：•通过设置白名单，减少诸如备份操作对cache的干扰•混合存储cache可管理Agenda商品库项目背景介绍以及约束技术要求和方案性能保证安全性保证运维保证优化成果交流时间优化成果•充足的容量规划，可对抗突增业务，满足未来几年业务增长•系统总体运行平稳，系统负载CPUutil50%，磁盘util10%，PCI-EFlash卡util20%•QPS/36000，其中读/32800，写/3200•请求平均延时时间：260微秒(包括网络时间)•掉电和操作系统失效的情况下，数据无丢失•第一次预热时间半个小时以内，之后只需几分钟Agenda商品库项目背景介绍以及约束技术要求和方案性能保证安全性保证运维保证优化成果交流时间谢谢!Talentswanted!联络：[e-mail]