MySQL Health Scout plugin

mysql-health-check.rb

# MySQL Health plugin
# 
# Plugin is intended to monitor basic MySQL parametrs
# plugin was inspired by mysqltuner
# 
# Currently plugin monitors
#   * InnoDB data size / buffer pool
#   * Highest usage of available connections
#   * Active processes

class MySQLHealthPlugin < Scout::Plugin
  needs 'mysql'

  OPTIONS=<<-EOS
  user:
    name: MySQL username
    notes: Specify the username to connect with
    default: root
  password:
    name: MySQL password
    notes: Specify the password to connect with
    attributes: password
  host:
    name: MySQL host
    notes: Specify something other than 'localhost' to connect via TCP
    default: localhost
  port:
    name: MySQL port
    notes: Specify the port to connect to MySQL with (if nonstandard)
  socket:
    name: MySQL socket
    notes: Specify the location of the MySQL socket
  buffer_pool_notify_treshhold:
    name: Buffer pool notify treshhold
    notes: When you want to be notify, that data size is approaching to buffer pool (Default - 1024M)
  EOS

  def build_report
    user     = option(:user) || 'root'
    password = option(:password)
    host     = option(:host)
    port     = option(:port)
    socket   = option(:socket)    

    # Buffer pool stuff

    data_size = nil
    innodb_buffer_pool = nil
	
    mysql = Mysql.connect(host, user, password, nil, (port.nil? ? nil : port.to_i), socket)
    result = mysql.query("SELECT   SUM(data_length)/1024/1024 AS data_mb FROM     information_schema.tables WHERE 	 engine = 'InnoDB'")
		
    result.each do |row|
      data_size = row.first.to_i
    end

    result = mysql.query("SHOW variables")
    result.each do |row|
      if row.first == 'innodb_buffer_pool_size'
	innodb_buffer_pool = row.last.to_i / 1024 / 1024
      end
    end
 
    buffer_pool_treshhold = option(:buffer_pool_notify_treshhold) || 1024

    if ( (data_size + buffer_pool_treshhold) > innodb_buffer_pool)
      alert('You should increase innodb_buffer_pool_size') unless memory(:notified)
      remember(:notified => true)
    else
      remember(:notified => false)
    end
 
    # Processlist count
    active_processes_count = nil

    result = mysql.query("SELECT COUNT(*) FROM information_schema.processlist WHERE command != 'Sleep'")
    result.each do |row|
      active_processes_count = row.first.to_i
    end


    # Connections
    max_connections = nil
    max_used_connections = nil
    
    result = mysql.query("show variables like 'max_connections'")
    result.each do |row|
      max_connections = row.last.to_f
    end

    result = mysql.query("show status like 'Max_used_connections'")
    result.each do |row|
      max_used_connections = row.last.to_f
    end

    connection_utilization = (max_used_connections / max_connections) * 100

    if (connection_utilization > 90.0)
      alert("Connection utilization exceed 90% and now is #{connection_utilization}. You should increase max_connection parametr")
    end

    report :innodb_buffer_pool => innodb_buffer_pool, :data_size => data_size, 
              :active_processes_count => active_processes_count,
              :connection_utilization => connection_utilization
  end

end