Skip to content

Instantly share code, notes, and snippets.

@itsderek23

itsderek23/mysql-health-check.rb

Created September 9, 2011 13:33
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save itsderek23/1206219 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save itsderek23/1206219 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. itsderek23 revised this gist Sep 9, 2011. 1 changed file with 2 additions and 3 deletions.
    5 changes: 2 additions & 3 deletions mysql-health-check.rb
    View file
    Original file line number Diff line number Diff line change
    @@ -32,8 +32,7 @@ class MySQLHealthPlugin < Scout::Plugin
    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)
    notes: When you want to be notify, that data size is approaching to buffer pool (Default - 1024M)
    EOS

    def build_report
    @@ -105,4 +104,4 @@ def build_report
    :connection_utilization => connection_utilization
    end

    end
    end
  2. @balepc balepc revised this gist Sep 9, 2011. 1 changed file with 43 additions and 3 deletions.
    46 changes: 43 additions & 3 deletions mysql-health-check.rb
    View file
    Original file line number Diff line number Diff line change
    @@ -5,7 +5,8 @@
    #
    # Currently plugin monitors
    # * InnoDB data size / buffer pool
    # * Highest usage of available connections (NOT IMPLEMENTED)
    # * Highest usage of available connections
    # * Active processes

    class MySQLHealthPlugin < Scout::Plugin
    needs 'mysql'
    @@ -29,6 +30,10 @@ class MySQLHealthPlugin < Scout::Plugin
    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
    @@ -38,6 +43,8 @@ def build_report
    port = option(:port)
    socket = option(:socket)

    # Buffer pool stuff

    data_size = nil
    innodb_buffer_pool = nil

    @@ -54,15 +61,48 @@ def build_report
    innodb_buffer_pool = row.last.to_i / 1024 / 1024
    end
    end

    buffer_pool_treshhold = option(:buffer_pool_notify_treshhold) || 1024

    if (data_size > innodb_buffer_pool)
    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
    report :innodb_buffer_pool => innodb_buffer_pool, :data_size => data_size,
    :active_processes_count => active_processes_count,
    :connection_utilization => connection_utilization
    end

    end
  3. @balepc balepc revised this gist Jul 29, 2011. 1 changed file with 18 additions and 18 deletions.
    36 changes: 18 additions & 18 deletions mysql-health-check.rb
    View file
    Original file line number Diff line number Diff line change
    @@ -11,24 +11,24 @@ 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
    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
    EOS

    def build_report
  4. @balepc balepc revised this gist Jul 26, 2011. 1 changed file with 31 additions and 36 deletions.
    67 changes: 31 additions & 36 deletions mysql-health-check.rb
    View file
    Original file line number Diff line number Diff line change
    @@ -11,24 +11,24 @@ 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
    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
    EOS

    def build_report
    @@ -38,36 +38,31 @@ def build_report
    port = option(:port)
    socket = option(:socket)

    #########################
    user = 'deploy'
    password = 'PRbPu3G4CY'
    host = 'localhost'

    data_size = nil
    innodb_buffer_pool = nil
    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 = 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
    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
    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

    if (data_size > innodb_buffer_pool)
    if (data_size > innodb_buffer_pool)
    alert('You should increase innodb_buffer_pool_size') unless memory(:notified)
    remember(:notified => true)
    else
    remember(:notified => false)
    end

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

    end
  5. @balepc balepc created this gist Jul 26, 2011.
    73 changes: 73 additions & 0 deletions mysql-health-check.rb
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,73 @@
    # 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 (NOT IMPLEMENTED)

    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
    EOS

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

    #########################
    user = 'deploy'
    password = 'PRbPu3G4CY'
    host = 'localhost'

    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

    if (data_size > innodb_buffer_pool)
    alert('You should increase innodb_buffer_pool_size') unless memory(:notified)
    remember(:notified => true)
    else
    remember(:notified => false)
    end

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

    end