20% to 50% improvement in MariaDB 5.3 Handler Interface using prepared statement

Following a previous post using MySQL as a NoSQL store with the native Handler Interface, some  refactoring have now been coded in MariaDB-5.3 as described in the following worklog  

On concurrency,  doing less work in HANDLER READ to put more caching on HANDLER OPEN is credited with a 7% performance gain.

Source code via

bzr branch lp:~maria-captains/maria/5.3-handler

More, HANDLER READ can now be used with prepared statement to save precious CPU cycles on parsing.    

handler t1 open;
prepare stmt from 'handler t1 read a=(?)';
set @a=1000;
execute stmt using @a;
deallocate prepare stmt;

On micro benchmark prepared statement show 40% better performance. On concurrency this improvement goes up to 13% better performance as demonstrated with a code in C using client side prepared statement, in regard to mysqlslap also in C but without client side prepared statement.

       

Handler Interface on Pached MariaDB 5.3	64
MyISAM/Client side prepared statement/PERL	313425
MyISAM/mysqlslap	436977
PBXT/mysqlslap	467880
PBXT/client side prepared statement/C client	527940
MyISAM/client side prepared statement/C client	493855

The MariaDB team also investigates some possible contentions on kernel_lock with a new server parameter that disables socket switching to non blocking mode -T8 --disable-thread-alarm, this is having so far no impact on this bench.

Code used during the run :

#include <iostream>
#include <cstdlib>
#include <cstring>
#include <cstdio>
#include "mysql.h"
using namespace std;

int main(int argc, char *argv[])
{
 cout << "start up" << endl;
 MYSQL * connection;
 connection = mysql_init(0);
 connection = mysql_real_connect(connection, 0, "stephane", "", "test", 0,
                                 "/tmp//mysql.sock", 0);
 if (0 == connection)
 {
   cerr << "connect failed:" << mysql_error(connection) << endl;
   return -1;
 }
 static const char * sql_statement ="HANDLER login READ `PRIMARY`=(?)";
 MYSQL_STMT * stmt_handle = mysql_stmt_init(connection);
 if (0 == stmt_handle)
 {
   cerr << "stmt init failed:" << mysql_error(connection) << endl;
   return -1;
 }
 int mysql_return_code = mysql_stmt_prepare(stmt_handle,
                                            sql_statement,
                                            strlen(sql_statement)
                                           );
if (0 != mysql_return_code)
 {
   cerr << "stmt prepare failed:" << mysql_stmt_error(stmt_handle) <<
     endl;
   mysql_stmt_close(stmt_handle);
   return -1;
 }

 MYSQL_BIND bind_var[1];
 unsigned long var_length[3];
 my_bool not_null = 0;
 int int_data;


 bind_var[0].buffer_type = MYSQL_TYPE_LONG;
 bind_var[0].buffer = (char *) &int_data;
 bind_var[0].length= 0;
 bind_var[0].is_null = 0;

mysql_return_code = mysql_stmt_bind_param(stmt_handle, bind_var);
 if (0 != mysql_return_code)
 {
   cerr << "stmt bind failed:" << mysql_stmt_error(stmt_handle) << endl;
   mysql_stmt_close(stmt_handle);
   return -1;
 }
int_data= 1000;
int user_id;
char user_name[50] = {'\0'};
char user_email[255] = {'\0'};
MYSQL_TIME created;
MYSQL_BIND result_var[4];
unsigned long result_var_length[4];
my_bool is_null[1];

for (int i=0; i<1000000; ++i)
{
 mysql_return_code = mysql_stmt_execute(stmt_handle);
 if (0 != mysql_return_code)
 {
   cerr << "stmt execute failed:" << mysql_stmt_error(stmt_handle) <<
     endl;
   mysql_stmt_close(stmt_handle);
   return -1;
 }
 bzero(result_var, sizeof(result_var));
 result_var[0].buffer_type= MYSQL_TYPE_LONG;
 result_var[0].buffer= (char *)&user_id;
 result_var[0].is_null= &is_null[0];
 result_var[0].length= &result_var_length[0];
 result_var[1].buffer_type = MYSQL_TYPE_VAR_STRING;
 result_var[1].buffer = (char *)user_name;
 result_var[1].buffer_length = sizeof(user_name)-1;
 result_var[1].is_null = &is_null[1];
 result_var[1].length = &result_var_length[1];
 result_var[2].buffer_type = MYSQL_TYPE_VAR_STRING;
 result_var[2].buffer = (char *)user_email;
 result_var[2].buffer_length = sizeof(user_email)-1;
 result_var[2].is_null = &is_null[2];
 result_var[2].length = &result_var_length[2];
 result_var[3].buffer_type = MYSQL_TYPE_DATETIME;
 result_var[3].buffer = (char *)&created;
 result_var[3].is_null = &is_null[3];
 result_var[3].length = &result_var_length[3];

 mysql_return_code = mysql_stmt_bind_result(stmt_handle, result_var);
 if (0 != mysql_return_code)
 {
   cerr << "stmt bind rslt failed:" << mysql_stmt_error(stmt_handle) <<
     endl;
   mysql_stmt_close(stmt_handle);
   return -1;
 }

 mysql_return_code = mysql_stmt_store_result(stmt_handle);
 if (0 != mysql_return_code)
 {
   cerr << "stmt store rslt failed:" << mysql_stmt_error(stmt_handle) <<
     endl;
   mysql_stmt_close(stmt_handle);
   return -1;
 }

 mysql_return_code = mysql_stmt_fetch(stmt_handle);
 if (0 != mysql_return_code && MYSQL_NO_DATA != mysql_return_code)
 {
   cerr << "stmt fetch failed:" << mysql_stmt_error(stmt_handle) << endl;
   mysql_stmt_free_result(stmt_handle);
   mysql_stmt_close(stmt_handle);
   return -1;
 }

 if (MYSQL_NO_DATA != mysql_return_code)
 {
 //  cout << "stmt - got: " << user_name << endl;
 }
} //end for
mysql_stmt_free_result(stmt_handle);
mysql_stmt_close(stmt_handle);
mysql_close(connection);
mysql_server_end();
cout << "done" << endl;
return 0;
}