【第24天】SQL进阶-查询优化- performance

阿里云国内75折 回扣 微信号：monov8
阿里云国际，腾讯云国际，低至75折。AWS 93折 免费开户实名账号 代冲值 优惠多多 微信号：monov8 飞机：@monov6

回城传送–》《32天SQL筑基》

文章目录

零、前言

今天是学习 SQL 打卡的第 24 天每天我会提供一篇文章供群成员阅读 不需要订阅付钱 。

希望大家先自己思考如果实在没有想法再看下面的解题思路自己再实现一遍。在小虚竹JAVA社区 中对应的 【打卡贴】打卡今天的任务就算完成了养成每天学习打卡的好习惯。

​ 虚竹哥会组织大家一起学习同一篇文章所以有什么问题都可以在群里问群里的小伙伴可以迅速地帮到你一个人可以走得很快一群人可以走得很远有一起学习交流的战友是多么幸运的事情。

​ 我的学习策略很简单题海策略+ 费曼学习法。如果能把这些题都认认真真自己实现一遍那意味着 SQL 已经筑基成功了。后面的进阶学习可以继续跟着我一起走向架构师之路。

今天的学习内容是SQL进阶-查询优化- performance_schema系列实战一利用等待事件排查MySQL性能问题

一、背景

在生产上线之前对数据库进行增删改查的基准测试收集基准数据为了后续做扩容和架构升级做准备。
MySQL数据库基准测试通常选择sysbench、tpcc-mysql、workbench。

以下我们以sysbench基准测试工具压测MySQL数据库为例介绍如何使用performance_schema的等待事件来排查数据库性能瓶颈所在。

二、performance_schema配置配置表启用等待事件的采集与记录

使用performance_schema配置表启用等待事件的采集与记录。

 use performance_schema;

在这里插入图片描述
修改setup_instruments 表的enabled和timed字段为yes表示启用对应的instruments

update setup_instruments set enabled='yes',timed='yes' where name like 'wait/%';

在这里插入图片描述
查看修改结果enabled和timed字段为YES即表示当前instruments已经启用但此时采集器并不会立即采集事件数据需要保存这些等待事件的表–consumers启用之后才会开始采集

select * from setup_instruments where name like 'wait/%';

在这里插入图片描述
在这里插入图片描述
启用等待事件的consumers

update setup_consumers set enabled='yes' where name like '%wait%';

在这里插入图片描述
查询结果

select * from setup_consumers where name like '%wait%';

在这里插入图片描述

三、sysbench基准测试工具

mysql 基准测试可以理解为对mysql数据库运行时的一种压力测试。关键指标有3个方面
TPS/QPS衡量吞吐量。
响应时间包括平均响应时间最小响应时间最大响应时间和时间百分比竺。
并发量同时处理查询请求的数量。
sysbench支持多线程工作并且能够跨平台安装部署。

3.1 安装和使用sysbench

3.1.1 yum安装

curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash

在这里插入图片描述

sudo yum -y install sysbench

如图为安装成功。
在这里插入图片描述

3.1.2 查看版本信息

sysbench --version

在这里插入图片描述

3.1.3 sysbench 使用说明

 sysbench --help

以下中括号中的值表示默认值

option类型参数名称参数含义
通用选项threads指定线程数[1]
通用选项events限制最大请求数0表示不限制[0]
通用选项time限制最长执行时间0表示不限制[10] 单位秒
通用选项forced-shutdown达到最长执行时间后还需等待多久关闭sysbench off表示禁用该功能[off]
通用选项thread-stack-size每个线程使用的栈空间大小[64K]
通用选项rate平均事务处理速率0表示不限制[0]
通用选项report-interval每隔几秒报告一次结果0表示禁用间隔报告[0]
通用选项config-file从文件中读取命令行选项
mysql专用选项mysql-hostmysql主机名[localhost]
mysql专用选项mysql-portmysql端口[3306]
mysql专用选项mysql-socket指定 socket 文件来连接
mysql专用选项mysql-user登录mysql的用户名默认值[sbtest]
mysql专用选项mysql-password登录mysql的密码
mysql专用选项mysql-db指定数据库名称默认值[sbtest]
mysql专用选项mysql-ssl使用ssl连接
mysql专用选项mysql-ssl-cipher使用ssl连接时的密码
mysql专用选项mysql-compression使用压缩算法
mysql专用选项mysql-debug跟踪所有的客户端使用[off]
mysql专用选项mysql-ignore-errors忽略指定的错误代码或者使用all忽略所有的错误[1213,1020,1205]

  • testname :指定要进行测试的名称。

  • command : 代表sysbench 要执行的命令包含prepare,run和cleanup三个命令。

    • prepare :为测试提前准备数据。
    • run : 执行正式的测试。
    • cleanup: 在测试完成后对数据库进行清理。

