My SQL QCon 2011 Beijing http blog yufeng
淘宝 商品�My. SQL�化�践 QCon 2011 Beijing 核心系�数据�� 余�(褚霸) http: //blog. yufeng. info/ 2011/04/08
硬件�� • • • 主机: Dell; Power. Edge C 2100; �理器: physical = 2, cores = 12, virtual = 24 内存: 96 G RAID卡:LSI Mega. SAS 9260/512 MB Memory PCI-E Flash卡: Fusion-io io. Drive 320 GB/MLC 硬�: SEAGATE ST 3300657 SS 300 G x 12
�件�� • • • �行版: Red Hat Enterprise Linux Server release 5. 4 内核: Kernel | 2. 6. 18 -164. el 5 文件系�:Ext 3 Flashcache: FB内部版本 My. SQL 版本: 5. 1. 48 -log Source
�� 具 • 源�+emacs+大� • 必� 具 o systemtap o oprofile o latencytop o blktrace/btt/seekwatcher o aspersa o tcprstat o sar o gdb • 自制 具 o bash脚本 o gnuplot脚本
Inno. DB引擎 考�因素: • 尽可能大的BP(buffer pool) • 日志和数据分��存� • 离散数据走direct-IO,�序日志走buffered-IO • 减少��的同步,提高命中率 • 减少��多核CPU性能的影响 • 提高底�存�默�的IO能力 ############### innodb_buffer_pool_size = 72 G innodb_flush_method = O_DIRECT innodb_sync_spin_loops=0 innodb_log_group_home_dir = /u 02/ innodb_io_capacity=2000 innodb_thread_concurrency = 64
Raid卡 考�因素: • ��分卷 • Cache使用写�先,�少分配(数据无相关性效果不好) • 数据安全和raid level • 少�� ############### Controller | LSI Logic / Symbios Logic LSI Mega. SAS 9260 (rev 03) Model | LSI Mega. RAID SAS 9260 -8 i, PCIE interface, 8 ports Cache | 512 MB Memory, BBU Present BBU | 95% Charged, Temperature 28 C, is. SOHGood= Virtual. Dev Size RAID Level Disks Spn. Dpth Stripe Status 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=0 options iomemory-vsl disable-msi=0 options iomemory-vsl use_large_pcie_rx_buffer=1
��! Talents wanted! ��:chuba@taobao. com
- Slides: 29