Ubuntu1604使用Sysbench压测Mysql以及Mysql 配置调优、慢查询

Sysbench压测

环境

VBOX的Ubuntu1604

安装Sysbench

sudo apt-get install sysbench

检测是否安装成功

sysbench --version

先创建名为sbtest的数据库，再运行创建测试数据的命令

sysbench --test=oltp --mysql-host=10.1.4.215 --mysql-db=sbtest --oltp-table-size=500000 --mysql-user=root --mysql-password=123456 prepare

压测

连接信息参数

参数	功能
–mysql-host	mysql地址
–mysql-port	mysql端口
–mysql-user	用户名
–mysql-password	密码

执行参数

参数	功能
–oltp-test-mode	执行模式（simple、nontrx、complex）
–oltp-tables-count	测试表的数量
–oltp-table-size	测试表的记录数
–threads	并发线程数
–time	测试执行时间（秒）
–report-interval	生成报告单的间隔时间（秒）

命令

命令	功能
prepare	准备测试数据
run	执行测试
cleanup	清除测试数据

执行压测

sysbench --num-threads=16 --test=oltp --mysql-table-engine=innodb --mysql-host=10.1.4.215 --mysql-db=sbtest --oltp-table-size=500000 --mysql-user=root --mysql-password=123456 run

read：多少次读操作
write：多少次写操作
other：其他操作次数
total：总的操作次数
transactions（118.26 per sec.）：数据库每秒执行的事务

CPU测试

sysbench --test=cpu --cpu-max-prime=5000 run

线程测试

sysbench  --test=threads --num-threads=1000 --thread-yields=200 --thread-locks=10 run

Mysql 配置调优

max_connections最大连接数

Mysql的最大并发连接数，默认151
最大连接数上线是16384
实际连接数是最大连接数的 85% 较为合适

// 当前配置中最大的并发连接数
SHOW VARIABLES LIKE 'max_connections';
// 当前实际的并发连接数
SHOW STATUS LIKE 'MAX_USED_CONNECTIONS';

修改配置文件，进行配置

vim /etc/mysql/mysql.conf.d/mysqld.cnf
max_connections=5000

Mysql会为每个链接创建缓存区，所以不应该盲目上调最大连接数

back_log优化请求堆栈

back_log是存放执行请求的堆栈大小，默认值是50
当数据库连接达到最大并发数，再有请求进来并不是拒绝请求，而是把请求存放在堆栈中，当释放连接，再从堆栈中取出。可以设置成最大连接数的20%-30%。
在Mysql的文件中配置

back_log=90

innodb_thread_concurrency并发线程数

默认是0
并发线程数应该设置为CPU核心数的两倍，过大也不好，会导致调度大量的线程，损耗CPU的性能，导致运行变慢。

innodb_thread_concurrency=2

wait-timeout超时时间

超时时间，单位秒
默认超时为 8 小时，连接长期不是用且不断开，浪费资源。

wait-timeout=600

InnoDB缓存大小

InnoDB缓存指的是 数据表的数据 以及 部分索引数据（将一部分二叉树数据缓存起来查询就更快了）
innodb_buffer_pool_size是InnoDB的缓存容量，默认是128MB
InnoDB缓存的大小可以设置为主机内存的70%-80%

innodb_buffer_pool_size=400M

Mysql慢查询

慢查询会把查询耗时超过规定时间的SQL记录下来
可以利用慢查询日志，定位性能瓶颈
Mysql默认关闭了慢查询日志

// 查询慢查询是否开启，以及日志存放位置
SHOW VARIABLES LIKE 'slow_query%'

设置慢查询日志开关状态

slow_query_log = ON

配置查询超时时间，单位秒

long_query_time = 1

EXPLAIN模拟测试

EXPLAIN是mysql自带的模拟查询测试

EXPLAIN SELECT
	* 
FROM
	sbtest

type为ALL就是执行了全表扫描

EXPLAIN SELECT
	* 
FROM
	sbtest 
WHERE
	id < 100

在possible_keys为PRIMARY就是走了索引查询