MySQL 新技术探索与实践

彭立勋WWW.PengLiXun.COM

Alibaba DBA Team

新技术风起云涌• 以 Percona/XtraDB 为代表的 MySQL 分支

（ MariaDB 、 Drizzle…… ）• 以 HandlerSocket 为代表的新接口（ Memca

ched Plugin…… ）• 以 XFS/EXT4 为代表的高性能文件系统（ Btrfs 、

ZFS…… ）• 以 Flashcache 为代表的二级缓存架构（ Inno

DB Secondary Buffer Pool…… ）• 以 Fusion-IO 为代表的 PCI-E SSD• 以 Intel C Compiler 为代表的高性能编译器• ……

Topics

• ICC• XFS• Percona• HandlerSocket

Why ICC

• 为何自己编译 MySQL ？ 官方无静态编译的 Innodb Plugin 版本 可以加入第三方 Patch 或修改源码 可以将第三方库静态编译到可执行文件（ TCMalloc ）

• 为何使用 ICC 编译？ 原生 Intel SSE2 指令集，浮点运算效率高 内置 Intel Math Lib ，提升数学函数效率 内置 Intel Thread Lib ，提升多线程稳定性和效率

ICC vs GCC(1)

硬件环境CPU ： Intel Xoen 5410内存： 24G硬盘： 10*15k SAS RAID10

ICC vs GCC(2)

硬件环境CPU ： Intel Xoen 5520内存： 24G硬盘： 10*15k SAS RAID10MySQL ： 5.1.40 Enterprise

Why XFS

• 为何不使用 EXT3 ？ 对 SSD 设备不友好， SSD 是未来数据存储设备的趋势 删除文件速度慢，导致数据库 Hang 对大文件读写性能不佳

• 为何选择 XFS ？ SGI 已经在其大型机上应用多年 (From 1994) ，稳定可靠 对 SSD 设备友好（延迟分配） 高并发下竞争少，性能好（分配组特性） 支持条带化分配，使得文件系统分配与 RAID 条带完全对

齐，最大化吞吐量 对大文件操作友好（基于 Extent 的分配方式）

Why NOT EXT4?

• EXT4 也是一款非常好的文件系统• 性能与 XFS 接近，甚至好一些• 并且可以从 EXT3 无缝升级• But ......• 我们没有运维 EXT4 的经验

XFS Tips

• 分配组（ Allocation Groups ）• 延时分配（ Delay Allocation ）• 多线程 DirectIO• 全 B+ Tree 管理空间

EXT3 vs XFS(1)

硬件环境CPU ： Intel Xoen 5520内存： 24G硬盘： 10*15k SAS RAID10MySQL ： 5.1.40 Enterprise

EXT3 vs XFS(2)

硬件环境CPU ： Intel Xoen 5520内存： 24G硬盘： 10*15k SAS RAID10MySQL ： 5.1.40 Enterprise

Why Percona

• Percona 的优势 对 SSD 设备有专门的优化 对 Flashcache 有 SQL 层接口 允许 XtraDB 静态编译 支持多种页大小 提供额外的监控参数 有被生产环境考验过（ SOHU ）

• Percona 存在的问题 引入第三方补丁较多，可能存在 Bug （可以接受）

New Future(1)

• 文件格式 Compressed 结构： CPU换 IO Dynamic 结构： ROW 中不存大字段前缀

• IO 参数 IO 容量： innodb_io_capacity IO 线程数： innodb_read_io_threads( 预

读 ) 、 innodb_write_io_threads( 赃页回写 ) 、 innodb_use_purge_thread( 清理 UNDO)

• 赃页刷新方式 innodb_adaptive_checkpoint (XtraDB) innodb_adaptive_flushing (InnoDB Plugin)

New Future(2)

• 扩展性 增强多处理机性能（ About 24 Cores ） 拆分 Buffer Pool Mutex （ buf_pool_mutex 、 LRU_lis

t_mutex 、 flush_list_mutex 、 page_hash_latch 、 free_list_mutex 、 zip_free_mutex 、 zip_hash_mutex ）

• 功能 可变页大小（ innodb_page_size ） 可控的 Insert Buffering 和 Adaptive Hash Index 可配置多回滚段（ innodb_extra_rsegments ） 快速 Warn Up （ innodb_buffer_pool_shm_key 、 XT

