Examples of MySQL Daemon Plugin

Exclusive offer: get 50% off this eBook here
MySQL 5.1 Plugin Development

MySQL 5.1 Plugin Development — Save 50%

Extend MySQL to suit your needs with this unique guide into the world of MySQL plugins

$26.99    $13.50
by Andrew Hutchings | August 2010 | MySQL Open Source Web Development

In the previous article, A look inside a MySQL Daemon plugin, we were introduced to the MySQL Plugin API. It talked about the most simple plugin type—Daemon plugins. It starts with the basic structure of a plugin—what a plugin declaration should look like, what plugin types are, and so on. Then it described features common to all plugin types—initialization and de-initialization callbacks, status variables, and configuration system variables.

This article, by Andrew Hutchings, co-author of MySQL 5.1 Plugin Development, describes and analyzes line by line four Daemon plugin examples—from a simple plugin that prints Hello World! when loaded, to a system monitoring plugin that periodically logs the number of connections, to a system usage status plugin that displays the memory and I/O usage of the MySQL server.

We will specifically cover:

  • A Hello World! Daemon plugin
  • A system and status variables demo plugin
  • A simple monitoring plugin
  • System Status Variables plugin

(For more resources on MySQL, see here.)

A Hello World! Daemon plugin

Now, let's look at our first complete plugin example. This plugin is probably the most basic plugin we can have. It simply prints a message into the MySQL error log when loaded:

#include <stdio.h>
#include <mysql/plugin.h>
#include <mysql_version.h>

These are the basic includes required for most Daemon plugins. The most important being mysql/plugin.h, which contains macros and data structures necessary for a MySQL plugin.

static int hello_world_plugin_init(void *p)
{
fprintf(stderr, "Hello World: "
"This is a static text daemon example plugin!\n");
return 0;
}

In the plugin initialization function we simply write a message to stderr. MySQL redirects stderr to the error log (if there is one) so our message will end up there. We then return 0 to indicate that the initialization was successful.

struct st_mysql_daemon hello_world_info =
{ MYSQL_DAEMON_INTERFACE_VERSION };

This structure is used for the info part of the plugin declaration. In Daemon plugins it simply contains the API version that this plugin was compiled against. The Daemon plugin API version matches the MySQL server version, which means MySQL Daemon plugins can only be used with a MySQL server version they have been compiled against. Indeed, for a Daemon plugin to do something non-trivial it will invariably need access to the server's internal functions and data structures that change with every MySQL version. Other plugins that are implemented according to a certain functionality API are separated from the server internals and are binary compatible with a wide range of server releases.

Having defined all of the functions and auxiliary structures, we can declare a plugin:

mysql_declare_plugin(hello_world)
{

This is a Daemon plugin so we need to specify it as such with this defined constant:

MYSQL_DAEMON_PLUGIN,

info points to the structure declared earlier. With other plugin types this may contain additional information valuable to the plugin functionality:

&hello_world_info,

We are calling this plugin "hello_world". This is its name for the INSTALL PLUGIN command and any plugin status:

"hello_world",

The author string, is useful for providing contact information about the author of the plugin:

"Andrew Hutchings (Andrew.Hutchings@Sun.COM)",

A Simple line of text that gives a basic description of what our plugin does:

"Daemon hello world example, outputs some static text",

This plugin is licensed under GPL so we set the license type to this:

PLUGIN_LICENSE_GPL,

This is our initialization function that has been defined earlier in the code:

hello_world_plugin_init,

As our simple plugin does not need a de-initialization function, we put NULL here:

NULL,

This plugin is given version 1.0 because it is our first GA release of the plugin. In future versions we can increment this:

0x0100,

There are no status or system variables in this example. Hence, everything below the version is set to NULL:

NULL,
NULL,
NULL
}
mysql_declare_plugin_end;

We can now install this plugin using the INSTALL PLUGIN syntax


Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.47 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current
input statement.

mysql> INSTALL PLUGIN hello_world SONAME 'hello_world.so';
Query OK, 0 rows affected (0.00 sec)

