MySQL 运行的状态

重点关注以下参数

1
2
3
4

show status like 'Queries';
show status like 'Threads_connected';
show status like 'Threads_running';
show status like 'Connection_errors_max_connections';

MySQL 运行线程

1

show processlist

开启慢查询日志

一、参数查询

  1. slow_query_log 开启慢查询

    1
2
3
4
5
6
7
8

    mysql> show variables like '%slow_query_log%';
+---------------------+--------------------------------------+
| Variable_name       | Value                                |
+---------------------+--------------------------------------+
| slow_query_log      | ON                                   |
| slow_query_log_file | /var/lib/mysql/7709d56792f9-slow.log |
+---------------------+--------------------------------------+
2 rows in set (0.00 sec)

    1

    set global slow_query_log=1;

  2. slow_query_log_file 慢日志存放位置

    1

    set global slow_query_log_file='/data/logs/slow-mysql.log';

  3. long_query_time 表示1秒的SQL就记录

    1
2
3
4
5
6
7

    mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

     1
 2
 3
 4
 5
 6
 7
 8
 9
10

    mysql> set global long_query_time=1;
Query OK, 0 rows affected (0.00 sec)
   
mysql> show global variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

  4. log_output 输出方式

    开启慢查询日志后，还要设定log_output变量决定将慢查询日志写到表中还是文件中。log_output由三种值TABLE、FILE和NONE，对应着表、文件和啥都不写。

    1
2
3
4
5
6
7

    mysql> show variables like '%log_output%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
1 row in set (0.00 sec)

     1
 2
 3
 4
 5
 6
 7
 8
 9
10

    mysql> set global log_output='TABLE,FILE';
Query OK, 0 rows affected (0.00 sec)
   
mysql> show variables like '%log_output%';
+---------------+------------+
| Variable_name | Value      |
+---------------+------------+
| log_output    | FILE,TABLE |
+---------------+------------+
1 row in set (0.01 sec)

  5. log_queries_not_using_indexes 记录没有使用索引的SQL语句

    log_queries_not_using_indexes：动态可修改变量，默认值OFF。如果设为ON，那么不使用索引的SQL会被记录到慢查询日志中。开启后，慢查询日志可能会增长的很快，可以设定 log_throttle_queries_not_using_indexes 变量来限制，其默认值是0，也就是不限制。如果值大于0，如log_throttle_queries_not_using_indexes=100，则每秒钟记录100条不使用索引的SQL语句到慢查询日志。

    1
2
3
4
5
6
7

    mysql> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | ON    |
+-------------------------------+-------+
1 row in set (0.00 sec)

    1

    set global log_queries_not_using_indexes=1;

  6. slow_queries 查询多少条慢日志

    1
2
3
4
5
6
7

    mysql> show global status like '%Slow_queries%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 0     |
+---------------+-------+
1 row in set (0.03 sec)

  7. 查看所有的参数

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

    mysql> show variables like '%quer%';
+----------------------------------------+---------------------------+
| Variable_name                          | Value                     |
+----------------------------------------+---------------------------+
| binlog_rows_query_log_events           | OFF                       |
| ft_query_expansion_limit               | 20                        |
| have_query_cache                       | NO                        |
| log_queries_not_using_indexes          | ON                        |
| log_throttle_queries_not_using_indexes | 0                         |
| long_query_time                        | 1.000000                  |
| query_alloc_block_size                 | 8192                      |
| query_prealloc_size                    | 8192                      |
| slow_query_log                         | OFF                       |
| slow_query_log_file                    | /data/logs/slow-mysql.log |
+----------------------------------------+---------------------------+
10 rows in set (0.00 sec)

以上命令行下操作只是当前连接有效，其它无效。需要生效则需要写入配置文件my.cnf

二、修改配置文件

在配置文件 my.cnf 中的 [mysqld] 添加以下参数

1
2
3
4
5
6

[mysqld]
log_output=file
slow_query_log=1
slow_query_log_file = /data/logs/slow-mysql.log
log_queries_not_using_indexes=1
long_query_time = 1

三、配置完后重启MySQL

1
2
3
4

# 系统安装
systemctl restart mysqld
# 如果是 docker 
docker restart containerID

