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!