Going to the error log we see:

090801 22:18:00 [Note] /home/linuxjedi/Programming/Builds/mysql-5.1.47/
libexec/mysqld: ready for connections.
Version: '5.1.47' socket: '/tmp/mysql.sock' port: 3306 Source
distribution
Hello World: This is a static text daemon example plugin!

A system and status variables demo plugin

Let's see a more complex example. This plugin shows how to create system and status variables. It has one global system variable and one status variable, both defined as long long. When you set the global system variable, its value is copied into the status variable.

#include <stdio.h>
#include <mysql/plugin.h>
#include <mysql_version.h>

long long system_var = 0;
long long status_var = 0;

struct st_mysql_show_var vars_status_var[] =
{
{"vars_status_var", (char *) &status_var, SHOW_LONGLONG},
{0, 0, 0}
};

We have one status variable in this plugin called vars_status_var which is bound to the status_var variable defined near the top of this source code. We are defining this variable as long long so we use the SHOW_LONGLONG type.

int sysvar_check(MYSQL_THD thd,
struct st_mysql_sys_var *var,
void *save, struct st_mysql_value *value)
{

This function is to be called before our system variable is updated. A plugin is not required to provide it but it can be used to check if the data entered is valid and, as an example, we will only allow values that are not too close to status_var.

long long buf;
value->val_int(value, &buf);

First we retrieve the new value-to-be and store it in buf.

*(longlong*) save = buf;

We then set save to the contents of buf, so that the update function could access it and store the value in our system_var variable. If we do not implement our own sysvar_check() function for our system variable, MySQL will provide a default one that performs all of the above (but nothing of the following).

if (buf * 2 < status_var || buf > status_var * 3)
return 0;
else
return 1;
}

This is our special condition. In this example we allow an update only if the new value is either less than a half of or three times bigger than the value of status_var. We return 0 when the new value is valid, and an update should be allowed, and 1 when an update should be canceled. In our update function we copy the value of the system_var to a status_var, to see how its value changes in SHOW STATUS and to get a different range on valid values for the system_var on every update. Note that the update function cannot return a value. It is not supposed to fail!

void sysvar_update(MYSQL_THD thd,
struct st_mysql_sys_var *var,
void *var_ptr, const void *save)
{
system_var = *(long long *)save;
status_var = system_var;
}

We update our system_var variable without any mutex protection, even though many threads may try to execute the SET statement at the same time. Nevertheless, it is safe. MySQL internally guards all accesses to global system variables with a mutex, which means we do not have to.

MYSQL_SYSVAR_LONGLONG(vars_system, system_var, 0,
"A demo system var", sysvar_check, sysvar_update,
0, 0, 123456789, 0);

This is the declaration for our system variable. It is a long long and is called vars_system. In fact as this is a variable for the vars plugin, the full name will be vars_vars_system in SHOW VARIABLES. It is associated with the system_var variable in the code, has the check function sysvar_check() and an update function sysvar_update() as defined above, and it can only take values between 0 and 123456789.

struct st_mysql_sys_var* vars_system_var[] = {
MYSQL_SYSVAR(vars_system),
NULL
};

This is the structure which stores all system variables to be passed to the declaration for this plugin. As we only have one variable we shall only include that.

struct st_mysql_daemon vars_plugin_info=
{ MYSQL_DAEMON_INTERFACE_VERSION };
mysql_declare_plugin(vars)
{
MYSQL_DAEMON_PLUGIN,
&vars_plugin_info,
"vars",
"Andrew Hutchings",
"A system and status variables example",
PLUGIN_LICENSE_GPL,
NULL,
NULL,
0x0100,
vars_status_var,
vars_system_var,
NULL
}
mysql_declare_plugin_end;

This is very similar to the declaration of our first plugin, but this one has structures for the status variables and system variable listed.

When putting our new plugin into action we should see the following:

