9.2. Modifying the Procedure Call and Arguments

Documentation

VoltDB Home » Documentation » Guide to Performance and Customization

9.2. Modifying the Procedure Call and Arguments

To vary the stored procedure called by a task or the arguments to that procedure, you can create a custom task that implements the ActionGenerator interface. The custom task consists of the Java class you create, and the SQL statements to load and declare the task. At run time, for each invocation of the task, VoltDB uses the custom class to determine what procedure to call and its arguments and the task declaration to determine when to run the task.

Let's look at an example. Say we want a task that periodically deletes unused sessions. The stored procedure to do this might look like the following, where the batch size — the maximum number of records to delete — is passed in as an argument to the procedure:

CREATE PROCEDURE PurgeOldSessions DIRECTED AS
    DELETE FROM session 
       WHERE last_access < DATEADD(MINUTE,-5,NOW()) 
       ORDER BY sessionID  ASC LIMIT ?;

We could schedule this procedure using a static task and a fixed batch size. But then there is no guarantee that the task can keep up with the volume of expired sessions. Instead, we would like to be able to adjust the batch size to accommodate changing workloads.

To do this we can create a custom task that checks how many records were actually deleted in each run. If the count of deleted records is less than 80% of the batch size, reduce the batch size for the next run. On the other hand, if the number deleted equals the batch size, increase the batch size incrementally until it matches the workload. Finally, we can use parameters to the custom task to specify the minimum, maximum, and starting batch size.

9.2.1. Designing a Java Class That Implements ActionGenerator

The first step is to write a Java class that implements the ActionGenerator interface. This must be a static class whose constructor takes no arguments. In our example, we will also declare class variables for a helper object that is used in subsequent methods plus minimum, maximum, and current values for the batch size.

The class must, at a minimum, declare or override three methods:

1

initialize()

The initialize() method is called first and receives a helper object (inserted automatically by the task subsystem), plus any parameters defined by the task definition in SQL. In our example there will be three parameters from the task definition: a minimum, maximum, and starting value for the batch size, which the method stores in the variables declared earlier. So the initialize() method ends up having a total of four arguments.

2

GetFirstAction()

The getFirstAction() method is called when the task starts; that is, when the task is first defined or when the database starts or resumes. The method must return an Action object, which includes the procedure to call, arguments to that procedure, and a callback method to be invoked once the first invocation is completed. In our example, the method creates an action using the PurgeOldSessions stored procedure, the initial batch size, and the callback procedure declared in the next step.

3

A callback method

Finally, your custom task must have a callback method (the method you specify when creating an Action object), which is invoked once the specified procedure instance completes. The callback method must return another Action, similar to getFirstAction.

In our example, the callback compares the current batch size to the number of records deleted by the last run and makes appropriate adjustments. It decreases the batch size if less than 80% were deleted, it increases the batch size if the full batch was used, and it keeps the size within the specified minimum and maximum. In this case, we are changing the arguments only, not the stored procedure invoked. Although that is possible if your application requires it.

Note that the callback method can be a name of your choosing. It does not have to be callback(). You are also not constrained to using just one callback; you might select different callback methods based on which stored procedure you are invoking or the current application context.

Example 9.1, “Custom Task Implementing ActionGenerator” shows the completed example task class, with the key elements highlighted.

Example 9.1. Custom Task Implementing ActionGenerator

package mytasks;

import java.util.concurrent.TimeUnit;
import org.voltdb.VoltTable;
import org.voltdb.client.ClientResponse;
import org.voltdb.task.*;

public class PurgeBatches implements ActionGenerator { 

    private TaskHelper helper;
    private long min, max, batchsize;

    public void initialize(TaskHelper helper,          1
                long min, long max, long batch) {
        this.min = min;
        this.max = max;
        this.batchsize = batch;
    }

    public Action getFirstAction() {                    2
        return Action.procedureCall(this::callback, 
               "PurgeOldSessions", this.batchsize);
    }

        /* 
        * Callback to handle the result of the task 
        * and return next Action.
        */
    private Action callback(ActionResult result) {      3

        /* Find out how many records were deleted */
        ClientResponse response = result.getResponse();
        VoltTable[] results = response.getResults();
        long count = results[0].fetchRow(0).getLong(0);

         /* If less than 80%, decrease by 10% */
        if (count < this.batchsize * .8) 
            this.batchsize -= this.max * .1;

        /* If equal to batch size, increase by 10% */
        if (count == this.batchsize) 
            this.batchsize += this.max * .1;

            /* Stay within min & max */
        if (this.batchsize > this.max) this.batchsize = this.max;
        if (this.batchsize < this.min) this.batchsize = this.min;
        
        return Action.procedureCall(this::callback, 
               "PurgeOldSessions", this.batchsize);
    }
}

9.2.2. Compiling and Loading the Class into VoltDB

Once you complete your Java source code, you need to compile, debug, and package it into a JAR file so it can be loaded into VoltDB. You compile and package task classes the same way you compile and package stored procedures. In fact, you can package tasks and procedures into the same or separate JARs if you choose. The following example compiles the Java classes in the src folder and packages them into the JAR file sessiontasks.jar:

$ javac -classpath "/opt/voltdb/voltdb/*" \
        -d ./obj  src/*.java
$ jar  cvf  sessiontasks.jar -C obj . 

You then load the classes from the JAR file into VoltDB using the sqlcmd LOAD CLASSES directive:

LOAD CLASSES sessiontasks.jar;

9.2.3. Declaring the Task

Finally, once the custom class is loaded into the database, you can declare the task and start it running. You declare the task using the CREATE TASK statement, replacing a procedure name with the FROM CLASS clause specifying the classpath to your new class. In our example, the custom task also requires three arguments: a minimum, maximum and starting batch size.

Because the stored procedure that the custom class specifies is a directed procedure (that is, it runs separately on every partition on the cluster), the task must be declared to RUN ON PARTITIONS. If the procedure was not directed, the task could be run on PARTITIONS, DATABASE, or HOSTS. However, for partitioned tables it is often necessary to have partitioned or directed procedures if the procedure's statements need to both ORDER BY and LIMIT the rows.

The following statement creates the custom task with a minimum batch size of 100 records, a maximum of 2,000, and a starting size of 1,000.

CREATE TASK batchcleanup 
  ON SCHEDULE EVERY 5 SECONDS
  PROCEDURE FROM CLASS mytasks.PurgeBatches
     WITH (100,2000,1000)
     RUN ON PARTITIONS;

Note that the task starts as soon as it is declared, unless you include the DISABLE clause. Alternately, you can use the ALTER TASK statement to change the state of the task. For example, the following statement disables our newly created task:

ALTER TASK batchcleanup DISABLE;