PHP - Filter slow mysql in mysql log file

Hello,

This is the PHP script figure out which query make your system go down:

• <?php 
  error_reporting(E_ALL);
  ini_set('error_reporting', E_ALL);
  ini_set('display_errors',1);
  
  $file      = file_get_contents('mysql-slow.log');
  $limitShow = 10;
  
  $explodes  = explode("Query_time: ", $file);
  
  $selects = array();
  $updates = array();
  $deletes = array();
  $inserts = array();
  
  foreach ($explodes as $k => $v) {
      if ($k > 0) {
          if (isQueryCategory($v, 'SELECT')) {
              $selects = updateArray($selects, $v, 'SELECT', 'WHERE');
          } elseif (isQueryCategory($v, 'UPDATE')) {
              $updates = updateArray($updates, $v, 'UPDATE', 'SET');
          } elseif (isQueryCategory($v, 'DELETE')) {
              $deletes = updateArray($deletes, $v, 'DELETE', 'WHERE');
          } else {
              $inserts = updateArray($inserts, $v, 'INSERT', ';');
          }
      }
  }
  
  function isQueryCategory($string, $category) {
      $string = substr($string, 0, 150);
  
      if (stripos($string, $category) > 0) {
          return true;

      } else {
          return false;

      }
  }
  
  function updateArray($arrays, $string, $keyFirst, $keyLast) {
      $posFirst = stripos($string, $keyFirst);
      $posLast  = stripos($string, $keyLast, $posFirst);
      if (! $posLast) $posLast = stripos($string, ";");
  
      $query    = substr($string, $posFirst, $posLast - $posFirst);
      $key      = trim(str_replace(" ", "", str_replace("\t", "", str_replace("\n", "", $query))));
      $time     = substr($string, 0, stripos($string, " "));
  
      if (! isset($arrays[$key])) $arrays[$key] = array('query' => $query, 'count' => 0, 'time' => 0);
      $arrays[$key]['count'] ++;
      $arrays[$key]['time'] += $time;
   
      return $arrays;
  }
  
  
  function sortArray($arrayIn = array(), $index = null) {
      $arrTemp = array();
      $arrayOut = array();
  

      foreach ($arrayIn as $key => $value) {
          reset($value);
          $arrTemp[$key] = is_null($index)
                           ? current($value)
                           : $value[$index];
      }
  
      natsort($arrTemp);
  
      foreach ( $arrTemp as $key=>$value ) {
          $arrayOut[$key] = $arrayIn[$key];
      }
  
      return $arrayOut;
  }
  
  
  function showReport($name, $arrays) {
      echo "<h1>" . $name . "</h1>";
      echo "<table border='1'>";
      echo "<tr>";
      echo "<th>Query</th>";
      echo "<th>Count</th>";
      echo "<th>Total Time</th>";
      echo "<th>Avg Time</th>";
      echo "<tr>";
  
      foreach ($arrays as $k => $v) {
          echo "<tr>";

          echo "<td>" . $v['query'] . "</td>";

          echo "<td>" . $v['count'] . "</td>";

          echo "<td>" . $v['time'] . "</td>";

          echo "<td>" . $v['time'] / $v['count'] . "</td>";

          echo "<tr>";

      }
      echo "<table>";
  }
  
  //print_r($selects);
  $selects = sortArray($selects, 'count');
  $updates = sortArray($updates, 'count');
  $deletes = sortArray($deletes, 'count');
  $inserts = sortArray($inserts, 'count');
  
  $selects = array_slice($selects, -$limitShow, $limitShow, true);
  $updates = array_slice($updates, -$limitShow, $limitShow, true);
  $deletes = array_slice($deletes, -$limitShow, $limitShow, true);
  $inserts = array_slice($inserts, -$limitShow, $limitShow, true);
  //print_r($selects);exit;
  
  
  showReport("SELECT", $selects);
  showReport("UPDATE", $updates);
  showReport("DELETE", $deletes);
  showReport("INSERT", $inserts);

Good luck!