mysql之优化测试工具

作者：yushun | 来源：互联网 | 2023-09-01 18:33

一、mysql调优1.1为什么要测试高性能不是指绝对性能强悍,而是指业务能发挥出硬件的最大水平.性能强的服务器并非设计,而是不断改进,提升短板、测试，量化

一、mysql调优

1.1 为什么要测试

高性能不是指"绝对性能"强悍,而是指业务能发挥出硬件的最大水平.性能强的服务器并非"设计",而是不断改进,提升短板、测试&＃xff0c;量化找出短板的过程。只有会测试能把数据量化,才能进一步优化.

1.2 测试指标?

吞吐量&＃xff1a;单位时间内的事务处理数,单位tps(每秒事务数)
响应时间&＃xff1a;语句平均响应时间,一般截取某段时间内,95%范围内的平均时间
并发性&＃xff1a;线程同时执行
可扩展性&＃xff1a;资源增加,性能也能正比增加

1.3 用什么工具测试?

mysqlslap
sysbench
tpcc

1.4 mysqlslap测试

mysqlslap --optionsmysqlslap -h 127.0.0.1 -uroot --auto-generate-sql --concurrency 20 --iterations 1 --create-schema&＃61;big_data --query&＃61;&＃39;select * from dict limit 1&＃39;mysqlslap -h 127.0.0.1 -a -c 100 --number-of-queries 1000 -i 10 -u root -p

–concurrency 代表并发数量&＃xff0c;多个可以用逗号隔开concurrency&＃61;10,50,100, 并发连接线程数分别是10、50、100个并发。
–create-schema 代表自定义测试库名 database。(没指定时&＃xff0c;可能会遇到mysqlslap: 1049 Unknown database ‘mysqlslap’)
–engines -e 代表要测试的引擎&＃xff0c;可以有多个&＃xff0c;用分隔符隔开。
–iterations -i 代表要运行这些测试多少次。
–auto-generate-sql -a 代表用系统自己生成的SQL脚本来测试。
–auto-generate-sql-load-type 代表要测试的是读还是写还是两者混合的&＃xff08;read,write,update,mixed&＃xff09;
–number-of-queries 代表总共要运行多少次查询。每个客户运行的查询数量可以用查询总数/并发数来计算。
–debug-info 代表要额外输出CPU以及内存的相关信息。

结果显示&＃xff1a;

BenchmarkAverage number of seconds to run all queries: 1.176 secondsMinimum number of seconds to run all queries: 0.512 secondsMaximum number of seconds to run all queries: 4.017 secondsNumber of clients running queries: 10Average number of queries per client: 10

结果中可以看到执行语句的平均耗时&＃xff0c;最大耗时和最小耗时&＃xff0c;并发线程数等等。

1.5 sysbench 测试

sysbench 安装&＃xff1a;yum install sysbench -y &＃xff0c;等下载完成之后&＃xff0c;在 /usr/share/sysbench/ 目录下会有一些软件自带等lua测试脚本文件&＃xff0c;提供系统的压测脚本。
在这里插入图片描述

1.5.1 测试CPU性能

#2个线程寻找20000以内的素数 sysbench --test&＃61;cpu --cpu-max-prime&＃61;20000 --num-threads&＃61;2 run

1.5.2 测试IO性能

sysbench --test&＃61;fileio --file-total-size&＃61;20G prepare sysbench --test&＃61;fileio --file-total-size&＃61;20G --file-test-mode&＃61;rndrw run sysbench --test&＃61;fileio --file-total-size&＃61;20G cleanup

seqwr&＃xff1a; 顺序写入
seqrewq&＃xff1a; 顺序重写
seqrd&＃xff1a; 顺序读取
rndrd&＃xff1a; 随机读取
rndwr&＃xff1a; 随机写入
rndrw&＃xff1a; 混合随机读写

1.5.3 mysql压测

准备&＃xff1a;数据库建测试库 test_db&＃xff0c;同时创建好对应的测试账号test_user&＃xff0c;密码也是test_user&＃xff0c;用户有访问权限

