RDS(MySQL) log analyzer using pt-query-digest and https://github.com/wvanbergen/request-log-analyzer

ptquery_mail_qiita.rb

#!/usr/bin/env ruby

require "qiita"
require "slack-notifier"
require "aws-sdk"
require 'yaml'
require 'json'


class RDSLogDownloader
  SLOWLOG = "slowquery/mysql-slowquery.log"
  @logs = {}
  @hours = 0..23
  @localtime = 0

  def initialize(conf)
    @client  = Aws::RDS::Client.new(
      region:  conf['region'],
      access_key_id: conf['access_key_id'],
      secret_access_key: conf['secret_access_key'],
    )
  end

  def fetchMany(instances)
    logs = {}
    instances.each do |instance|
      logs[instance] = []
    end
    threads = []
    instances.each do |instance|
      threads << Thread.new do
        logs[instance] = []
        @hours.each do |h|
          logs[instance] << fetchSlowLog(instance, h)
        end
      end
    end
    threads.each { |t| t.join }
    logs
  end

  def fetchSlowLog(instance, num)
    isEnd = false
    marker = '0:0'
    data = []
    file = SLOWLOG + ".#{num}"
    while !isEnd do
      log = downloadLog(instance, file, marker)
      data << log.log_file_data if !log.log_file_data.nil?
      isEnd = !log.additional_data_pending
      marker = log.marker
    end
    data
  end

  def downloadLog(instance, file, marker)
      puts "#{instance}:#{file} downloading..."
      @client.download_db_log_file_portion({
        :db_instance_identifier => instance,
        :log_file_name => file,
        :marker => marker,
      })
  end

  def setHours(from, to)
    from -= @localtime
    to -= @localtime
    if from > to
      to += 24
    end
    @hours = from..to
  end

  def setLocalTime(lt)
    @localtime = lt
  end

  def getRDSHour(hour)
    if hour > 23 then
      return hour - 24
    elsif hour < 0 then
      return hour + 24
    end
    hour
  end
end

class PTQuery
  TEMP = "/tmp/.pt-query.log"
  def self.DigestSummary(data)
    File.open(TEMP, 'w') do |f|
      f.write data.join("\n")
    end
    return `pt-query-digest --report-format=rusage,hostname,files,header,profile #{TEMP}`
  end

  def self.DigestDetail(data)
    File.open(TEMP, 'w') do |f|
      f.write data.join("\n")
    end
    return `pt-query-digest #{TEMP}`
  end

end

class QiitaPost
  @client
  def initialize(team, token)
    @client = Qiita::Client.new(access_token: token, team: team, ssl: false)
  end

  def post(opt)
    if !opt[:tags].nil? then
      opt[:tags] = self::createTagArray opt[:tags]
    end
    @client.create_item(opt)
  end

  def update(id, opt)
    @client.update_item(id, opt)
  end

  def createTagArray(tags)
    data = []
    tags.each do |tag|
      data << {name: tag}
    end
    data
  end
end

class MySlack
  @icon_url
  def initialize(url)
    @client = Slack::Notifier.new url
  end

  def setChannel(channel)
    @client.channel = channel
  end

  def setName(name)
    @client.username = name
  end

  def setIconURL(url)
    @icon_url = url
  end

  def send(body)
    if !@icon_url.nil? then
      @client.ping body, icon_url: @icon_url
    else
      @client.ping body
    end
  end
end


