mysql-log-analytics.js

var fs = require('fs')

var file = process.argv[2]
console.log(file)

var text = fs.readFileSync(__dirname + '/' + file, "utf8")

var lines = text.split("\n")

var selectStat = {}
var whereCount = {}

var nextInfo
lines.forEach(function(line) {
    var m = line.match(/# Query_time: ([\d.]+)  Lock_time: ([\d.]+) Rows_sent: (\d+)  Rows_examined: (\d+)/i)
    if (m) {
      nextInfo = {
        querytime: Number(m[1]),
        locktime:Number(m[2]),
        rows: Number(m[3]),
        exrows: Number(m[4])
      }
      return
    }
    m = line.match(/(select .* from .*?|insert .*?|update.*?)(where .*)?;$/i)
    if (m) {
      var select = m[1]
      var where = m[2]
      var stat = selectStat[select]
      if(!stat) {
        stat = selectStat[select] = {
          querytime: 0,
          locktime: 0,
          rows: 0,
          exrows: 0,
          count: 0
        }
      }
      stat.querytime += nextInfo.querytime
      stat.locktime += nextInfo.locktime
      stat.rows += nextInfo.rows
      stat.exrows += nextInfo.exrows
      stat.count += 1
      whereCount[where] = (whereCount[where] | 0) + 1
    }
})

var selectarr = []

for(var s in selectStat) {
  selectarr.push([s, selectStat[s]])
}

function sortField(fieldname) {
  return function(a, b) {
    if(a[1][fieldname] > b[1][fieldname]) {
      return -1
    }
    if(a[1][fieldname] < b[1][fieldname]) {
      return 1
    }
    return 0
  }
}

function logSelectArr(title, selectarr) {
  console.log(title)
  console.log('----------------------')
  for(var i in selectarr) {
    var item = selectarr[i]
    console.log("Total: querytime: %d locktime: %d rows: %d exrows: %d count: %d SQL: %s", item[1].querytime, item[1].locktime, item[1].rows, item[1].exrows, item[1].count, item[0])
  }
  console.log('----------------------')
}

selectarr.sort(sortField('querytime'))
logSelectArr("Sort by total querytime", selectarr)

selectarr.sort(sortField('locktime'))
logSelectArr("Sort by total locktime", selectarr)

selectarr.sort(sortField('count'))
logSelectArr("Sort by count", selectarr)