sysbench --db-driver&＃61;mysql --time&＃61;300 --threads&＃61;10 --report-interval&＃61;1 --mysql-host&＃61;127.0.0.1 --mysql-port&＃61;3306 --mysql-user&＃61;test_user --mysql-password&＃61;test_user --mysql-db&＃61;test_db --tables&＃61;20 --table_size&＃61;1000000 oltp_read_write --db-ps-mode&＃61;disable prepare

参数说明&＃xff1a;

–db-driver&＃61;mysql &＃xff1a;基于mysql驱动连接mysql数据库&＃xff0c;如oracle或sqlserver&＃xff0c;那就是其他数据库驱动
–time&＃61;300&＃xff1a;连续访问300秒
–threads&＃61;10&＃xff1a;用10个线程模拟并发访问
–report-interval&＃61;1&＃xff1a;每隔1秒输出一下压测情况
–mysql-host&＃61;127.0.0.1 --mysql-port&＃61;3306 --mysql-user&＃61;test_user --mysql-password&＃61;test_user&＃xff1a;连接到哪台机器的哪个端口上的MySQL库&＃xff0c;用户名和密码是什么
–mysql-db&＃61;test_db --tables&＃61;20 --table_size&＃61;1000000&＃xff1a;在test_db这个库里&＃xff0c;构造20个测试表&＃xff0c;每个测试表构造100万条测试数据&＃xff0c;测试表名字类似于sbtest1&＃xff0c;sbtest2
oltp_read_write&＃xff1a;执行oltp数据库的读写测试
–db-ps-mode&＃61;disable&＃xff1a;禁止ps模式

测试数据库的综合读写TPS&＃xff0c;用oltp_read_write模式&＃xff08;命令最后是run而非prepare&＃xff0c;即运行压测&＃xff09;&＃xff1a;

测试数据库的只读性能&＃xff0c;用oltp_read_only模式&＃xff08;命令中oltp_read_write已变为oltp_read_only&＃xff09;&＃xff1a;

测试数据库的删除性能&＃xff0c;用oltp_delete模式&＃xff1a;

测试数据库的更新索引字段的性能&＃xff0c;用oltp_update_index模式&＃xff1a;

测试数据库的更新非索引字段的性能&＃xff0c;用oltp_update_non_index模式&＃xff1a;

测试数据库的插入性能&＃xff0c;用oltp_insert模式&＃xff1a;

测试数据库的写入性能&＃xff0c;用oltp_write_only模式&＃xff1a;

sysbench工具会根据你的指令构造出各种SQL语句去更新或查询20张测试表的数据&＃xff0c;同时监测数据库的压测性能指标&＃xff0c;最后完成压测后&＃xff0c;可执行下面cleanup命令&＃xff0c;清理数据。

结果分析&＃xff1a;

按照上面命令&＃xff0c;每隔1秒都会输出一次压测报告&＃xff0c;此时他每隔一秒会输出类似下面的东西&＃xff1a;

[ 22s ] thds: 10 tps: 380.99 qps: 7312.66 (r/w/o: 5132.99/1155.86/1321.35) lat (ms, 95%): 21.33 err/s: 0.00 reconn/s: 0.00

首先这是第22s输出的一段压测统计报告&＃xff0c;然后是其他一些统计字段&＃xff1a;

thds: 10&＃xff0c;这个意思就是有10个线程在压测
tps: 380.99&＃xff0c;这个意思就是每秒执行了380.99个事务
qps: 7610.20&＃xff0c;这个意思就是每秒可以执行7610.20个请求
(r/w/o: 5132.99/1155.86/1321.35)&＃xff0c;这个意思就是说&＃xff0c;在每秒7610.20个请求中&＃xff0c;有5132.99个请求是读请求&＃xff0c;1155.86个请求是写请求&＃xff0c;1321.35个请求是其他的请求&＃xff0c;就是对QPS进行了拆解
lat (ms, 95%): 21.33&＃xff0c;这个意思就是说&＃xff0c;95%的请求的延迟都在21.33毫秒以下
err/s: 0.00 reconn/s: 0.00&＃xff0c;这两个的意思就是说&＃xff0c;每秒有0个请求是失败的&＃xff0c;发生了0次网络重连