RA_LRU_DUMP/XTRA_LRU_RESTORE ） 快速创建索引和索引快速重命名

New Future(3)

• 监控 扩展 information_schema

– INDEX_STATISTICS– TABLE_STATISTICS– USER_STATISTICS

扩展 InnoDB 统计– INNODB_TABLE_STATS– INNODB_INDEX_STATS

For Example– 可以获取未使用过的索引– 可以获取索引被用于访问的行数– 可以获取当前锁定信息– 可以获取用户连接统计信息– ……

Percona Performance

每秒处理 50~75 万行读取

每秒处理 2.5K~5K Query

每秒网卡吞吐 400~750Mbps

Why Handler Socket• SQL 执行的 Oprofile

samples % app name symbol name259130 4.5199 mysqld MYSQLparse(void*)196841 3.4334 mysqld my_pthread_fastmutex_lock106439 1.8566 libc-2.5.so _int_malloc……63435 1.1065 mysqld JOIN::optimize()55825 0.9737 vmlinux wakeup_stack_begin55054 0.9603 mysqld MYSQLlex(void*, void*)50833 0.8867 libpthread-2.5.so pthread_mutex_trylock49602 0.8652 ha_innodb_plugin.so.0.0.0

row_search_for_mysql……46499 0.8111 libc-2.5.so malloc

Why Handler Socket• HandlerSocket 执行的 Oprofile

samples % app name symbol name984785 5.9118 bnx2 /bnx2847486 5.0876 ha_innodb_plugin.so.0.0.0 ut_delay545303 3.2735 ha_innodb_plugin.so.0.0.0

btr_search_guess_on_hash317570 1.9064 ha_innodb_plugin.so.0.0.0

row_search_for_mysql……206057 1.2370 HandlerSocket.so

dena::hstcpsvr_worker::run_one_ep()183330 1.1006 ha_innodb_plugin.so.0.0.0 mutex_spin_wait175738 1.0550 HandlerSocket.so dena::dbcontext::

cmd_find_internal(dena::dbcallback_i&, dena::prep_stmt const&, ha_rkey_function, dena::cmd_exec_args const&)

……149611 0.8981 ha_innodb_plugin.so.0.0.0

row_sel_store_mysql_rec

HS vs MC vs SQL

硬件环境CPU ： Intel Xoen 5520内存： 24G硬盘： 10*15k SAS RAID10MySQL ： 5.1.48

Our Solution(1)

Our Solution(2)

• RAID 卡 关闭预读：预读效果不佳，直接读取磁盘 关闭磁盘 Cache ： RAID 卡缓存已经缓冲了写操作，磁

盘 Cache 无电池 条带：默认 64K ，调整为 1M

• Linux IO 调度： /sys/block/sdb/queue/scheduler ，默认 cfq ，

调整为 deadline 减少预读： /sys/block/sdb/queue/read_ahead_kb ，

默认 128 ，调整为 16 增大队列： /sys/block/sdb/queue/nr_requests ，默认

128 ，调整为 512 NUMA 策略： numactl --interleave=all 或 --cpunodeb

ind=0 --localalloc

Our Solution(3)

• Flashcache Block Size=4K ：与 SSD 设备页对齐 dirty_thresh_pct = 90 ：一个 SET 内 90% 都是脏块则

刷新 write_merge = 1 ：写入合并，提升写磁盘的性能 fast_remove = 1 ：解除绑定时无需将脏块写入磁盘

• Percona 页设置： innodb_page_size=4096 、 innodb_fast_chec

ksum=1 刷新策略： innodb_adaptive_checkpoint=3 、 innodb

_flush_neighbor_pages=0 IO 容量： innodb_io_capacity>10000 IO 线程： innodb_read_io_threads = 1 、 innodb_write

_io_threads = 16

Our Solution(4)

• 监控 Fusion-IO （ fio-status ）：

– Logical bytes written ：逻辑写– Logical bytes read ：逻辑读– Physical bytes written ：物理写– Physical bytes read ：物理读

Flashcache （ dmsetup status ）：– read hit percent ：读命中– write hit percent ：写命中

Q&AE-Mail: PengLiXun@gmail.com