Mysql优化技巧  慢日志分析-张柏沛IT博客

正文内容

Mysql优化技巧 慢日志分析

栏目:MySQL 发布时间:2020-01-02 17:43 浏览量:180

如果开启了mysql的慢日志,那么该日志会记录下所有mysql认为效率低的sql语句,我们可以通过查看慢日志获取这些语句并进行优化

MySQL的慢查询日志比较粗略,主要是基于以下3项基本的信息。 
Query_time:查询耗时。 

Rows_examined:检查了多少条记录。 

Rows_sent:返回了多少行记录(结果集)。 以上3个值可以大致衡量一条查询的成本。 如果检查了大量记录,而只返回很小的结果集,则往往意味着查询质量不佳。

其他信息包括如下几点。 
Time:执行SQL的开始时间。 
Lock_time:等待tablelock的时间,注意InnoDB的行锁等待是不会反应在这里的。 
User@Host:执行查询的用户和客户端IP。

可以使用mysqldumpslow命令获得慢查询 日志摘要来处理慢查询日志,或者使用更好的第三方工具pt-query-digest。,慢查询日志里的慢查询不一定就是不良SQL,还可能是受其他的查询影响,或者受系统资源限制所导致的慢查询。 比如下面的例子,会话被阻塞了,实际上是一个行锁等待50s超时,然后记录到了慢查询日志里。


A 查看是否开启慢日志:

MariaDB [(none)]> show variables like "%query_log%";

+---------------------+-------------------------+
| Variable_name       | Value                   |
+---------------------+-------------------------+
| slow_query_log      | OFF                     |
| slow_query_log_file | VM_0_13_centos-slow.log |       #   这个是慢日志保存的路径
+---------------------+-------------------------+

发现没有打开

如果配置文件或启动参数没有给出file_name值,慢查询日志将默认命名为“主机名-slow.log”,如果给出了文件名,但不是绝对路径名,文件则写入数据目录。

像这里,慢日志就是写入数据目录

我们可以在MySQL客户端下使用命令

SHOW VARIABLES LIKE '%query_time%'

查看全局变量long_query_time。所有执行时间超过long_query_time秒的SQL语句都会被记录到慢查询日志里。

建议将这个时间设为1秒

如何开启慢日志并设置保存路径和超时时间:
在配置文件的 [mysqld] 下添加下面几句:

slow_query_log = 1
slow_query_log_file = /tmp/mysql-slow.log
long_query_time = 1

即可

可能不是直接在my.cnf 中添加,而是在其引入的其他配置文件中才能找到[mysqld]

然后重启一下mysql服务


B 分析慢查询日志

如果慢查询日志内容太多,看文件是很难看出东西的,可以使用mysqldumpslow或者pt-query-digest工具
    
mysqldumpslow:

mysqldumpslow -t10 绝对路径    # 获取访问时长最长的10条语句,并存到指定路径中
mysqldumpslow -s c -t10 绝对路径  # 访问次数最多的10条语句
mysqldumpslow -s r -t10 绝对路径  # 访问条数最多的10条


pt-query-digest:

首先安装pt-query_digest命令

wget www.percona.com/get/pt-query-digest chmod u+x pt-query-digest    # 下载pt-query-digest命令
chmod u+x pt-query-digest

基本命令格式
pt-query-digest [options] [files] [dsn]

常见用法:假设慢日志路径为 /tmp/mysql-slow.log

pt-query-digest /tmp/mysql-slow.log > slow.rtf      #直接分析慢查询
pt-query-digest --since 1800s /tmp/mysql-slow > slow.rtf   #分析半小时内的慢查询
pt-query-digest --since '2020-01-01 00:00:00' --until '2020-01-02 00:00:00' /tmp/mysql-slow > slow.rtf    #分析指定范围内时间的慢查询
pt-query-digest --limit 95%:20 /tmp/mysql-slow > slow.rtf    #显示95%的最差查询或者20个最差查询

相应参数解释: 
Exectime:执行时间。 
Lock time:表锁的时间。 
Rows sent:返回的结果集记录数。 
Rowsexamine:实际扫描的记录数。 
Query size:应用和数据库交互的查询文本大小。 
Rank:所有查询日志分析完毕后,此查询的排序。 
Query ID:查询的标识字符串。 
Responsetime:总的响应时间,以及总占比。一般小于5%可以不用关注。 
Calls:查询被调用执行的次数。 
R/Call:每次执行的平均响应时间。 
Apdx:应用程序的性能指数得分。(Apdex响应的时间越长,得分越低。) 
V/M:响应时间的方差均值比(变异数对平均数比,变异系数)。可说明样本的分散程度,这个值越大,往往是越值得考 虑优化的对象。 
Item:查询的简单显示,包括查询的类型和所涉及的表。

当然该命令也可以用于分析mysql二进制日志文件,注意,二进制日志不是mysql服务的日志,二进制日志一般放在数据目录,可以通过show master status查看是否开启二进制日志

mysqlbinlog mysql-bin.012639 > /tmp/012639.log   # 将二进制文件转为文本格式
pt-query-digest --type binlog /tmp/012639.log > binlog.rtf

对于以上分析命令,同样可以加上参数筛选信息,如“--since”、“--until”。

代码段 小部件

如果您需要转载,可以点击下方按钮可以进行复制粘贴;本站博客文章为原创,请转载时注明以下信息

张柏沛IT技术博客 > Mysql优化技巧 慢日志分析

热门推荐
推荐新闻