最近在转移网站到阿里云RDS数据库服务器的时候遇到一些负载瓶颈方面的问题,需要在调整MySQL参数、修改Drupal设置、添加索引等工作的时候,查看某些MySQL语句执行时间的变化,所以搞了一个很小的PHP程序来循环检测:
<?php ini_set('display_errors', 'On'); error_reporting(E_ALL); $serverName = 'xxx.mysql.rds.aliyuncs.com'; $userName = 'un'; $password = 'pw'; $databaseName = 'db'; $link = mysql_connect("$serverName","$userName","$password") or die("unable to connect to msql server: " . msql_error()); mysql_select_db("$databaseName", $link) or die("unable to select database 'db': " . msql_error()); $sql = " SELECT s.source, s.context, t.translation, t.language FROM locales_source s LEFT JOIN locales_target t ON s.lid = t.lid AND t.language = 'zh-hans' WHERE s.textgroup = 'default' AND s.version = '7.38' AND LENGTH(s.source) < '75'; "; //$sql = "show tables;"; FOR ($i = 0; $i <= 10; $i++) { $time_start = microtime(); $result = mysql_query("$sql"); $time_end = microtime(); $time_long = ($time_end - $time_start)*1000; if (!$result) { die('query failed: '); } print_r($result); //$row = mysql_fetch_array($result); //print_r ($row); //while ($row = mysql_fetch_array($result)){ //print_r($row); //} print "time_long = $time_long ms\n"; sleep(1); } ?>
在这个程序中设置了1秒为循环,每隔一秒进行一次MySQL查询操作,并输出所花费的时间。如果我们在进行了某项优化后,看到这个数值有了明显的变化,那就说明这次修改找到了问题所在。
评论