商品庫MySQL優(yōu)化實踐

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

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

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

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

5、mp對系統(tǒng)的影響，避免系統(tǒng)swap 開啟binlog帶來的性能開銷 限制最大鏈接數(shù) # max_binlog_cache_size=2Gmax_binlog_size = 500Mmax_connections = 1020max_user_connections=1000query_cache_size = 30MInnoDB引擎考慮因素： 盡可能大的BP(buffer pool) 日志和數(shù)據(jù)分設備存儲 離散數(shù)據(jù)走direct-IO，順序日志走buffered-IO 減少臟頁的同步，提高命中率 減少鎖對多核CPU性能的影響 提高底層存儲默認的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高速頁緩存考慮因素： page資源傾斜給數(shù)據(jù)庫, 盡量不浪費，兼顧臨時內存申請 避免NUMA架構帶來的zone內存分配不均而導致的swap現(xiàn)象 cache大部分由InnoDB日志產生，適時清除，限制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ù)變化產生的IO 對混合存儲系統(tǒng)友好 關閉barrier # /dev/mapper/cachedev (rw,noatime,nodiratime,barrier=0) /u01 /dev/sda12 (rw,barrier=0) /u02IO調度考慮因素： 調度算法對減少磁頭移動的效果 關閉預讀 設備隊列長度 #sda

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

9、先，讀少分配（數(shù)據(jù)無相關性效果不好） 數(shù)據(jù)安全和raid level 少預讀 # 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存儲設備驅動考慮因素： 減少IO的抖動，提高IOPS 提高壽命 關閉或減少預讀 # PCI-E Flash卡驅動： $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性能保證小結解決IO瓶頸: 高速PCI-E Flash卡做Cache，讀寫速度可達800/500M 10 x SAS 300G 存放離散度高數(shù)據(jù)文件 2 x SAS 300G 存放順序binlog和trx日志 控制數(shù)據(jù)庫臟頁面的刷新頻率和強度 優(yōu)化操作系統(tǒng)的pagecache，資源傾斜, 杜絕swap發(fā)生 優(yōu)化文件系統(tǒng)減少meta數(shù)據(jù)的產生，以及寫入延遲 優(yōu)化IO調度器和預讀 開啟raid卡的讀寫cache 優(yōu)化設備驅動，適應高強度的讀寫請求，減少jitter 解決CPU瓶頸: 業(yè)務上優(yōu)化掉復雜查詢 優(yōu)化自旋鎖 Agenda商品庫項目背

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