这个压测结果会根据每个人的机器的性能不同有很大差距&＃xff0c;要是机器性能特别高&＃xff0c;可开很多的并发线程去压测&＃xff0c;如100个线程&＃xff0c;此时可能会发现数据库每秒的TPS有上千个&＃xff0c;如果机器性能很低&＃xff0c;可能压测出TPS才二三十个&＃xff0c;QPS才几百个&＃xff0c;这都有可能。

另外在完成压测后&＃xff0c;会显示一个总的压测报告&＃xff0c;如下&＃xff1a;

SQL statistics:queries performed:read: 1480084 // 这就是说在300s的压测期间执行了148万多次的读请求write: 298457 // 这是说在压测期间执行了29万多次的写请求other: 325436 // 这是说在压测期间执行了30万多次的其他请求total: 2103977 // 这是说一共执行了210万多次的请求// 这是说一共执行了10万多个事务&＃xff0c;每秒执行350多个事务transactions: 105180( 350.6 per sec. ) // 这是说一共执行了210万多次的请求&＃xff0c;每秒执行7000&＃43;请求queries: 2103977 ( 7013.26 per sec. )ignored errors: 0 (0.00 per sec.)reconnects: 0 (0.00 per sec.)// 下面就是说&＃xff0c;一共执行了300s的压测&＃xff0c;执行了10万&＃43;的事务 General staticstics: total time: 300.0052stotal number of events: 105180 Latency (ms):min: 4.32 // 请求中延迟最小的是4.32msavg: 13.42 // 所有请求平均延迟是13.42msmax: 45.56 // 延迟最大的请求是45.56ms95th percentile: 21.33 // 95%的请求延迟都在21.33ms以内

1.5.4 测试事务性能

sysbench --test&＃61;oltp --mysql-table-engine&＃61;innodb \ --mysql-user&＃61;root --db-driver&＃61;mysql --mysql-db&＃61;test \ --oltp-table-size&＃61;3000 --oltp-table-name&＃61;t1 \ --mysql-socket&＃61;/var/lib/mysql/mysql.sock prepare

1.5.5 测试事务实例

1.6 tpcc测试

1.6.1 创建数据库/表

# mysql -h 192.168.1.201 -uroot -p -e &＃39;create database tpcc1000&＃39; # mysql -h 192.168.1.201 -uroot -p tpcc1000 # mysql -h 192.168.1.201 -uroot -p tpcc1000

1.6.2 tpcc_load装载数据

命令格式:

tpcc_load [server] [DB] [user] [pass] [warehouse]

./tpcc_load 192.168.2.201 tpcc5 root "" 5 tpcc5库下创建5个仓库(仓库越多数据量越大)
#注:普通机器约需要10分钟的时间

1.6.3 tpcc模拟订单事务

格式:

tpcc_start -h server_host -P port -d database_name -u user -p password -w warehouses -c connections -r warmup_time -l running_time

-w 仓库 -c 连接数 -r预热时间 -l 测试时间

./tpcc_start -h 192.168.1.201 -d tpcc1000 -u root -p &＃39;&＃39; -w 5 -c 5 -r 30 -l 30

注&＃xff1a;5线程测试5个仓库,30秒预热,30秒测试

1.7 mysql_status观察

1.7.1 测试场景&＃xff1a;

总数据3W以上,50个并发,每秒请求500-1000次&＃xff0c;请求结果缓存与memcache,生命周期为5分钟&＃xff0c;观察mysql连接数,每秒请求数的周期变化

1.7.2 mysql status观察脚本

#!/bin/bash while true do mysqladmin -h192.168.1.201 -uroot ext |awk &＃39; /Queries/ {q&＃61;$4} /Threads_connected/{tc&＃61;$4} /Threads_running/{tr&＃61;$4} END{printf "%3d %s %s\n",q,tc,tr} &＃39; >> num.txt sleep 1 done #awk 计算每秒查询数 awk &＃39;{q&＃61;$1-last;last&＃61;$1}{printf("%d\t%d\t%d\n",q,$2,$3)}&＃39; num.txt > num2.txt

1.7.3 模拟缓存周期失效状态图

1.7.4 模拟缓存失效时的状态变化

1.7.5 观察mysql进程状态

