基于BoostKit的MySQL性能优化

1. 数据库发展趋势

1.1 数据发展趋势

• 传统数据库经历从单机到主备库，到RAC集群的演化，而RAC集中式架构性能扩展有限，以互联网流派为主分布式数据库应对大并发量已成为主流

  数据库场景每个线程同时处理10个并发，单RAC节点处理在1000并发内，RAC架构3个节点以上线性度以不能扩展

• 互联网阵营的阿里Oceanbase，腾讯TD-SQL基于自身业务支撑孕育出有生命力的分布式数据库，Ping CAP的分布式TiDB深耕企业市场
• 云化数据库存在物理机多实例，Docker，虚拟机多种部署方式，IO延时挑战更大，需要更低延时的网络和存储

1.2 数据库领域架构及技术趋势

1.3 TaiShan 数据库解决方案生态规划

1.4 TaiShan + 开源MySQL主流解决方案架构

1.5 鲲鹏数据库解决方案优势

2. MySQL数据架构介绍

2.1 MySQL介绍

• MySQL是最流行的关系型数据库管理系统之一，尤其在web应用，MySQL是最好的关系型数据库软件之一
• MySQL数据库采用双授权政策，分为社区版和商业版
• MySQL体积小、速度快、成本低，中小型网站通常选择MySQL作为网站数据库

2.2 MySQL数据库架构

2.3 MySQL执行流程

2.4 InnoDB引擎架构

2.5 InnoDB表结构

2.6 InnoDB - 索引组织表

• 在InnoDB存储引擎中，表都是根据主键顺序组织存放的，这种存储方式的表组成索引组织表，叶子节点存储的是索引值和数据本身
  

2.7 InnoDB - 二级索引

• 二级索引的叶子节点存储的是索引值以对应行数据的主键，通过主键索引获得最终的数据
  

2.8 堆表

• 堆表就是一般的表，数据随机存放，由空闲空间决定，无先后之分
• 数据和索引分开存放，索引保存记录所在位置的rowid
  

2.9 索引组织表 vs 堆表

• 索引组织表
  • 数据有序存放的，数据的位置是预先预定好的，与插入顺序没有关系
  • 查询效率高，但插入数据慢
• 堆表
  • 写入速度快
  • 数据和索引是分开存放的
  • 数据更新可能出现行迁移

2.10 InnoDB特性

• Change buffer
  • 用户缓存非唯一性二级索引页变化的数据结构，当需要修改的非唯一性二级索引页不在缓冲池中，将变化的索引页缓存在change buffer中
• 两次写（double write）
  • Double write 保证数据页完整的被写入到数据文件中，避免出现部分写
• 自适应哈希索引（adaptive hash index）
  • 对频繁访问的二级索引建立哈希索引，提升性能
• 预读机制（read-ahead）
  • 预读操作是一种IO优化操作，异步将磁盘中数据页读到buffer pool中，预计这些页会马上被用到

3. MySQL性能影响因素及定位工具

3.1 影响数据库性能因素

• 硬件及操作系统
  • CPU、IO、网络、内存
  • 操作系统参数
• 数据库层
  • 数据库参数
  • 统计信息
• 业务层
  • 并发数及数据量
  • 慢SQL

3.2 硬件及操作系统层性能监控

• 操作系统层性能监控工具
  • CPU
    • top、vmstat
  • 内存
    • top、free
  • IO
    • iostat、vmstat
  • 网络
    • Sar -n DEV
  • SQL
    • Top SQL
  • 热点函数
    • perf

3.3 MySQL数据库性能监控工具

• MySQL数据库层定位工具
  • 状态变量
  • 慢日志
  • Show full processlist 查看 session 执行状态
  • Show engine innodb status 查看引擎状态
  • Performance_schema 查看性能视图
• SQL语句性能定位工具
  • Explain 查看执行计划
  • Profiling 查看执行耗时
  • Optimizer trace 查看SQL解析、优化、执行过程
• 第三方定位工具
  • Innotop
  • Percona-toolkit
  • Zenoss MySQL Database Zenpack

3.4 MySQL数据库层性能定位工具 - 状态变量

• MySQL数据库中提供大量状态变量
  • SQL执行频率
  • 数据库的磁盘读写
  • 线程连接
  • Flush脏页
• 查看状态变量
  • select * from performance_schema.global_status;
  • select global status;
    

3.5 MySQL数据库层性能定位工具 - 慢日志

• 慢日志是MySQL提供的一种日志记录，用来记录MySQL响应时间超过阈值的语句
• 参数设置
  • Slow_query_log：是否开启慢日志
  • Slow_query_log_file：MySQL数据库慢查询日志存储路径
  • Long_query_time：慢查询域值
