MySQL :: 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:

<span style="color: #003366;">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);</span>

<span style="color: #003366;">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);</span>

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

custom_xml_permissions — Change permissions for custom.xml if needed

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

<span style="color: #003366;"><class>
     <namespace>mysql</namespace>
     <classname>connection_usage_excessive</classname>
     <query><![CDATA[SELECT scale_percentage AS 
         connection_usage_excessive_scale_percentage
         FROM clusterdb.thresholds 
         WHERE name='connect_usage_excessive']]></query>
     <attributes>
          <default counter="false" type="INTEGER"/>
     <attribute name="connection_usage_excessive_scale_percentage"/>
     </attributes>
</class>
</span>

<namespace>mysql</namespace>

<classname>connection_usage_excessive</classname>

<query><![CDATA[SELECT scale_percentage AS

connection_usage_excessive_scale_percentage

FROM clusterdb.thresholds

WHERE name='connect_usage_excessive']]></query>

</attributes>

</class>

</span>

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.

Copy_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.

edit_rule — 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.