Andrew Morgan on Databases

MySQL Cluster 7.1 introduced the ndbinfo database which contains views giving real-time access to a whole host of information that helps you monitor and tune your MySQL Cluster deployment. Because this data can be accessed through regular SQL, various systems can be configured to monitor the Cluster. This post gives one example, extending MySQL Enterprise Monitor to keep an eye on the amount of free memory on the data nodes (through a graph) and then raise an alarm when it starts to run low – even generating SNMP traps if that’s what you need.

One of the features of MySQL Enterprise Monitor is that you can define custom data collectors and that those data collectors can run SQL queries to get the data. The information retrieved by those custom data collectors can then be used with rules that the user defines through the MySQL Enterprise Monitor GUI to create warning/alarms.

In this example, I create two new data collectors in the file”<MySQL Enterprise Monitor installation directory>/agent/share/mysql-proxy/items/cluster.xml” before starting up the MySQL Enterprise Monitor agent (note that these should be created for the agent of each MySQL Server in the Cluster that you would like to use to present the information from the data nodes):

cluster.xml:

<?xml version="1.0" encoding="utf-8"?>
<classes>
  <class>
    <namespace>mysql</namespace>
    <classname>cluster_max_used</classname>
    <query><![CDATA[SELECT MAX(used) AS Used FROM ndbinfo.memoryusage WHERE memory_type = 'Data Memory';]]></query>
  </class>
  <class>
    <namespace>mysql</namespace>
    <classname>cluster_min_avail</classname>
    <query><![CDATA[SELECT MIN(total) AS Total FROM ndbinfo.memoryusage WHERE memory_type = 'Data Memory';]]></query>
  </class>
</classes>

So that the agent picks up this file, it should be referenced within <MySQL Enterprise Monitor installation directory>/agent/mysql-monitor-agent.ini:

agent-item-files = share/mysql-monitor-agent/items/quan.lua,share/mysql-monitor-agent/items/items-mysql-monitor.xml,
share/mysql-monitor-agent/items/custom.xml,share/mysql-monitor-agent/items/cluster.xml

In MySQL Enterprise Monitor, events are raised by rules. Rules are grouped together into Advisors and so I create a new Advisor called “MySQL Cluster” and then create just one new rule within that Advisor group.

As shown in Fig. 1 the rule is called “Data Node Low Memory”. The “Variable Assignment” section is used to define 2 variables %used_mem% and %config_mem% which are populated from the Used and Total results from the 2 new data collectors. The “Expression” section is used to test “((Total – Used)/Total)x100< THRESHOLD” and then the values to be substituted for THRESHOLD are defined in the “Thresholds” section – indicating at what points the Info, Warning and Critical Alters should be raised.

There are then a number of optional sections that you can use to add useful information to the person investigating the alert.

Once the rule has been created, the next step is to schedule and (if desired) tag that the alerts should also result in SNMP traps being raised. This is standard MySQL Enterprise Monitor practice and so it isn’t explained here except to point out that this rule is monitoring information from the data nodes but the rule has to be applied to a MySQL Server in the Cluster (MySQL Enterprise Monitor has no idea what a data node is) and so you need to schedule the rule against one or more arbitrary MySQL Server instances in the Cluster).

Fig. 2 Warning alert

To test the functionality, start adding more data to your MySQL Cluster until the Warning alert is triggered as shown in Fig. 2. As you can see, the optional information we included is shown – including values from Used and Total.

Fig. 3 Major alert

I then add more data to the database until the critical alert is raised and confirm that it’s displayed on the main monitoring panel of the MySQL Enterprise Monitor dashboard. Note that if you requested these alerts be included with the SNMP feed then SNMP traps will also be raised.

Please note that this example is intended to illustrate the mechanics of setting up monitoring on an arbitrary piece of data from ndbinfo and obviously in the real world you would want to monitor more than just the memory and even for the memory, you might want to use a more sophisticated rule.

Fig. 4 Custom graph for memory usage

It is sometimes more useful to see how a value changes over time. For this, MySQL Enterprise Monitor provides graphs. The data collectors created for the rule can also be used to add a new graph to Enterprise monitor. The graph is defined by creating the following file:

<com_mysql_merlin_server_graph_Design>
  <version>1.0</version>
  <uuid>b0bc2bba-ea9b-102b-b396-94aca32b0b28</uuid>
  <tag></tag>
  <name>Per Data Node Data Memory Use</name>
  <rangeLabel>MB</rangeLabel> <frequency>00:01:00</frequency>
  <series>
    <label>Used</label>
    <expression>cluster_data_node_used_data_memory/1024/1024</expression>
  </series>
  <series>
    <label>Avail</label>
    <expression>cluster_data_node_config_data_memory/1024/1024</expression>
  </series>
  <variables>
    <name>cluster_data_node_used_data_memory</name>
    <dcItem>
      <nameSpace>mysql</nameSpace>
      <className>cluster_max_used</className>
      <attribName>Used</attribName>
    </dcItem>
    <instance>local</instance>
  </variables>
  <variables>
    <name>cluster_data_node_config_data_memory</name>
    <dcItem>
      <nameSpace>mysql</nameSpace>
      <className>cluster_min_avail</className>
      <attribName>Total</attribName>
    </dcItem>
    <instance>local</instance>
  </variables>
</com_mysql_merlin_server_graph_Design>
 

Fig. 5 MySQL Enterprise Monitor dashboard

Click on Import/Export in the Graphs tab in Enterprise Monitor (2.2) and then import the file defining the graph.

The graph will then appear on the graphs tab and can also be configured to appear on the main dashboard as shown in Fig. 5