Write Your First Advisor

We can use this function in our advisor to fetch the Created_tmp_disk_tables and Created_tmp_tables.

    for (idx = 0; idx < hosts.size(); ++idx)
    {
        host        = hosts[idx];
        map         = host.toMap();
        connected     = map["connected"];
        var advice = new CmonAdvice();
        var tmp_tables = readStatusVariable(host, ‘Created_tmp_tables’);
        var tmp_disk_tables = readStatusVariable(host, ‘Created_tmp_disk_tables’);

And now we can calculate the temporary disk tables ratio:

        var tmp_disk_table_ratio = tmp_disk_tables / (tmp_tables + tmp_disk_tables) * 100;

And alert if this ratio is greater than the threshold we set in the beginning:

        if(checkPrecond(host))
        {
           if(tmp_disk_table_ratio > WARNING_THRESHOLD) {
               advice.setJustification("Temporary tables written to disk is excessive");
               msg = ADVICE_WARNING;
           }
           else {
               advice.setJustification("Temporary tables written to disk not excessive");
               msg = ADVICE_OK;
           }
        }

It is important to assign the Advice to the msg variable here as this will be added later on into the advice object with the setAdvice() function. The full script for completeness:

#include "common/mysql_helper.js"

/**
 * Checks the percentage of max ever used connections 
 * 
 */ 
var WARNING_THRESHOLD=20;
var TITLE="Temporary tables on disk ratio";
var ADVICE_WARNING="More than 20% of temporary tables are written to disk. It is advised to review your queries, for example, via the Query Monitor.";
var ADVICE_OK="Temporary tables on disk are not excessive.";

function main()
{
    var hosts     = cluster::mySqlNodes();
    var advisorMap = {};

    for (idx = 0; idx < hosts.size(); ++idx)
    {
        host        = hosts[idx];
        map         = host.toMap();
        connected     = map["connected"];
        var advice = new CmonAdvice();
        var tmp_tables = readStatusVariable(host, 'Created_tmp_tables');
        var tmp_disk_tables = readStatusVariable(host, 'Created_tmp_disk_tables');
        var tmp_disk_table_ratio = tmp_disk_tables / (tmp_tables + tmp_disk_tables) * 100;
        
        if(!connected)
            continue;
        if(checkPrecond(host))
        {
           if(tmp_disk_table_ratio > WARNING_THRESHOLD) {
               advice.setJustification("Temporary tables written to disk is excessive");
               msg = ADVICE_WARNING;
               advice.setSeverity(0);
           }
           else {
               advice.setJustification("Temporary tables written to disk not excessive");
               msg = ADVICE_OK;
           }
        }
        else
        {
            msg = "Not enough data to calculate";
            advice.setJustification("there is not enough load on the server or the uptime is too little.");
            advice.setSeverity(0);
        }
        advice.setHost(host);
        advice.setTitle(TITLE);
        advice.setAdvice(msg);
        advisorMap[idx]= advice;
    }
    return advisorMap;
}

Now you can play around with the threshold of 20, try to lower it to 1 or 2 for instance and then you probably can see how this advisor will actually give you advice on the matter.

As you can see, with a simple script you can check two variables against each other and report/advice based upon their outcome. But is that all? There are still a couple of things we can improve!

Improvements on my first advisor

The first thing we can improve is that this advisor doesn’t make a lot of sense. What the metric actually reflects is the total number of temporary tables on disk since the last FLUSH STATUS or startup of MySQL. What it doesn’t say is at what rate it actually creates temporary tables on disk. So we can convert the Created_tmp_disk_tables to a rate using the uptime of the host:

    var tmp_disk_table_rate = tmp_disk_tables / uptime;

This should give us the number of temporary tables per second and combined with the tmp_disk_table_ratio, this will give us a more accurate view on things. Again, once we reach the threshold of two temporary tables per second, we don’t want to immediately send out an alert/advice.

Another thing we can improve is to not use the readStatusVariable(, ) function from the common/mysql_helper.js library. This function executes a query to the MySQL host every time we read a status variable, while CMON already retrieves most of them every second and we don’t need a real-time status anyway. It’s not like two or three queries will kill the hosts in the cluster, but if many of these advisors are run in a similar fashion, this could create heaps of extra queries.

In this case we can optimize this by retrieving the status variables in a map using the host.sqlInfo() function and retrieve everything at once as a map. This function contains the most important information of the host, but it does not contain all. For instance the variable uptime that we need for the rate is not available in the host.sqlInfo() map and has to be retrieved with the readStatusVariable(, ) function.

This is what our advisor will look like now, with the changes/additions marked in bold:

#include "common/mysql_helper.js"

/**
 * Checks the percentage of max ever used connections 
 * 
 */ 
var RATIO_WARNING_THRESHOLD=20;
var RATE_WARNING_THRESHOLD=2;
var TITLE="Temporary tables on disk ratio";
var ADVICE_WARNING="More than 20% of temporary tables are written to disk and current rate is more than 2 temporary tables per second. It is advised to review your queries, for example, via the Query Monitor.";
var ADVICE_OK="Temporary tables on disk are not excessive.";

function main()
{
    var hosts     = cluster::mySqlNodes();
    var advisorMap = {};

    for (idx = 0; idx < hosts.size(); ++idx)
    {
        host        = hosts[idx];
        map         = host.toMap();
        connected     = map["connected"];
        var advice = new CmonAdvice();
        var hostStatus = host.sqlInfo();
        var tmp_tables = hostStatus['CREATED_TMP_TABLES'];
        var tmp_disk_tables = hostStatus['CREATED_TMP_DISK_TABLES'];
        var uptime = readStatusVariable(host, 'uptime');
        var tmp_disk_table_ratio = tmp_disk_tables / (tmp_tables + tmp_disk_tables) * 100;
        var tmp_disk_table_rate = tmp_disk_tables / uptime;
        
        if(!connected)
            continue;
        if(checkPrecond(host))
        {
           if(tmp_disk_table_rate > RATE_WARNING_THRESHOLD && tmp_disk_table_ratio > RATIO_WARNING_THRESHOLD) {
               advice.setJustification("Temporary tables written to disk is excessive: " + tmp_disk_table_rate + " tables per second and overall ratio of " + tmp_disk_table_ratio);
               msg = ADVICE_WARNING;
               advice.setSeverity(0);
           }
           else {
               advice.setJustification("Temporary tables written to disk not excessive");
               msg = ADVICE_OK;
           }
        }
        else
        {
            msg = "Not enough data to calculate";
            advice.setJustification("there is not enough load on the server or the uptime is too little.");
            advice.setSeverity(0);
        }
        advice.setHost(host);
        advice.setTitle(TITLE);
        advice.setAdvice(msg);
        advisorMap[idx]= advice;
    }
    return advisorMap;
}

Scheduling my first advisor

After we have saved this new advisor, compiled it and run, we now can schedule this advisor. Since we don’t have an excessive workload, we will probably run this advisor once per day.

The base scheduling mode is similar to Cron which has every minute, 5 minutes, hour, day, month preset and this is exactly what we need and is very easy to manage the scheduling. Changing this to advanced will unlock the other greyed out input fields. These input fields work exactly the same as a crontab, so you can even schedule for a particular day, day of the month or even set it on weekdays.

Following this blog, we will create a checker for SELinux or security checks for Spectre and Meltdown if nodes are affected. Stay tuned!