MySQL embedded server prepared statement执行分析

By | 2013 年 9 月 4 日

MySQL embedded server简介

MySQL可以作为一个embedded server执行,这时它就充当了一个与应用紧密耦合的轻量级数据中心,适合资源有限、应用需要连接很少(通常1个)的场景,使用方便,编译后就可以随应用一起启动,具有部署方便,速度更快的优点,当然它有很多限制,如:不支持UDF,不支持主备复制,InnoDB层不支持多个连接等,更多请参考 libmysqld, the Embedded MySQL Server Library.

因为DRC团队在使用embedded server的过程中遇到了执行prepared statment时内存泄露的问题,为了定位并fix问题,分析了这部分逻辑

prepared statement API

mysql_stmt_init statement
mysql_stmt_prepare prepare一条SQL语句
mysql_stmt_bind_param
mysql_stmt_bind_result
mysql_stmt_execute
mysql_stmt_restore_result
mysql_stmt_fetch
mysql_stmt_affected_rows insert/update/delete返回影响的数据条数
mysql_stmt_num_rows select返回结果集大小
mysql_stmt_free_result
mysql_stmt_close statement

embedded server数据交互

相对于C/S架构的MySQL server,embedded server是运行于应用内部的,因此数据传输不需要走网络,通过内存拷贝就可以完成,但是在逻辑上还是维持C/S结构,libmysqld中重新实现了Protocol类的一些方法如:Protocol::send_fields,以及net_send_ok,net_send_eof,net_send_error_packet,所有这些操作都是些内存操作,结果集数据写到THD中为embedded server增加的st_mysql_data类型的链表中:(sql/sql_class.h)

#ifdef EMBEDDED_LIBRARY
  struct st_mysql  *mysql;
  unsigned long	 client_stmt_id;
  unsigned long  client_param_count;
  struct st_mysql_bind *client_params;
  char *extra_data;
  ulong extra_length;
  struct st_mysql_data *cur_data;
  struct st_mysql_data *first_data;
  struct st_mysql_data **data_tail;
  void clear_data_list();
  struct st_mysql_data *alloc_new_dataset();
  /*
    In embedded server it points to the statement that is processed
    in the current query. We store some results directly in statement
    fields then.
  */
  struct st_mysql_stmt *current_stmt;
#endif

重要数据结构

MYSQL_DATA是一个数据集,其中alloc负责为embedded_info中的fields_list分配内存,data为结果数据集(链表),rows为结果集数据行数

typedef struct st_mysql_data {
  MYSQL_ROWS *data;
  struct embedded_query_result *embedded_info;
  MEM_ROOT alloc;
  my_ulonglong rows;
  unsigned int fields;
  /* extra info for embedded library */
  void *extension;
} MYSQL_DATA;

embedded_query_result主要包含结果集的状态信息,结果集的列集合field_list(用于解析MYSQL_ROWS)以及指向下一个结果集的指针

/* This one is used by embedded library to gather returning data */
typedef struct embedded_query_result
{
  MYSQL_ROWS **prev_ptr;
  unsigned int warning_count, server_status;
  struct st_mysql_data *next;
  my_ulonglong affected_rows, insert_id;
  char info[MYSQL_ERRMSG_SIZE];
  MYSQL_FIELD *fields_list;
  unsigned int last_errno;
  char sqlstate[SQLSTATE_LENGTH+1];
} EQR;

MYSQL_ROWS是一个链表节点,保存一行数据和指向下行数据的指针

typedef struct st_mysql_rows {
  struct st_mysql_rows *next;		/* list of rows */
  MYSQL_ROW data;
  unsigned long length;
} MYSQL_ROWS;

另外两个重要数据结构st_mysql_stmt和st_mysql,在此不列举了

函数调用分析

mysql_stmt_init

为stmt分配内存,初始化变量,为stmt->mem_root和stmt->result.alloc分配内存

init_alloc_root(&stmt->mem_root, 2048, 2048);
init_alloc_root(&stmt->result.alloc, 4096, 4096);

mysql_stmt_prepare

1、stmt_command发送COM_STMT_PREPARE命令,最终通过emb_advanced_command调用dispatch_command执行mysqld_stmt_prepare
2、read_prepare_resut调用emb_read_prepare_result读取prepare语句执行结果
3、为stmt->param(绑定变量参数)分配内存(在stmt->mem_root上)

