|
|
51CTO旗下网站
|
|
移动端

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

大家都知道,slow query系统做的好不好,直接决定了解决slow query的效率问题,一个数据库管理平台,拥有一个好的slow query系统,基本上就拥有了解锁性能问题的钥匙,但是今天主要分享的并不是平台,而是在平台中看到的奇葩指数五颗星的slow issue。

作者:阿里云云栖社区来源:今日头条|2019-03-08 16:30

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

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

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

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

一、症状 

  1. 一堆如下慢查询  
  1. # User@Host: cra[cra] @ [xx] Id: 3352884621  
  2. # Query_time: 0.183673 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0  
  3. use xx_db;  
  4. SET timestamp=1549900927 
  5. # administrator command: Prepare;  
  6. # Time: 2019-02-12T00:02:07.516803+08:00  
  7. # User@Host: cra[cra] @ [xx] Id: 3351119968  
  8. # Query_time: 0.294081 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0  
  9. SET timestamp=1549900927 
  10. # administrator command: Prepare; 

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

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

二、排查

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

  •  MySQL客户端 模拟prepare 
  1. * 模拟  
  2. root:xx> prepare stmt1 from 'select * from xx_operation_log where id = ?';  
  3. Query OK, 0 rows affected (0.00 sec)  
  4. Statement prepared  
  5. * 结果  
  6. # Time: 2019-02-14T14:14:50.937462+08:00  
  7. # User@Host: root[root] @ localhost [] Id: 369  
  8. # Query_time: 0.000105 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0  
  9. SET timestamp=1550124890 
  10. prepare stmt1 from 'select * from xx_operation_log where id = ?'; 

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

  •  perl 模拟prepare 
  1. #!/usr/bin/perl  
  2. use DBI;  
  3. my $dsn = "dbi:mysql:database=${db_name};hostname=${db_host};port=${db_port}";#数据源  
  4. #获取数据库句柄  
  5. my $dbh = DBI->connect("DBI:mysql:database=xx;host=xx", "xx", "xx", {'RaiseError' => 1});  
  6. my $sql = qq{select * from xx_operation_log where id in (?)};  
  7. my $sth = $dbh->prepare($sql);  
  8. $sth->bind_param (1, '100');  
  9. sleep 3;  
  10. $sth->execute(); 

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

  •  php 模拟prepare

php模拟得到的slow 结果:

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

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

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