mysql -h 192.168.1.201 -u root -e &＃39;show processlist\G&＃39;|grep State:|sort|uniq -c|sort -rn 5 State: Sending data 2 State: statistics 2 State: NULL 1 State: Updating 1 State: update

1.7.6 值得注意的mysql进程状态

converting HEAP to MyISAM 查询结果太大时,把结果放在磁盘 create tmp table 创建临时表(如group时储存中间结果) Copying to tmp table on disk 把内存临时表复制到磁盘 locked 被其他查询锁住 logging slow query 记录慢查询 #注:把临时表内存变小,重现前

什么情况下产生临时表?

1: group by 的列和order by 的列不同时, 2表边查时,取A表的内容,group/order by另外表的列
2: distinct 和 order by 一起使用时
3: 开启了 SQL_SMALL_RESULT 选项

什么情况下临时表写到磁盘上?

取出的列含有text/blob类型时 —内存表储存不了text/blob类型
在group by 或distinct的列中存在>512字节的string列
select 中含有>512字节的string列,同时又使用了union或union all语句

如果服务器频繁出现converting HEAP to MyISAM说明:

sql有问题,取出的结果或中间结果过大,内存临时表放不下
服务器配置的临时表内存参数过小. [ tmp_table_size | max_heap_table_size ]

1.7.7 开启慢查询

1.7.8 profile分析语句

查看 profile 状态

mysql> show variables like &＃39;profiling&＃39;; &＃43;---------------&＃43;-------&＃43; | Variable_name | Value | &＃43;---------------&＃43;-------&＃43; | profiling | OFF | &＃43;---------------&＃43;-------&＃43;
打开 profile

mysql> set profiling&＃61;on; Query OK, 0 rows affected (0.00 sec)
profiles 查看分析列表

mysql> show profiles; &＃43;----------&＃43;------------&＃43;-----------------------------&＃43; | Query_ID | Duration | Query | &＃43;----------&＃43;------------&＃43;-----------------------------&＃43; | 1 | 0.00073300 | SELECT DATABASE() | | 2 | 0.00734900 | select * from dict limit 1 | &＃43;----------&＃43;------------&＃43;-----------------------------&＃43; 2 rows in set (0.00 sec)
查看单条语句执行过程

mysql> show profile for query 6;&＃43;--------------------&＃43;----------&＃43;| Status | Duration |&＃43;--------------------&＃43;----------&＃43;| starting | 0.000052 || Opening tables | 0.000009 || System lock | 0.000003 || Table lock | 0.000006 || init | 0.000016 |... 省略...| freeing items | 0.000029 || logging slow query | 0.000002 || cleaning up | 0.000019 |&＃43;--------------------&＃43;----------&＃43;15 rows in set (0.00 sec)
请重现create tmp table 等值得注意的状态
http://dev.mysql.com/doc/refman/5.1/en/internal-temporary-tables.html
http://dev.mysql.com/doc/refman/5.1/en/general-thread-states.html

提高插入的方法

当mysql大批量插入数据的时候就会变的非常慢, mysql提高insert into 插入速度的方法有三种:

第一种插入提速方法:
如果数据库中的数据已经很多(几百万条), 那么可以加大mysql配置中的 bulk_insert_buffer_size&＃xff0c;这个参数默认为8M

bulk_insert_buffer_size&＃61;100M
第二种mysql插入提速方法:
改写所有 insert into 语句为 insert delayed into

这个insert delayed不同之处在于&＃xff1a;立即返回结果&＃xff0c;后台进行处理插入。

第三个方法: 一次插入多条数据:
insert中插入多条数据&＃xff0c;举例:

insert into table values(‘11’,‘11’),(‘22’,‘22’),(‘33’,‘33’)…;

推荐阅读

foreach
包含phppdoerrorcode的词条

包含phppdoerrorcode的词条 ... [详细]

蜡笔小新 2024-11-14 12:06:14
python
Spring Data JdbcTemplate 入门指南

本文将介绍如何使用 Spring JdbcTemplate 进行数据库操作，包括查询和插入数据。我们将通过一个学生表的示例来演示具体步骤。 ... [详细]

蜡笔小新 2024-11-14 10:33:29
config
解决Only fullscreen opaque activities can request orientation错误的方法

