class Mysqlcollector::Collector

Constants

SQL_INNODB
SQL_LOCKS
SQL_MASTER
SQL_PROCESS
SQL_SLAVE
SQL_STATUS
SQL_VARIABLES

Public Class Methods

new() click to toggle source
# File lib/mysqlcollector/collector.rb, line 13
def initialize
  @mysql  = Mysql.new
  @influx = Influxdb.new
end

Public Instance Methods

innodb() click to toggle source
# File lib/mysqlcollector/collector.rb, line 141
def innodb
  metadata = @mysql.execute(SQL_INNODB).first['Status']
  innodb_parse(metadata)
end
innodb_parse(text) click to toggle source
# File lib/mysqlcollector/collector.rb, line 18
def innodb_parse(text)
  parsing = [# SEMAPHORES:
             'Mutex spin waits (?<spin_waits>\d+), rounds (?<spin_rounds>\d+), OS waits (?<os_waits>\d+)',
             'RW-shared spins (?<spin_waits>\d+), rounds (\d+), OS waits (?<os_waits>\d+)',
             'RW-excl spins (?<spin_waits>\d+), rounds (\d+), OS waits (?<os_waits>\d+)',
             # File I/O:
             '(?<file_reads>\d+) OS file reads, (?<file_writes>\d+) OS file writes, (?<file_fsyncs>\d+) OS fsyncs',
             'Pending normal aio reads: (?<pending_normal_aio_reads>\d+)(?: \[(?:\d+, )*\d+\] )?, aio writes: (?<pending_normal_aio_writes>\d+)(?: \[(?:\d+, )*\d+\] )?',
             'ibuf aio reads: (?<pending_ibuf_aio_reads>\d+), log i\/o\'s: (?<pending_aio_log_ios>\d+), sync i\/o\'s: (?<pending_aio_sync_ios>\d+)',
             'Pending flushes \(fsync\) log: (?<pending_log_flushes>\d+); buffer pool: (?<pending_buf_pool_flushes>\d+)',
             # INSERT BUFFER AND ADAPTIVE HASH INDEX:
             'Ibuf: size (?<ibuf_used_cells>\d+), free list len (?<ibuf_free_cells>\d+), seg size (?<ibuf_cell_count>\d+), (?<ibuf_merges>\d+) merges',
             'merged operations:\n insert (?<ibuf_inserts>\d+), delete mark \d+, delete \d+\ndiscarded operations:\n insert (?<ibuf_merged>\d+)',
             'Hash table size (?<hash_index_cells_total>\d+), node heap has (?<hash_index_cells_used>\d+) buffer',
             # LOG:
             '(?<log_writes>\d+) log i\/o\'s done',
             '(?<pending_log_writes>\d+) pending log writes, (?<pending_chkp_writes>\d+) pending chkp writes',
             'Log sequence number\W+(?<log_bytes_written>\d+)',
             'Log flushed up to\W+(?<log_bytes_flushed>\d+)',
             'Last checkpoint at\W+(?<last_checkpoint>\d+)',
             # BUFFER POOL AND MEMORY
             'Total memory allocated (?<total_mem_alloc>\d+); in additional pool allocated (?<additional_pool_alloc>\d+)',
             'Buffer pool size\W+(?<pool_size>\d+)',
             'Free buffers\W+(?<free_pages>\d+)',
             'Database pages\W+(?<database_pages>\d+)',
             'Modified db pages\W+(?<modified_pages>\d+)',
             'Pages read (?<pages_read>\d+), created (?<pages_created>\d+), written (?<pages_written>\d+)',
             # ROW OPERATIONS
             'Number of rows inserted (?<rows_inserted>\d+), updated (?<rows_updated>\d+), deleted (?<rows_deleted>\d+), read (?<rows_read>\d+)',
             '(?<queries_inside>\d+) queries inside InnoDB, (?<queries_queued>\d+) queries in queue',
             # TRANSACTIONS
             'Trx id counter (?<innodb_transactions>\d+)',
             'History list length (?<history_list>\d+)' ]

  variables = { 'additional_pool_alloc'     => 0,
                'database_pages'            => 0,
                'file_fsyncs'               => 0,
                'file_reads'                => 0,
                'file_writes'               => 0,
                'free_pages'                => 0,
                'hash_index_cells_total'    => 0,
                'hash_index_cells_used'     => 0,
                'history_list'              => 0,
                'ibuf_cell_count'           => 0,
                'ibuf_free_cells'           => 0,
                'ibuf_inserts'              => 0,
                'ibuf_merged'               => 0,
                'ibuf_merges'               => 0,
                'ibuf_used_cells'           => 0,
                'innodb_transactions'       => 0,
                'last_checkpoint'           => 0,
                'log_bytes_flushed'         => 0,
                'log_bytes_written'         => 0,
                'log_writes'                => 0,
                'modified_pages'            => 0,
                'os_waits'                  => 0,
                'pages_created'             => 0,
                'pages_read'                => 0,
                'pages_written'             => 0,
                'pending_aio_log_ios'       => 0,
                'pending_aio_sync_ios'      => 0,
                'pending_buf_pool_flushes'  => 0,
                'pending_chkp_writes'       => 0,
                'pending_ibuf_aio_reads'    => 0,
                'pending_log_flushes'       => 0,
                'pending_log_writes'        => 0,
                'pending_normal_aio_reads'  => 0,
                'pending_normal_aio_writes' => 0,
                'pool_size'                 => 0,
                'queries_inside'            => 0,
                'queries_queued'            => 0,
                'rows_deleted'              => 0,
                'rows_inserted'             => 0,
                'rows_read'                 => 0,
                'rows_updated'              => 0,
                'spin_rounds'               => 0,
                'spin_waits'                => 0,
                'total_mem_alloc'           => 0 }

  parsing.each do |regular_expression|
    matchs = text.match(/#{regular_expression}/i)

    unless matchs.nil?
      matchs.names.each do |variable_name|
        variables[variable_name] += matchs[variable_name].to_i
      end
    end
  end

  metrics_innodb = variables.map do |variable_name, variable_value|
    [variable_name, variable_value]
  end

  # Transactions:
  active_transactions  = text.scan(/---TRANSACTION \d+, ACTIVE \d+ sec/).count
  current_transactions = text.scan(/---TRANSACTION \d+, not started/).count

  innodb_lock_structs  = text.scan(/---TRANSACTION \d+, ACTIVE \d+ sec\n(?<innodb_lock_structs>\d+) lock struct\(s\), heap size/)
  locked_transactions  = text.scan(/---TRANSACTION \d+, ACTIVE \d+ sec\nLOCK WAIT (?<locked_transactions>\d+) lock struct\(s\), heap size/)

  innodb_lock_structs  = innodb_lock_structs.inject{|x,y| x.first.to_i + y.first.to_i }
  locked_transactions  = locked_transactions.inject{|x,y| x.first.to_i + y.first.to_i }

  innodb_lock_structs  = innodb_lock_structs.first.to_i if innodb_lock_structs.kind_of?(Array)
  locked_transactions  = locked_transactions.first.to_i if locked_transactions.kind_of?(Array)

  innodb_lock_structs  = 0 if innodb_lock_structs.nil?
  locked_transactions  = 0 if locked_transactions.nil?

  current_transactions = current_transactions + active_transactions

  variables = { 'innodb_lock_structs'  => innodb_lock_structs,
                'current_transactions' => current_transactions,
                'active_transactions'  => active_transactions,
                'locked_transactions'  => locked_transactions }

  metrics_transactions = variables.map do |variable_name, variable_value|
    [variable_name, variable_value]
  end

  metrics_innodb + metrics_transactions
end
locks() click to toggle source
# File lib/mysqlcollector/collector.rb, line 168
def locks
  @mysql.execute(SQL_LOCKS).map do |lock|
    ["#{lock['Database']}.#{lock['Table']}", lock['In_use']]
  end
end
processlist() click to toggle source
# File lib/mysqlcollector/collector.rb, line 174
def processlist
  metrics = []
  states = { 'State_closing_tables'       => 0,
             'State_copying_to_tmp_table' => 0,
             'State_end'                  => 0,
             'State_freeing_items'        => 0,
             'State_init'                 => 0,
             'State_locked'               => 0,
             'State_login'                => 0,
             'State_none'                 => 0,
             'State_other'                => 0,
             'State_preparing'            => 0,
             'State_reading_from_net'     => 0,
             'State_sending_data'         => 0,
             'State_sorting_result'       => 0,
             'State_statistics'           => 0,
             'State_updating'             => 0,
             'State_writing_to_net'       => 0 };

  @mysql.execute(SQL_PROCESS).each do |process|
    state = process['State']
    state = 'none' if process['State'].nil?
    state = state.gsub(/^(Table lock|Waiting for .*lock)$/, 'Locked')
    state = state.downcase
    state = state.gsub(/ /, '_')
    state = "State_#{state}"

    if states.has_key?(state)
      states[state] += 1
    else
      states['State_other'] += 1
    end

    metrics = states.map do |variable_name, value|
      [variable_name, value]
    end
  end
  metrics
end
slave() click to toggle source
# File lib/mysqlcollector/collector.rb, line 158
def slave
  metrics = @mysql.execute(SQL_SLAVE).map do |stats|
    stats.map do |variable_name, value|
      [variable_name, value]
    end
  end

  metrics.first
end
start() click to toggle source
# File lib/mysqlcollector/collector.rb, line 214
def start
  @influx.send('innodb', innodb)
  @influx.send('process', processlist)
  @influx.send('slave', slave)
  @influx.send('status', status)
  @influx.send('variables', variables)
  @influx.send('locks', locks)

  @mysql.close
end
status() click to toggle source
# File lib/mysqlcollector/collector.rb, line 146
def status
  @mysql.execute(SQL_STATUS).map do |stats|
    [stats['Variable_name'], stats['Value']]
  end
end
variables() click to toggle source
# File lib/mysqlcollector/collector.rb, line 152
def variables
  @mysql.execute(SQL_VARIABLES).map do |stats|
    [stats['Variable_name'], stats['Value']]
  end
end