Thursday, October 15, 2009

Using MySQL User-Defined Functions (UDF) to get MySQL internal informations

In one of my previous posts I was writing about how to read other processes memory [1]. As an example I tried to get the value of the hard coded MySQL internal InnoDB variable spin_wait_delay (srv_spin_wait_delay).

In this example we were using gdb or the operating system ptrace function to retrieve this value. This method has the disadvantage that it is pretty invasive.

When I was working on a customer support case I had the idea to solve this by the much less invasive method of User-Defined Functions (UDF).

UDF were introduced in MySQL 5.0 [2]. They provide the feasibility to enlarge the MySQL functionality by adding external code.

The clue is now that you also can use this external code to do some MySQL internal stuff.

My idea was now, instead of using gdb/ptrace to get the value of spin_wait_delay, to write and UDF to get and set this value.

More details about the UDF itself, how to compile and load it you can find on my website [3].

Then the UDF has to be loaded and activated in the database:

mysql> CREATE FUNCTION spin_wait_delay RETURNS INTEGER SONAME "udf_spin_wait_delay.so";

To remove the UDF again you can use the following command:

mysql> DROP FUNCTION spin_wait_delay;

To check if an UDF is installed or to see which ones are installed the following command gives you the right answer:

mysql> SELECT * FROM mysql.func;
+-----------------+-----+------------------------+----------+
| name            | ret | dl                     | type     |
+-----------------+-----+------------------------+----------+
| spin_wait_delay |   2 | udf_spin_wait_delay.so | function |
+-----------------+-----+------------------------+----------+

When the UDF is compiled and properly loaded into the database you can get the value of spin_wait_delay as follows:

mysql> SELECT spin_wait_delay();
+--------------------+
| spin_wait_delay(5) |
+--------------------+
|                  5 |
+--------------------+

And now the real nice thing is that you can even set this value as follows:

mysql> SELECT sping_wait_delay(8);
+--------------------+
| spin_wait_delay(8) |
+--------------------+
|                  8 |
+--------------------+

With this function we can make a static hard coded InnoDB value dynamically changeable. To make it permanent also after a database restart possibly the functionality of init_file could help you further [4].

With this concept we can think about implementing many missing things without touching the MySQL code itself or recompiling MySQL. Please let me know what is missing in your opinion and I can try to implement it. Because I am not a programer the help of those guys would be very appreciated.

If anybody sees a problem with this method please let me know. I do not know about such things like thread safe and mutexes etc. But I think at least reading should not harm.

Caution: When you have a crash in your UDF the whole MySQL server will crash. So be careful and test it intensively!

Binary

udf_spin_wait_delay.so (md5 807c6bc09b5dc88a8005788519f2483a)

8 comments:

Shinguz said...

Andrew pointed me to the following blog entry of Brian: Daemon Example (deamon plugin)
http://krow.livejournal.com/481313.html
Thanks!

Mark Robson said...

I also tried to use mysql UDFs to call internal MySQL APIs. It works.

All you really have to do is include the relevant headers, call the appropriate functions, and it just works.

But it is a bit dangerous, as your compiled UDF will then be dependent on the *exact* MySQL version that you're using.

Shinguz said...

Hi Mark,

Thanks a lot for your feedback. And how is the correct way to make it version IN-dependent and thus better portable?

papagnome said...

Just FWIW, UDFs existed in 4.1 and I'm pretty sure in 4.0 as well.

Thanks for the nice writeup!

Shinguz said...

Hi Papagnome,

You are absolutely right, UDF's came in in 3.23 already (I was told).
What was in my mind was more the pluggable stuff. This feature is very nice an pretty new. But somehow I did not manage to express what I wanted to... :)

Regards,
Oli

Shinguz said...

I was told that spin_wait_delay is exposed to public with the InnoDB pluggable storage engine 1.0.4. Seems I have to look for an other hidden variables now. :)

Jo said...

I wrote an interesting UDF you may be interested. JsMap - running JavaScript in MySQL

mygamebest said...
This comment has been removed by a blog administrator.