本文介绍了在使用PictureSelectorLight第三方框架时遇到的Only fullscreen opaque activities can request orientation错误，并提供了一种有效的解决方案。 ... [详细]

蜡笔小新 2024-11-13 09:46:25
bit
华为捐赠欧拉操作系统，承诺不推商用版

华为近日宣布将欧拉开源操作系统捐赠给开放原子开源基金会，并承诺不会推出欧拉的商用发行版。此举旨在推动欧拉和鸿蒙操作系统的全场景融合与生态发展。 ... [详细]

蜡笔小新 2024-11-14 13:19:40
ip
Java 15 发布，带来多项重要更新！

2020年9月15日，Oracle正式发布了最新的JDK 15版本。本次更新带来了许多新特性，包括隐藏类、EdDSA签名算法、模式匹配、记录类、封闭类和文本块等。 ... [详细]

蜡笔小新 2024-11-14 12:11:09
ip
Android Studio SQLite 数据库增删改查简单（代码参考）

一个建表一个执行crud操作建表代码importandroid.content.Context;importandroid.database.sqlite.SQLiteDat ... [详细]

蜡笔小新 2024-11-14 11:01:49
ip
日志处理流程：Flume+MapReduce+Hive+Sqoop+MySQL

本文介绍了如何使用Flume从Linux文件系统收集日志并存储到HDFS，然后通过MapReduce清洗数据，使用Hive进行数据分析，并最终通过Sqoop将结果导出到MySQL数据库。 ... [详细]

蜡笔小新 2024-11-13 18:47:34
python
操作系统如何通过进程控制块管理进程

本文详细介绍了操作系统如何通过进程控制块（PCB）来管理和控制进程。PCB是操作系统感知进程存在的重要数据结构，包含了进程的标识符、状态、资源清单等关键信息。 ... [详细]

蜡笔小新 2024-11-13 16:14:39
go
Spring – Bean Life Cycle

Spring – Bean Life Cycle ... [详细]

蜡笔小新 2024-11-13 13:24:40
go
MySQL 中 SQL 正则表达式的应用与技巧

本文介绍了在 MySQL 中如何使用正则表达式来提高查询效率，通过具体示例展示了如何筛选包含中文字符的记录，并详细解释了正则表达式的各种特殊字符和结构。 ... [详细]

蜡笔小新 2024-11-12 21:03:49
go
SQL 批量导入时管理外键约束

本文介绍如何在将数据库从服务器复制到本地时，处理因外键约束导致的数据插入失败问题。 ... [详细]

蜡笔小新 2024-11-12 19:05:11
config
Ext JS MVC系列一：环境搭建与框架概览

本文主要介绍了如何在项目中使用Ext JS 4作为前端框架，并详细讲解了Ext JS 4的MVC开发模式。文章将从项目目录结构、相关CSS和JS文件的引用以及MVC框架的整体认识三个方面进行总结。 ... [详细]

蜡笔小新 2024-11-12 16:05:44
config
MySQL 5.7 学习指南：SQLyog 中的主键、列属性和数据类型

本文介绍了 MySQL 5.7 中主键（Primary Key）和自增（Auto-Increment）的概念，以及如何在 SQLyog 中设置这些属性。同时，还探讨了数据类型的分类和选择，以及列属性的设置方法。 ... [详细]

蜡笔小新 2024-11-12 15:57:04
数组
如何在Java中使用DButils类

这期内容当中小编将会给大家带来有关如何在Java中使用DButils类，文章内容丰富且以专业的角度为大家分析和叙述，阅读完这篇文章希望大家可以有所收获。D ... [详细]

蜡笔小新 2024-11-12 13:46:11
bit
单片机入门指南：基础理论与实践

本文介绍了单片机的基础知识及其应用。单片机是一种将微处理器（类似于CPU）、存储器（类似硬盘和内存）以及多种输入输出接口集成在一块硅片上的微型计算机系统。通过详细解析其内部结构和功能，帮助初学者快速掌握单片机的基本原理和实际操作方法。 ... [详细]

蜡笔小新 2024-11-11 23:53:26

yushun

这个家伙很懒，什么也没留下！

Tags | 热门标签

RankList | 热门文章