3.2 sysbench 测试服务器cpu性能

sysbench cpu --cpu-max-prime=20000 --threads=2 run

对cpu的测试是进行素数的加法运算测试其中
–cpu-max-prime生成素数的数量上限。
–threads启动进行素数计算的线程数。

在这里插入图片描述
说明

Running the test with following options:
#指定线程个数
Number of threads: 2
Initializing random number generator from current time

#每个线程产生的素数上限为2万个。
Prime numbers limit: 20000

Initializing worker threads...

Threads started!

CPU speed:
#所有线程每秒完成了839.89次event
    events per second:   839.89

General statistics:
    total time:                          10.0022s 
    total number of events:              8402	#在10.0022s秒内共完成了8402次event
    

Latency (ms):
         min:                                    2.29
         avg:                                    2.38
         max:                                   10.31
         95th percentile:                        2.61  #95%的events都在2.61毫秒内完成。
         sum:                                19997.47

Threads fairness:
# 平均每完成4201.0000次event,标准差是2
    events (avg/stddev):           4201.0000/2.00
    # 每个线程平均耗时9.9987秒。
    execution time (avg/stddev):   9.9987/0.00

3.3 sysbench测试硬盘的IOPS

3.3.1 准备测试数据

sysbench fileio --file-total-size=1G --file-test-mode=rndrw --time=30 --max-requests=0 prepare

在这里插入图片描述
在这里插入图片描述

3.3.2 开始测试

 sysbench fileio --file-total-size=1G --file-test-mode=rndrw --time=30 --max-requests=0 run

执行效果
在这里插入图片描述

IOPS的计算公式如下

IOPS=Throughput read +Throughput written*1024/16KB
Throughput read :每秒的输入量
Throughput written:每秒的输出量。

从图上的测试数据可以计算出当前硬盘的IOPS为

IOPS= 12.31+8.21*1024/16=1313.28

3.3.3 清除测试数据

 sysbench fileio --file-total-size=1G --file-test-mode=rndrw --time=30 --max-requests=0 cleanup

在这里插入图片描述

3.4 实战使用sysbench测试mysql数据库

sysbench提供了对数据库性能测试的lua脚本。这些脚本放在 /usr/share/sysbench/目录下。
为了以树形查看结构先安装tree命令。

sudo yum install -y tree

在这里插入图片描述

列 出lua脚本。

tree /usr/share/sysbench/ -P *.lua

在这里插入图片描述
下面实战演示使用lua脚本测试mysql数据库。

3.4.1 准备测试数据

创建测试数据库sysbenchdemo

create database sysbenchdemo;

在这里插入图片描述
准备测试数据

sysbench /usr/share/sysbench/oltp_insert.lua \
--mysql-host=localhost \
--mysql-port=3306 \
--mysql-socket=/tmp/mysql.sock \
--mysql-user=root \
--mysql-password=xiaoxuzhu \
--mysql-db=sysbenchdemo \
--db-driver=mysql \
--tables=5 \
--table-size=100000 \
--time=180 prepare

在这里插入图片描述

说明

使用/usr/share/sysbench/oltp_insert.lua脚本创建了5张表往每张表插入100000条数据。

3.4.2 开始测试

sysbench /usr/share/sysbench/oltp_insert.lua \
--mysql-host=localhost \
--mysql-port=3306 \
--mysql-socket=/tmp/mysql.sock \
--mysql-user=root \
--mysql-password=xiaoxuzhu \
--mysql-db=sysbenchdemo \
--db-driver=mysql \
--tables=5 \
--table-size=100000 \
--time=180 run

sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:
Number of threads: 1
Initializing random number generator from current time


Initializing worker threads...

Threads started!

