Informatics

monitoring MariaDB MaxScale with cacti

MaxScale is an interesting software developed by the MariaDB team:

It is a database proxy for MariaDB and MySQL: it accepts SQL requests at one server, and redirects them to backend database servers behind it, so load is distributed among them. As for small enterprises the infrastructure usually permits only one master (which accepts read/write SQL operations), the distributed work will usually be only read requests.

Anyway, once it is installed and configured, can we add some performance graphics to our cacti server?

Fortunately, cacti is flexible enough to accept almost any type of input data… in this case I opted for the easy way: SNMP.

And snmpd in linux can be configured to easily extend the MIB with our own commands.

In this case, after properly configuring our snmpd server, just this line in snmpd.conf will be enough to extend our server’s MIB with the output of a crafted script:

extend maxscale /usr/local/bin/maxscale_stats.sh

The script will filter and output some lines from this MaxScale command:

$ /usr/local/mariadb-maxscale/bin/maxadmin -pmariadb list threads

As you can see, the script uses the default MaxScale v 1.1 path and admin password: if you use different values, edit the script maxscale_stats.sh and write them there. The script has commented values for MaxScale v1.0, so in this case you’ll only have to uncomment them:

# maxscale v 1.1 :
MAXSCALE_PATH=’/usr/local/mariadb-maxscale’
MAXSCALE_PASSWORD=’mariadb’

# maxscale v 1.0 :
#MAXSCALE_PATH=’/usr/local/skysql/maxscale’
#MAXSCALE_PASSWORD=’skysql’

In brief, the script filters and outputs:

  • Current Thread Load Average (5 Minutes Average),
  • Pending event queue length (5 Minutes Average),
  • Total Threads (just the sum of the next two values),
  • Polling Threads, and
  • Processing Threads

So a sample output of the script would be for example:

$ /usr/local/bin/maxscale_stats.sh
0.77
0.01
4
3
1

And this output can be accessed via snmp on our server with these OID’s:

iso.3.6.1.4.1.8072.1.3.2.4.1.2.8.109.97.120.115.99.97.108.101.1

iso.3.6.1.4.1.8072.1.3.2.4.1.2.8.109.97.120.115.99.97.108.101.5

If you see these numbers carefully, “109.97.120.115.99.97.108.101” is just “maxscale” in ASCII — the string we used after “extend” in our previous snmpd configuration.

Check from your cacti server that snmpd is correctly returning the data:

$ snmpwalk -v 2c -c yourPublicCommunity yourMaxscaleServerIP .1.3.6.1.4.1.8072.1.3.2.4

iso.3.6.1.4.1.8072.1.3.2.4.1.2.8.109.97.120.115.99.97.108.101.1 = STRING: “0.63”
iso.3.6.1.4.1.8072.1.3.2.4.1.2.8.109.97.120.115.99.97.108.101.2 = STRING: “0.00”
iso.3.6.1.4.1.8072.1.3.2.4.1.2.8.109.97.120.115.99.97.108.101.3 = STRING: “4”
iso.3.6.1.4.1.8072.1.3.2.4.1.2.8.109.97.120.115.99.97.108.101.4 = STRING: “3”
iso.3.6.1.4.1.8072.1.3.2.4.1.2.8.109.97.120.115.99.97.108.101.5 = STRING: “1”

You can browse the NET-SNMP-EXTEND-MIB OID top structure here.

Now cacti can be configured to read (with cacti’s “Data Templates”) and graphic (with a cacti “Graphic Template”) these values. This requires some obscure knowledge… (you can consult this, or this) but after everything is done, the configuration can be exported so anyone can easily use it:

The “maxscale monitoring with cacti” bundle with instructions is available here.

Instructions, in brief:

  • On your maxscale server, install and configure snmpd if not yet available.
  • Add this line at the end of /etc/snmp/snmpd.conf and restart the service
    extend maxscale /usr/local/bin/maxscale_stats.sh   
  • Copy the file maxscale_stats.sh in /usr/local/bin. Add exec permissions:
    chmod a+x /usr/local/bin/maxscale_stats.sh
  • Check that it outputs five lines when executed. If not, probably your maxscale
    installation path or password are not the defaults ones (at least for v1.1):
    edit the maxscale_stats.sh file and change both values accordingly.
  • On your cacti server, using cacti’s admin Console, Import the xml file included.
  • Create a cacti Device for your maxscale server if not yet done.
  • Under Device / Associated Graph Templates,
    Add “SNMP – MariaDB MaxScale Information”,
    click “Create Graphs for this Host”, and
    select SNMP – MariaDB MaxScale Information.
  • Done!

Happy maxscaling!

 

maxscale.cacti_graph_image

 

Advertisements

One thought on “monitoring MariaDB MaxScale with cacti

  1. Pingback: monitoring php-pfm threads with cacti (via snmp) | circulos meos

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s