mysqldumpslow 分析慢日志

  1. -s：排序方式，后边接着如下参数 c：访问次数 l：锁定时间 r：返回记录 t：查询时间
  2. al：平均锁定时间
  3. ar：平均返回记录书
  4. at：平均查询时间
  5. -t：返回前面多少条的数据
  6. -g：翻遍搭配一个正则表达式，大小写不敏感

得到返回记录集最多的10个SQL。

1

mysqldumpslow -s r -t 10 /data/logs/slow-mysql.log

得到访问次数最多的10个SQL

1

mysqldumpslow -s c -t 10 /data/logs/slow-mysql.log

得到按照时间排序的前10条里面含有左连接的查询语句。

1

mysqldumpslow -s t -t 10 -g “left join” /data/logs/slow-mysql.log

另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现刷屏的情况。

1

mysqldumpslow -s r -t 20 /data/logs/slow-mysql.log | more

percona-toolkit 分析慢日志

  1. 下载

    1

    wget https://www.percona.com/downloads/percona-toolkit/3.2.1/binary/redhat/7/x86_64/percona-toolkit-3.2.1-1.el7.x86_64.rpm

  2. 依赖包

    1
2
3
4
5

    [root@xxx ~]# yum install perl-DBI.x86_64
[root@xxx ~]# yum install perl-DBD-MySQL.x86_64
[root@xxx ~]# yum install perl-IO-Socket-SSL.noarch
[root@xxx ~]# yum install perl-Digest-MD5.x86_64
[root@xxx ~]# yum install perl-TermReadKey.x86_64

  3. 安装

    1

    rpm -iv percona-toolkit-3.2.1-1.el7.x86_64.rpm

  4. 位置

    1

    whereis pt-query-digest

  5. 分析慢日志

    回看上面如何开启慢日志

    1

    pt-query-digest mysql-slow.log

mysqlslap 基准压测

参考说明：

  1. --concurrency 并发数,可指定多个值，以逗号
  2. --iterations 运行次数
  3. --auto-generate-sql 自动生成测试表和数据
  4. --auto-generate-sql-load-type 测试语句的类型,read，key，write，update和mixed(默认)。
  5. --auto-generate-sql-add-auto-increment 代表对生成的表自动添加auto_increment列，从5.1.18版本开始支持。
  6. --engine=engine_name 代表要测试的引擎，可以有多个，用分隔符隔开。例如：–engines=myisam,innodb。
  7. --number-of-queries 总的测试查询次数(并发客户数×每客户查询次数)
  8. --query 自己的SQL　　　　　　　　 脚本执行测试
  9. --only-print 　　　　　　　　　　 如果只想打印看看SQL语句是什么，可以用这个选项

运行命令

  1. 测试单SQL

    1

    mysqlslap -a -uroot -p123456

  2. 测试多并发

    1

    mysqlslap -a -c 100 -uroot -p123456 -h 127.0.0.1

  3. 模拟不同并发，并打印错误信息

    1
2

    # 执行一次测试，分别50和100个并发，执行1000次总查询：
mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --debug-info -uroot -p12345

  4. 更复杂的测试

    1
2

    mysqlslap -h 127.0.0.1 -u root -p123456 --concurrency=100 --iterations=1 --auto-generate-sql --auto-generate-sql-load-type=mixed --auto-generate-sql-add-autoincrement --engine=innodb --number-of-queries=5000
# -p密码 密码与p没有空格

  5. 查看SQL

    1

    mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --iterations=5 --debug-info -uroot -p123456 -h 127.0.0.1 --only-print

结果分析：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

Benchmark
	Average number of seconds to run all queries: 0.160 seconds  # 平均响应时间
	Minimum number of seconds to run all queries: 0.156 seconds	 # 最小响应时间
	Maximum number of seconds to run all queries: 0.166 seconds  # 最大响应时间
	Number of clients running queries: 50 	 # 模拟客户端数量
	Average number of queries per client: 20 # 每个客户端的平均查询SQL条数



User time 0.24, System time 0.37 # 用户响应时间，系统响应时间
Maximum resident set size 14568, Integral resident set size 0
Non-physical pagefaults 8068, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 19587, Involuntary context switches 723

参考

  1. https://juejin.im/post/59d83f1651882545eb54fc7e
  2. https://coolshell.cn/articles/1846.html
  3. MySQL慢查询日志总结