mysql> INSTALL PLUGIN vars SONAME 'vars.so';
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW STATUS LIKE 'vars_%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| vars_status_var | 0 |
+-----------------+-------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'vars_%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| vars_vars_system | 0 |
+------------------+-------+
1 row in set (0.00 sec)

Our status and system variables are both set to 0 by default.

mysql> SET GLOBAL vars_vars_system=2384;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW STATUS LIKE 'vars_%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| vars_status_var | 2384 |
+-----------------+-------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'vars_%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| vars_vars_system | 2384 |
+------------------+-------+
1 row in set (0.00 sec)

Setting our system variable to 2384 has altered both the system variable and the status variable, so we have success!

mysql> SET GLOBAL vars_vars_system=2383;
ERROR 1210 (HY000): Incorrect arguments to SET

Our special check function works too. The variable cannot be updated to a value that is too close to its old value!

MySQL 5.1 Plugin Development Extend MySQL to suit your needs with this unique guide into the world of MySQL plugins
Published: August 2010
eBook Price: $26.99
Book Price: $44.99
See more
Select your format and quantity:

Read more about this book

(For more resources on MySQL, see here.)

A simple monitoring plugin

Our previous examples have demonstrated how to create a plugin and how to use status and system variables, but they did not do anything practically useful. The next plugin will record the connection statistics every five seconds into a log file so that load spikes can be recorded or monitored using an external application.

This plugin will remove any previous copy of the log file, create a new one, and then start a thread to retrieve the data and record it every five seconds. Upon removal of the plugin or shutdown, the plugin will record the shutdown time and close the file gracefully:

#include <string.h>
#include <mysql/plugin.h>
#include <mysql_version.h>
#include <my_global.h>
#include <my_sys.h>

#define MONITORING_BUFFER 1024

extern ulong thread_id;
extern uint thread_count;
extern ulong max_connections;

There are three internal MySQL variables we wish to monitor in our example. They are declared in sql/mysqld.cc so we need to declare them here as extern to be able to access them. The thread_id variable is used for the Connections status variable, thread_count for the Threads_connected, and max_connections is the max_connections system variable.

In theory, our plugin could read as well as alter them, and this can be a very powerful tool. But remember, with great power comes great responsibility.

static pthread_t monitoring_thread;
static int monitoring_file;

We will need these variables in our plugin. Because there can be only one monitoring file and only one monitoring thread, we can declare these variables on the global scope.

pthread_handler_t monitoring(void *p)
{

This function will be run in our monitoring thread, which is created during initialization. It has an endless loop retrieving data. This loop will sleep for five seconds, retrieve the current time and date, and then write the statistics to the file.

char buffer[MONITORING_BUFFER];
char time_str[20];
while(1)
{
sleep(5);
get_date(time_str, GETDATE_DATE_TIME, 0);
sprintf(buffer, "%s: %u of %lu clients connected, "
"%lu connections made\n",
time_str, thread_count,
max_connections, thread_id);
write(monitoring_file, buffer, strlen(buffer));
}
}

get_date() is a function that can be found in MySQL sources in mysys/mf_getdate.c. It is designed to return the current date and time in a MySQL format. Using MySQL functionality in this way is convenient, but it makes our plugin dependent on MySQL internals, which are not part of the plugin API and can change virtually anytime. Luckily, Daemon plugins already depend on the MySQL server version, they cannot be made more dependent than that. However, if a plugin is separated from the server by an API, adding such a dependency on the server internals may be undesirable.

