Introducing ClusterControl Developer Studio and Creating Your Own Database Advisors in JavaScript

Creating a new script

Let’s try to create a new file. First, we click on the “New” button.

In this example, we’ll get access to the ClusterControl statistics to watch the number of selects per second and raise an alert if needed. This script is based on the “Galera template” so once you create a new script, some of the skeleton code will be automatically included.

#include "common/mysql_helper.js"

/**
 * Checks the qps for selects 
 * 
 */ 
var WARNING_THRESHOLD=100;
var TITLE="Select QPS check";
function main()
{
    var hosts     = cluster::galeraNodes();
    var advisorMap = {};

    for (idx = 0; idx < hosts.size(); ++idx)
    {
        host        = hosts[idx];
        map         = host.toMap();
        connected     = map["connected"];
        var advice = new CmonAdvice();
        print(host);
        if(!connected)
            continue;
        if(checkPrecond(host))
        {
         // Get the current time
            var endTime   = CmonDateTime::currentDateTime();
            // set the start time to 10s ago 
            var startTime = endTime - 10; 
          // Get the sql stats
            var stats = host.sqlStats(startTime, endTime);
            // we are interested in COM_SELECT
            var select_arr     = stats.toArray("COM_SELECT");
     // and interval, to calculate per second average 
            var interval_arr    = stats.toArray("interval"); 
        
            //calculate sum of selects over the samples
            var com_select = sum(select_arr);      
            //calculate time period for a sample, ms -> s conversion       
            var interval = sum(interval_arr)/1000; 
         // calculate selects/s based on the time period length
            var com_select_qps = com_select / interval; 


            // uncomment this line to get the output in the Messages section below
            //print(host, ": ", com_select_qps); w
          

           if( com_select_qps > WARNING_THRESHOLD)
           {
               advice.setJustification("Number of selects per second is larger than " 
                                         + WARNING_THRESHOLD + ": " + com_select_qps);
               advice.setSeverity(1);
           }

           else
           {
               advice.setJustification("Number of selects per second is within”
                                       " limits (" + WARNING_THRESHOLD +" qps): " + 
                                       com_select_qps);
               advice.setSeverity(0);
           }

        }
        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;
}

The new thing here, compared to the scripts we looked at earlier, is the way ClusterControl handles the statistics sampling - we can get them using:

stats = host.sqlStats(startTime, endTime); 

You can always check the whole data set by using:

print(stats);

Then we need to generate an array containing interesting data from the whole statistics, e.g., an array containing data for a particular interval.

            // we are interested in COM_SELECT
            var select_arr     = stats.toArray("COM_SELECT");
            // and interval, to calculate per second average
            var interval_arr    = stats.toArray("interval"); 

In general, the sampling in ClusterControl is based on intervals. The latest data is by default close to 4000ms, but it may change. As the data ages, it is aggregated and rolled up into intervals of tens of minutes or several hours. There may be multiple samples needed to cover the timeframe we want. Also, those samples may not cover it completely or may cover additional timeframe. That’s why we have to sum all of the data from all of the samples and then sum the interval (timeframe for a given sample). Then, finally, we can divide the sum of selects by the sum of intervals, convert the value from milliseconds to seconds to get the result of average selects per second.

            //calculate sum of selects over the samples
            var com_select = sum(select_arr); 
            //calculate time period for a sample, ms -> s conversion
            var interval = sum(interval_arr)/1000; 
            // calculate selects/s based on the time period length
            var com_select_qps = com_select / interval; 

The rest is what we saw in our earlier example - we compare the result with a defined threshold and build the advisor data accordingly.

How to execute commands on remote hosts?

ClusterControl DSL gives you the ability to execute commands on remote hosts using SSH connection. It shares ClusterControl’s access so it has the same privileges. The example below is not exactly designed to do anything useful, but it gives you an idea of how it works. 

#include "common/mysql_helper.js"

function main()
{
    var hosts     = cluster::galeraNodes();

    for (idx = 0; idx < hosts.size(); ++idx)
    {
        host        = hosts[idx];
        map         = host.toMap();
        connected     = map["connected"];
        var advice = new CmonAdvice();

        print("############## Host: " + host.toString() + " ##############");
        // Execute remote command and retrieve results
        retval = host.system("ifconfig  | grep eth0 -A 1 | awk 'match($0, /[0-9]{1,3}.[0-9]{1,3}.[0-9]{1,3}.[0-9]{1,3}/) {print substr($0, RSTART, RLENGTH)}'");
        ip = retval['result'];
        print("IP collected from ifconfig: " + ip);
        print("Echoing ip to /tmp/ssh_js_ipaddr");
        // Create file on the remote host
        retval = host.system("echo '" + ip + "' > /tmp/ssh_js_ipaddr");
        // Check the status of the command
        print("Status of echo: " + retval);
        if (retval['success'] == true)
        {
            retval = host.system("cat /tmp/ssh_js_ipaddr");
            print("Output of cat /tmp/ssh_js_ipaddr: " + retval['result']);
            // Remove the file on remote server
            host.system("rm /tmp/ssh_js_ipaddr");
        }
        
        retval = host.system("ls -alh /tmp/ssh_js_ipaddr");
        print("We expect error here, /tmp/ssh_js_ipaddr"
              " should not exist by now" + retval);
        print(" ");
        
    }    
}

As you can see, we executed remote ifconfig, stored the result in a variable in our script, used that variable to modify data on the remote servers and finally removed the file on remote hosts.

Such options can be used to build all types of the scripts, starting from creating advisors based on some status counters accessible from the console, to scripts which would perform some kind of maintenance (custom-tailored backups for example). It may also be useful for periodical monitoring of some non-MySQL activity running in the background (let’s say for example, to check if pt-stalk created new files in its log directory or if it’s running at all).

As you can see, ClusterControl DSL and the Developer Studio gives you a significant number of additional options for customizing your monitoring patterns and build new ones, so you can extend ClusterControl to solve some specific challenges in your environment. We’d love to hear your opinion on this.