SYSBENCH 关于OLTP测试源码分析

3190阅读　2评论2013-12-26　zxszcaijin
分类：Mysql/postgreSQL

    今天打算做一个表的sysbench测试，看看压缩之前和压缩之后，以及不同版本之间的性能对比。但是由于每次测试所针对的表是默认的表，
过于简单。所以就想改改sysbench的源代码，来适应自己的特定结构的表的性能测试。主要针对transaction操作的源码进行了分析。
首先在分析之前，我们来看看几个特殊的结构体：

db_bind_type_t该枚举类型的结构体，主要用于绑定变量的类型。比如DB_TYPE_TINYINT代表绑定变量为tinyint类型，DB_TYPE_INT代表int类型等等。
 typedef enum
{
  DB_TYPE_NONE,
  DB_TYPE_TINYINT,
  DB_TYPE_SMALLINT,
  DB_TYPE_INT,
  DB_TYPE_BIGINT,
  DB_TYPE_FLOAT,
  DB_TYPE_DOUBLE,
  DB_TYPE_TIME,
  DB_TYPE_DATE,
  DB_TYPE_DATETIME,
  DB_TYPE_TIMESTAMP,
  DB_TYPE_CHAR,
  DB_TYPE_VARCHAR
} db_bind_type_t;

db_bind_t用于存放绑定变量的值和绑定变量的其他一些属性，具体属性显而易见。
typedef struct
{
  db_bind_type_t   type;
  void             *buffer;
  unsigned long    *data_len;
  unsigned long    max_len;
  char             *is_null;
} db_bind_t;

oltp_stmt_set_t该结构体表示在进行sysbench oltp类型的性能测试时，存放具体操作。
typedef struct                
{                             
  db_stmt_t *lock;            
  db_stmt_t *unlock;          
  db_stmt_t *point;           
  db_stmt_t *call;            
  db_stmt_t *range;           
  db_stmt_t *range_sum;       
  db_stmt_t *range_order;     
  db_stmt_t *range_distinct;  
  db_stmt_t *update_index;    
  db_stmt_t *update_non_index;
  db_stmt_t *delete;          
  db_stmt_t *insert;          
} oltp_stmt_set_t;            

oltp_bind_set_t绑定变量缓冲池，所有的绑定变量信息都会存在这个结构体中。
/* Bind buffers for statements */                                                   
 typedef struct                                                                     
 {                                                                                  
   sb_sql_query_point_t  point;                                                     
   sb_sql_query_range_t  range;                                                     
   sb_sql_query_range_t  range_sum;                                                 
   sb_sql_query_range_t  range_order;                                               
   sb_sql_query_range_t  range_distinct;                                            
   sb_sql_query_update_t update_index;                                              
   sb_sql_query_update_t update_non_index;                                          
   sb_sql_query_delete_t delete;                                                    
   sb_sql_query_insert_t insert;                                                    
   sb_sql_query_call_t   call;                                                      
   /* Buffer for the 'c' table field in update_non_index and insert queries */      
   char                  c[120];                                                    
   unsigned long         c_len;                                                     
   /* Buffer for the 'pad' table field in insert query */                           
   char                  pad[60];                                                   
   unsigned long         pad_len;                                                   
 } oltp_bind_set_t;                                                                 


