微认证：基于BoostKit的MySQL性能优化

基于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 鲲鹏平台性能优化

关闭SMMU

进入BIOS -> “Advanced > MISC Config” -> “Support Smmu” 设置 Disable

关闭预取

进入BIOS -> “Advanced > MISC Config” -> “CPU Prefetching Configurtion” 设置 Disable

关闭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.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资源

MySQL 数据库

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。