latte Studio

mysql压测与生产环境配置示例

mysql压测与生产环境配置示例
 运维/数据库  2006  5 分钟  

mysql压测

关系型数据库性能衡量指标

  • qps：query per second，每秒查询数
  • tps：transaction per second，每秒事务数

常用压测工具

  • mysqlslap
  • sysbench
  • tpcc-mysql
  • mysql benchmark suite
  • mysql super-smack
  • mybench

mysqlslap压测

1、安装：

[root@host2 ~]# rpm -qf /usr/bin/mysqlslap 
mariadb-5.5.65-1.el7.x86_64
mariadb的rpm包中包含该工具

[root@host2 ~]# which mysqlslap 
/usr/local/mysql/bin/mysqlslap
# 通用二进制的mysql 5.7也带有该工具

2、使用语法

[root@host2 ~]# man mysqlslap
[root@host2 ~]# mysqlslap --help

3、常用options

--auto-generate-sql ,-a 
#mysqlslap自动生成测试的sql语句；

--auto-generate-sql-load-type=type
#生成的语句类型，读，写，或2者混合；

--auto-generate-sql-add-auto-increment 
#对生成的包，添加auto-increment列

--number-char-cols=N
# 自动生成的测试表，有N个字符类型的列

--numer-int-cols=N
# 自动生成的测试有，有n个整数列

--number-of-queries=n
# 总查询次数

--query=name ,-q
# name是脚本文件名，里面可以定义slq语句，进行测试

--create-schema
# 指定创建的测试库名

--concurrency=n,-c n
# 并发客户端数量

--engine=name,-e name
# 测试哪个存储引擎

--iterations=n,-i n
#  The number of times to run the tests.执行的测试次数，如50个并发执行4次，求平均

--only-print
# 不实际执行，只生成执行步骤信息；

4、测试示例

[root@host2 ~]# mysqlslap -a -uroot
Benchmark
	Average number of seconds to run all queries: 0.003 seconds
	Minimum number of seconds to run all queries: 0.003 seconds
	Maximum number of seconds to run all queries: 0.003 seconds
	Number of clients running queries: 1
	Average number of queries per client: 0
#自动生成测试sql

[root@host2 ~]# mysqlslap -a -c 100 -uroot
Benchmark
	Average number of seconds to run all queries: 0.284 seconds
	Minimum number of seconds to run all queries: 0.284 seconds
	Maximum number of seconds to run all queries: 0.284 seconds
	Number of clients running queries: 100
	Average number of queries per client: 0
并发100，测试1次

[root@host2 ~]# mysqlslap -a -i 10 -uroot
Benchmark
	Average number of seconds to run all queries: 0.002 seconds
	Minimum number of seconds to run all queries: 0.002 seconds
	Maximum number of seconds to run all queries: 0.003 seconds
	Number of clients running queries: 1
	Average number of queries per client: 0
并发1，测试10次

[root@host2 ~]# mysqlslap -a -c 100 -i 10 -uroot
Benchmark
	Average number of seconds to run all queries: 0.178 seconds
	Minimum number of seconds to run all queries: 0.167 seconds
	Maximum number of seconds to run all queries: 0.211 seconds
	Number of clients running queries: 100
	Average number of queries per client: 0
并发100，测试10次

[root@host2 ~]# mysqlslap -a --auto-generate-sql-add-autoincrement -c 100 -i 10 -uroot
Benchmark
	Average number of seconds to run all queries: 0.096 seconds
	Minimum number of seconds to run all queries: 0.091 seconds
	Maximum number of seconds to run all queries: 0.113 seconds
	Number of clients running queries: 100
	Average number of queries per client: 0
并发100，测试10次，有自动增长字段


[root@host2 ~]# mysqlslap -a --auto-generate-sql-load-type=read -c 100 -i 10 -uroot
Benchmark
	Average number of seconds to run all queries: 0.112 seconds
	Minimum number of seconds to run all queries: 0.110 seconds
	Maximum number of seconds to run all queries: 0.117 seconds
	Number of clients running queries: 100
	Average number of queries per client: 0
并发100，测试10次，读类型sql测试

[root@host2 ~]# mysqlslap -a --auto-generate-sql-write-number=1000 -c 100 -i 10 -uroot
Benchmark
	Average number of seconds to run all queries: 0.401 seconds
	Minimum number of seconds to run all queries: 0.362 seconds
	Maximum number of seconds to run all queries: 0.610 seconds
	Number of clients running queries: 100
	Average number of queries per client: 0
并发100，测试10次，测试写类型sql共1000次


