true Eric Lindvall

mysql

# 
# Created by Eric Lindvall <eric@5stops.com>
#

require 'set'

class MysqlQueryStatistics < Scout::Plugin
  ENTRIES = %w(Com_insert Com_select Com_update Com_delete).to_set
  
  needs "mysql"

  def build_report
    # get_option returns nil if the option value is blank
    user     = get_option(:user) || 'root'
    password = get_option(:password)
    host     = get_option(:host)
    port     = get_option(:port)
    socket   = get_option(:socket)
    
    now = Time.now
    mysql = Mysql.connect(host, user, password, nil, (port.nil? ? nil : port.to_i), socket)
    result = mysql.query('SHOW /*!50002 GLOBAL */ STATUS')
    rows = []
    total = 0
    result.each do |row| 
      rows << row if ENTRIES.include?(row.first)

      total += row.last.to_i if row.first[0..3] == 'Com_'
    end
    result.free

    report_hash = {}
    rows.each do |row|
      name = row.first[/_(.*)$/, 1]
      value = calculate_counter(now, name, row.last.to_i)
      # only report if a value is calculated
      next unless value
      report_hash[name] = value
    end


    total_val = calculate_counter(now, 'total', total)
    report_hash['total'] = total_val if total_val
    
    report(report_hash)
  end

  private
  
  # Returns nil if an empty string
  def get_option(opt_name)
    val = option(opt_name)
    val = (val.is_a?(String) and val.strip == '') ? nil : val
    return val
  end
  
  # Note this calculates the difference between the last run and the current run.
  def calculate_counter(current_time, name, value)
    result = nil
    # only check if a past run has a value for the specified query type
    if memory(name) && memory(name).is_a?(Hash)
      last_time, last_value = memory(name).values_at(:time, :value)
      # We won't log it if the value has wrapped
      if last_value and value >= last_value
        elapsed_seconds = current_time - last_time
        elapsed_seconds = 1 if elapsed_seconds < 1
        result = value - last_value

        # calculate per-second
        result = result / elapsed_seconds.to_f
      end
    end
    remember(name => {:time => current_time, :value => value})
    
    result
  end
end

2008-06-24T15:14:02-07:00

Returns the number of each of type of query as well as the total number of queries: * Delete * Insert * Update * Select Requires the MySQL gem. false 22 --- |- options: user: name: MySQL username notes: Specify the username to connect with default: root password: name: MySQL password notes: Specify the password to connect with 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 metadata: select: label: Select Queries units: /sec delete: label: Delete Queries units: /sec update: label: Update Queries units: /sec insert: label: Insert Queries units: /sec replace: label: Replace Queries units: /sec total: label: Total Queries units: /sec triggers: - type: trend data_series_name: total direction: UP percentage_change: 30 duration: 60 window_reference: LAST_WEEK MySQL Statistics

116

0.0

Returns the number of each of type of query (delete, insert, update, select, and total). Requires the MySQL gem.

linux osx

2009-12-07T13:34:15-08:00

http://github.com/highgroove/scout-plugins/raw/1583aee15d63ae531d6fbd0c2cff53f8873bf348/mysql_query_statistics/mysql_query_statistics.rb