mysql-slow-queries visualize

I received a file called mysqld-slow-queries.log from my web-hosting provider. I need to analyze this file, because there are probably some functions which cause a huge load on the server.

However, when I open the file with Notepad++ or similar I see 14000 lines with information, without any structure.

Is there a program where I can import the .log file and visualize the report? Basically I want to be able to sort on the Query_time value.

Answers


This is a noddy script I use to help me sort them out. Change the file name at the top and execute it. Puts the details out in CSV format so you can manipulate them easily (note, delete the 2nd line of the CSV file as it is garbage). Note that I cannot confirm whether or not the file you have is exactly the same format, but hopefully you can play with this to sort out what you need.

<?php
$handle = fopen('C:\\somelocation\\mysql-slow.log', "rb");
$fp = fopen('someoutputfile.csv', 'w');
$inline = '';
$inline = fgets($handle, 8192);
$OutLine = array();
$OutLine['Time'] = 'Time';
$OutLine['Timestamp'] = 'Timestamp';
$OutLine['User'] = 'User';
$OutLine['Query_time'] = 'Query_time';
$OutLine['Lock_time'] = 'Lock_time';
$OutLine['Rows_sent'] = 'Rows_sent';
$OutLine['Rows_examined'] = 'Rows_examined';
$OutLine['Database'] = 'Database';
$OutLine['SqlOut'] = 'SqlOut';
WriteOut($fp, $OutLine);
$OutLine = array();
$OutLine['Time'] = '';
$OutLine['Timestamp'] = '';
$OutLine['User'] = '';
$OutLine['Query_time'] = '';
$OutLine['Lock_time'] = '';
$OutLine['Rows_sent'] = '';
$OutLine['Rows_examined'] = '';
$OutLine['Database'] = '';
$OutLine['SqlOut'] = '';
$PossibleUse = true;
$TimeTriggeredOut = true;
$CurrentTime = '';
$CurrentDatabase = '';

while (!feof($handle)) 
{
    switch (true)
    {
        case substr($inline, 0, 8) == '# Time: ' :
            WriteOut($fp, $OutLine);
            $PossibleUse = true;
            $Timings = explode(': ', $inline);
            $CurrentTime = $Timings[1];
            $OutLine = array();
            $OutLine['Time'] = $CurrentTime;
            $OutLine['Timestamp'] = '';
            $OutLine['User'] = '';
            $OutLine['Query_time'] = '';
            $OutLine['Lock_time'] = '';
            $OutLine['Rows_sent'] = '';
            $OutLine['Rows_examined'] = '';
            $OutLine['Database'] = $CurrentDatabase;
            $OutLine['SqlOut'] = '';
            $TimeTriggeredOut = true;
            break;
        case substr($inline, 0, 6) == '# User' :
            if (!$TimeTriggeredOut)
            {
                WriteOut($fp, $OutLine);
                $PossibleUse = true;
                $OutLine = array();
                $OutLine['Time'] = $CurrentTime;
                $OutLine['Timestamp'] = '';
                $OutLine['User'] = '';
                $OutLine['Query_time'] = '';
                $OutLine['Lock_time'] = '';
                $OutLine['Rows_sent'] = '';
                $OutLine['Rows_examined'] = '';
                $OutLine['Database'] = $CurrentDatabase;
                $OutLine['SqlOut'] = '';
            }
            $OutLine['User'] = $inline;
            $TimeTriggeredOut = false;
            break;
        case substr($inline, 0, 12) == '# Query_time' :
            $Timings = explode(' ', $inline);
            //print_r($Timings);
            $OutLine['Query_time'] = $Timings[2];
            $OutLine['Lock_time'] = $Timings[5];
            $OutLine['Rows_sent'] = $Timings[7];
            $OutLine['Rows_examined'] = $Timings[10];
            $PossibleUse = true;
            break;
        case substr($inline, 0, 14) == 'SET timestamp=' :
            $Timings = explode('=', $inline);
            $OutLine['Timestamp'] = $Timings[1];
            $PossibleUse = true;
            break;
        case $PossibleUse AND substr($inline, 0, 4) == 'use ' :
            $Timings = explode(' ', $inline);
            $CurrentDatabase = $Timings[1];
            $OutLine['Database'] = $CurrentDatabase;
            $PossibleUse = false;
            break;
        default;
            $OutLine['SqlOut'] .= $inline;
    }
    $inline = fgets($handle, 8192);
}
fclose($fp);
fclose($handle);

function WriteOut($fp, $OutLine)
{
    foreach($OutLine as &$aOutLine)
    {
        $aOutLine = str_replace("\n", " ", $aOutLine);
        $aOutLine = str_replace("\r", " ", $aOutLine);
        $aOutLine = str_replace("\t", " ", $aOutLine);
    }
    fputcsv($fp, $OutLine);
}
?>

There are 2 problems any query analysis tool needs to address. The first (which Kickstart's solution doesn't appear to do) is to strip out parameters from the query - the same queries will be run lots of times but with different parameters. The second is to agregate the data and report on it.

MySQL comes with a tool which does both these - mysqldumpslow. Or you could use this standalone script.

The queries you should prioritize are the ones with the highest product of frequency and duration - whether the duration is the execution time or the lock time or a combination of both depends on how your database is currently behaving. (but as a rough rule of thumb, I would go with (frequency^1.5) * ((lock time ^ 1.5)+query time) for ISAM tables and a simple product for Innodb).

Obsessing about the specific queries which took longest to execute can give a very misleading picture when these are infrequent (although they can be a particular problem with engines which don't support concurrency).


There is also mysqlsla (mysql slow query log analyser)

mysqlsla parses, filters, analyzes and sorts MySQL slow, general, binary and microslow patched logs in order to create a customizable report of the queries and their meta-property values.


https://github.com/LeeKemp/mysql-slow-query-log-parser/

A ruby script that gives a clear output in a format like this:

################################################################################
1973 Queries
user: XXX[XXX]
url: localhost
ip: []
Taking 2 to 19354 seconds to complete
Locking for 0 to 210 seconds
Average time: 120, Median time 4
Average lock: 0, Median lock 0

SET timestamp=XXX;
SELECT tXXX.id AS idXXX...

################################################################################
2233 Queries
user: ...

Need Your Help

Checkboxes are responding slow in IE8

html internet-explorer twitter-bootstrap checkbox

I am using twitter bootstrap along with jquery for achieving RWD. The checkboxes are responding fine in chrome/firefox.But in IE8 it is taking couple of seconds for checkbox responding i.e., on cli...

How can get object from a list<> with a int index?

c# list

How can get object from a list&lt;> with a int index?

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.