最近在转移网站到阿里云RDS数据库服务器的时候遇到一些负载瓶颈方面的问题,需要在调整MySQL参数、修改Drupal设置、添加索引等工作的时候,查看某些MySQL语句执行时间的变化,所以搞了一个很小的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查询操作,并输出所花费的时间。如果我们在进行了某项优化后,看到这个数值有了明显的变化,那就说明这次修改找到了问题所在。