static int monitoring_plugin_init(void *p)
{

Our initialization function has more work to do this time. We need to open the file, we are recording to and create the thread that will handle the monitoring.

pthread_attr_t attr;
char monitoring_filename[FN_REFLEN];
char buffer[MONITORING_BUFFER];
char time_str[20];

fn_format(monitoring_filename, "monitor", "", ".log",
MY_REPLACE_EXT | MY_UNPACK_FILENAME);

The fn_format() function is designed to build a filename and path compatible with the current operating system given a set of parameters. More details on its functionality can be found in mysys/mf_format.c.

In this example our output file will be called monitor.log and should be found in the data directory of your MySQL installation.

unlink(monitoring_filename);
monitoring_file = open(monitoring_filename,
O_CREAT | O_RDWR, 0644);
if (monitoring_file < 0)
{
fprintf(stderr, "Plugin 'monitoring': "
"Could not create file '%s'\n",
monitoring_filename);
return 1;
}

We wish to unlink (delete) any old file with the same filename and create a new one to write to. We could instead append and/or rotate the file, but we are aiming for simplicity in this example. If the file cannot be created then the plugin will fail with an error.

get_date(time_str, GETDATE_DATE_TIME, 0);
sprintf(buffer, "Monitoring started at %s\n", time_str);
write(monitoring_file, buffer, strlen(buffer));

A line of text is written to our new file to signify when the monitoring was started.

pthread_attr_init(&attr);
pthread_attr_setdetachstate(&attr,
PTHREAD_CREATE_JOINABLE);

Pthreads (POSIX threads) is a GNU library to control the creation and handling of threads. This initializes the new thread and sets its state as a joinable thread. This means that this thread can pass its exit status back to the main thread upon termination. We could alternatively create a detached thread here.

if (pthread_create(&monitoring_thread, &attr,
monitoring, NULL) != 0)
{
fprintf(stderr, "Plugin 'monitoring': "
"Could not create monitoring thread!\n");
return 1;
}

This creates a new thread for monitoring. If the thread creation fails, an error message is written to the error log and the initialization function returns a failure.

return 0;
}

If we have managed to get this far we should have the file successfully opened and a thread started and running.

static int monitoring_plugin_deinit(void *p)
{
char buffer[MONITORING_BUFFER];
char time_str[20];

pthread_cancel(monitoring_thread);
pthread_join(monitoring_thread, NULL);

Now that we are shutting down this plugin we need to clean things up. We start from the monitoring thread. The first function tells the thread to terminate, the second waits until it actually does.

get_date(time_str, GETDATE_DATE_TIME, 0);
sprintf(buffer, "Monitoring stopped at %s\n", time_str);
write(monitoring_file, buffer, strlen(buffer));
close(monitoring_file);

To complete the log file we write a message signifying the termination of the plugin. The file is then closed.

return 0;
}

struct st_mysql_daemon monitoring_plugin =
{ MYSQL_DAEMON_INTERFACE_VERSION };

mysql_declare_plugin(monitoring)
{
MYSQL_DAEMON_PLUGIN,
&monitoring_plugin,
"monitoring",
"Andrew Hutchings",
"Daemon monitoring example, monitors MySQL",
PLUGIN_LICENSE_GPL,
monitoring_plugin_init,
monitoring_plugin_deinit,
0x0100,
NULL,
NULL,
NULL
}
mysql_declare_plugin_end;

The plugin called monitoring is declared exactly as before, nothing new here.

When we install this plugin, run a few connections, and then uninstall the plugin, we should see in the file monitor.log something similar to the following:

Monitoring started at 2009-08-01 22:22:57
2009-08-01 22:23:02: 2 of 151 clients connected, 7 connections made
2009-08-01 22:23:07: 2 of 151 clients connected, 8 connections made
2009-08-01 22:23:12: 3 of 151 clients connected, 9 connections made
2009-08-01 22:23:17: 3 of 151 clients connected, 9 connections made
2009-08-01 22:23:22: 4 of 151 clients connected, 10 connections made
2009-08-01 22:23:27: 3 of 151 clients connected, 10 connections made
2009-08-01 22:23:32: 2 of 151 clients connected, 10 connections made
2009-08-01 22:23:37: 2 of 151 clients connected, 10 connections made
Monitoring stopped at 2009-08-01 22:23:41

System Status Variables plugin

At the end of the article, let's try to create a different Daemon plugin. This plugin, called sys_status, does not start any threads and does not do anything in the background. It uses the status variables to provide access to the getrusage() statistics. The getrusage() system call returns information about the process resource usage such as number of page faults, number of signals received, and number of context switches. How could we let MySQL users see this information?