然后就是具体操作如下：
int prepare_stmt_set_trx(oltp_stmt_set_t *set, oltp_bind_set_t *bufs, db_conn_t *conn)
{
  db_bind_t binds[11];
  char      query[MAX_QUERY_LEN];

  [1] 对于主键的等值查询SELECT c from %s where id=? 
  /* Prepare the point statement [对于主键为id的查询]*/
  snprintf(query, MAX_QUERY_LEN, "SELECT c from %s where id=?",args.table_name); 
  set->point = db_prepare(conn, query); 
  if (set->point == NULL)
    return 1;
  binds[0].type = DB_TYPE_INT;
  binds[0].buffer = &bufs->point.id;
  binds[0].is_null = 0;
  binds[0].data_len = 0;
  if (db_bind_param(set->point, binds, 1))
    return 1;

 [2] 对于主键的rang查询 SELECT c from %s where id between ? and ?
 /* Prepare the range statement[rang 查询] */
  snprintf(query, MAX_QUERY_LEN, "SELECT c from %s where id between ? and ?",args.table_name);
  set->range = db_prepare(conn, query);
  if (set->range == NULL)
    return 1;
  binds[0].type = DB_TYPE_INT;
  binds[0].buffer = &bufs->range.from;
  binds[0].is_null = 0;
  binds[0].data_len = 0;
  binds[1].type = DB_TYPE_INT;
  binds[1].buffer = &bufs->range.to;
  binds[1].is_null = 0;
  binds[1].data_len = 0;
  if (db_bind_param(set->range, binds, 2))
    return 1;

[3] 对于主键的sum查询 SELECT SUM(K) from %s where id between ? and ?
  /* Prepare the range_sum statement */
  snprintf(query, MAX_QUERY_LEN, "SELECT SUM(K) from %s where id between ? and ?", args.table_name); 
  set->range_sum = db_prepare(conn, query);
  if (set->range_sum == NULL)
    return 1;
  binds[0].type = DB_TYPE_INT;
  binds[0].buffer = &bufs->range_sum.from;
  binds[0].is_null = 0;
  binds[0].data_len = 0;
  binds[1].type = DB_TYPE_INT;
  binds[1].buffer = &bufs->range_sum.to;
  binds[1].is_null = 0;
  binds[1].data_len = 0;
  if (db_bind_param(set->range_sum, binds, 2))
    return 1;

[4] 对于主键的rang_order查询 SELECT c from %s where id between ? and ? order by c
/* Prepare the range_order statement */
  snprintf(query, MAX_QUERY_LEN,"SELECT c from %s where id between ? and ? order by c", args.table_name);
  set->range_order = db_prepare(conn, query);
  if (set->range_order == NULL)
    return 1;
  binds[0].type = DB_TYPE_INT;
  binds[0].buffer = &bufs->range_order.from;
  binds[0].is_null = 0;
  binds[0].data_len = 0;
  binds[1].type = DB_TYPE_INT;
  binds[1].buffer = &bufs->range_order.to;
  binds[1].is_null = 0;
  binds[1].data_len = 0;
  if (db_bind_param(set->range_order, binds, 2))
    return 1;

[5] 对于主键的distinct查询 SELECT DISTINCT c from %s where id between ? and ? order by c
  /* Prepare the range_distinct statement */
  snprintf(query, MAX_QUERY_LEN, "SELECT DISTINCT c from %s where id between ? and ? order by c",args.table_name);
  set->range_distinct = db_prepare(conn, query);
  if (set->range_distinct == NULL)
    return 1;
  binds[0].type = DB_TYPE_INT;
  binds[0].buffer = &bufs->range_distinct.from;
  binds[0].is_null = 0;
  binds[0].data_len = 0;
  binds[1].type = DB_TYPE_INT;
  binds[1].buffer = &bufs->range_distinct.to;
  binds[1].is_null = 0;
  binds[1].data_len = 0;
  if (db_bind_param(set->range_distinct, binds, 2))
    return 1;

 [6] 对于二级索引的列进行更新 UPDATE %s set k=k+1 where id=?
  /* Prepare the update_index statement */
  snprintf(query, MAX_QUERY_LEN, "UPDATE %s set k=k+1 where id=?",args.table_name);
  set->update_index = db_prepare(conn, query);
  if (set->update_index == NULL)
    return 1;
  binds[0].type = DB_TYPE_INT;
  binds[0].buffer = &bufs->update_index.id;
  binds[0].is_null = 0;
  binds[0].data_len = 0;
  if (db_bind_param(set->update_index, binds, 1))
    return 1;

[7] 对于非索引的列进行更新 UPDATE %s set c=? where id=?
  /* Prepare the update_non_index statement */
  snprintf(query, MAX_QUERY_LEN,"UPDATE %s set c=? where id=?",args.table_name);
  set->update_non_index = db_prepare(conn, query);
  if (set->update_non_index == NULL)
    return 1;
  binds[0].type = DB_TYPE_INT;
  binds[0].buffer = &bufs->update_index.id;
  binds[0].is_null = 0;
  binds[0].data_len = 0;
  if (db_bind_param(set->update_index, binds, 1))
    return 1;
  /*
    Non-index update statement is re-bound each time because of the string
    parameter
  */
 
[8] 对该表的主键等值删除操作 DELETE from %s where id=?
  /*pepare the delete statement */
  snprintf(query, MAX_QUERY_LEN, "DELETE from %s where id=?",args.table_name);
  set->delete = db_prepare(conn, query);
  if (set->delete == NULL)
    return 1;
  binds[0].type = DB_TYPE_INT;
  binds[0].buffer = &bufs->delete.id;
  binds[0].is_null = 0;
  binds[0].data_len = 0;
  if (db_bind_param(set->delete, binds, 1))
    return 1;

 [9] 对该表插入操作 INSERT INTO %s values(?,0,' ',""'aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy')
  /* Prepare the insert statement */
  snprintf(query, MAX_QUERY_LEN, "INSERT INTO %s values(?,0,' ',""'aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy')", args.table_name);
  set->insert = db_prepare(conn, query);
  if (set->insert == NULL)
    return 1;
  binds[0].type = DB_TYPE_INT;
  binds[0].buffer = &bufs->insert.id;
  binds[0].is_null = 0;
  binds[0].data_len = 0;
  if (db_bind_param(set->insert, binds, 1))
    return 1;
  if (args.skip_trx)
    return 0;

//事物开始，并对事物所处的类型加相关的锁
  /* Prepare the lock statement */
  if (driver_caps.transactions)
    strncpy(query, "BEGIN", MAX_QUERY_LEN);
  else
  {
    if (args.read_only)
      snprintf(query, MAX_QUERY_LEN, "LOCK TABLES %s READ", args.table_name);
    else
      snprintf(query, MAX_QUERY_LEN, "LOCK TABLES %s WRITE", args.table_name);
  }
  set->lock = db_prepare(conn, query);
  if (set->lock == NULL)
    return 1;
  //事物结束，并解锁
  /* Prepare the unlock statement */
  if (driver_caps.transactions)
    strncpy(query, "COMMIT", MAX_QUERY_LEN);
  else
    strncpy(query, "UNLOCK TABLES", MAX_QUERY_LEN);
  set->unlock = db_prepare(conn, query);
  if (set->unlock == NULL)
    return 1;
  return 0;
}

