Skip to main content

Small python script for monitoring MySQL performance

I have few services which use MySQL as database server. I would like to have information about load in PNG  image  or  in Cacti app.
MySQL   has  performance information at 'SHOW STATUS' command.

Values  which  are monitored : 
 threads_running, threads_connected, thread_cached, slow_queries
 Of course,  it is really easy to add more variables.

Connection to MySQL is accomplished by MySQLdb  module. Typical example of usage is below :
import MySQLdb
mydb = MySQLdb.connect(host = 'hostname', 
                        user = 'username',
                        password = 'secret',
                        database = 'mysatabase'
)
mycursor = mydb.cursor()
mycursor.execute('SQL command')
sqlresult = cur.fetchall()

Storing data in rrd file is aviable via rrdtools package. This one is present in debian and Centos OS. example of creating file is below:
import rrdtool
rrdtool.create("myfile.rrd" ,
"DS:value1:datatype:heartbeat:lowerlimit:upperlimit ", 
"RRA:functionname:percentage:dataset:storedvalues")
This one function is more interested : "DS:value1:datatype:heartbeat:lowerlimit:upperlimit "means : value1 -- value which is stored in RRD heartbeat - howmuch time we wait before setting data to unknown lowerlimit:upperlimit - this is limits for a data "RRA:functionname:percentage:dataset:storedvalues" Functionname can be : AVERAGE the average of the data points is stored. MIN the smallest of the data points is stored. MAX the largest of the data points is stored. LAST the last data points is used. Percentage - how much unknown values can be but calculation be performed. dataset : how many values is used for calculation storedvalues : how many data are stored. Example: we are storing 5 min data and one day should be stored. In this case we need : 60/5 * 24 = 288 if we need information for week and with one hour interval then : 24 records for day (every hour) * 7 day = 168 Unite all together (Config parser is trivial and I skip it ):
#!/usr/bin/python
import MySQLdb
import sys
import rrdtool
from ConfigParser import SafeConfigParser

def main(conf_file="./mysqlmonitor.conf"):

    mydb, rrdfilename  = databaseconnect(conf_file)    

    cur = mydb.cursor()
    
    command = cur.execute('SHOW STATUS')
    res = cur.fetchall()

    for record in res:

        if record[0] == "Threads_running":
            threads_running = record[1]
            print "Threads_running:", threads_running
        if record[0] == "Threads_connected":
            threads_connected = record[1]
            print "Threads_connected:", threads_connected
        if record[0] == "Threads_cached":
            threads_cached  = record[1]
            print "Threads_cached:", threads_cached
        if record[0] == "Slow_queries":
            slow_queries = record[1]
            print slow_queries
            
                
    mydb.close()
    try:
        with open(rrdfilename) as rrdfile :
            rrdupdate(rrdfilename, threads_running, threads_connected, threads_cached, slow_queries)
    except IOError as e:
        print 'RRD file is not present creating'
        rrdcreate(rrdfile)

def rrdcreate(rrdfilename):
    """ function for creating RRD file"""
    ret = rrdtool.create(rrdfilename, "--step", "300", "--start", "0",
    "DS:threads_running:GAUGE:600:U:U",
    "DS:threads_connected:GAUGE:600:U:U",
    "DS:threads_cached:GAUGE:600:U:U",
    "DS:slow_queries:GAUGE:600:U:U",
    "RRA:AVERAGE:0.5:1:600",
    "RRA:AVERAGE:0.5:6:700",
    "RRA:AVERAGE:0.5:24:775",
    "RRA:MAX:0.5:1:600",
    "RRA:MAX:0.5:6:700",
    "RRA:MAX:0.5:444:797")


def rrdupdate(rrdfilename, threads_running, threads_connected, threads_cached, slow_queries):
    """ updating rrd data withnew information"""
    ret = rrdtool.update(rrdfilename, "N:%s:%s:%s:%s" %(threads_running, threads_connected, threads_cached, slow_queries))
    print "Updating"


