Ruby script for MySQL monitoring

ruby mysql

In February, I was tasked with the creation of a simple monitor script for MySQL server as part of the exercises of a technical interview. To review the complete code, you can check https://github.com/renehernandez/bok-db_monitor.

Background

Monitoring the status of the database server is essential to guarantee the correct functioning of your system, be it a simple web application or a critical piece of software for the NASA. There are several tools available to automatize the monitoring process, some of them are free while others can be expensive. Some examples are:

  • Innotop, a write up can be found here
  • mysqlreport comes as part of the mysql-client installation in Linux box
  • MySQL Enterprise Monitor. Check it out here
  • Percona Toolkit is a collection of monitoring tools.

If you are deciding to set up a monitoring system for your production system, it is highly recommended to evaluate all the existing tools in the market, before deciding to roll out your own.

After having said that, let’s roll our own implementation here :).

For the remainder of this post, I am going to assume that you have set up a Mysql server in an Ubuntu box, you can read here for how to do it.

Database creation

In this section, we are going to create the database to use in this post.

First, log as the root user on your MySQL server:

$ mysql -u root -p # Provide your root password as requested

Then, create a new user and grant him permission on the database to be used for the exercise.

mysql> CREATE USER 'your_username'@'localhost' IDENTIFIED BY 'your_password';
mysql> CREATE DATABASE your_database;
mysql> GRANT ALL PRIVILEGES ON your_database.* to 'your_username'@'localhost';

Add a table

Next, let’s add a table to the database:

mysql> use your_database;
mysql> create table articles (id integer primary key, name varchar(50));

Creating the Ruby script

Now, that we have our database set up let’s focus on what we want to achieve with our monitoring script.

Initial setup

For this post, we want to monitor the performance of our Mysql server when running select queries and benchmark the running time of the queries.

Since we are very fond of Ruby (aren’t we all!), let’s create a ruby script:

$ touch db.rb

Next, we are going to install the mysql2 gem to connect to our MySQL database. For more information, check out https://github.com/brianmario/mysql2.

$ gem install mysql2

Depending on your system, it may require the installation of the libmysqlclient-dev or mysql-devel packages

Before starting to write our script, we need to decide what we are going to monitor and how we are going to do it. So let’s agree on these two points:

  • Monitor condition: The amount of time it takes to perform a select query from the database.
  • How to monitor: Using the Benchmark module which it is part of Ruby standard library.

In order to have more realistic time measures, let’s insert 100_000 records in the articles table before measuring the performance of select queries.

require 'mysql2'
require 'benchmark'

client = Mysql2::Client.new(host: 'localhost',
                            username: 'your_username',
                            password: 'your_password')

ROWS_NUMBER = 100000

insert_query = "INSERT INTO your_database.articles (id, name) VALUES"

(ROWS_NUMBER - 1).times do |i|
  insert_query << " (#{i}, '#{i}_name'),"
end

insert_query << " (#{ROWS_NUMBER - 1}, '#{ROWS_NUMBER - 1}_name');"

client.query(insert_query)

Now, let’s move to the fun part.

Measuring time

We are going to use the Benchmark modulde mentioned above. Specifically, we are going to use measure method.

time_obj = Benchmark.measure { method }

Which returns an object of type Benchmark::Tms

The new version of our script features two new queries: a select query which is going to be measured, and a status query which is used to gain insights about the state of the MySQL server in case we found that the performance is degraded. Then, if we detect degraded performance we print to console the output of the status query.

require 'mysql2'
require 'benchmark'

client = Mysql2::Client.new(host: 'localhost',
                            username: 'your_username',
                            password: 'your_password')

# This should be commented by now, to avoid inserting another 100_000 records.
ROWS_NUMBER = 100000
insert_query = "INSERT INTO your_database.articles (id, name) VALUES"

(ROWS_NUMBER - 1).times do |i|
  insert_query << " (#{i}, '#{i}_name'),"
end

insert_query << " (#{ROWS_NUMBER - 1}, '#{ROWS_NUMBER - 1}_name')"

client.query(insert_query)

select_query = "SELECT * FROM your_database.articles"
status_query = "SELECT * FROM performance_schema.global_status"
upper_bound = 0.05 # Maximum value to consider the performance as good

time_obj = Benchmark.measure {
  results = client.query(select_query)
}

if time_obj.real >= upper_bound
  results = client.query(status_query)

  # Prints the output to console
  puts "============================="
  puts "Time: #{Time.now}"

  puts "VARIABLE_NAME VARIABLE_VALUE"
  output = ""

  results.each do |r|
    output << "#{r["VARIABLE_NAME"]} #{r["VARIABLE_VALUE"]}\n"
  end

  puts output
end

With the script above, we can see the status of the MySQL server if we execute the script and the time the query takes is longer than 0.05 seconds for this example. You can play with the upper_bound to get more or fewer hits of degraded performance.

Daemonize the process

