商品庫(kù)MySQL優(yōu)化實(shí)踐

1、商品庫(kù)MySQL優(yōu)化實(shí)踐Agenda商品庫(kù)項(xiàng)目背景介紹以及約束技術(shù)要求和方案 性能保證安全性保證 運(yùn)維保證優(yōu)化成果 交流時(shí)間 商品庫(kù)(單機(jī)，測(cè)試)情況 無(wú)復(fù)雜查詢，離散度高 記錄數(shù)：1億條鍵值對(duì) 記錄大?。?00字節(jié) 數(shù)據(jù)文件：170G 訪問(wèn)熱點(diǎn)情況：20%的鍵占用55的訪問(wèn)量 鍵讀寫(xiě)比例： 10：1 硬件選擇 主機(jī)： Dell; PowerEdge C2100; 處理器： physical = 2, cores = 12, virtual = 24 內(nèi)存： 96 G RAID卡：LSI MegaSAS 9260/512MB Memory PCI-E Flash卡: Fusion-io ioD

2、rive 320GB/MLC 硬盤(pán): SEAGATE ST3300657SS 300G x 12 軟件選擇 發(fā)行版： Red Hat Enterprise Linux Server release 5.4 內(nèi)核： Kernel | 2.6.18-164.el5 文件系統(tǒng)：Ext3 Flashcache: FB內(nèi)部版本 MySQL 版本： 5.1.48-log Source Agenda商品庫(kù)項(xiàng)目背景介紹以及約束技術(shù)要求和方案 性能保證安全性保證 運(yùn)維保證優(yōu)化成果 交流時(shí)間商品庫(kù)技術(shù)要求 高可用，安全第一 高性能，性能平穩(wěn)，性價(jià)比高 控制運(yùn)維風(fēng)險(xiǎn) 技術(shù)方案 MySQL數(shù)據(jù)庫(kù)集群，數(shù)據(jù)水平切割，主

3、從備份 采用高性價(jià)比PC服務(wù)器，大內(nèi)存，強(qiáng)勁CPU 采用高性能PCI-E Flash卡作為cache, 提高系統(tǒng)的IO性能 充分利用系統(tǒng)各部件的cache, 大膽采用新技術(shù) 充分考慮容災(zāi)，在各個(gè)層面考慮數(shù)據(jù)的安全性系統(tǒng)資源規(guī)劃內(nèi)存分配： MySQL InnoDB buffer pool OS pagecache 驅(qū)動(dòng)程序 IO能力分配： 讀能力，零散讀，提高IOPS 寫(xiě)能力，集中寫(xiě)，提高吞吐量 Cache分配： MySQL內(nèi)部cache 匿名頁(yè)面/文件頁(yè)面 Flashcache 混合存儲(chǔ) Raid卡內(nèi)部cache 調(diào)優(yōu)指導(dǎo)思想 杜絕拍腦袋，理論（源碼）指導(dǎo)+精確測(cè)量+效果驗(yàn)證 內(nèi)存為王 數(shù)據(jù)訪

4、問(wèn)規(guī)律導(dǎo)向，隨機(jī)數(shù)據(jù)和順序數(shù)據(jù)盡量分離 盡量提高IO的利用率，減少無(wú)謂的IO能力浪費(fèi) 在安全性的前提下，盡可能的利用好系統(tǒng)各個(gè)層次cache 調(diào)優(yōu)工具 源碼+emacs+大腦 必備工具osystemtapooprofileolatencytop oblktrace/btt/seekwatcheroaspersaotcprstatosarogdb 自制工具obash腳本ognuplot腳本 Agenda商品庫(kù)項(xiàng)目背景介紹以及約束技術(shù)要求和方案 性能保證安全性保證 運(yùn)維保證優(yōu)化成果 交流時(shí)間MySQL數(shù)據(jù)庫(kù) 考慮因素： 主從備份帶來(lái)的性能影響 復(fù)雜數(shù)據(jù)查詢操作是否需要預(yù)留內(nèi)存以及上限 數(shù)據(jù)備份du

