MariaDB: Get the Load Average

Here we present a simple user defined function in C, that returns the load average.

The function returns a string formatted like top‘s because C functions cannot return individual rows nor an array of reals.

The following code can be viewed as an example code for user defined functions in C — or alternatively as an example of how to use snprintf() correctly.

We start by including the necessary header files.

#include <mysql/mysql.h>
#include <stdlib.h>
#include <stdio.h>
#include <string.h>

Then we add the function declarations we’re going to use.

my_bool loadavg_init(UDF_INIT *initid, UDF_ARGS *args, char *message);

void loadavg_deinit(UDF_INIT *initid);

char *loadavg(UDF_INIT *initid, UDF_ARGS *args, char *result,
	      unsigned long *length, char *is_null, char *error);

Then we define the initialization function. This is called at the start of each statement where we use the function.

my_bool loadavg_init(UDF_INIT *initid __attribute((unused)),
		     UDF_ARGS *args, char *message)
{
  if ( args->arg_count != 0 )
    {
      strcpy( message, "no arguments accepted." );
      return 1;
    }
  return 0;
}

Here we use the potentially unsafe function strcpy() because we have no idea how long the message is allowed to be. At least I have never seen any documentation which mentions a maximum buffer length for the error message. This is a flaw in MariaDB’s internal API and we’ll just have to live with it. The short error string given above seems to be fine.

The function is simple, it just checks if the argument count is zero, and returns an error if it’s not; otherwise we return success.

We don’t need anything in the deinitialization function, so we leave it empty.

void loadavg_deinit(UDF_INIT *initid  __attribute((unused)))
{
}

We now come to the meat of our code, even though most parameters are unused.

char *loadavg(UDF_INIT *initid  __attribute((unused)),
	      UDF_ARGS *args  __attribute((unused)), 
	      char *result,
	      unsigned long *length,
	      char *is_null  __attribute((unused)), 
	      char *error  __attribute((unused)))
{

We use negative load values to signal some sort of errors to our users. This is not really “user friendly” but this is a situation that “should never happen” so we’re not going to worry much about the getloadavg() system call to fail.

  double loadavg[ 3 ] = { -1.0, -1.0, -1.0 }; 

The getloadavg() system call returns the number of samples, or -1 on failure. We totally ignore the return value though, because there’s nothing for us to do in either case.

  getloadavg( loadavg, 3 );

It is unfortunate that snprintf() returns an int instead of size_t like its length argument. So we have to deal with type conversions below. Fortunately, it’s very unlikely snprintf() returns a negative number; but then, we can’t dismiss the possibility of buggy C libraries.

  int s = snprintf( result, *length, "%2.2f, %2.2f, %2.2f",
		    loadavg[ 0 ], loadavg[ 1 ], loadavg[ 2 ] );

The output is always null terminated, so snprintf() will return at most *length - 1 on success. On truncation, it’ll return >= length so we have to check for that, and handle it properly.

And for pedantic error checking, we test for negative return values and set the length to zero in that case; it really shouldn’t happen unless the C library is buggy, but even in that case we shouldn’t crash the database server.

In the normal case, where s is less than our buffer length, we just set the *length.

In case of truncation, we reduce the *length parameter by one, to account for the terminating zero.

  
  if ( s < 0 ) {
    *length = 0;
  } else  if ( (unsigned long) s < *length ) {
    *length = s;
  } else {
    (void) *length--;
  }

The (void) cast is to silence a warning about expression results being unused.

And finally we return the results.

  
  return result;
}

We use the following SQL to install the function, once the C code has been built and the library file installed into the plugin directory.

CREATE FUNCTION loadavg RETURNS STRING SONAME 'loadavg.so';

The accompanying Makefile takes care of the heavy lifting for us. Remember that the variables can be overridden on the command line, there’s rarely a need to edit the Makefile itself.

MYSQL=mysql
MYSQL_CONFIG=mysql_config

CFLAGS=-Wall -Wextra `${MYSQL_CONFIG} --include` -fPIC
LDFLAGS=-shared
CC=cc

DBNAME=test
DBUSER=root

SO=so

all: loadavg.${SO}

loadavg.${SO}: loadavg.c
	${CC} ${CFLAGS} ${LDFLAGS} -o $@ loadavg.c

clean:
	rm -f loadavg.${SO}

install: loadavg.${SO}
	cp loadavg.${SO} `${MYSQL_CONFIG} --plugindir`
	${MYSQL} --user=${DBUSER} --database=${DBNAME} -p -B < loadavg.sql

Downloads

• loadavg.c
• loadavg.sql
• Makefile