Sun/MySQL

Today I spent the larger part of the afternoon at the Sun office in Amersfoort. And,

Yes, I signed ;-)

and joined the now largest open source company in the world.

UDFs at the MySQL User's conference

The MySQL User's conference will be held in less than a month from now!!!

This year there is quite a good number of sessions on adding your own functions and procedures, such as:

• Advanced Stored Procedures


• A Tour of External Language Stored Procedures for MySQL


• Code Generators for MySQL Plugins and User Defined Functions (UDFs)


• Extending MySQL


• Using User-defined Functions and Aggregates to Speed Up Your Data Warehouse Processing


• Writing MySQL user-defined functions

I will be doing the 3 hour tutorial on writing user-defined functions, and I am currently adding the last few final touches to my slides.

My tutorial will be very much a hands-on experience. The ambition is to allow people with some programming skills in either C/C++, PHP or Java to leave the room with a bunch of UDFs they created themselves during one of labs. With that and the supporting materials (slides and a handout with detailed instructions) the attendees will be able to write UDFs themselves, and have the knowledge that allows them to make a sensible decision when they have to choose between stored SQL functions, UDFs or raw expressions built of built-in functions.

Allow me to tell you a bit about UDFs - you might decide you want to take my tutorial ;)

User-defined functions

User-defined functions or UDFs are often confused with Stored SQL functions but unrightly so. Other than the fact that stored functions and user-defined functions can be created by the user (as opposed to hard-wired, built-in functions), they have little in common. If you want to know exactly what the difference is and what the strengths and weaknesses are of either feature, my tutorial is for you.

Features

For now I don't want unveil too much but I think that it might be good to point out a number of key advantages of using UDF's over stored SQL functions:

• Flexible argumentlists - UDFs allow a flexible number of dynamically typed arguments, and it is possible to identify arguments by name (rather than position).


• The ability to leverage the functionality from external libraries - UDFs can be linked to existing libraries in order to expose their functionality to SQL statements


• Aggregate functions - UDFs can compute a value for a collection of rows just like the built-in functions COUNT and GROUP_CONCAT

Neither of these features is available to stored SQL functions, and a generic workaround is simply impossible or far from trivial. So - UDFs might be for you if you need one of these things.

Performance

Another reason to use UDFs might be performance. UDFs are much, much better than stored SQL functions when it comes to computation. To prove this fact, I'd like to share a number of benchmarks I did.

For the benchmarks I use a stored procedure like this:

create procedure sp_<SOME-NAME>_benchmark(p_num int unsigned)
begin
    declare v_num int unsigned default 0;
    declare v_return <SOME-DATA-TYPE>;
    declare v_begin int unsigned default unix_timestamp();    
    while v_num < p_num do
        set v_return := <SOME-EXPRESSION>;
        set v_num := v_num + 1;
    end while;
    call sp_store_function_benchmark(
        '<SOME-NAME>'
    ,   p_num
    ,   unix_timestamp() - v_begin
    );
end;

As you can see, the procedure repeats the evaluation of <SOME-EXPRESSION>, depending on the specified number of iterations passed to the parameter p_num. Depending on the benchmark, an expression is plugged in place of <SOME-EXPRESSION>. The variable v_return is used to capture the evaluation result, and a suitable data type is used in place of <SOME-DATA-TYPE>. Before running the loop, the time is recorded. After the loop, the elapsed time, the number of repetitions and the benchmark name are stored in a table for later analysis. This allows me to test a series of increasing repetitions of the same expression.

I also use a sp_noop_benchmark() procedure that is identical to the one described above, except that it omits the assignment of the expresion. So, it lacks this line:

        set v_return := <SOME-EXPRESSION>;

This allows us to isolate the time spent on evaluating the expression and doing the assignment. I also use another control measurement that uses an assignment like this:

        set v_return := <SOME-LITERAL-VALUE>;

This allows us to estimate the time spent on the value assignment, which can be used to get a bit closer to the actual time spent only on expression evaluation alone. (Of course, this assumes that the time spent on the assignment alone is comparable for expressions, function calls and literals).

All these measurements were made by comparing single, complete calls many times. All tests were done on Ubuntu Gutsy Gibbon using MySQL 5.1.23. Compilation of UDFs was performed using gcc version 4.1.3 20070929 (prerelease) (Ubuntu 4.1.2-16ubuntu2). No optimizations were employed.

Stored functions and UDFs

To compare UDFs and Stored functions, I first used simple "Hello World" expressions that all evaluate to a VARCHAR(14). This was measured:

• String literal sp_string_benchmark. The expression was:

  v_return := 'Hello, String!';



• Stored SQL function sp_ssf_benchmark. The expresion was:

  v_return := ssf_hello_world();

  The ssf_hello_world() itself was:

  
  create function ssf_hello_world()
  returns varchar(14)
  return 'Hello, SSFs!!!';



• User defined function sp_udf_benchmark. The expression was:

  v_return := udf_hello_world();

  The UDF itself was:

  
  my_bool udf_hello_world_init(
    UDF_INIT *initid, UDF_ARGS *args,
    char *message
  ){
    return 0;
  }
  char *udf_hello_world(
    UDF_INIT *initid, UDF_ARGS *args
  , char* result, unsigned long* length
  , char *is_null, char *error 
  ){
    *length = 14;
    return "Hello, UDFs!!!";
  }

Here are the raw results:

