MySQL系列之十五 MySQL常用配置和性能压力测试

今天给大家带来一篇教程关于MySQL常用配置和性能压力测试的知识,文中配置参数以32内存的服务器为例,通过实例代码给大家介绍的非常详细,需要的朋友参考下吧

一、MySQL常用配置

以下所有配置参数以32G内存的服务器为基

1、打开独立的表空间

innodb_file_per_table = 1

2、MySQL服务所允许的同时会话数的上限,默认为151,经常出现Too Many Connections的错误提示,则需要增大此值

max_connections = 8000

3、操作系统在监听队列中所能保持的连接数

back_log = 300

4、每个客户端连接最大的错误允许数量,当超过该次数,MYSQL服务器将禁止此主机的连接请求,直到MYSQL服务器重启或通过flush hosts命令清空此主机的相关信息

max_connect_errors = 1000

5、所有线程所打开表的数量

open_files_limit = 10240

6、每个连接传输数据大小,最大1G,须是1024的倍数,一般设为最大的BLOB的值

max_allowed_packet = 32M

7、请求的最大连接时间

wait_timeout = 10

8、排序缓冲被用来处理类似ORDER BY以及GROUP BY队列所引起的排序

sort_buffer_size = 16M

9、不带索引的全表扫描,使用的buffer的最小值

join_buffer_size = 16M

10、查询缓冲大小

query_cache_size = 128M

11、指定单个查询能够使用的缓冲区大小,默认为1M

query_cache_limit = 4M

12、设定默认的事务隔离级别

transaction_isolation = REPEATABLE-READ

13、 线程使用的堆大小,此值限制内存中能处理的存储过程的递归深度和SQL语句复杂性,此容量的内存在每次连接时被预留

thread_stack = 512K

14、开启二进制日志功能

log_bin

15、二进制日志格式:基于行

binlog_format = row

16、InnoDB使用一个缓冲池来保存索引和原始数据, 可设置这个变量到服务器物理内存大小的80%

innodb_buffer_pool_size = 6G

17、用来同步IO操作的IO线程的数量

innodb_file_io_threads = 4

18、在Innodb核心内的允许线程数量,建议的设置是CPU数量加上磁盘数量的两倍

innodb_thread_concurrency = 16

19、用来缓冲日志数据的缓冲区的大小

innodb_log_buffer_size = 16M

20、在日志组中每个日志文件的大小

innodb_log_file_size = 512M

21、在日志组中的文件总数

innodb_log_files_in_group = 3

22、SQL语句在被回滚前,InnoDB事务等待InnoDB行锁的时间

innodb_lock_wait_timeout = 120

23、慢查询记录的阈值时长,默认10秒

long_query_time = 2

24、将没有使用索引的查询也记录下来

log-queries-not-using-indexes

my.cnf示例:

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

symbolic-links=0

innodb_file_per_table = 1

innodb_buffer_pool_size = 6442450944 #内存不足6G会报错

innodb_file_io_threads = 4

innodb_thread_concurrency = 16

innodb_log_buffer_size = 16M

innodb_log_file_size = 512M

innodb_log_files_in_group = 3

innodb_lock_wait_timeout = 120

log_bin = /var/lib/mysql/mariadb-bin

binlog_format = row

slow_query_log

long_query_time = 2

log-queries-not-using-indexes

transaction_isolation = REPEATABLE-READ

query_cache_size = 128M

query_cache_limit = 4M

max_connections = 8000

back_log = 300

max_connect_errors = 1000

open_files_limit = 10240

max_allowed_packet = 32M

wait_timeout = 10

sort_buffer_size = 16M

join_buffer_size = 16M

thread_stack = 512K

二、MySQL的性能压力测试

常见测试工具:

  • mysqlslap
  • Sysbench
  • tpcc-mysql
  • MySQL Benchmark Suite
  • MySQL super-smack
  • MyBench

mysqlslap工具介绍

​mysqlslap来自于mariadb包,测试的过程默认生成一个mysqlslap的schema,生成测试表t1,查询和插入测试数据,mysqlslap库自动生成,如果已经存在则先删除。用–only-print来打印实际的测试过程,整个测试完成后不会在数据库中留下痕迹。

常用选项:

  • –auto-generate-sql, -a 自动生成测试表和数据,表示用mysqlslap工具自己生成的SQL脚本来测试并发压力
  • –auto-generate-sql-load-type=type 测试语句的类型。代表要测试的环境是读操作还是写操作还是两者混合的。取值包括:read,key,write,update和mixed(默认)
  • –auto-generate-sql-add-auto-increment 代表对生成的表自动添加auto_increment列,从5.1.18版本开始支持
  • –number-char-cols=N, -x N 自动生成的测试表中包含多少个字符类型的列,默认1
  • –number-int-cols=N, -y N 自动生成的测试表中包含多少个数字类型的列,默认1
  • –number-of-queries=N 总的测试查询次数(并发客户数×每客户查询次数)
  • –query=name,-q 使用自定义脚本执行测试,例如可以调用自定义的存储过程或者sql语句来执行测试
  • –create-schema 代表自定义的测试库名称,测试的schema,MySQL中schema也就是database
  • –commint=N 多少条DML后提交一次
  • –compress, -C 如服务器和客户端都支持压缩,则压缩信息
  • –concurrency=N, -c N 表示并发量,即模拟多少个客户端同时执行select;可指定多个值,以逗号或者–delimiter参数指定值做为分隔符
  • –engine=engine_name, -e engine_name 代表要测试的引擎,可以有多个,用分隔符隔开
  • –iterations=N, -i N 测试执行的迭代次数,代表要在不同并发环境下,各自运行测试多少次
  • –only-print 只打印测试语句而不实际执行
  • –detach=N 执行N条语句后断开重连
  • –debug-info, -T 打印内存和CPU的相关信息