5、mp對(duì)系統(tǒng)的影響，避免系統(tǒng)swap 開(kāi)啟binlog帶來(lái)的性能開(kāi)銷 限制最大鏈接數(shù) # max_binlog_cache_size=2Gmax_binlog_size = 500Mmax_connections = 1020max_user_connections=1000query_cache_size = 30MInnoDB引擎考慮因素： 盡可能大的BP(buffer pool) 日志和數(shù)據(jù)分設(shè)備存儲(chǔ) 離散數(shù)據(jù)走direct-IO，順序日志走buffered-IO 減少臟頁(yè)的同步，提高命中率 減少鎖對(duì)多核CPU性能的影響 提高底層存儲(chǔ)默認(rèn)的IO能力 # innodb_buffer_pool

6、_size = 72Ginnodb_flush_method = O_DIRECTinnodb_sync_spin_loops=0innodb_log_group_home_dir = /u02/innodb_io_capacity=2000innodb_thread_concurrency = 64高速頁(yè)緩存考慮因素： page資源傾斜給數(shù)據(jù)庫(kù), 盡量不浪費(fèi)，兼顧臨時(shí)內(nèi)存申請(qǐng) 避免NUMA架構(gòu)帶來(lái)的zone內(nèi)存分配不均而導(dǎo)致的swap現(xiàn)象 cache大部分由InnoDB日志產(chǎn)生，適時(shí)清除，限制page數(shù)量 # # numactl -interleave=all mysqld# sysctl

7、vm.drop_caches = 1 vm.swappiness = 0vm.dirty_ratio = ？vm.dirty_background_ratio =？vm.pagecache = ?文件系統(tǒng)考慮因素(選擇)： Ext3/4 Xfs 考慮因素(配置)： 減少元數(shù)據(jù)變化產(chǎn)生的IO 對(duì)混合存儲(chǔ)系統(tǒng)友好 關(guān)閉barrier # /dev/mapper/cachedev (rw,noatime,nodiratime,barrier=0) /u01 /dev/sda12 (rw,barrier=0) /u02IO調(diào)度考慮因素： 調(diào)度算法對(duì)減少磁頭移動(dòng)的效果 關(guān)閉預(yù)讀 設(shè)備隊(duì)列長(zhǎng)度 #sda

8、| deadline 128sdb | deadline 128混合存儲(chǔ)（Flashcache）考慮因素 結(jié)合磁盤(pán)的大容量，PCI-E Flash卡的高隨機(jī)讀寫(xiě)性能優(yōu)點(diǎn) 數(shù)據(jù)盡可能多停留在PCI-E Flash卡上,提高讀寫(xiě)命中率 減少同步次數(shù)，保留磁盤(pán)的IO能力 適時(shí)同步數(shù)據(jù)，減少安全風(fēng)險(xiǎn) #dev.flashcache.dirty_thresh_pct = 90dev.flashcache.cache_all = 0dev.flashcache.fast_remove = 1dev.flashcache.reclaim_policy = 1Raid卡考慮因素： 邏輯分卷 Cache使用寫(xiě)優(yōu)

9、先，讀少分配（數(shù)據(jù)無(wú)相關(guān)性效果不好） 數(shù)據(jù)安全和raid level 少預(yù)讀 # Controller | LSI Logic / Symbios Logic LSI MegaSAS 9260 (rev 03) Model | LSI MegaRAID SAS 9260-8i, PCIE interface, 8 ports Cache | 512MB Memory, BBU Present BBU | 95% Charged, Temperature 28C, isSOHGood= VirtualDev Size RAID Level Disks SpnDpth Stripe Status