+----------+------+--------+-----+-----+
| repeated | noop | string | UDF | SSF |
+----------+------+--------+-----+-----+
|        0 |    0 |      0 |   0 |   0 |
|   500000 |    3 |      4 |   6 |   8 |
|  1000000 |    7 |      9 |  10 |  16 |
|  1500000 |   10 |     13 |  16 |  25 |
|  2000000 |   13 |     17 |  21 |  33 |
|  2500000 |   16 |     22 |  25 |  42 |
|  3000000 |   19 |     27 |  31 |  50 |
|  3500000 |   23 |     31 |  36 |  58 |
|  4000000 |   26 |     36 |  41 |  67 |
|  4500000 |   30 |     40 |  45 |  75 |
|  5000000 |   33 |     44 |  51 |  83 |
|  5500000 |   36 |     49 |  55 |  90 |
|  6000000 |   39 |     53 |  61 |  99 |
|  6500000 |   42 |     57 |  66 | 107 |
|  7000000 |   45 |     62 |  71 | 115 |
|  7500000 |   49 |     66 |  76 | 123 |
|  8000000 |   52 |     71 |  81 | 131 |
|  8500000 |   56 |     75 |  86 | 140 |
|  9000000 |   58 |     80 |  90 | 147 |
|  9500000 |   62 |     83 |  96 | 156 |
| 10000000 |   64 |     88 | 101 | 164 |
+----------+------+--------+-----+-----+

And here is a graph:

At a glance we see that doing nothing is the fastest, followed by the assignment of a literal string. After that, comes the UDF and the stored SQL function is by far the slowest. Another thing that is immediately apparent is that the execution time increases proportionally as the number of repetitions is increased. This means that we can essentially forget the entire series and focus on the measurements with the highest number of repetitions (which is presumably more accurate than the earlier measurements).

But exactly how much faster is the UDF as compared to the SQL function? Well, it depends on how you look at it. If we just divide the raw execution time of the stored function by that of the UDF, we see that the ratio is:

164/101 = 1.60

This would tempt us into thinking that UDF's are about 60% faster.

However, when we calculate it like this, we are also counting the overhead of the benchmark procedures themselves. We get a better result if we focus on only the expression assignments. We can express the performance in terms of the time required to execute the no-operation benchmark. So:

noop / udf = 64 / 101 = .63

and

noop / ssf = 64 / 164 = .39

This correction indicates that the UDF performs at 63% of the no-operation benchmark, whereas the stored SQL function performs at 39% of the no-operation benchmark. We could say that relative to the no-operation benchmark, UDFs are 24% faster as compared to SQL functions

Now we could stretch it a bit more and try to isolate only the time spent on evaluating the functions. To do that, we'd have to assume that "assignment" costs the same, no matter if we are assigning from a stored SQL function, user-defined function or string literal. If we do that, we get this figure:

literal / udf = 88 / 101 = .87

and

literal / ssf = 88 / 164 = .54

This correction indicates that the UDF performs at 87% of the string-literal benchmark, whereas the stored SQL function performs at 54% of the string-literal benchmark. This would indicate that relative to the string literal assignment, UDFs are 33% faster than SQL functions.

Now, I can imagine that a lot of people will have some reservations against this method of correcting the measurements. Personally I think the method is valid, although the result itself is not that useful. I mean, in real life, the fact is that we will be assigning the value of the function, and from that perspective it doesn't help us much to know how fast it would have been if we didn't perform an assignment. Another reservation that we may have is that this benchmark still doesn't tell us much more than the relative differences in overhead. Basically, both the UDF and the stored function are empty - so this benchmark can tell us nothing about performance in a more realistic case where the function is actually doing some work.

Addition

In an attempt to measure at least some basic processing I decided to benchmark addition too. I took a INTEGER as data type and did the following benchmarks:

• Integer literal: sp_num_benchmark with the expression:

  v_return := 3;



• Addition operator: sp_opr_benchmark with the expression:

  v_return := 1+2;



• UDF: sp_udf_benchmark with the expression:

  v_return := UDF_ADD(1,2);

  The code for the UDF is

  my_bool udf_add_init(
    UDF_INIT *initid
  , UDF_ARGS *args
  , char *message
  ){
    if(args->arg_count!=2){
      strcpy(message, "Require two arguments");
      return 1;
    } else {
      args->arg_type[0]= INT_RESULT;
      args->arg_type[1]= INT_RESULT;
    }
    initid->maybe_null= 1;
    return 0;
  }
  
  long long udf_add(
    UDF_INIT *initid
  , UDF_ARGS *args
  , char *is_null
  , char *error 
  ){
    if((args->args[0]==NULL)||(args->args[1]==NULL)){
      *is_null= 1;
      return 0;
    } else {
      return (*((long long*)args->args[0])) + (*((long long*)args->args[1]));
    }
  }
  



• Stored SQL function: sp_ssf_benchmark with the expression:

  v_return := SSF_ADD(1,2);

  The code for the function is

  
  create function ssf_add(l int, r int)
  returns int
  return l+r;
  

Here is the raw data:

+--------------+------+-----+-----+-----+-----+
| repeat_count | noop | num | opr | udf | ssf |
+--------------+----- +-----+-----+-----+-----+
|            0 |    0 |   0 |   0 |   0 |   0 |
|       500000 |    3 |   5 |   5 |   6 |   9 |
|      1000000 |    7 |   9 |   9 |  11 |  18 |
|      1500000 |   10 |  14 |  13 |  16 |  27 |
|      2000000 |   13 |  19 |  18 |  21 |  35 |
|      2500000 |   17 |  23 |  22 |  26 |  47 |
|      3000000 |   20 |  27 |  27 |  33 |  56 |
|      3500000 |   23 |  33 |  33 |  38 |  62 |
|      4000000 |   27 |  38 |  36 |  43 |  74 |
|      4500000 |   30 |  42 |  42 |  50 |  83 |
|      5000000 |   34 |  46 |  47 |  54 |  91 |
|      5500000 |   36 |  51 |  60 |  68 | 108 |
|      6000000 |   42 |  56 |  59 |  67 | 115 |
|      6500000 |   45 |  60 |  62 |  71 | 123 |
|      7000000 |   47 |  65 |  69 |  78 | 132 |
|      7500000 |   50 |  67 |  71 |  84 | 143 |
|      8000000 |   54 |  72 |  78 |  89 | 149 |
|      8000000 |   54 |  73 |  78 |  89 | 149 |
|      8500000 |   59 |  78 |  79 |  92 | 157 |
|      9000000 |   60 |  85 |  85 |  99 | 168 |
|      9500000 |   65 |  89 |  92 | 104 | 178 |
|     10000000 |   67 |  91 |  95 | 111 | 189 |
+--------------+------+-----+-----+-----+-----+ 