测试示例:

1)单线程测试

?

1

2

3

4

5

6

7

8

[root@centos7 ~]# mysqlslap -a -uroot -p

Enter password:

Benchmark

Average number of seconds to run all queries: 0.004 seconds

Minimum number of seconds to run all queries: 0.004 seconds

Maximum number of seconds to run all queries: 0.004 seconds

Number of clients running queries: 1

Average number of queries per client: 0

2)多线程测试,使用–concurrency来模拟并发连接

?

1

2

3

4

5

6

7

8

[root@centos7 ~]# mysqlslap -uroot -p -a -c 500

Enter password:

Benchmark

Average number of seconds to run all queries: 3.384 seconds

Minimum number of seconds to run all queries: 3.384 seconds

Maximum number of seconds to run all queries: 3.384 seconds

Number of clients running queries: 500

Average number of queries per client: 0

3)同时测试不同的存储引擎的性能进行对比

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

[root@centos7 ~]# mysqlslap -uroot -p -a –concurrency=500 –number-of-queries 1000 –iterations=5 –engine=myisam,innodb –debug-info

Enter password:

Benchmark

Running for engine myisam

Average number of seconds to run all queries: 0.192 seconds

Minimum number of seconds to run all queries: 0.187 seconds

Maximum number of seconds to run all queries: 0.202 seconds

Number of clients running queries: 500

Average number of queries per client: 2

Benchmark

Running for engine innodb

Average number of seconds to run all queries: 0.355 seconds

Minimum number of seconds to run all queries: 0.350 seconds

Maximum number of seconds to run all queries: 0.364 seconds

Number of clients running queries: 500

Average number of queries per client: 2

User time 0.33, System time 0.58

Maximum resident set size 22892, Integral resident set size 0

Non-physical pagefaults 46012, Physical pagefaults 0, Swaps 0

Blocks in 0 out 0, Messages in 0 out 0, Signals 0

Voluntary context switches 31896, Involuntary context switches 0

4)执行一次测试,分别500和1000个并发,执行5000次总查询

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

[root@centos7 ~]# mysqlslap -uroot -p -a –concurrency=500,1000 –number-of-queries 5000 –debug-info

Enter password:

Benchmark

Average number of seconds to run all queries: 3.378 seconds

Minimum number of seconds to run all queries: 3.378 seconds

Maximum number of seconds to run all queries: 3.378 seconds

Number of clients running queries: 500

Average number of queries per client: 10

Benchmark

Average number of seconds to run all queries: 3.101 seconds

Minimum number of seconds to run all queries: 3.101 seconds

Maximum number of seconds to run all queries: 3.101 seconds

Number of clients running queries: 1000

Average number of queries per client: 5

User time 0.84, System time 0.64

Maximum resident set size 83068, Integral resident set size 0

Non-physical pagefaults 139977, Physical pagefaults 0, Swaps 0

Blocks in 0 out 0, Messages in 0 out 0, Signals 0

Voluntary context switches 31524, Involuntary context switches 3

5)迭代测试

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

[root@centos7 ~]# mysqlslap -uroot -p -a –concurrency=500 –number-of-queries 5000 –iterations=5 –debug-info

Enter password:

Benchmark

Average number of seconds to run all queries: 3.307 seconds

Minimum number of seconds to run all queries: 3.184 seconds

Maximum number of seconds to run all queries: 3.421 seconds

Number of clients running queries: 500

Average number of queries per client: 10

User time 2.18, System time 1.58

Maximum resident set size 74872, Integral resident set size 0

Non-physical pagefaults 327732, Physical pagefaults 0, Swaps 0

Blocks in 0 out 0, Messages in 0 out 0, Signals 0

Voluntary context switches 73904, Involuntary context switches 3

以上就是MySQL系列之十五 MySQL常用配置和性能压力测试的详细内容,更多关于MySQL常用配置和性能压力测试的资料请关注钦钦技术栈其它相关文章!

原文链接:https://www.cnblogs.com/L-dongf/p/9192724.html

版权声明:本文(即:原文链接:https://www.qin1qin.com/catagory/6599/)内容由互联网用户自发投稿贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 630367839@qq.com 举报,一经查实,本站将立刻删除。

(0)
上一篇 2022-07-29 10:17:32
下一篇 2022-07-29 10:17:46

软件定制开发公司

相关阅读

发表回复

登录后才能评论
通知:禁止投稿所有关于虚拟货币,币圈类相关文章,发现立即永久封锁账户ID!