class Main
  @conf
  @title

  def initialize()
    dir = File.expand_path(File.dirname(__FILE__))
    p dir
    @conf = YAML.load(File.read("#{dir}/setting.yml"))
    dt = Date.today - 1
    timeConf = @conf['TIME']
    @title = "MySQL Slowlog #{dt} #{timeConf['start']}:00 - #{timeConf['end']}:00"
  end

  def run()
    results = self.downloadLog()
    qiitaBody = self.digestLogSummary results
    resp = postQiita qiitaBody
    self.sendSlack resp.body['url']
    mailBody = self.digestLogDetail results
    self.sendMail mailBody
  end

  def downloadLog()
    dl = RDSLogDownloader.new @conf['AWS']
    timeConf = @conf['TIME']
    dl.setLocalTime(timeConf['localtime'])
    dl.setHours(timeConf['start'], timeConf['end'])
    dl.fetchMany @conf['RDS']
  end

  def digestLogSummary(logs)
    body = []
    body << "----\n#{@title}\n----\n"
    logs.each do |key, val|
      digested = PTQuery.DigestSummary val
      body << "# #{key}:\n```sh\n#{digested}\n```\n"
    end
    body
  end

  def digestLogDetail(logs)
    body = []
    body << "#{@title}\n"
    logs.each do |key, val|
      digested = PTQuery.DigestDetail val
      body << "#{key}:\n\n#{digested}"
    end
    body
  end

  def postQiita(data)
    conf = @conf['Qiita']
    data << conf['script'] if !conf['script'].nil?
    tags = ["infra/database"]
    qiita = QiitaPost.new conf['team'], conf['token']
    resp = qiita.post title: @title, body: data.join("\n"), tags: tags
  end

  def sendSlack(url)
    conf = @conf['Slack']
    slack = MySlack.new conf['url']
    slack.setChannel conf['channel']
    slack.setName conf['name']
    slack.setIconURL conf['icon']
    slack.send "MySQL Slow-queryが更新されました\n#{url}"
  end

  def sendMail(data)
    aws = @conf['AWS']
    conf = @conf['Mail']
    charset = conf['charset']
    body = data.join("\n\n=======================================\n\n")
    ses = Aws::SES::Client.new(
      region:  aws['ses_region'],
      access_key_id: aws['access_key_id'],
      secret_access_key: aws['secret_access_key'],
    )
    ses.send_email(
      source: conf['from'],
      destination: {
        to_addresses: [conf['to']],
      },
      message: {
        subject: {
          data: @title,
          charset: charset,
        },
        body: {
          text: {
            data: body,
            charset: charset,
          },
        },
      },
    )
  end
end

main = Main.new
main.run()

rds-log-analyzer.sh

#!/bin/bash

# peak time in JST
TIME_FROM=12
TIME_TO=14
#LAST=22
#LAST=`date -u +"%-k" -d "1 hours ago"`


AWS_REGION="ap-northeast-1"
RDS_INSTANCES="db01 db02 db03" # rds name
S3_BUCKET="your-bucket-name"
S3_PATH="/mysql/slowlog"

TMP_FILE="/tmp/mysql.log"
DATE=`date +"%Y%m%d"`
url=()

for ins in $RDS_INSTANCES; do
  f=$TMP_FILE.$DATE.$ins
  for i in `seq $TIME_FROM $TIME_TO`; do
    aws rds download-db-log-file-portion --region $AWS_REGION --db-instance-identifier $ins --log-file-name "slowquery/mysql-slowquery.log.${i}"|  jq -r ".LogFileData | add" >> $f.$i
  done
  #aws rds download-db-log-file-portion --region $AWS_REGION --db-instance-identifier $ins --log-file-name "slowquery/mysql-slowquery.log.$LAST" | jq -r ".LogFileData | add" >> $f.$LAST
  cat $f.* > $f
  request-log-analyzer $f --format mysql --output HTML --file $f.html
  aws s3 cp --region $AWS_REGION $f.html s3://$S3_BUCKET/$S3_PATH/$ins.$DATE.html --acl public-read
  rm $f.*
  rm $f
  url+=("https://$S3_BUCKET/$S3_PATH/$ins.$DATE.html")
done

setting.yml

AWS:
    access_key_id: <your-key-id>
    secret_access_key: <your-secret-key>
    region: ap-northeast-1
    ses_region: us-east-1
TIME:
    start: 20 # peak time for JST
    end: 1
    localtime: +9
RDS:
    - db01
    - db02
    - db03
Qiita:
    team: <your-qiita-team-name>
    token: <your-token>
    script: '<script>/* js script */</script>'
Slack:
    url: https://hooks.slack.com/services/path/to/slack_hook
    icon: https://s3-us-west-2.amazonaws.com/slack-files2/avatars/2015-03-11/4000487445_97a475f13f846c08b203_48.jpg
    channel: '#slack-channel'
    name: Qiita
Mail:
    from: example@example.com
    to: example@example.com
    charset: UTF-8