10、Cache 0(no name) 278.875 GB 1 (1-0-0) 2 1-1 64 Optimal WB, RA 1(no name) 1.361 TB 1 (1-0-0) 2 5-5 64 Optimal WB, RA存儲(chǔ)設(shè)備驅(qū)動(dòng)考慮因素： 減少I(mǎi)O的抖動(dòng)，提高IOPS 提高壽命 關(guān)閉或減少預(yù)讀 # PCI-E Flash卡驅(qū)動(dòng)： $cat /etc/modprobe.d/iomemory-vsl.conf options iomemory-vsl use_workqueue=0options iomemory-vsl disable-msi=0options iomemory-v

11、sl use_large_pcie_rx_buffer=1性能保證小結(jié)解決IO瓶頸: 高速PCI-E Flash卡做Cache，讀寫(xiě)速度可達(dá)800/500M 10 x SAS 300G 存放離散度高數(shù)據(jù)文件 2 x SAS 300G 存放順序binlog和trx日志 控制數(shù)據(jù)庫(kù)臟頁(yè)面的刷新頻率和強(qiáng)度 優(yōu)化操作系統(tǒng)的pagecache，資源傾斜, 杜絕swap發(fā)生 優(yōu)化文件系統(tǒng)減少meta數(shù)據(jù)的產(chǎn)生，以及寫(xiě)入延遲 優(yōu)化IO調(diào)度器和預(yù)讀 開(kāi)啟raid卡的讀寫(xiě)cache 優(yōu)化設(shè)備驅(qū)動(dòng)，適應(yīng)高強(qiáng)度的讀寫(xiě)請(qǐng)求，減少jitter 解決CPU瓶頸: 業(yè)務(wù)上優(yōu)化掉復(fù)雜查詢 優(yōu)化自旋鎖 Agenda商品庫(kù)項(xiàng)目背

12、景介紹以及約束技術(shù)要求和方案 性能保證安全性保證 運(yùn)維保證優(yōu)化成果 交流時(shí)間安全性保證概要 Raid卡帶Flash，掉電保護(hù)，raid level10防止磁盤(pán)損害 PCI-E卡自身有日志系統(tǒng)，恢復(fù)時(shí)間最差10分鐘 Ext3文件系統(tǒng)帶日志保護(hù) Flashcache上的cache數(shù)據(jù)最多24小時(shí)都會(huì)同步到SAS盤(pán) 數(shù)據(jù)庫(kù)Innodb引擎本身有redo日志，數(shù)據(jù)安全校驗(yàn)，高級(jí)別日志同步 MySQL主從備份 商品庫(kù)應(yīng)用方有事務(wù)日志Agenda商品庫(kù)項(xiàng)目背景介紹以及約束技術(shù)要求和方案 性能保證安全性保證 運(yùn)維保證優(yōu)化成果 交流時(shí)間運(yùn)維保證概要數(shù)據(jù)預(yù)熱： 支持熱點(diǎn)數(shù)據(jù)每秒150M從磁盤(pán)直接加載到混合存儲(chǔ) 數(shù)據(jù)庫(kù)重新啟動(dòng)，無(wú)需重新預(yù)熱 數(shù)據(jù)庫(kù)DDL 操作： 控制數(shù)據(jù)表的大小，讓DDL時(shí)間可接受 減少DDL對(duì)性能的沖擊 混合存儲(chǔ)cache： 通過(guò)設(shè)置白名單，減少諸如備份操作對(duì)cache的干擾 混合存儲(chǔ)cache可管理 Agenda商品庫(kù)項(xiàng)目背景介紹以及約束技術(shù)要求和方案 性能保證安全性保證 運(yùn)維保證優(yōu)化成果 交流時(shí)間優(yōu)化成果 充足的容量規(guī)劃，可對(duì)抗突增業(yè)務(wù)，滿足未來(lái)幾年業(yè)務(wù)增長(zhǎng) 系統(tǒng)總體運(yùn)行平穩(wěn)，系統(tǒng)負(fù)載CPU util 50