MySQL开启slow=0的抓包模式

  1. 可以定位到同一个session id(3415357118) 的 prepare + execute + close stmt  
  2. # User@Host: xx_rx[xx_rx] @ [xx.xxx.xxx.132] Id: 3415357118  
  3. # Query_time: 0.401453 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0  
  4. use xx_db;  
  5. SET timestamp=1550017125 
  6. # administrator command: Prepare;  
  7. # Time: 2019-02-13T08:18:45.624671+08:00  
  8. --  
  9. # User@Host: xx_rx[xx_rx] @ [xx.xxx.xxx.132] Id: 3415357118  
  10. # Query_time: 0.001650 Lock_time: 0.000102 Rows_sent: 0 Rows_examined: 1  
  11. use xx_db;  
  12. SET timestamp=1550017125 
  13. update `xx` set `updated_at` = '2019-02-13 08:18:45', `has_sales_office_phone` = 1, `has_presale_permit` = 1 where `id` = 28886;  
  14. # Time: 2019-02-13T08:18:45.626138+08:00  
  15. --  
  16. # User@Host: xx_rx[xx_rx] @ [xx.xxx.xxx.132] Id: 3415357118  
  17. # Query_time: 0.000029 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 1  
  18. use xx_db;  
  19. SET timestamp=1550017125 
  20. # administrator command: Close stmt;  
  21. # 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) 
  1. <?php  
  2. $dbms='mysql'; //数据库类型  
  3. $host='xxx'; //数据库主机名  
  4. $dbName='test'; //使用的数据库  
  5. $user='xx'; //数据库连接用户名  
  6. $pass='123456'; //对应的密码  
  7. $dsn="$dbms:host=$host;dbname=$dbName" 
  8. try {  
  9.  $pdo = new PDO($dsn, $user, $pass); //初始化一个PDO对象  
  10.  $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);  
  11.  echo "----- prepare begin -----  
  12. ";  
  13.  $stmt = $pdo->prepare("select * from test.chanpin where id = ?");  
  14.  echo "----- prepare after -----  
  15. ";  
  16.  $stmt->execute([333333]);  
  17.  echo "----- execute after -----  
  18. ";  
  19.  $rs = $stmt->fetchAll();  
  20. } catch (PDOException $e) {  
  21.  die ("Error!: " . $e->getMessage() . "<br/>");  

strace -s200 -f php mysql1.php 跟踪

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

  •  本地prepare模式 (ATTR_EMULATE_PREPARES = true ) 
  1. <?php  
  2. $dbms='mysql'; //数据库类型  
  3. $host='xx'; //数据库主机名  
  4. $dbName='test'; //使用的数据库  
  5. $user='xx'; //数据库连接用户名  
  6. $pass='123456'; //对应的密码  
  7. $dsn="$dbms:host=$host;dbname=$dbName" 
  8. try {  
  9.  $pdo = new PDO($dsn, $user, $pass); //初始化一个PDO对象  
  10.  $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,true);  
  11.  echo "----- prepare begin -----  
  12. ";  
  13.  $stmt = $pdo->prepare("select * from test.chanpin where id = ?");  
  14.  echo "----- prepare after -----  
  15. ";  
  16.  $stmt->execute([333333]);  
  17.  echo "----- execute after -----  
  18. ";  
  19.  $rs = $stmt->fetchAll();  
  20. } catch (PDOException $e) {  
  21.  die ("Error!: " . $e->getMessage() . "<br/>");  

strace -s200 -f php mysql1.php 跟踪

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

跟业务方确认后,他们使用的是后者,也就是修改了默认值,他们原本是想提升数据库的性能,因为预处理后只需要传参数就好了。

但是对于我们的业务场景并不适合,我们的场景是频繁打开关闭连接,也就是预处理基本就用不到。

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

调整和验证

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

  1. dba:(none)> show global status like 'Com_stmt_prepare';  
  2. +------------------+-----------+  
  3. | Variable_name | Value |  
  4. +------------------+-----------+  
  5. | Com_stmt_prepare | 716836596 |  
  6. +------------------+-----------+  
  7. 1 row in set (0.00 sec) 

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

总结

  •  prepare的优点 
  1. 1. 防止SQL注入  
  2. 2. 特定场景下提升性能  
  3.  什么是特定场景: 就是先去服务端用占位符占位,后面可以直接发送请求来填空(参数值)  
  4.  这样理论上来说, 你填空的次数非常多,性能才能发挥出来 
  •  prepare的缺点 
  1. 1. 在服务器端的prepare毕竟有消耗,当并发量大,频繁prepare的时候,就会有性能问题  
  2. 2. 服务端的prepare模式还会带来的另外一个问题就是,排错和slow 优化有困难,因为大部分情况下是看不到真实query的  
  3. 3. 尽量设置php-pdo为 $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,true) ,在本地prepare,不要给服务器造成额外压力 
  •  建议 
  1. 1. 默认情况下,应该使用php-pdo的默认配置,采用本地prepare的方式,这样可以做到防SQL注入的效果,性能差不到哪里去  
  2. 2. 除非真的是有上述说的特定场景,可以考虑配置成服务器prepare模式,前提是要做好测试  

【编辑推荐】

  1. PingCAP CTO 黄东旭:我眼中的未来数据库技术趋势
  2. 3 月数据库排行:MySQL 指数持续大涨,PostgreSQL 下跌
  3. 推荐 | 超实用的MySQL数据库乱码问题的对应方式
  4. 数据库之分库分表-垂直?水平?
  5. 什么影响了数据库查询速度、什么影响了MySQL性能?
【责任编辑:庞桂玉 TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢

订阅专栏+澳门新濠天地平台

Redis运维秘籍

Redis运维秘籍

运维标配技术
共15章 | one叶孤舟

60人订阅学习

活学活用 Ubuntu Server

活学活用 Ubuntu Server

实战直通车
共35章 | UbuntuServer

235人订阅学习

Java EE速成指南

Java EE速成指南

掌握Java核心
共30章 | 51CTO王波

91人订阅学习

读 书 +澳门新濠天地平台

JavaScript核心技术

它从最简单的地方入手,不仅讲述了JavaScript的基础知识,还讲述了JavsScript如何操作CSS、DOM等Ajax基础技术。而关于跨浏览器兼容问题的解...

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊

51CTO服务号

51CTO播客

博聚网