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!