• 慢日志分析
  • MySQL提供了日志分析工具mysqldumpslow
  • mysqldumpslow可根据访问次数、锁定时间、返回记录、查询时间等进行排序

3.6 MySQL数据库层性能定位工具 - show processlist

• Show processlist/show full processlist 查看当前MySQL是否由压力，当前执行语句，当前语句耗时等
• 执行方法
  • show processlist/show full processlist
  • select * from information_schema.processlist
• Column说明
  • ld：线程ID，使用kill id，杀死线程
  • db：数据库
  • User：用户
  • Command：当前执行命令状态，sleep、Query、Execute
  • Time：消耗时间，单位秒
  • State：执行状态，Sending data，update，updating等
  • Info：执行的sql语句

3.7 MySQL数据库层性能定位工具 - performance_schema

• 监控MySQL运行过程中的资源消耗及等待情况
  • 查看耗时语句
    SELECT DIGEST_TEXT,COUNT_STAR,AVG_TIMER_WAIT FROM events_statements_summary_by_digest ORDER BY AVG_TIMER_WAIT DESC limit 3;
  • 查看文件物理IO开销
    SELECT file_name,event_name,SUM_NUMBER_OF_BYTES_READ,SUM_NUMBER_OF_BYTES_WRITE FROM file_summary_by_instance ORDER BY SUM_NUMBER_OF_BYTES_READ + SUM_NUMBER_OF_BYTES_WRITE DESC LIMIT 3;
  • 分析当前最耗时的事件
    select EVENT_NAME,COUNT_STAR,SUM_TIMER_WAIT,AVG_TIMER_WAIT from events_waits_summary_global_by_event_name order by count_star desc limit 5;
  • 分析具体SQL各阶段耗时
    select t2.event_name,t2.THREAD_ID,t2.EVENT_ID,t2.TIMER_WAIT from events_statements_history_long t1,event_stages_history_long t2 where t1.event_id=t2.NESTING_EVENT_ID and t2.sql_next='?';
  • 分析具体某阶段耗时
    select t2.event_id,t2.evnet_name,t2.object_name,t2.operation,t2.timer_waitl,t2.nesting_event_id,t1.timer_wait from events_stages_history t1,events_waits_history_long t2 where t1.event_id=t2.nesting_event_id and t1.thread_id='?';

3.8 MySQL InnoDB层性能定位工具 - show engine innodb status

• Show engine innodb status 是MySQL提供的一个用于查看innodb引擎状态信息的工具
  • 信号
  • 最新的外键错误
  • 最新的死锁信息
  • 事物信息
  • 文件I/O
  • Insert Buffer 和自适应hash
  • 日志
  • Buffer pool和内存
  • 行操作数据
    

3.9 SQL语句性能定位工具 - 执行计划

• 执行计划是SQL在数据库中执行时的表现情况，用于单个SQL语句的性能分析、优化等场景

• 执行计划使用explain关键字
  

• 执行计划列信息说明

  • type：连接使用类型
  • key：实际用到的索引
  • ref：当前表在利用key列记录中的索引进行查询时用到的行或常量
  • Rows：执行查询时估算返回的行数
  • Filtered：返回结果的行数占需读取行数的百分比，越大越好，实际读取的行数与需要返回的行数接近
  • Extra：额外的信息

3.10 SQL语句性能定位工具 - Profiling

• Show profile 是MySQL提供可以用来分析当前会话中语句执行的资源消耗情况，用户SQL调优

• 开启profiling：set profiling=1

• 执行show profile 查看最近执行的15次sql
  

• 执行show profile for query query_id 查看具体SQL的执行细节

• Profiling的数据存放在information_schema.profiling表里
  

3.11 SQL语句性能定位工具 - optimizer trace

• Optimizer trace 把MySQL Optimizer的决策和执行过程输出成文本，了解每个SQL是如何被解析、优化并到执行
• 开启 optimizer trace：set optimizer_trace="enable=on"
• Optimizer trace的信息存放在 Information_schema.optimize r_trace 表中

3.12 MySQL数据库性能问题定位思路

4. MySQL优化思路、

4.1 鲲鹏平台性能优化

4.1.1 鲲鹏平台性能优化 - BIOS 配置优化

• 关闭SMMU
  • 进入BIOS -> “Advanced > MISC Config” -> “Support Smmu” 设置 Disable
• 关闭预取
  • 进入BIOS -> “Advanced > MISC Config” -> “CPU Prefetching Configurtion” 设置 Disable

4.1.2 鲲鹏平台性能优化 - 网卡中断亲和性

• 关闭irqbalance
  • systemctl stop irpbalance.service
  • systemctl disable irqbalance.servlce
  • systemctl status irqbalance.service
