Visualize mysql_slow_queries
Mysql의 슬로우쿼리를 그래프화 하기 위해서 만들어진 솔루션. 인터넷으로 찾아봐도 별 자료가 없어서 php와 Chart.js를 이용해서 XML로 뿌려서 중앙서버에서 운영하고 있는 각 서버의 Mysql현황을 파악하기 위해서 만들어진 것. 잘 모르는 것을 뒤져가며 그래프가 나타났을때 많이 기뻤다. 아래는 방법을 더 많은 사람들과 공유하기 위해 그냥 영어로 썼다. 문법적으로 틀린 부분이 있겠지만 이해하는데는 어려움이 없을 것이라 믿으면서 ^^;;
I recently got stress moments with four servers running on hostmonster.com with over 200 sites. The hosting company has been good to me, but they have a hidden policy to limit my account. Mostly they looks into mysql’s performance. So I needed to make kind of mysql_slow_query monitoring tool in my admin page. After searching for what others have done, http://code.google.com/p/mysql-slow-query-log-visualizer/ is suggested to start. But I want to be more simple to check slow queries ran for the last 24hours in order to help me to get a right log file to see in detail when its server is overloaded. The following captured image is its result. When the mouse is over the graph, it shows the total of query time on each hour.
Mysql_slow_queries folder on each server located under /tmp with log files formatting like ‘date-hour.log’ – 20150221-15.log, 20150222-18.log etc. Each file has following log.
# Mon Feb 23 13:01:03 2015 # Thread_id: 29258199 Schema: chfivchn_kosinusa Last_errno: 0 Killed: 0 # Query_time: 1.643232 Lock_time: 0.010832 Rows_sent: 505 Rows_examined: 1010 Rows_affected: 0 Rows_read: 1010 # Bytes_sent: 2078910 use chfivchn_kosinusa; SET timestamp=1424721663; SELECT * FROM `xe_trackbacks` as `trackbacks` WHERE ( `document_srl` = 12000 ) and `list_order` <= 2100000000 ORDER BY `list_order` asc# Mon Feb 23 13:01:03 2015 # Thread_id: 29258201 Schema: chfivchn_kosinusa Last_errno: 0 Killed: 0 # Query_time: 1.583801 Lock_time: 0.001654 Rows_sent: 370 Rows_examined: 740 Rows_affected: 0 Rows_read: 740 # Bytes_sent: 1612132 SET timestamp=1424721663; SELECT * FROM `xe_trackbacks` as `trackbacks` WHERE ( `document_srl` = 5544 ) and `list_order` <= 2100000000 ORDER BY `list_order` asc# Mon Feb 23 13:01:03 2015 # Thread_id: 29258183 Schema: chfivchn_kosinusa Last_errno: 0 Killed: 0 # Query_time: 1.587748 Lock_time: 0.001202 Rows_sent: 100 Rows_examined: 318 Rows_affected: 0 Rows_read: 318 # Bytes_sent: 1497946 SET timestamp=1424721663; SELECT * FROM `xe_documents` as `documents` WHERE ( `documents`.`module_srl` not in (0) and `documents`.`module_srl` in (306,1484,823,3654) ) and `list_order` <= 2100000000 ORDER BY `list_order` asc LIMIT 0, 100# Mon Feb 23 13:02:40 2015 # Thread_id: 29260463 Schema: chfivchn_kosinusa Last_errno: 0 Killed: 0 # Query_time: 1.657942 Lock_time: 0.002074 Rows_sent: 1 Rows_examined: 0 Rows_affected: 0 Rows_read: 0 # Bytes_sent: 60 use chfivchn_kosinusa; SET timestamp=1424721760; SELECT count(*) as `count` FROM `xe_counter_log` as `counter_log` WHERE `site_srl` = 0 and `ipaddress` = '201.81.200.61' and `regdate` like '20150223%'more and more
So, I coded a file named ‘_checkSlowQueries.php‘ in order to make a XML file.
<?
header("Content-type: text/xml");
header("Cache-Control: no-cache, must-revalidate");
header("Pragma: no-cache");
$source = "../../../tmp/mysql_slow_queries"; // based on your file location
$dirHandle=opendir($source);
$files = array();
while($file=readdir($dirHandle)) {
if($file!="." && $file!="..") {
$files[fusion_builder_container hundred_percent="yes" overflow="visible"][fusion_builder_row][fusion_builder_column type="1_1" background_position="left top" background_color="" border_size="" border_color="" border_style="solid" spacing="yes" background_image="" background_repeat="no-repeat" padding="" margin_top="0px" margin_bottom="0px" class="" id="" animation_type="" animation_speed="0.3" animation_direction="left" hide_on_mobile="no" center_content="no" min_height="none"][] = $file;
}
}
closedir($dirHandle);
usort($files,"cmp");
$logFileData = array();
foreach ($files as $key => $value) {
$logdate ="";
$querytime=0;
$lockTime =0;
$myfile = file_get_contents($source.'/'.$value);
$eachData = explode("SET timestamp",$myfile);
for($i=0;$i<count($eachData)-1;$i++) {
$getData = explode("#",$eachData[$i]); // ** important
$logdate = substr($getData[1],1,13);
$tmpTime = explode(" ",$getData[3]);
$querytime +=$tmpTime[2];
$lockTime += $tmpTime[5];
}
$logFileData[$key][filename] = $value;
$logFileData[$key][logdate] = $logdate;
$logFileData[$key][month] = substr($logdate,4,3);
$logFileData[$key][week] = substr($logdate,0,3);
$logFileData[$key][day] = substr($logdate,8,2);
$logFileData[$key][hour] = substr($logdate,11,2);
$logFileData[$key][querytime] = $querytime;
$logFileData[$key][lockTime] = (double)$lockTime;
}
foreach($logFileData as $key => $value){
foreach($value as $item => $val){
$strXml .=" <{$item}>".$val."</{$item}>\n";
}
}
$strXmlTop = "<?xml version=\"1.0\" encoding=\"utf-8\"?>\n";
$strXmlTop .= "<server name='MYSQL_SLOW_QUERY'>\n";
$strXmlTop .= $strXml;
$strXmlTop .="</server>\n";
echo $strXmlTop;
function cmp($a, $b) {
if ($a == $b) {
return 0;
}
return ($a < $b) ? -1 : 1;
}
?>
It creates a XML file like:
<?xml version="1.0" encoding="UTF-8"?> <server name="MYSQL_SLOW_QUERY"> <filename>20150223-22.log</filename> <logdate>Mon Feb 23 22</logdate> <month>Feb</month> <week>Mon</week> <day>23</day> <hour>22</hour> <querytime>5.218595</querytime> <lockTime>0.000159</lockTime> <filename>20150223-23.log</filename> <logdate>Mon Feb 23 23</logdate> <month>Feb</month> <week>Mon</week> <day>23</day> <hour>23</hour> <querytime>6.912773</querytime> <lockTime>0.000225 - more and more </server>
This XML file is called from my admin page. $labelData and $numData are for the data to create a graph which is supported by Chart.js
<?
$xml = simplexml_load_file("http://yourhosting.com/_checkSlowQueries.php");
$logCount = count($xml->filename);
for($i = $logCount-24; $i < $logCount; $i++) {
$y[]= str_replace(" ","",$xml->hour[$i]);
$x[]= (int)$xml->querytime[$i];
}
$labelData = "'".implode("','",$y)."'";
$numData = implode(",",$x2);
?>
<script type="text/javascript" src="js/Chart.js"></script>
<div class='mysql'>
<h3>HS2 MYSQL</h3>
<canvas id="canvas"></canvas>
</div>
<script>
var data = {
labels: [<?=$labelData?>],
datasets: [
{
label: "My First dataset",
fillColor: "rgba(220,220,220,0.2)",
strokeColor: "rgba(220,220,220,1)",
pointColor: "rgba(220,220,220,1)",
pointStrokeColor: "#fff",
pointHighlightFill: "#fff",
pointHighlightStroke: "rgba(220,220,220,1)",
data: [<?=$numData?>]
}
]
};
window.onload = function(){
var ctx = document.getElementById("canvas").getContext("2d");
var myLineChart = new Chart(ctx).Line(data, {
responsive : true,
scaleShowLabels: false,
tooltipTemplate:"<%= value %>"
});
}
</script>
[/fusion_builder_column][/fusion_builder_row][/fusion_builder_container]
