s

Posts Tagged with "mysql"

Using God Gem to Monitor mySQL

Oct 02, 2011  -  Comments

I recently ran into a problem on a client server where the msyqld process died, and neither of us noticed it for almost 24 hours. That meant that the site was completely useless that entire time. Needless to say, the client wasn't thrilled about this.

I told him that I'd look into setting up a monitoring service to, at the very least, notify me if msyqld ever crashed. During my research, I stumbled upon the god gem (which I'd used in the past to monitor mongrel processes back in the days before Passenger). I didn't realize it could be used to monitor other processes as well, such as msyqld.

God is a rubygem, so it's easy to install on any *nix operating system. It's config files are also written in Ruby, so that makes it even better.

What you'll need to get the script running:

  1. Install the god gem (as root).
    sudo gem install god
  2. Find the location of your msyqld pid file.
    mysqladmin -u root -p variables | grep pid_file
  3. Determine the command to start/stop/restart the msyqld service. Usually /etc/init.d/mysqld

Here is the config file I wrote to watch my mysqld process:

  # run in non-daemonized mode (so you can monitor it) with `god -c /path/to/mysql.god -D`
  # run normally with `god -c /path/to/mysql.god`

  # Settings for email notifications (optional)
  God::Contacts::Email.defaults do |d|
    d.from_email = 'god@my-app.com'
    d.from_name = 'God'
    d.delivery_method = :smtp # this can also be :sendmail
    d.server_host = 'smtp.myapp.com'
    d.server_port = 25
    d.server_auth = true
    d.server_domain = 'myapp.com'
    d.server_user = 'smtp_user@myapp.com'
    d.server_password = 'password'
  end

  # you can create as many email entries as you'd like
  God.contact(:email) do |c|
    c.name = 'me'
    c.to_email = 'me@email.com'
  end

  God.watch do |w|
    # you can name this whatever you want
    w.name = "mySQL Server"

    # polling interval
    w.interval = 30.seconds

    # command to start service
    w.start = "/etc/initd/mysqld_start_ampamp_/etc/initd/httpd_restart/index.html"

    # command to stop service
    w.stop = "/etc/initd/mysqld_stop/index.html"

    # command to restart service
    w.restart = "/etc/initd/mysqld_restart_ampamp_/etc/initd/httpd_restart/index.html"

    # how long to wait after starting service before monitoring resumes
    w.start_grace = 20.seconds

    # how long to wait after restarting service before monitoring resumes
    w.restart_grace = 20.seconds

    # location of pid file
    w.pid_file = "/var/run/mysqld/mysqld.pid"

    # tell god to delete the pid file when mysqld crashes
    w.behavior(:clean_pid_file)

    # determine the state on startup
    w.transition(:init, { true => :up, false => :start }) do |on|
      on.condition(:process_running) do |c|
        c.running = true
      end
    end

    # determine when process has finished starting
    w.transition([:start, :restart], :up) do |on|
      on.condition(:process_running) do |c|
        c.running = true
      end
      # failsafe
      on.condition(:tries) do |c|
        c.times = 8
        c.within = 2.minutes
        c.transition = :start
      end
    end

    # start if process is not running
    w.transition(:up, :start) do |on|
      on.condition(:process_exits) do |c|
        # send an email to me to notify me that the service has crashed
        c.notify = 'me'
      end
    end

    # lifecycle
    w.lifecycle do |on|
      # If the service keeps triggering a restart over and over, it is considered to be "flapping".
      on.condition(:flapping) do |c|
        c.to_state = [:start, :restart]
        c.times = 5
        c.within = 1.minute
        c.transition = :unmonitored
        # If the service is flapping, wait 10 minutes, then try to start/restart again.
        c.retry_in = 10.minutes
        c.retry_times = 5
        c.retry_within = 2.hours
      end
    end
  end

A few things to note about my script:

  1. In my start and restart commands, I also restart Apache. Running my site with Passenger, I would get a Rack error when mysqld restarted, but apache hadn't. YMMV.
  2. I'm using an SMTP server for my email notification. You can also use sendmail, or you can exclude the email notifications altogether.
  3. When running the script for the first time, it's a good idea to add the -D flag. This runs god in non-daemonized mode so all output is piped to STDOUT. That way, you can watch what it's doing to ensure everything is working correctly.

For more information about the god gem, see their site: god.rubyforge.org

Tagged: rubymysqlgod