MySQL运维实战 之 PHP访问MySQL你使用对了吗

Wesley13
• 阅读 549

大家都知道,slow query系统做的好不好,直接决定了解决slow query的效率问题

一个数据库管理平台,拥有一个好的slow query系统,基本上就拥有了解锁性能问题的钥匙

但是今天主要分享的并不是平台,而是在平台中看到的奇葩指数五颗星的slow issue

好了,关子卖完了,直接进入正题

一、症状

一堆如下慢查询

# User@Host: cra[cra] @  [xx]  Id: 3352884621
# Query_time: 0.183673  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
use xx_db;
SET timestamp=1549900927;
# administrator command: Prepare;

# Time: 2019-02-12T00:02:07.516803+08:00
# User@Host: cra[cra] @  [xx]  Id: 3351119968
# Query_time: 0.294081  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1549900927;
# administrator command: Prepare;

从我们的监控图上可以看到,每天不定时间段的slow query 总数在攀升,但是却看不到任何query 语句

这是我接触到的slow query优化案例中从来没有过的情况,比较好奇,也比较兴奋,至此决心要好好看看这个问题

二、排查

要解决这个问题,首先想到的是,如何复现这个问题,如何模拟复现这个症状

  • MySQL客户端 模拟prepare

    • 模拟 root:xx> prepare stmt1 from 'select * from xx_operation_log where id = ?'; Query OK, 0 rows affected (0.00 sec) Statement prepared

    • 结果

    Time: 2019-02-14T14:14:50.937462+08:00

    User@Host: root[root] @ localhost [] Id: 369

    Query_time: 0.000105 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0

    SET timestamp=1550124890; prepare stmt1 from 'select * from xx_operation_log where id = ?';

结论是: MySQL client 模拟出来的prepare 并不是我们期待的,并没有得到我们想要的 administrator command: Prepare

  • perl 模拟prepare

    #!/usr/bin/perl

    use DBI;

    my $dsn = "dbi:mysql:database=${db_name};hostname=${db_host};port=${db_port}";#数据源

    #获取数据库句柄 my $dbh = DBI->connect("DBI:mysql:database=xx;host=xx", "xx", "xx", {'RaiseError' => 1});

    my $sql = qq{select * from xx_operation_log where id in (?)};

    my $sth = $dbh->prepare($sql); $sth->bind_param (1, '100');

    sleep 3; $sth->execute();

结论是:跟MySQL客户端一样,同样是看不到administrator command: Prepare

  • php 模拟prepare

    1. 官方网址:

    https://dev.mysql.com/doc/apis-php/en/apis-php-mysqli-stmt.prepare.html

  • php模拟得到的slow 结果

    [root@xx 20190211]# cat xx-slow.log | grep 'administrator command: Prepare' -B4 | grep 'User@Host' | grep 'xx_rx' | wc -l 7891

    [root@xx 20190211]# cat xx-slow.log | grep 'administrator command: Prepare' -B4 | grep 'User@Host' | wc -l 7908

结论: 通过php代码,我们成功模拟出了想要的结果

那顺藤摸瓜,抓取下这段时间有相同session id的整个sql执行过程

MySQL开启slow=0的抓包模式

可以定位到同一个session id(3415357118) 的 prepare + execute + close stmt


# User@Host: xx_rx[xx_rx] @  [xx.xxx.xxx.132]  Id: 3415357118
# Query_time: 0.401453  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
use xx_db;
SET timestamp=1550017125;
# administrator command: Prepare;
# Time: 2019-02-13T08:18:45.624671+08:00
--
# User@Host: xx_rx[xx_rx] @  [xx.xxx.xxx.132]  Id: 3415357118
# Query_time: 0.001650  Lock_time: 0.000102 Rows_sent: 0  Rows_examined: 1
use xx_db;
SET timestamp=1550017125;
update `xx` set `updated_at` = '2019-02-13 08:18:45', `has_sales_office_phone` = 1, `has_presale_permit` = 1 where `id` = 28886;
# Time: 2019-02-13T08:18:45.626138+08:00
--
# User@Host: xx_rx[xx_rx] @  [xx.xxx.xxx.132]  Id: 3415357118
# Query_time: 0.000029  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 1
use xx_db;
SET timestamp=1550017125;
# administrator command: Close stmt;
# Time: 2019-02-13T08:18:45.626430+08:00

结论:我们发现,prepare时间的确很长,但是sql语句却执行的很快,这就很尴尬了

本来是想通过抓包,看看是否能够验证我们的猜想: prepare的语句非常大,或者条件非常复杂,从而导致prepare在服务器端很慢
结果发现query语句也都非常简单

那么既然如此,我们就找了业务方,将对应业务的prepare方法一起看看
结果发现,业务使用的是php-pdo的方式,所以我们就又有了如下发现

php-pdo 两种prepare模式

  • http://php.net/manual/zh/pdo.prepare.php

    1. 本地prepare $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES,true); 不会发送给MySQL Server

    2. 服务器端prepare $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES,false); 发送给MySQL Server