[root@host2 ~]# mysqlslap -a -c 50,100,400 --number-of-queries 2000 --iterations=5 --debug-info -uroot
Benchmark
	Average number of seconds to run all queries: 0.390 seconds
	Minimum number of seconds to run all queries: 0.379 seconds
	Maximum number of seconds to run all queries: 0.399 seconds
	Number of clients running queries: 50
	Average number of queries per client: 40

Benchmark
	Average number of seconds to run all queries: 0.388 seconds
	Minimum number of seconds to run all queries: 0.384 seconds
	Maximum number of seconds to run all queries: 0.397 seconds
	Number of clients running queries: 100
	Average number of queries per client: 20

Benchmark
	Average number of seconds to run all queries: 0.473 seconds
	Minimum number of seconds to run all queries: 0.464 seconds
	Maximum number of seconds to run all queries: 0.493 seconds
	Number of clients running queries: 400
	Average number of queries per client: 5


User time 1.13, System time 2.05
Maximum resident set size 17240, Integral resident set size 0
Non-physical pagefaults 60033, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 77541, Involuntary context switches 1
# 50,100,400个并发情况，分别测试，
每种情况测试5次， 
每种情况的查询总次数是200
所以并发小的，每个客户端发起的查询就多一些


[root@host2 ~]# mysqlslap -a -c 50,100,400 --number-of-queries 2000 --iterations=1 --debug-info -uroot
Benchmark
	Average number of seconds to run all queries: 0.400 seconds
	Minimum number of seconds to run all queries: 0.400 seconds
	Maximum number of seconds to run all queries: 0.400 seconds
	Number of clients running queries: 50
	Average number of queries per client: 40

Benchmark
	Average number of seconds to run all queries: 0.399 seconds
	Minimum number of seconds to run all queries: 0.399 seconds
	Maximum number of seconds to run all queries: 0.399 seconds
	Number of clients running queries: 100
	Average number of queries per client: 20

Benchmark
	Average number of seconds to run all queries: 0.487 seconds
	Minimum number of seconds to run all queries: 0.487 seconds
	Maximum number of seconds to run all queries: 0.487 seconds
	Number of clients running queries: 400
	Average number of queries per client: 5

# iterations表示的是每种并发场景下，测试几次，可以看到只测一次的情况下，平均，最大，最小都相同，因为只有测试了一次，
一般测试次数越多，求平均值，越能体现该并发场景下的真实性能；

mysql最佳实践思路

思路：

尽可能解放数据库，将计算逻辑向前转移到业务服务层，mysql的函数，存储过程可以实现的功能尽可能的转移到业务层，大并发情况下，通过业务层扩容可以较为容易实现水平扩展；数据库专注存储和索引逻辑

58到家数据库使用规范

https://zhuanlan.51cto.com/art/201702/531364.htm

  • 必须使用innodb引擎
  • 必须使用utf8编码
  • 不使用存储过程，函数，触发器，将这些计算移动到业务层
  • .........

my.cnf生产配置示例

硬件：内存32G

innodb_file_per_table=1
# 每表一个单独表文件

max_connections=8000
# 最大并发连接

back_log=300
# 内核级别监听队列排队队列长度

max_connect_erros=1000
#每个客户端允许的最大错误数，超过后，该客户端会被禁止连接，直到mysql重启，或flush hosts清空记录

open_files_limit=10240
# 所有线程打开的表总数量

max_allowed_packet=32m
# 每个连接传输数据大小

wait_timeout=10
# 指定一个请求的最大连接时间

sort_buffer_size=16m
# order by，group by时用到的缓存大小；

join_buffer_size=16m
#不带索引的全表扫描使用的buffer大小

query_cache_limit=4m
# 单个查询能使用的缓冲大小

query_cache_size=128m
# 查询缓存大小

transaction_isolation=repateable-read
# 事务隔离等级

thread_stack=512k
# 线程堆大小

log-bin
# 二进制日志文件前缀

binlog_format=row|mixed|statement
#binlog记录格式

innodb_buffer_pool_size=24G
#innodb使用的缓冲池保存数据和索引，可设置为物理内存的80%

innodb_file_io_threads = 4
#用来同步IO操作的IO线程的数量 

innodb_thread_concurrency = 16 
#在InnoDb核心内的允许线程数量，建议的设置是CPU数量加上磁盘数量的两倍
innodb_log_buffer_size = 16M 
# 用来缓冲日志数据的缓冲区的大小 
innodb_log_file_size = 512M
在日志组中每个日志文件的大小 
innodb_log_files_in_group = 3 
# 在日志组中的文件总数 
innodb_lock_wait_timeout = 120 
# SQL语句在被回滚前,InnoDB事务等待InnoDB行锁的时间 
long_query_time = 2 
#慢查询时长 
log-queries-not-using-indexes
#将没有使用索引的查询也记录下来
updatedupdated2020-10-232020-10-23

相关文章：

加载评论