So far, each time we want to measure the performance we need to run the script from the console to get the results. What happens then if we want the script to monitor the MySQL server at all time?

A simple alternative could be to run the measurement and status output within a while true loop. But that would lock the console window to the output of the script and once we closed the console we would effectively kill the script monitoring process.

Instead, a better approach is to daemonize the process (i.e., to make it run in background). Since we are going to run it in background mode, we will better off writing the status information in log file (db.log in this example), so we can check it later to see if there was any performance degradation.

require 'mysql2'
require 'benchmark'

# Removed the insertion code for easier readability

Process.daemon(true) # Keeps the current working directory

pid = Process.fork do
  client = Mysql2::Client.new(host: 'localhost',
                            username: 'your_username',
                            password: 'your_password')

  select_query = "SELECT * FROM your_database.articles;"
  status_query = "SELECT * FROM performance_schema.global_status"

  upper_bound = 0.05

  while true do
    time_obj = Benchmark.measure {
      results = client.query(select_query)
    }

    if time_obj.real >= upper_bound
      results = client.query(status_query)
      output = ""

      results.each do |r|
        output << "#{r["VARIABLE_NAME"]} #{r["VARIABLE_VALUE"]}\n"
      end

      File.open("db.log", "w+") do |f|

        f.puts "============================="
        f.puts "Time: #{Time.now}"
        f.puts "VARIABLE_NAME VARIABLE_VALUE"
        f.puts output

      end

    end
  end
end

We have two new things in the above code:

  • Writing to file
  • Managing daemonizing the process.

The File.open section writes our the diagnostic info at the end of db.log file using the w+ flag (if the file does not exist, it will be created by the method).

Daemonizing a process in Ruby can be made using the Process module from the Ruby core. The Process.daemon(true) call sets the process to system daemon and the Process.fork call specifies the code we want to run a background. In this way, we allow the parent script, (i.e., the ruby process which invokes our script) to finish and that no terminal signal for the parent is going to kill the child process.

For more information, regarding processes and daemons, you can check the here Extract from Working with Unix Processes book

Send an email

So far, we have our script running in background and configured to log the diagnostic info to a log file. Now, let’s suppose we have our script in a remove VPS, how do we find out there was a problem?

We would have to connect to the VPS and check if there was a change in the log file. But this is not realistic for any production system, so we should try to send a notification each time the script detects a problem with the database server.

So let’s go down this route and send an email!

There are several choices of gems to use to send an email, for this example we are going to use mail gem.

So installing the gem is as easy as:

$ gem install mail

And requiring the gem in the script:

require 'mail'

To see if the emails work, there are several options, you can configure SMTP protocol to a remote mail server (e.g., your gmail account for example), use sendmail or simply use test as the delivery method for the library. We are going to use a configuration for a gmail account to send the emails in case of degradation.

require 'mysql2'
require 'benchmark'
require 'mail'

# Removed the insertion code for easier readability

Process.daemon(true)

# To use this, you would have to enable less secure access for google apps
options = { :address              => "smtp.gmail.com",
            :port                 => 587,
            :user_name            => 'your_email@gmail.com',
            :password             => 'gmail_password',
            :authentication       => 'plain',
            :enable_starttls_auto => true  }



Mail.defaults do
  delivery_method :smtp, options # Configuring email gem to send through smtp
                                 # other options are :sendmail, :test
end

pid = Process.fork do
  client = Mysql2::Client.new(host: 'localhost',
                            username: 'your_username',
                            password: 'your_password')

  select_query = "SELECT * FROM your_database.articles;"
  status_query = "SELECT * FROM performance_schema.global_status"

  upper_bound = 0.05

  while true do
    time_obj = Benchmark.measure {
      results = client.query(select_query)
    }

    if time_obj.real >= upper_bound
      results = client.query(status_query)
      output = ""

      results.each do |r|
        output << "#{r["VARIABLE_NAME"]} #{r["VARIABLE_VALUE"]}\n"
      end

      File.open("db.log", "w+") do |f|

        f.puts "============================="
        f.puts "Time: #{Time.now}"
        f.puts "VARIABLE_NAME VARIABLE_VALUE"
        f.puts output

      end

      Mail.deliver do  # Delivering the email
        from 'db@monitor.server'
        to 'your_email@email.server'
        subject 'Database Perfomance Degradation'
        body output
      end

    end
  end
end

At this point of the post, we should have a script capable of monitoring a MySQL server regarding the performance of select queries. You could modify the script by changing the types of queries, the diagnostic info query or the upper bound for time to see how the system behaves in different situations.

To wrap it up, in this post we have created a simple script to monitor the performance of a MySQL server, we make it run in background as a system daemon and we save the diagnostic info to a log file and send it by email. In the next post on this series, we would analyze how to refactor this solution into a more flexible one by creating a gem. Stay tuned for more!!

Disclaimer: The opinions expressed herein are my own personal opinions and do not represent my employer’s view in any way.