验证两种prepare模式

  • 服务端prepare模式( ATTR_EMULATE_PREPARES = false)

    setAttribute(PDO::ATTR_EMULATE_PREPARES,false); echo "----- prepare begin -----\n"; $stmt = $pdo->prepare("select * from test.chanpin where id = ?"); echo "----- prepare after -----\n"; $stmt->execute([333333]); echo "----- execute after -----\n"; $rs = $stmt->fetchAll(); } catch (PDOException $e) { die ("Error!: " . $e->getMessage() . "
    "); }

strace -s200 -f php mysql1.php 跟踪

MySQL运维实战 之 PHP访问MySQL你使用对了吗

大家可以看到这个模式下,prepare的时候,是将query+占位符 发送给服务端的

  • 本地prepare模式 (ATTR_EMULATE_PREPARES = true )

    setAttribute(PDO::ATTR_EMULATE_PREPARES,true); echo "----- prepare begin -----\n"; $stmt = $pdo->prepare("select * from test.chanpin where id = ?"); echo "----- prepare after -----\n"; $stmt->execute([333333]); echo "----- execute after -----\n"; $rs = $stmt->fetchAll(); } catch (PDOException $e) { die ("Error!: " . $e->getMessage() . "
    "); }

strace -s200 -f php mysql1.php 跟踪

MySQL运维实战 之 PHP访问MySQL你使用对了吗

大家可以看到这个模式下,prepare的时候,是不会将query发送给服务端的,只有execute的时候才会发送

跟业务方确认后,他们使用的是后者,也就是修改了默认值,他们原本是想提升数据库的性能,因为预处理后只需要传参数就好了
但是对于我们的业务场景并不适合,我们的场景是频繁打开关闭连接,也就是预处理基本就用不到

另外文档上面也明确指出prepared statements 性能会不好

MySQL运维实战 之 PHP访问MySQL你使用对了吗

调整和验证

如何验证业务方是否将prepare修改为local了呢?

dba:(none)> show global status like 'Com_stmt_prepare';
+------------------+-----------+
| Variable_name    | Value     |
+------------------+-----------+
| Com_stmt_prepare | 716836596 |
+------------------+-----------+
1 row in set (0.00 sec)

通过观察,发现这个值没有变化,说明调整已经生效

总结

  • prepare的优点

    1. 防止SQL注入
    2. 特定场景下提升性能 什么是特定场景: 就是先去服务端用占位符占位,后面可以直接发送请求来填空(参数值) 这样理论上来说, 你填空的次数非常多,性能才能发挥出来
  • prepare的缺点

    1. 在服务器端的prepare毕竟有消耗,当并发量大,频繁prepare的时候,就会有性能问题

    2. 服务端的prepare模式还会带来的另外一个问题就是,排错和slow 优化有困难,因为大部分情况下是看不到真实query的

    3. 尽量设置php-pdo为 $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,true) ,在本地prepare,不要给服务器造成额外压力

  • 建议

    1. 默认情况下,应该使用php-pdo的默认配置,采用本地prepare的方式,这样可以做到防SQL注入的效果,性能差不到哪里去

原文链接

点赞
收藏
评论区
推荐文章
blmius blmius
3年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
Jacquelyn38 Jacquelyn38
3年前
2020年前端实用代码段,为你的工作保驾护航
有空的时候,自己总结了几个代码段,在开发中也经常使用,谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
Easter79 Easter79
3年前
Twitter的分布式自增ID算法snowflake (Java版)
概述分布式系统中,有一些需要使用全局唯一ID的场景,这种时候为了防止ID冲突可以使用36位的UUID,但是UUID有一些缺点,首先他相对比较长,另外UUID一般是无序的。有些时候我们希望能使用一种简单一些的ID,并且希望ID能够按照时间有序生成。而twitter的snowflake解决了这种需求,最初Twitter把存储系统从MySQL迁移
Wesley13 Wesley13
3年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Wesley13 Wesley13
3年前
00:Java简单了解
浅谈Java之概述Java是SUN(StanfordUniversityNetwork),斯坦福大学网络公司)1995年推出的一门高级编程语言。Java是一种面向Internet的编程语言。随着Java技术在web方面的不断成熟,已经成为Web应用程序的首选开发语言。Java是简单易学,完全面向对象,安全可靠,与平台无关的编程语言。
Stella981 Stella981
3年前
Django中Admin中的一些参数配置
设置在列表中显示的字段,id为django模型默认的主键list_display('id','name','sex','profession','email','qq','phone','status','create_time')设置在列表可编辑字段list_editable
Wesley13 Wesley13
3年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
为什么mysql不推荐使用雪花ID作为主键
作者:毛辰飞背景在mysql中设计表的时候,mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一),而是推荐连续自增的主键id,官方的推荐是auto_increment,那么为什么不建议采用uuid,使用uuid究
Python进阶者 Python进阶者
10个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这