def databaseconnect(conf_file=""):
    """ Log conf file and connect to database"""
    if not conf_file:
        sys.exit(-1)
    
    config = SafeConfigParser()
    config.read(conf_file)
    mydb = MySQLdb.connect(
        host = config.get('database','host'),
        user = config.get('database','user'),
        passwd = config.get('database','password'),
        db = 'INFORMATION_SCHEMA'
    )
    workingpath = config.get('files', 'rrd')
    return mydb, workingpath

if __name__ == "__main__":
    main()

Not bad for 3 hours of work!

Comments

  1. It's not bad idea to keep RRD definitions in config file - it should be quick improvement.

    Also, you can add minimal advantage: you can predict future values and show it on graph (in this case it will look like typical stock diagram

    http://en.wikipedia.org/wiki/Stock_and_flow

    ReplyDelete
  2. This comment has been removed by a blog administrator.

    ReplyDelete
  3. This comment has been removed by a blog administrator.

    ReplyDelete

Post a Comment

Popular posts from this blog

Update grub using dracut

Fixing grub using dracut Last kernel update was not successful to me. Centos can not boot with next messages:  [ 180.098802] dracut-initqueue[376]: Warning: dracut-initqueue timeout - starting timeout scripts [ 180.610167] dracut-initqueue[376]: Warning: dracut-initqueue timeout - starting timeout scripts [ 181.121619] dracut-initqueue[376]: Warning: dracut-initqueue timeout - starting timeout scripts [ 181.633093] dracut-initqueue[376]: Warning: dracut-initqueue timeout - starting timeout scripts [ 182.144831] dracut-initqueue[376]: Warning: dracut-initqueue timeout - starting timeout scripts [ 182.656146] dracut-initqueue[376]: Warning: dracut-initqueue timeout - starting timeout scripts [ 183.167306] dracut-initqueue[376]: Warning: dracut-initqueue timeout - starting timeout scripts [ 183.678755] dracut-initqueue[376]: Warning: dracut-initqueue timeout - starting timeout scripts  Of course simples way  is creating  linux  usb stick  and fix it. But dracut
  debian,  amavis,  virus inside archive   One my client asked informed me, that amavis skips some files types. mail server configuration is really simple: Postfix as SMTP server and  amavis working as context filter. Also amavis runs spamassasin and clamd antivirus. Amavis gets files from attachment and unpack it. lha file is not detected. short investigation First I deceided to run amavis  in debug mode and verify how virus passed postix+amavis.  root@newserver:/var/lib/amavis# /etc/init.d/amavis stop [ ok ] Stopping amavis (via systemctl): amavis.service. root@newserver:/var/lib/amavis# /etc/init.d/amavis debug Trying to run amavisd-new in debug mode. Debug mode inform about loaded plugins: ' Nov 13 22:07:23.335 newserver. /usr/sbin/amavisd-new[40334]: Found decoder for .cpio at /bin/pax Nov 13 22:07:23.336 newserver. /usr/sbin/amavisd-new[40334]: Found decoder for .tar at /bin/pax Nov 13 22:07:23.336 newserver. /usr/sbin/amavisd-new[40334]

Postfix can not start via systemd (simple fix)

Solving problem related to systemd process I like postfix.   This is really smart and secure mail server. I'm helping above  dozen clients around the world and  tunning  postfix is really fun task. This morning I was downgrading postfix  to the stable version for one of the my friends and come across interesting issue.  root@newserver:/etc/init.d# systemctl status postfix ● postfix.service Loaded: masked (/dev/null; bad) Active: inactive (dead) since вт 2017-06-13 14:35:41 EEST; 1h 48min ago Main PID: 25145 (code=exited, status=0/SUCCESS) чер 13 14:47:09 newserver systemd[1]: Stopped postfix.service. чер 13 14:47:29 newserver systemd[1]: Stopped postfix.service. чер 13 14:58:22 newserver systemd[1]: Stopped postfix.service. чер 13 14:58:23 newserver systemd[1]: Stopped postfix.service. чер 13 15:05:20 newserver systemd[1]: Stopped postfix.service. чер 13 15:29:06 newserver systemd[1]: Stopped postfix.service. чер 13 15:29:06 newserver systemd[1]: Stopp