Setting per-server threshold in MySQL Enterprise Monitor

I was speaking with a MySQL customer last week and he had a feature request for MEM. He wants different servers to have different threshold values for certain MEM advisor rules and suggests that users be allowed to override the thresholds when scheduling the rule against a server – just as you can with the refresh interval. At the moment they work around this by creating multiple duplicates of the same rule and then set different thresholds in each. This is a good feature request but in the mean-time this post explains an alternate workaround – having the rules act on thresholds that are defined within the databases of the individual serves.

Step 1. Create a new threshold table in each MySQL Server

The table should be created and then a row added for each rule that needs a per-server threshold:

mysql> create database clusterdb;use clusterdb;
mysql> CREATE TABLE thresholds (name VARCHAR(50) NOT NULL PRIMARY KEY, 
       scale_percentage INT);
mysql> INSERT INTO thresholds VALUES ('connect_usage_excessive',50);

The scale_percentage value will be used in the new MEM rule as a scaling factor for the info, warning and critical threshold levels.
Note that if using MySQL Cluster then you have the option as to whether each MySQL Server in the Cluster has its own set of thresholds (create the table with the InnoDB storage engine) or if they share the same thresholds (create the table with the ndbcluster storage engine).

Step 2. Add custom data collector(s)

First of all, check that you have permissions to edit the custom.xml file – on Windows this will be stored in a location such as C:Program FilesMySQLEnterpriseAgentsharemysql-monitor-agentitems

Change permissions for custom.xml if needed

For each rule that needs a variable threshold, add a new class:

     <query><![CDATA[SELECT scale_percentage AS 
         FROM clusterdb.thresholds 
         WHERE name='connect_usage_excessive']]></query>
          <default counter="false" type="INTEGER"/>
     <attribute name="connection_usage_excessive_scale_percentage"/>

This custom.xml files should be copied to every server. For this data collector to be picked up by MEM, simply restart the agent(s).

Step 3. Copy and extend the rule

From the “Advisors/Manage Rules” tab of the MEM browser click on the copy icon next to the rule you want to apply per-server thresholds to and then edit that rule.

Create a copy of the advisor rule

When editing the rule, add a new variable “%threshold_scale%” which uses the new data collector you’ve just created and then in the expression apply that as a percentage to factor the threshold.

Apply the threshold scaler within the rule

Step 4. Schedule the rule

You can now go ahead and schedule this rule against each of the servers – and the threshold will automatically be scaled appropriately for each server.

Schedule new rule against each server

One comment

  1. Chris Parker says:

    Brilliant!!! I’m going to give this a go, will save me a lot of pain! Thank you

Leave a Reply

Your email address will not be published. Required fields are marked *