As we remember, status variables are defined in terms of pointers to data. That is, SHOW STATUS takes the st_mysql_show_var structure and shows the data pointed to by its value member. It works well when a value to show is stored in a variable; for example, in our vars plugin, where we have stored the value in a status_vars C variable. But in this case we want to show the result of a function call. In other words, we need to use SHOW_FUNC type of a "status variable". And because we have many variables to show and all of their data are obtained from one function call, we put all of these variables in an array and use SHOW_ARRAY to display it. Let's put it all together.

We start by including all headers that we will need:

#include <mysql/plugin.h>
#include <mysql_version.h>
#include <sys/time.h>
#include <sys/resource.h>
#include <stdlib.h>

In addition to MySQL headers we will need a few system headers to be able to use getrusage(). Now we declare our status variables. We declare only one "variable" of the SHOW_FUNC type; MySQL will invoke the specified function to obtain the real status variable and we will be able to collect getrusage() data and convert it into status variables:

static struct st_mysql_show_var sys_status_var[] =
{
{"Sys", (char *) &make_var_array, SHOW_FUNC},
{0, 0, 0}
};

Let's write this function now. According to the table above, it should be declared as taking a thread context, a status variable structure to fill in, and a convenience buffer as arguments:

static int make_var_array(MYSQL_THD thd,
struct st_mysql_show_var* var,
char *buff)

In this function we will need to call getrusage() and create an array of status variables. We may as well declare needed local variables now:

struct st_mysql_show_var *status;
struct rusage *rusage;

We start by allocating the memory we need. MySQL has provided us with a buffer, and unless we need more than 1024 bytes we can simply use that buffer. However, we will need both an array of status variables and a rusage structure, which may not fit into the buffer. Also, we cannot simply malloc() the memory, because we will not be able to free it as the plugin API has no call for freeing a memory allocated in the SHOW_FUNC function. Luckily, there is a better solution. The thd_alloc() function allocates the memory in the connection's local memory pool. This memory is freed automatically at the end of the statement. We do not need to worry about memory leaks. Additionally, thd_alloc() can be much faster than malloc(). So, as rusage structure has 14 long members, we use thd_alloc() to allocate an array of 15 status variables—14 for values and one to terminate the array. We also need a memory for the rusage structure itself, but that we can safely put in the buff buffer:

status = thd_alloc(thd, sizeof(*status)*15);
rusage = (struct rusage*) buff;

Having done that, we configure our var status variable, which is of SHOW_ARRAY type, and its value points to the array to show:

var->type = SHOW_ARRAY;
var->value = (char*)status;

There is no need to set a name of the var variable—it will be ignored. MySQL will use the name that we have specified in the sys_status_var[] array for the SHOW_FUNC type. The name was "Sys" and the new type is SHOW_ARRAY. As we remember from before, it means that all variables in the array will automatically get the "Sys_" prefix.

Now, that we have prepared the array, all that is left is to fill it. Now is a good time to invoke getrusage() to grab the data. We check the return value, as the function may fail:

if (getrusage(RUSAGE_SELF, rusage) == 0)
{

And now we create status variables for every long member of the rusage structure. Basically, we will need to do something like the following and repeat it for all 14 members of the structure:

status->name = "maxrss";
status->value = (char*) & (rusage->ru_maxrss);
status->type = SHOW_LONG;
status++;

To save on typing and reduce the amount of copy-pasted code we can define a convenience macro for it:

#define show_rusage(X) \
status->name = #X; \
status->value = (char*) & (rusage->ru_ ## X); \
status->type = SHOW_LONG; \
status++;

This makes filling the array as easy as the following:

show_rusage(maxrss);
show_rusage(ixrss);
show_rusage(idrss);
show_rusage(minflt);
show_rusage(majflt);
show_rusage(nswap);
show_rusage(inblock);
show_rusage(oublock);
show_rusage(msgsnd);
show_rusage(msgrcv);
show_rusage(nsignals);
show_rusage(nvcsw);
show_rusage(nivcsw);
}

The members of the rusage structure have quite cryptic names. You can find the complete description of all information returned by getrusage() by typing man getrusage either at the shell prompt or in the Google search form.

The array is done. We just need to terminate it with a zero element—an element with no name:

status->name = 0;
return 0;
}