对于源码我们很清楚了，在操作过程中，sysbench对数据库做了那些dml的操作。接下来，我们再来看看文档上的解释：
Simple

In this mode each thread runs simple queries of the following form: 

 SELECT c FROM sbtest WHERE id=N

where N takes a random value in range 1..


对于简单的类型，用SELECT c FROM sbtest WHERE id=N，N在1..table_size 进行查询即可


Advanced transactional

Each thread performs transactions on the test table. If the test table and database support transactions (e.g. InnoDB engine in MySQL), then BEGIN/COMMIT
statements will be used to start/stop a transaction. Otherwise, SysBench will use LOCK TABLES/UNLOCK TABLES statements (e.g. for MyISAM engine in MySQL).
If some rows are deleted in a transaction, the same rows will be inserted within the same transaction, so this test mode does not destruct any data in the test table
and can be run multiple times on the same table.

Depending on the command line options, each transaction may contain the following statements:
  • Point queries: 
SELECT c FROM sbtest WHERE id=N
  • Range queries: 
    SELECT c FROM sbtest WHERE id BETWEEN N AND M
  • Range SUM() queries: 
    SELECT SUM(K) FROM sbtest WHERE id BETWEEN N and M
  • Range ORDER BY queries: 
    SELECT c FROM sbtest WHERE id between N and M ORDER BY c
  • Range DISTINCT queries: 
    SELECT DISTINCT c FROM sbtest WHERE id BETWEEN N and M ORDER BY c
  • UPDATEs on index column: 
    UPDATE sbtest SET k=k+1 WHERE id=N
  • UPDATEs on non-index column: 
    UPDATE sbtest SET c=N WHERE id=M
  • DELETE queries: 
    DELETE FROM sbtest WHERE id=N
  • INSERT queries: 
    INSERT INTO sbtest VALUES (...)
    • 由此我们知道，只要更改相关的sql就能得到对特定表的测试，从而更准确的结果，而不是用默认结构的表，这样对于测试的真实性会有更高的精度。对于特定的更改和测试结果，待进一步进行改进。
    当然，我们还可以对源码表结构的创建来进行更改，从而更加符合真实的场景。

    --EOF--

    祝玩的开心


    上一篇：Mysql优化的几点小建议
    下一篇：MySQL使用IN代替OR的Tips

    文章评论