Search This Blog

17 February 2009

Yet more on Stored Procedure performance

In Karlsson's blog post, More on Stored Procedure performance, he has written a simple C program to send queries repeatedly to the MySQL server in order to do performance measurement. Unfortunately, his sample client will end up factoring a lot of the synchronous communications overhead in to his performance comparison.

I think that he should enable the MySQL multi-statement feature and then perform his comparison by concatenating the test statement repeatedly before sending it to the server:

#include <my_global.h>
#include <mysql.h>
#include <my_sys.h>
#define MY_SOCKET "/tmp/mysql.sock"

int main(int argc, char *argv[])
{
MYSQL *pMySQL;
int i, nLoop;
char *pStmt;

if(argc < 2)
{
fprintf(stderr, "Usage: %s \n", argv[0]);
return 0;
}
nLoop = atoi(argv[1]);
pStmt = argv[2];

DYNAMIC_STRING query;
init_dynamic_string(&query, "", 0, 4096);

for(i = 0; i < nLoop; ++i)
{
dynstr_append(&query, pStmt);
if (query.str[query.length-1] != ';')
dynstr_append(&query, ";");
}


pMySQL = mysql_init(NULL);
if(mysql_real_connect(pMySQL, NULL, "perf", "perf", "test",
0, MY_SOCKET, CLIENT_COMPRESS | CLIENT_MULTI_STATEMENTS) == NULL)
{
fprintf(stderr, "Error %s connecting to MySQL.\n",
mysql_error(pMySQL));
mysql_close(pMySQL);
return 1;
}

if(mysql_query(pMySQL, query.str) != 0)
{
fprintf(stderr, "Error %s in MySQL query.\n", mysql_error(pMySQL));
mysql_close(pMySQL);
return 1;
}

mysql_close(pMySQL);
return 0;
}


So, what do my results look like...

$ time ./spperf 100000 'INSERT INTO foo VALUES(57, "Some data")'

real 0m5.198s
user 0m3.101s
sys 0m1.887s
$ time ./spperf 1 'CALL PERF(100000)'

real 0m16.944s
user 0m0.005s
sys 0m0.003s


Oops! very different results right at the outset!

Calling the PERF procedure 100000 in a multi-statement query seems to crash the server... Looks like the free_root() memory reclamation which occurs at the end of the dispatch_command() function in sql_parse.cc probably needs to be called more often when working with large multi-statement queries.
No, I haven't bothered to check if this is a known bug in 5.1.31.

Be careful with pseudo-benchmarks!

1 comment:

Karlsson said...

Hey Anthony!

Good comments. I was thinking of testing this also, but wanted to start with something simple. The comparison is relevant, but I think mine is also. And the conclusion I think is the same: Procedures are good for certain things, but performance is still lacking.
To be honest, my next attemp would not be to use the multi-statement thingy, but to test this with libmysqld. And not YOU tried this, so I guess this is still what I will do next, test using libmysqld.
As far as the client / server communication drawback, that is exactly why I think procedures really are valid sometimes. But the multi-statement thingy is ALSO valid.
I'll look into the issues with multi statement CALL and see what the issue is, and report a bug if there isn't one already.

Cheers
/Karlsson