This is it. We declare the plugin as usual, and we are done:

static struct st_mysql_daemon sys_status =
{ MYSQL_DAEMON_INTERFACE_VERSION };
mysql_declare_plugin(sys_status)
{
MYSQL_DAEMON_PLUGIN,
&sys_status,
"sys_status",
"Sergei Golubchik",
"Export getrusage() via SHOW STATUS",
PLUGIN_LICENSE_GPL,
NULL,
NULL,
0x0100,
sys_status_var,
NULL,
NULL
}
mysql_declare_plugin_end;

Now we can build, install it, and try it out:


mysql> show status like 'sys%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Sys_maxrss | 0 |
| Sys_ixrss | 0 |
| Sys_idrss | 0 |
| Sys_minflt | 3584 |
| Sys_majflt | 0 |
| Sys_nswap | 0 |
| Sys_inblock | 0 |
| Sys_oublock | 0 |
| Sys_msgsnd | 0 |
| Sys_msgrcv | 0 |
| Sys_nsignals | 0 |
| Sys_nvcsw | 10 |
| Sys_nivcsw | 19 |
+---------------+-------+
13 rows in set (0.00 sec)

Works!

Summary

In this article we have covered:

  • A Hello World! Daemon plugin
  • A system and status variables demo plugin
  • A simple monitoring plugin
  • System Status Variables plugin

Further resources on this subject:


MySQL 5.1 Plugin Development Extend MySQL to suit your needs with this unique guide into the world of MySQL plugins
Published: August 2010
eBook Price: $26.99
Book Price: $44.99
See more
Select your format and quantity:

About the Author :


Andrew Hutchings

Andrew Hutchings has been working in the IT industry almost all his life. He started his development career programming embedded microcontrollers in assembly language for environmental monitoring systems. He then went on to systems administrator for a hosting company, in which he got his first taste for modifying the MySQL source code.

Andrew created his own business in development and DBA work, which led him to be employed by Dennis Publishing, a major UK magazine publisher. In this role as Technical Architect he designed and implemented hosting setups, acting as senior developer and DBA. This was to be his first taste of MySQL clusters and led to his development of ndb_watch, a cluster monitoring daemon.

After gaining Zend Certified Engineer and Certified MySQL Database Administrator qualifications he gained a position at Sun Microsystems as a MySQL Support Engineer. This involves working daily with the MySQL source code and customer API code to help diagnose and fix issues. He is now a specialist in MySQL Cluster and C APIs.

In his spare time Andrew is a community contributor to MySQL with feature patches and bug fixes in both 5.1 and 6.0. He has also contributed to the Drizzle project, a fork of MySQL aimed at having a clean micro-kernel core, committing a significant amount of code to the project.

Books From Packt


MySQL Admin Cookbook
MySQL Admin Cookbook

Mastering phpMyAdmin 3.1 for Effective MySQL Management
Mastering phpMyAdmin 3.1 for Effective MySQL Management

High Availability MySQL Cookbook
High Availability MySQL Cookbook

MySQL for Python: Database Access Made Easy
MySQL for Python: Database Access Made Easy

Creating your MySQL Database: Practical Design Tips and Techniques
Creating your MySQL Database: Practical Design Tips and Techniques

Alfresco 3 Web Content Management
Alfresco 3 Web Content Management

WordPress Top Plugins
WordPress Top Plugins

TYPO3 4.3 Multimedia Cookbook
TYPO3 4.3 Multimedia Cookbook


No votes yet

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
K
9
Z
N
q
C
Enter the code without spaces and pay attention to upper/lower case.
Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software