mysql_stmt_bind_param

绑定prepare语句变量,对参数做一些判断,初始化stmt->param,根据数据类型设置param->store_param_func

mysql_stmt_bind_result

绑定结果集变量,对参数做一些判断,初始化stmt->bind,调用setup_one_fetch_function根据数据类型设置bind->fetch_result

mysql_stmt_execute

1、reset_stmt_handle,释放stmt->result.alloc内存(MY_KEEP_PREALLOC方式)
2、emb_stmt_execute,通过emb_advanced_command以COM_STMT_EXECUTE作为参数调用dispatch_command执行mysqld_stmt_execute
3、emb_read_query_result设置结果读取位置(thd->cur_data),主要执行一下代码:

static my_bool emb_read_query_result(MYSQL *mysql)
{
  THD *thd= (THD*) mysql->thd;
  MYSQL_DATA *res= thd->first_data;
  thd->first_data= res->embedded_info->next;
  ..
  if (res->embedded_info->fields_list)
  {
    mysql->status=MYSQL_STATUS_GET_RESULT;
    thd->cur_data= res;
  }
  ..

mysql_stmt_restore_result

1、emb_read_binary_rows:调用emb_read_rows获得结果集读取位置(thd->cur_data)之后赋值:stmt->result= *data,到此歩,stmt结果集可以通过stmt->result返回了
2、为stmt设置fetch结果集的函数指针和游标:

stmt->read_row_func= stmt_read_row_buffered;
stmt->data_cursor= stmt->result->data

mysql_stmt_fetch

1、stmt_read_row_buffered,代码如下:

static int stmt_read_row_buffered(MYSQL_STMT *stmt, unsigned char **row)
{
  if (stmt->data_cursor)
  {
    *row= (uchar *) stmt->data_cursor->data;
    stmt->data_cursor= stmt->data_cursor->next;
    return 0;
  }
  *row= 0;
  return MYSQL_NO_DATA;
}

2、stmt_fetch_row,根据stmt_read_row_buffered得到的行数据,将结果解析到绑定结果变量中

mysql_stmt_affected_rows, mysql_stmt_num_rows

返回stmt->affected_rows和stmt->result.rows

mysql_stmt_close

关闭stmt,释放stmt->mem_root和stmt->result.alloc内存

两个Memeory leak的bug

代码调试过程中通过valgrind发现了两处内存泄露(mysql-5.6.11)

==29639== 2,064 bytes in 1 blocks are definitely lost in loss record 3 of 4
==29639==    at 0x4A05FDE: malloc (vg_replace_malloc.c:236)
==29639==    by 0x4635E1: my_malloc (my_malloc.c:38)
==29639==    by 0x462C72: init_alloc_root (my_alloc.c:63)
==29639==    by 0x448935: mysql_stmt_init (libmysql.c:1512)
==29639==    by 0x444277: main (in /u01/mysql5611/bug62136)
==29639== 
==29639== 4,112 bytes in 1 blocks are definitely lost in loss record 4 of 4
==29639==    at 0x4A05FDE: malloc (vg_replace_malloc.c:236)
==29639==    by 0x4635E1: my_malloc (my_malloc.c:38)
==29639==    by 0x462C72: init_alloc_root (my_alloc.c:63)
==29639==    by 0x44894B: mysql_stmt_init (libmysql.c:1513)
==29639==    by 0x444277: main (in /u01/mysql5611/bug62136)
==29639== 
==29639== LEAK SUMMARY:
==29639==    definitely lost: 6,176 bytes in 2 blocks
==29639==    indirectly lost: 0 bytes in 0 blocks
==29639==      possibly lost: 0 bytes in 0 blocks
==29639==    still reachable: 568 bytes in 2 blocks
==29639==         suppressed: 0 bytes in 0 blocks
==29639== 
==29639== For counts of detected and suppressed errors, rerun with: -v
==29639== ERROR SUMMARY: 2 errors from 2 contexts (suppressed: 6 from 6)

经过分析,最终定位到问题原因并fix,向官方提交了一个bug report,详情:mysql bug#70238

发表评论

电子邮件地址不会被公开。 必填项已用*标注