• 网卡中断绑核
  • 查询中断号
    • cat /proc/interrupts | grep $eth
  • 手动绑定中断到不同CPU
    • echo $cpunum > /proc/irq/$irq/smp_affinity_list

4.1.3 鲲鹏平台性能优化 - NVME SSD原子特性写

4.2 编译优化

• 目前在ARM架构，开源数据库只有部分版本由ARM版本，其他版本都要通过源码编译安装
• 源码编译安装提供了更多的选择，可以根据实际应用场景选择编译选项，例如
  • MySQL数据库支持多种字符集和多种引擎，可以根据实际情况只安装需要的字符集和引擎
    • -with-extra-charsets=none
  • 将MySQL编译成静态执行文件而无需共享页也能获得更好的性能
    • -with-mysqld-ldflags=-all-static
  • 根据ARM架构，编译的时候添加适配于ARM指令也能提供更好的性能
    • DCMAKE_C_FLAGS="-march=armv8.2-a+lse -mtune=tsv110"

4.3 参数优化

• MySQL内存参数优化
  • Innodb_buffer_pool_size
  • Innodb_buffer_pool_instance
  • Innodb_log_file_size
• 并发控制参数优化
  • Innodb_thread_concurrency
  • Innodb_spin_wait_delay
  • Innodb_sync_spin_loops
  • Innodb_spin_wait_pause_multiplier
• IO参数优化
  • Innodb_io_capacity
  • Innodb_io_capacity_max

4.4 jdbc 连接优化

• useServerPrepStmts=true
  Server端开启 prepare statement，提升解析效率
• cachePrepStmts=true
  开启每个连接缓存 prepareStatement
• preStmtCacheSize
  缓存prepareStatement对象个数
• preStmtCacheSqlLimit
  prepareStatement对象大小

4.5 表结构及SQL优化

• 优化目标
  • 减少IO次数：IO永远是数据库的瓶颈，数据库90%的时间都是被IO操作所占用
  • 减少CPU计算：优化CPU 运算量
• 优化原则
  • 尽量使用索引访问数据，减少IO操作
  • 创建高性能索引，避免过多索引引起磁盘利用率以过高内存占用，如果创建索引index key1(c1,c2,c3)，那么索引 index key2(c1,c2)->不需要 index key3(c1)->不需要
  • 长字段索引，考虑创建前缀索引
  • 避免复杂的多表join
  • 减少参与排序的数据量或者不必要的排序，减少CPU计算

5. 性能优化案例分享

5.1 MySQL数据库优化案例 - 参数优化

• 问题现象
  • MySQL数据库高并发读场景下，并发压力增加，数据库性能无提升，CPU、IO、网络均不存在瓶颈问题
• 问题分析
  • 发现table_cache_hits命中率非常低，状态变量Open_tables超过参数参数设置
    
    
• 优化方法
  • 调整参数table_open_cache=10000

5.2 MySQL数据优化案例 - 回表优化

• 问题现象
  • 某web平台的订单查询系统，在高并发下，数据库服务器的CPU利用率达到90%，且几乎都被MySQL占用
• 问题分析

  • show full processlist 查看运行SQL
    

  • 查看SQL执行计划
    
    Extra列显示 “using index condition”，通过 O_ICAT_IDX 非唯一性索引过滤数据，再到基表找到所有符合条件的行

  • 辅助索引访问路径

• 优化方法
  • 创建覆盖索引
    CREATE INDEX I_ID_1 on O_ITEM(I_CATEGORY,I_ID,I_DESC,I_DESCOUNT,I_NAME,I_PRICE,I_VERSION)

5.3 MySQL数据库优化案例 - spinlock优化

• 问题现象
  • 使用 benchmarksql 进行tpcc测试，tpmC指标一直比较低，CPU利用率大概在70%左右
• 问题分析
  • Perf工具查看热点函数，热点函数主要集中在queued_spin_lock_slowpath
    
  • Show engine innodb mutex 查看 mutex 争用
    
• 优化方法
  • MySQL的互斥自旋锁是使用自带的TTASEventMutex，与OS的spinlock相比，MySQL的自旋锁支持自定义自旋时间，自旋结束后会释放CPU时间片，让其他的进程使用CPU资源。MySQL的两大热点锁 lock_sys->mutex 和 trx_sys->mutex 均使用TTASEventMutex来保护对应的临界区资源，实现多并发
  • 默认的自旋参数，Kunpeng920 tpcc场景在高并发场景下，会出现大量线程进入 sync_array，并在进出时会由于争抢 sync_array->mutex 陷入内核态，会出现 queued_spin_lock_slowpath 的热点函数
  • 调整 Innodb_spin_wait_delay 和 Innodb_sync_spin_loops 参数，使得线程尽量少的陷入内核态，充分利用CPU资源