SQL statistics:
    queries performed:
        read:                            0
        # 总的写次数
        write:                           29970
        other:                           0
        total:                           29970
        # 总的事务数和每秒事务数
    transactions:                        29970  (166.50 per sec.)
    queries:                             29970  (166.50 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
# 总的执行时间和事件数。
    total time:                          180.0018s
    total number of events:              29970
# 延时统计信息
Latency (ms):
         min:                                    3.16
         avg:                                    6.00
         max:                                  237.40
         95th percentile:                       11.24
         sum:                               179912.58

Threads fairness:
    events (avg/stddev):           29970.0000/0.00
    execution time (avg/stddev):   179.9126/0.00

3.4.3 清理测试数据

sysbench /usr/share/sysbench/oltp_insert.lua \
--mysql-host=localhost \
--mysql-port=3306 \
--mysql-socket=/tmp/mysql.sock \
--mysql-user=root \
--mysql-password=xiaoxuzhu \
--mysql-db=sysbenchdemo \
--db-driver=mysql \
--tables=5 \
--table-size=100000 \
--time=180 cleanup

在这里插入图片描述

四、sysbench对mysql数据库执行加压

逐渐增加并发线程数直到tps、qps不再随着线程数的增加而增加为止

4.1 准备测试数据

sysbench /usr/share/sysbench/oltp_insert.lua \
--mysql-host=localhost \
--mysql-port=3306 \
--mysql-socket=/tmp/mysql.sock \
--mysql-user=root \
--mysql-password=xiaoxuzhu \
--mysql-db=sysbenchdemo \
--db-driver=mysql \
--tables=8 \
--table-size=100000 \
--time=180 prepare

在这里插入图片描述

4.2 开始测试

sysbench /usr/share/sysbench/tests/include/oltp_legacy/oltp.lua \
--mysql-host=localhost \
--mysql-port=3306 \
--mysql-socket=/tmp/mysql.sock \
--mysql-user=root \
--mysql-password=xiaoxuzhu \
--mysql-db=sysbenchdemo \
--db-driver=mysql \
--oltp-table-size=5000000 \
--oltp-tables-count=8 \
--num-threads=16 \
--max-time=1800 \
--max-requests=0 \
--report-interval=1 run

从sysbench的输出结果中我们可以看到在16个并发线程oltp压力下tps可以跑到600～900qps:15000左右且延迟在1000ms+
在这里插入图片描述
为什么延迟这么高使用top看下可以看出mysql 的cpu占用率高。
在这里插入图片描述
为了方便查询等待事件统计我们可以先创建一个视图用于实时统计当前等待事件非历史数据

 use performance_schema;

在这里插入图片描述

create view sys.test_waits as select sum(TIMER_WAIT) as TIMER_WAIT,sum(NUMBER_OF_BYTES) as NUMBER_OF_BYTES, EVENT_NAME,OPERATION from events_waits_current where EVENT_NAME!='idle' group by EVENT_NAME,OPERATION;

在这里插入图片描述

select sys.format_time(TIMER_WAIT),sys.format_bytes(NUMBER_OF_BYTES),EVENT_NAME,OPERATION from sys.test_waits where sys.format_time(TIMER_WAIT) not regexp 'ns|us' order by TIMER_WAIT desc;

在这里插入图片描述
也可以直接查询events_waits_current表

select THREAD_ID,EVENT_NAME,sys.format_time(TIMER_WAIT),INDEX_NAME,NESTING_EVENT_TYPE,OPERATION,NUMBER_OF_BYTES 
from events_waits_current 
where EVENT_NAME!='idle' order by TIMER_WAIT desc;

在这里插入图片描述

从上述等待事件的查询结果中是cpu不够wait/synch/cond/mysqlx/scheduler_dynamic_worker_pending 调度程序动态工作程序挂起。

五、总结

本文分享了sysbench如何安装和使用详细介绍了通过sysbench压测mysql数据库利用等待事件排查MySQL性能问题。
哈哈哈你学会了吗～

六、参考

应用示例荟萃 | performance_schema全方位介绍上

【mysql 数据库进阶实战-第10章mysql性能优化与运维管理】作者赵渝强老师

我是虚竹哥我们明天见~

阿里云国内75折 回扣 微信号：monov8
阿里云国际，腾讯云国际，低至75折。AWS 93折 免费开户实名账号 代冲值 优惠多多 微信号：monov8 飞机：@monov6
返回列表

上一篇：学习笔记——NoSQL数据库；Redis概述；redis中常用的数据类型（key、string）

下一篇：C#基础到入门(一篇就够了)_c#怎么学

“【第24天】SQL进阶-查询优化- performance” 的相关文章

京东数据分析（竞品监控）：飞利浦王牌产品在中国失利1年前 (2023-02-02)
透视华为云云原生数据库的前世今生及未来演进，能给行业带来哪些启发？1年前 (2023-02-02)
如何存储 Git 大文件？1年前 (2023-02-02)
前端经典面试题 | 吊打面试官系列 之 说说你对TypeScript 和 JavaScript的理解1年前 (2023-02-02)
记录Android Studio连接华为(nova)手机鸿蒙系统踩过的坑_android studio 鸿蒙1年前 (2023-02-02)
真题解析 | 2022数模美赛C题：股票投资策略1年前 (2023-02-02)
【云原生 | 从零开始学Kubernetes】二十三、Kubernetes控制器Statefulset1年前 (2023-02-02)
day13-实现Spring底层机制-031年前 (2023-02-02)
分布式协议与算法-Raft算法1年前 (2023-02-02)
【算法训练营day31】LeetCode455. 分发饼干 LeetCode376. 摆动序列 LeetCode53. 最大子序和1年前 (2023-02-02)

阿里云国际版