And here is the graph of the results:

We can immediately see that the result is quite similar to what we saw in the previous measurements. Let's look at the performance relative to assigning a literal integer:

    int / opr = 91 / 95 = 0.96

and

    int / udf = 91 / 111 = 0.82

and

    int / ssf = 91 / 189 = 0.48

So, A direct addition operator is less than 5% slower than a literal assignment. The UDF 18% slower than the literal assignment and 14% slower than the addition operator. The stored SQL function is quite a good deal slower: 48% slower than the direct addition operator and 34% slower than the UDF. If we want to compare only UDFs vs stored SQL functions, we should compare relative to the addition operator, which gives a minutely different result:

    opr / udf = 95 / 111 = 0.83

and

    opr / ssf = 95 / 189 = 0.5

Built-in functions and UDFs

While I was busy doing benchmarks, I also figured that it'd be nice to figure out how UDFs and built-in functions compare performance-wise. To make things slightly more realistic than simply returning a value, I chose to implement my own version of CONCAT().

Because CONCAT can take on a number of arguments I decided to measure the effect of a varying number of arguments too. I ended up choosing VARCHAR(10) for the data type, and tested these different benchmarks:

• Built-in function sp_bif<1..10>_benchmark - where <1..10> is a number from 1 to 10, and where the expression was:

  v_return := CONCAT(0[,1[,...,9]);

  That is, 10 different CONCAT measurements, from CONCAT(0) to CONCAT(0,1,2,3,4,5,6,7,8,9)


• UDF sp_udf<1..10>_benchmark - where <1..10> is a number from 1 to 10, and where the expression was:

  v_return := UDF_CONCAT(0[,1[,...,9]);

  That is, 10 different measurements of my UDF implementation of concat, from UDF_CONCAT(0) to UDF_CONCAT(0,1,2,3,4,5,6,7,8,9).The code for my UDF_CONCAT is:

  
  my_bool udf_concat_init(
    UDF_INIT *initid, UDF_ARGS *args
  , char *message
  ){
    int i;  
    size_t bytes = 0;
    for(i=0;iarg_count; i++){
      args->arg_type[i] = STRING_RESULT;
      bytes += args->lengths[i];
    }
    if(!(initid->ptr= malloc(bytes))){
      strcpy(message, "Error allocating memory.");
      return 1;
    } else {
      return 0;
    }
  }
  
  char *udf_concat(
    UDF_INIT *initid, UDF_ARGS *args
  , char* result, unsigned long* length
  , char *is_null, char *error 
  ){
    int i;  
    char *buff = initid->ptr;
    *length = 0;
    for(i=0;iarg_count; i++){
      if(args->args[i]==NULL){
        *is_null = 1;
        break;
      } else {
        memcpy(buff + *length, args->args[i], args->lengths[i]);
        *length += args->lengths[i];
      }
    }
    return buff;
  }
  
  void udf_concat_deinit(
    UDF_INIT *initid
  ){
    if(initid->ptr){
      free(initid->ptr);
    }
  }
  




I also measured the same string literal assignment and the noop benchmarks as control measurements. I won't give the raw results here - send me an email or post a comment if you'd like to have them. I do have a graph of the result here:
Note that for this graph I already subtracted the no-operation benchmark. This was done to make it easier to examine the data sets of the UDFs and Built-in functions. The graph does include the literal string benchmark, which is the orange line nearest to the X axis.

Now, what do we see here apart from the control benchmark? Basically, we see two bundles of series. The bundle nearest to the X axis corresponds with the built-in CONCAT() benchmarks. The bundle above that corresponds to the UDF benchmarks. So, this tells us that on every occasion, the built-in CONCAT() was faster than the UDF.

We can also see that there is a relatively small but measurable effect for passing more arguments. The calls with fewer arguments are consistently faster than the calls with more arguments within both the group of built-in and UDF calls.

Frankly, I didn't expect to see this. I had imagined that the effect of passing more arguments would be larger, so I expected to see pairs of UDF/Built-in function calls having the same number of parameters. Clearly, I was wrong.

So how much faster are UDFs? Well, if we look at the raw ratios of execution times for the largest number of repetitions, we see:

UDF_CONCAT(0) / CONCAT(0) = 91 / 107 = .85

Interestingly, if we compare that for the calls with 10 arguments we see the same ratio:

UDF_CONCAT(0,1,2,3,4,5,6,7,8,9) / CONCAT(0,1,2,3,4,5,6,7,8,9) = 91 / 107 = .85

So, UDF_CONCAT() would seem to be 15% slower than the built-in CONCAT(). Interestingly, there seems to be no difference between the call with the larger number of arguments, indicating that the performance impact of passing another argument is about the same for UDFs as it is for built-in functions.

What happens if we express the execution time relative to the no-operation benchmark? Well, we get:

noop / UDF_CONCAT(0)                     = 64 / 107 = .60
noop / CONCAT(0)                         = 64 /  91 = .70

and

noop / UDF_CONCAT(0,1,2,3,4,5,6,7,8,9)   = 64 / 122 = .52
noop / CONCAT(0,1,2,3,4,5,6,7,8,9)       = 64 / 104 = .62

Here we see that relative to the no-operation benchmark, the UDF is 10% slower than the built-in function. We could say that 10% is quite a lot, but it is more than half as large as the difference between UDFs and stored SQL functions.

We see something interesting if we look at the execution time relative to the execution time spent on the string literal assignment:

string / UDF_CONCAT(0)                   = 88 / 107 = .82
string / CONCAT(0)                       = 88 /  91 = .87

and

string / UDF_CONCAT(0,1,2,3,4,5,6,7,8,9) = 88 / 122 = .72 
string / CONCAT(0,1,2,3,4,5,6,7,8,9)     = 88 / 104 = .85

Here, the difference between the single argument calls is only 5%, wereas the difference becomes more 12% for the calls with 10 arguments. This is interesting because in the comparison with the no-operation benchmark we did not detect a difference caused by the the amount of arguments.

My current suspicion is that we are actually witnessing the effect of the length of the left hand expression in the assignment operation. Our string literal is a VARCHAR(14), and it should probably be a CHAR(1) for the comparison to the single argument calls and a CHAR(10) for the comparison with the 10 argument calls.

Conclusion

The UDFs I tested are

• somewhere around and between 25% to 33% faster than stored SQL functions


• somewhere around and between 5% to 20% slower than built-in functions

Another interesting finding is that argument passing seems to have about the same impact on UDFs as it has on built-in functions. A single argument seems to cost about 1% of the function performance in both cases. For stored SQL functions no such data is currently available.

Discussion

Currently, all functions call are single complete function calls. That means that for UDFs, the initialization function and the row-level function are both called exactly once per UDF instance. When using UDFs in multi-row SQL statements, the initialization function will be called only once per occurence of the UDF, and the row-level function will be called for each row. For functions where the initialization function is relatively expensive, this means that the performance observed in these benchmarks is probably poorer that it can be. A new set of benchmarks should be done to measure the performance of functions in multi-row SQL statements.

Finally

So, are you interested? If you are, go and register for the conference. You can contact me and get a 20% discount! Of course I'd love to see you attend my tutorial too!

See you at the conference!

MySQL Stored Procedures: CASE syntax

Thank you all for taking the time to respond to the little challenge I posted yesterday! I am pleasantly surprised to note that so many people took the time to post a solution. And most people provided the correct answer too: you are all entitled to a well deserved discount to register for the MySQL User's conference!!!

For those of you interested in the solution: there are two different forms of the CASE statement syntax: the so-called simple case and the searched case.

The simple case selects one WHEN...THEN branch by comparing the value of the expression that appears after the CASE keyword with the value of the expressions given in each of the WHEN...THEN branches. It enters the first branch found where the values are equal to one another:

CASE expr
    WHEN expr1 THEN ...statements...
    ...    
    WHEN exprN THEN ...statements...
    ELSE ...statements...
END CASE

The searched case syntax simply chooses the first WHEN...THEN branch for which the condition appearing after the WHEN keyword is TRUE:

CASE 
    WHEN cond1 THEN ...statements...
    ...    
    WHEN condN THEN ...statements...
    ELSE ...statements...
END CASE

The p_find_slash procedure uses a simple case but accidentally used conditions the WHEN...THEN branches:

CASE v_char
    WHEN v_char = '/' THEN ...
    ...
END CASE

What many people don't realize is that syntactically this is perfectly valid. That's because to MySQL, those conditions are simply particular types of expression. It's just that their value will be either 0 or 1 depending on whether the condition holds FALSE or TRUE respectively (consequently, the built-in constants FALSE and TRUE are in fact synonyms for 0 and 1 respectively).

I already emailed a few of you, so if you didn't yet receive an email from me, send me your email address and I'll make sure you get the discount code. I can be reached via email:

Roland dot Bouman at gmail dot com

See you at the UC!

MySQL stored procedurs: ...the CASE that they gave me...

Let's see if you can solve this little puzzle...

Consider this stored procedure:

-- finds the first slash and exits
create procedure p_find_slash(p_text text)
begin
    declare v_index int default 1;
    declare v_length int default character_length(p_text);
    declare v_char char(1);
    
    _main_loop: while v_index <= v_length do                       -- loop over all characters
                
        set v_char := substring(p_text, v_index, 1);               -- grab the current character
        case v_char
            when v_char = '/' then                                 -- found a slash!                
                select concat('A slash at ', v_index) message;     -- report it
                leave _main_loop;                                  -- and then stop
            else                                                   
                select concat(v_char, ' is not a slash.') message; -- not a slash...               
        end case;        
        set v_index := v_index + 1;                                -- next character pls
    end while;
end;

Of course, it's a bogus stored procedure, but that's not the point right now. Most people can guess what the intention is of this procedure: it should examine each character in the argument text, check if it is a slash, and if so, report its position and then stop execution. If the character is not a slash, the procedure moves on to the next character.

So, what do you expect when we call it with only a literal slash as argument? Let's find out:

call p_find_slash('/');

Well? It may come as a surprise to some, but this is the result:

+-------------------+
| message           |
+-------------------+
| / is not a slash. |
+-------------------+

So, can you explain this result? Can you fix it?

Just leave a comment to this post with your explanation and the solution. Results published later this week....and oh!! If you know how to fix this, maybe you're ready to move on to the next level and should attend the "Advanced Stored Procedures" tutorial by Mariella Di Giacomothe at the MySQL User's Conference. Like Giuseppe Maxia wrote earlier, you can earn a 20% discount code by asking a speaker!!

So that is the return: if you post your solution as a comment on this blog, I'll make sure you'll get that code for a 20% discount.

Online VMWare image Creator Service

Note to self: don't hack up .vmx files manually anymore. Instead, generate the entire image including vmdk's here: http://www.easyvmx.com/

c,mm,n - Open Source defining the future of Mobility

Today was at the faculty of Industrial Design in Delft to attend the first 'c,mm,n Garage' event. c,mm,n - pronounced as common - is a project to develop sustainable mobility. It is led by Stichting Natuur en Milieu, The Netherlands Society for Nature and Environment and currently the main participants are students from a number of Dutch technical universities.

Right now, engineering efforts are focused on a clean, remanufacturable car that runs on electricity generated by a hydrogen fuel cell:
The car is almost completely built out of biodegradable plastics, making it extremely light and environmentally friendly.
There is a YouTube Video available that shows off the exterior design and which gives you a good impression of how open and spacious the design is. For example, the windshield extends very far to the front, giving somewhat the impression if sitting inside a helicopter.

One of the coolest things of the project is that it is open source. For example, the car's blueprints, composition of materials, construction of components, etc. is released under an open source license. (Not sure which one exactly). Check out the Developer's Wiki.

Another cool thing of this project is that it doesn't just focus on products like vehicles. Instead, the people involved in the project are trying to define mobility services (the lease company Athlon is already experimenting with this), and also how community participation can change the ways in which we are mobile.

The idea behind a 'mobility community' is something that very much intrigues me. It is kind of hard to explain the ambitions of the project, as they are very far-stretching. But let me try and give a tangible example.

One of the things that will be implemented in this car is that it will have on-board internet access to exchange routes with other people in your part of the mobility community. Through the network, you can catch up with each other and participate in a so-called 'platoon': basically, several cars form a train, which is controlled by the car in front. The other cars switch on an automatic pilot, allowing the fellow travelers to stop driving and do some work or chat or whatever. The interior of the car is designed in a manner that it allows the seats to rotate in order to support this.

Allowing the other drivers to do something more useful than driving is of course great in itself, but what is really terrific is that the car is designed so that riding in a platoon will actually reduce fuel consumption even more. So it is not only convenient, it will actually be more efficient. I can see all kinds of applications for this platoon-riding. Think of a taxi service or hotel shuttle service...Instead of being cramped up with many people in one van, dropping off everybody sequentially, you can now leave as train (or even form one as you are going) and let the cars with individual destinations bud off the train, taking those passengers to the exact desired location, after which the cars travel back to join a new train that's underway.

Reporting MySQL Internals with Information Schema plug-ins

Last week, I described how to use the MySQL plug-in API to write a minimal 'Hello world!' information schema plug-in. The main purpose of that plug-in is to illustrate the bare essentials of the MySQL information schema plug-in interface.

In this article, I'd like to take that to the next level and demonstrate how to write an information schema plug-in that can access some of the internals of the MySQL server. For this particular purpose, we will focus on a plug-in that reports all the SAVEPOINTs available in the current session. This MYSQL_SAVEPOINTS plug-in may be of some value when debugging scripts and stored routines that rely on complex scenarios using transactions and savepoints.

In a forthcoming article, I will describe a few information schema plug-ins that are arguably more interesting, such as a plug-in to list the currently existing TEMPORARY tables, user-defined variables, and the contents of the query cache. Although the plug-in described in this article may be of some use, its main purpose is to illustrate the minimal requirements for plug-ins that can access the server's internals.

A Quick Recapitulation

You might recall that:

• The MySQL plug-in API is one of the new features in MySQL 5.1, and forms a generic extension point of the MySQL database server, allowing privileged database users to add functionality to the MySQL Server by loading a shared library from the plug-in directory


• Loading and unloading a plug-in is a completely dynamic process controlled using the INSTALL PLUGIN and UNINSTALL PLUGIN syntax, and does not involve compiling the server or even restarting it


• There are several types of plug-ins, the most well-known being storage engines and full-text parsers. Less well-known types include information schema and daemon plug-ins.


• An information schema plug-in provides the implementation of a table (or actually, a system view) in the information_schema database


• Plug-ins are usually implemented in C/C++. To implement a plug-in, the implementor must include the header file plugin.h and provide an initialized instance of the st_mysql_plugin structure. In addition, the implementor must provide code to implement the plug-in type dependent part of the interface


• The plug-in type dependent part of the interface for information schema plug-ins consists of two things: the column definitions of the information schema table and a fill_table function that is called whenever the server wants to retrieve the rows of data from that table.

How Information Schema plug-ins can access MySQL Server internals

Before we discuss the MYSQL_SAVEPOINTS information schema plug-in in detail, let's take a look at the way information schema plug-ins can obtain access to the internals of the MySQL server.

Like I just recapitulated from last week's article, the plug-in type dependent inferface for information schema plug-ins consists of two things:

• An array of ST_FIELD_INFO structures, each of which defines a column of the information schema table
• A fill_table function that is called by the server when it needs to retrieve the data from table

The column definitions may be considered a static part of the interface - they simply define the structure of the table - no more, no less. The fill_table function is a different matter. Let's take a look at the signature of the signature of that function:

int fill_table(THD *thd, TABLE_LIST *tables, COND *cond);

The TABLE_LIST *tables argument provides the handle to the information schema table that is being filled, and the COND *cond argument represents the WHERE condition of the SQL statement that is currently being handled, allowing the fill_table function to directly filter rows (instead of relying on the query execution engine to do that). As such, these arguments are occupied with the actual delivery of rows of data to the server.

The first argument to the fill_table function offers all kinds of interesting opportunities to see what is going on inside the server. We will discuss it in more detail in the next section.

Public accessors to the current THD instance

The first argument to the fill_table function is THD *thd. This is the so-callled thread descriptor - something that is best thought of as a handle to the current session. Note that in a MySQL context, the terms connection, thread and session are often used interchangeably. However, I find the term thread too broad, and the term connection too narrow. As there are many parts in THD that maintain state regarding the events that occurred since a connection is established, it seems most sensible to think of THD as the server-side implementation of a session.

This session handle or thread descriptor has the form of a pointer to an instance of the THD class. The plugin.h header file contains a forward declaration to this class, but the actual declaration is contained in sql/sql_class.h. The THD class is one of the key data structures in understanding the workings of the MySQL server as it is passed as an argument to many internal server functions. Consequently it provides a wealth of possibilities to create interesting new information schema plug-ins. In fact, the number of possibilities are so great, that a number of common usages has been explicitly set aside in the plugin.h header file.

The plugin.h header file contains a number of function declarations and macros that provide access to the members of a THD instance. I will not discuss all of them here, but highlight just a few just to give you an idea:

• thd_test_options() - Find out which options are set. This can be used to find out whether a number of boolean options like big_tables, (general and binary) logging, and autocommit are enabled or disabled.


• thd_proc_info() - Should be used by the plug-in implementor before starting a potentially time-consuming operation so the rest of the world can monitor what this session doing. The code set here corresponds to the value reported in the State column by the SHOW PROCESSLIST statement


• thd_killed() - Can be used by the plug-in implementor to find out if the thread in which this session lives was killed. If the plug-in is involved in a potentially time-consuming process, the plugin-in implementor should periodically check this and gracefully abort the plugin-ins work when it detects that the thread was killed.


• thd_alloc() - Allocates some memory from this session's memory pool. If the plug-in requires some small amount of memory, plug-in implementors should use this rather than the standard malloc() function. Calling thd_alloc(); is likely to be faster because it takes memory out of a pre-allocated pool, reducing contention. In addition, it is more convenient because the memory need not be explicitly freed: it is automatically reclaimed by the pool after handling the current statement.

If you are interested in seeing all these declarations, just open plugin.h and look for comments like this:

/*************************************************************************
  Miscellaneous functions for plugin implementors
*/

plugin.h describes a public interface

In plugin.h, the declarations as described in the previous section together form a public interface to the current session. They are there for the convenience of plug-in implementors and represent a 'safe' way to work with the THD pointer passed to the fill_table function.

To say that these form a public interface is to stay that these are officially supported by MySQL AB. That is: they will be supported officially once the MySQL 5.1 Server is a generally available release. From that point on you can rely on these functions when writing plug-ins in the sense that you do not have to be afraid that they will change. At least, the public interface will remain the same for all forthcoming builds of the 5.1 server. Any interface changes in future releases will involve a proper process, giving everybody the chance to update their code well in time.

Unfortunately, not every function declaration in plugin.h has source code comments. This means that for now, you sometimes need to do some digging in the server's source code to find out what you can do with them. I admit that this situation is not exactly perfect. However, the matter has been reported as a bug, and hopefully, it will be adressed soon.

Beyond the public interface

I just described the public interface plug-in implementors can rely on. A distinct advantage of the public interface is that it takes away a lot of the complexity of the underlying internals of the MySQL Server. However, there will always be cases where the public interface does not offer the features you really need. In those cases, you simply need to be able to work directly on the server internals.

The advantage of directly referencing the server's internals is that you can access all the interesting nuts and bolts and bits and pieces. The downside is that there is absolutely no guarantee that your code will work in another version of the server. The internals are by definition the parts that are not meant to be exposed. As such, it is possible that your code does not work or behaves unexpectedly in another version of the server.

Let's not dwell too long on the disadvantages. Instead, let's focus on the merits of pluggable information schema tables. Granted, it is inconvenient that we may need to make our code resilient to each different build of the server. However, for many applications, it is not very likely that we have to constantly do that.

Even if we do have to change our code, the burden will be on the developer of the plug-in. For each specific build of the server, your code may need to be different. Even if the code itself does not change, you will probably at least have to recompile your plug-in for each specific build of the server. However, your users are still not required to recompile the server itself. They can still install the plug-in without stopping or restarting the server, which in many cases seems more important than bearing the burden of changing the code.

You need to break some eggs to bake an omelet - so if you're hungry, you better get over it and start breaking some eggs ;-)

The MYSQL_SERVER define

In order to access the server's internals beyond the public interface, we need to use some C/C++ preprocessor magic and define MYSQL_SERVER. This define needs to be present before we include any MySQL header (or source) files:

#ifndef MYSQL_SERVER
#define MYSQL_SERVER
#endif

Throughout the MySQL codebase, there are many sections that are conditionally included or excluded depending on whether MYSQL_SERVER is defined. It is hard to pinpoint the exact effect of adding this definition, because there many spots that use this definition to control conditional compilation.

Normally the MYSQL_SERVER definition need be present only when compiling the server proper, but in this case we need it to let the plug-in code work with internal structures such as THD instances directly, that is, without using the accessors provided by the public interface.

To be absolutely clear: using the MySQL_SERVER define in your code does not mean you must compile your plug-in as part of the server. On the contrary - you can compile your plug-ins separately from the server, and still (un)install them at runtime. The only thing the MySQL_SERVER define does, is pull in the declarations that are normally considered to be 'internal'. They will for example allow us to work directly with the members of the THD class instead of being required to use the public accessors defined in plugin.h.

Implementing the MYSQL_SAVEPOINTS Information Schema plug-in

Now that we sketched the backgrounds, we can quickly proceed and discuss the implementation of the MYSQL_SAVEPOINTS information schema plug-in. (Note that you can download the source code file mysql_is_savepoints.cc here.)

Most of the things are rather similar to what was described in the article describing the MYSQL_HELLO plug-in, for which you can still download the mysql_is_hello.cc source code.

We will do like we did last week and assume the following things are in place on your system:

• g++, the GNU C++ compiler


• The MySQL 5.1.22 source distribution - we need to include some of the header files


• A text editor or IDE (like Eclipse with CDT)

Creating the source file

First, we need to create a C++ source file. We will assume that the working directory is ~/mysql_is_savepoints/, and that the source file is called mysql_is_savepoints.cc.

The MYSQL_SERVER define

Like we explained in the previous sections, we need to define MYSQL_SERVER so we can directly reference the members of the THD class passed to our fill_table function.

#ifndef MYSQL_SERVER
#define MYSQL_SERVER
#endif

Because this affects how the included files are processed, we do this at the very top of our source file.

Include files

We can use the same list of includes we used for the MYSQL_HELLO plug-in - the MYSQL_SERVER define is responsible for including all the additional things we require to write the MYSQL_SAVEPOINTS plug-ins.

#include <mysql_priv.h>
#include <stdlib.h>
#include <ctype.h>
#include <mysql_version.h>
#include <mysql/plugin.h>
#include <my_global.h>
#include <my_dir.h>

Defining the columns

For the MYSQL_SAVEPOINTS plug-in, we will define two columns: SAVEPOINT_ID and SAVEPOINT_NAME. At the SQL level, it will look something like this:

+----------------+-------------+------+-----+---------+-------+
| Field          | Type        | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| SAVEPOINT_ID   | bigint(0)   | NO   |     | 0       |       |
| SAVEPOINT_NAME | varchar(64) | NO   |     |         |       |
+----------------+-------------+------+-----+---------+-------+

...and this is what it looks like in the C/C++ source file:

#define COLUMN_SAVEPOINT_ID 0
#define COLUMN_SAVEPOINT_NAME 1

static ST_FIELD_INFO mysql_is_savepoints_field_info[]=
{
  {"SAVEPOINT_ID", 0, MYSQL_TYPE_LONGLONG, 0, 0, "Savepoint Id"},    
  {"SAVEPOINT_NAME", 64, MYSQL_TYPE_STRING, 0, 0, "Savepoint Name"},    
  {NULL, 0, MYSQL_TYPE_NULL, 0, 0, NULL, 0}
};

This time, in addition to creating the ST_FIELD_INFO array of column definitions, we also create #defines for the array entry indexes. The defines allow us to refer to the column definitions using the names rather than the raw, literal integer array indexes. This has the advantage that we do not have to change code should we want to change the positions of the columns. Another advantage is that our fill_table code will be easier to read: by consistently referring to COLUMN_SAVEPOINT_ID and COLUMN_SAVEPOINT_NAME rather than 0 and 1 it will be much easier to see what is going on.

Filling the table

Now we come to the heart of the matter: generating a row for each SQL SAVEPOINT available in the current session.

The savepoints for the current session are available in the transaction member of the THD class. The transaction member is an instance of the st_transactions struct, which is declared locally inside the THD class:

class THD :public Statement,
           public Open_tables_state
{

...many, many lines here...

public:

  struct st_transactions {
    SAVEPOINT *savepoints;

    ...a few more lines here...

  } transaction;

...many, many more lines here ...

};

Now you might recall that the THD class is declared in sql/sql_class.h. However, you might have some trouble locating the transaction member, because the declaration of the THD class is extremely large and long-winded: in the MySQL 5.1.22-rc source distribution, it ranges from lines 960 to 1886(!!) - and those 900 something lines make up only the declaration!

(Although the official explanation for the name of the THD class is that it is an acronym for THread Descriptor, some developers* explained that it is one of the few class names that is spelled in capitals because it is so incredibly heavy. According to this anecdote, its name should be pronounced as "...THUD!!...THUD!!..." because of the sound it makes each time it is dumped into the argument list of a function that makes up the servers source code.

* = Thanks to Eric Herman for painting this creative and tangible likeness ;-)

Anyway, you will find it easier when you look for st_transactions, or go directly to line 1149, but note that the line number is likely to be different in other versions of the server code.

Now, we can see that the st_transaction struct contains a pointer to a SAVEPOINT pointer called savepoints. As we shall see later, this is actually the list of savepoints we need. But what kind of type is this SAVEPOINT exactly?

Well, to get past this point, you really need some patience and a set of tools that allow you to search the source code. In the case of SAVEPOINT, it turns out that this is actually a typedef for the st_savepoint structure. Now, the odd thing is that this typedef appears in sql/handler.h:

typedef struct st_savepoint SAVEPOINT;

But the structure st_savepoint itself is declared in sql/sql_class.h - that is, the same file that declares THD, which seems to prefer SAVEPOINT rather than st_savepoint!

Well - it is beyond me why it was done like this. For our purpose it doesn't really matter though, let's examine the declaration of st_savepoint instead:

struct st_savepoint {
  struct st_savepoint *prev;
  char                *name;
  uint                 length, nht;
};

Here we can see that each st_savepoint has a char * member called name, which is presumably whatever name the user provided in the savepoint syntax:

mysql> SAVEPOINT my_savepoint;

So in this case, the name member of the st_savepoint instance corresponding to this SQL SAVEPOINT will point to the character string "my_savepoint".

Apart from the name we can also see that each st_savepoint has itself a pointer to another st_savepoint called prev. This suggests a single linked list of savepoints.

This is about all the information we need to implement the fill_table function. So, here it is:

int mysql_is_savepoints_fill_table(THD *thd, TABLE_LIST *tables, COND *cond)
{
  int status = 0;                           /* return value for this func, 0=success, 1=error*/
  CHARSET_INFO *scs= system_charset_info;   /* need this to store field into table */
  TABLE *table= (TABLE *)tables->table;     /* handle to the I_S table. class declared in table.h */
  uint savepoint_id = 0; 

  SAVEPOINT *sv= thd->transaction.savepoints;

  while(sv && !status)
  {
    /* store the savepoint sequence into the table column */
    table->field[COLUMN_SAVEPOINT_ID]->store(++savepoint_id, 0);
    /* store the savepoint name into the table column */
    table->field[COLUMN_SAVEPOINT_NAME]->store(sv->name, strlen(sv->name), scs);

    status= schema_table_store_record(thd, table);
    sv= sv->prev;
  }
  return status;
}

The biggest difference with the fill_table function we used in the MYSQL_HELLO example is that instead of just storing one single row, we have a loop, storing one row for each iteration. The loop is initialized by assigning the SAVEPOINT pointer from the transaction member from the THD instance that is passed as the first argument to the fill_table function to a local sv variable:

SAVEPOINT *sv= thd->transaction.savepoints;

Of course, it is possible that there are no savepoints in the current session, in which case thd->transaction.savepoints will be the NULL pointer. However, if there are savepoints, a pointer to the last savepoint that was created in the current session will now be stored in sv. We can now set up the actual loop:

  while(sv && !status)
  {

    ...lines here...

    status= schema_table_store_record(thd, table);
    sv= sv->prev;
  }

Note that the loop will be entered only if sv points to a savepoint. If it does, data from the savepoint is written to the columns of our information schema table.

In the bottom of the loop, we store the current record using the schema_table_store_record, which we discussed already for the MYSQL_HELLO example:

    status= schema_table_store_record(thd, table);

Interestingly, we were required to make a forward declaration to it in the MYSQL_HELLO example. Now, we don't have to do this, presumably because we defined MYSQL_SERVER.

You might recall that schema_table_store_record function returns 0 in case of success and 1 instead of failure. Note that if a failure occurs at this point, the loop will not iterate again, as the while condition requires status to be not true (that is, zero).

After storing the row, the last step of the loop is to move back and examine the previous savepoint:

    sv= sv->prev;

If the end of the list is reached, sv will be NULL, preventing the loop to iterate again. However, if there is in fact a previous savepoint, the loop will run once again and create a new row for that savepoint too, on and on until we reach the end of the list of savepoints.

In the top of the loop, we store data into the columns of our information schema table:

    /* store the savepoint sequence into the table column */
    table->field[COLUMN_SAVEPOINT_ID]->store(++savepoint_id, 0);
    /* store the savepoint name into the table column */
    table->field[COLUMN_SAVEPOINT_NAME]->store(sv->name, strlen(sv->name), scs);

This time, we use our defines COLUMN_SAVEPOINT_ID and COLUMN_SAVEPOINT_NAME instead of the literal numerical field indexes. We already demonstrated in the MYSQL_HELLO example how to store a string, so we won't discuss the line that stores the savepoint's name. Instead, let's find out how we can store an integer value by looking at the line that stores the savepoint id.

As you can see, we stipulate the value for the SAVEPOINT_ID column ourselves by simply adding one for each row:

    table->field[COLUMN_SAVEPOINT_ID]->store(++savepoint_id, 0);

Savepoints by no means have a numerical ID of their own, but it makes sense to make one up in order to unambigously indicate the order in which the savepoints were created during this session. Note the second argument to the store method, which is always 0 here:

    table->field[COLUMN_SAVEPOINT_ID]->store(++savepoint_id, 0);

This second argument is there to tell the store method whether the value represents a signed or an unsigned value. In this case, we are storing an unsigned value - it should be 1 for an unsigned value.

The rest of the implementation

The remainder of the implementation is quite similar to what was discussed for the MySQL_HELLO example. The most important difference is actually the plug-in name, but otherwise the implementation is identical. Therefore, it is not discussed here further.

Building and Installing

The build and install process is pretty much similar to that for the MYSQL_HELLO plug-in.

Compiling

We can compile the plug-in like this:

g++ -DMYSQL_DYNAMIC_PLUGIN -Wall -shared
-I/home/user/mysql-5.1.22-rc/include
-I/home/user/mysql-5.1.22-rc/regex
-I/home/user/mysql-5.1.22-rc/sql
-o mysql_is_savepoints.so mysql_is_savepoints.cc

This will create the shared library mysql_is_savepoints.so.

Installing the plug-in

You might recall that the shared library needs to be moved to the plug-in directory. After that, we can install the plug-in using the INSTALL PLUGIN syntax:

mysql> INSTALL PLUGIN MYSQL_SAVEPOINTS soname 'mysql_is_savepoints.so';
Query OK, 0 rows affected (0.00 sec)

Using the plug-in

Now we can finally see our plug-in in action. At first, there will be no savepoints present:

mysql> SELECT * FROM information_schema.MYSQL_SAVEPOINTS;
Empty set (0.02 sec)

Even if we set one, we won't see it immediately:

mysql> SAVEPOINT A;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM information_schema.MYSQL_SAVEPOINTS;
Empty set (0.00 sec)

This is beause by default, the session has autocommit enabled. As each statement is wrapped in its own transaction, we will never be able to see any savepoints. So we disable autocommit:

mysql> SET autocommit = OFF;
Query OK, 0 rows affected (0.00 sec)

And now we can really witness the behaviour of our plug-in:

mysql> SAVEPOINT A;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM information_schema.MYSQL_SAVEPOINTS;
+--------------+----------------+
| SAVEPOINT_ID | SAVEPOINT_NAME |
+--------------+----------------+
|            1 | A              |
+--------------+----------------+
1 row in set (0.00 sec)

mysql> SAVEPOINT B;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM information_schema.MYSQL_SAVEPOINTS;
+--------------+----------------+
| SAVEPOINT_ID | SAVEPOINT_NAME |
+--------------+----------------+
|            1 | B              |
|            2 | A              |
+--------------+----------------+
2 rows in set (0.00 sec)

mysql> ROLLBACK TO SAVEPOINT A;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM information_schema.MYSQL_SAVEPOINTS;
+--------------+----------------+
| SAVEPOINT_ID | SAVEPOINT_NAME |
+--------------+----------------+
|            1 | A              |
+--------------+----------------+
1 row in set (0.00 sec)

Learn More

This has been quite a ride! In this article it was demonstrated how we can use information schema plug-ins to report some of the things that are going on inside the current session. As such, the MYSQL_SAVEPOINTS plug-in is a big step forward compared to the MYSQL_HELLO plug-in.

However, this is just the tip of the iceberg - the current session harbours much more interesting information about the current session, and in a forthcoming article I will demonstrate a number of other usages. In particular, I will present a plug-in to report the user variables in the current session, and the temporary tables defined in the current session.

In another article, we will also see that it is sometimes possible to look beyond the current session and report on the status of server-wide structures, such as the query cache.

Meet us at the user's conference

When I discussed the MYSQL_HELLO plug-in, I already hinted that there will be a lot there for those people that want to learn more about extending the server with (information schema) plug-ins. You can find all those links in the bottom of that article.

In addition, you can learn a lot about the MySQL Server internals. And...you can learn it from one of the founding fathers: Monty himself will be doing A tour into MySQL's internals. So, I guess that's going to be one of those occasions where you get the opportunity to clear up some of those details in the source code you never quite managed to wrap your head around.

If you register before the 26th of februari, you'll get a $200 discount. There are more discounts available depending on whether you attended before, or if you register together with a number of colleagues; there's special student and non-profit discounts too - check it out here.

See you at the conference! (Bonus points for the first one to ask Monty in the Q&A why SAVEPOINT is typedef-ed in sql/handler.h instead of sql/sql_class.h; double bonus points for the first one to ask Monty if THD is really called like that because it is so heavy ;-)