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