一流的聊城网站建设,网络运维工程师的月薪有多少,wordpress分类目录标签转换器,品牌建设综述大家都知道#xff0c;slow query系统做的好不好#xff0c;直接决定了解决slow query的效率问题
一个数据库管理平台#xff0c;拥有一个好的slow query系统#xff0c;基本上就拥有了解锁性能问题的钥匙
但是今天主要分享的并不是平台#xff0c;而是在平台中看到的奇…大家都知道slow query系统做的好不好直接决定了解决slow query的效率问题
一个数据库管理平台拥有一个好的slow query系统基本上就拥有了解锁性能问题的钥匙
但是今天主要分享的并不是平台而是在平台中看到的奇葩指数五颗星的slow issue
好了关子卖完了直接进入正题
一、症状 一堆如下慢查询 # UserHost: cra[cra] [xx] Id: 3352884621
# Query_time: 0.183673 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0
use xx_db;
SET timestamp1549900927;
# administrator command: Prepare;# Time: 2019-02-12T00:02:07.51680308:00
# UserHost: cra[cra] [xx] Id: 3351119968
# Query_time: 0.294081 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0
SET timestamp1549900927;
# 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.93746208:00
# UserHost: root[root] localhost [] Id: 369
# Query_time: 0.000105 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0
SET timestamp1550124890;
prepare stmt1 from select * from xx_operation_log where id ?;
结论是 MySQL client 模拟出来的prepare 并不是我们期待的并没有得到我们想要的 administrator command: Prepare
perl 模拟prepare
#!/usr/bin/perluse DBI;my $dsn dbi:mysql:database${db_name};hostname${db_host};port${db_port};#数据源#获取数据库句柄
my $dbh DBI-connect(DBI:mysql:databasexx;hostxx, 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
$link mysqli_connect(xx, dba, xx, xx_db);/* check connection */
if (mysqli_connect_errno()) {printf(Connect failed: %s\n, mysqli_connect_error());exit();
}$city 1;/* create a prepared statement */
$stmt mysqli_stmt_init($link);if (mysqli_stmt_prepare($stmt, select * from xx_operation_log where id in (1,2,3)){/* bind parameters for markers *//* mysqli_stmt_bind_param($stmt, s, $city);/* execute query */mysqli_stmt_execute($stmt);/* bind result variables */mysqli_stmt_bind_result($stmt, $district);/* fetch value */mysqli_stmt_fetch($stmt);printf(%s is in district %s\n, $city, $district);/* close statement */mysqli_stmt_close($stmt);
}/* close connection */
mysqli_close($link);
?
php模拟得到的slow 结果
[rootxx 20190211]# cat xx-slow.log | grep administrator command: Prepare -B4 | grep UserHost | grep xx_rx | wc -l
7891[rootxx 20190211]# cat xx-slow.log | grep administrator command: Prepare -B4 | grep UserHost | wc -l
7908结论 通过php代码我们成功模拟出了想要的结果
那顺藤摸瓜抓取下这段时间有相同session id的整个sql执行过程
MySQL开启slow0的抓包模式
可以定位到同一个session id3415357118 的 prepare execute close stmt# UserHost: 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 timestamp1550017125;
# administrator command: Prepare;
# Time: 2019-02-13T08:18:45.62467108:00
--
# UserHost: 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 timestamp1550017125;
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.62613808:00
--
# UserHost: 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 timestamp1550017125;
# administrator command: Close stmt;
# Time: 2019-02-13T08:18:45.62643008: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 Server2. 服务器端prepare $dbh-setAttribute(PDO::ATTR_EMULATE_PREPARES,false);发送给MySQL Server
验证两种prepare模式
服务端prepare模式( ATTR_EMULATE_PREPARES false)
?php
$dbmsmysql; //数据库类型
$hostxxx; //数据库主机名
$dbNametest; //使用的数据库
$userxx; //数据库连接用户名
$pass123456; //对应的密码
$dsn$dbms:host$host;dbname$dbName;try {$pdo new PDO($dsn, $user, $pass); //初始化一个PDO对象$pdo-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() . br/);
}strace -s200 -f php mysql1.php 跟踪 大家可以看到这个模式下prepare的时候是将query占位符 发送给服务端的
本地prepare模式 (ATTR_EMULATE_PREPARES true )
?php
$dbmsmysql; //数据库类型
$hostxx; //数据库主机名
$dbNametest; //使用的数据库
$userxx; //数据库连接用户名
$pass123456; //对应的密码
$dsn$dbms:host$host;dbname$dbName;try {$pdo new PDO($dsn, $user, $pass); //初始化一个PDO对象$pdo-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() . br/);
}strace -s200 -f php mysql1.php 跟踪 大家可以看到这个模式下prepare的时候是不会将query发送给服务端的只有execute的时候才会发送
跟业务方确认后他们使用的是后者也就是修改了默认值他们原本是想提升数据库的性能因为预处理后只需要传参数就好了 但是对于我们的业务场景并不适合我们的场景是频繁打开关闭连接也就是预处理基本就用不到
另外文档上面也明确指出prepared statements 性能会不好 调整和验证
如何验证业务方是否将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注入的效果性能差不到哪里去2. 除非真的是有上述说的特定场景可以考虑配置成服务器prepare模式前提是要做好测试
原文链接 本文为云栖社区原创内容未经允许不得转载。
