TPCC-MYSQL源码阅读

目录

1、关于Tpcc-MySQL

2、源码地址

3、全球数据库TPCC排名

4、TPCC相关表

 5、TPCC-MYSQL源码分析

5.1、源码文件

5.2、main函数

5.3、thread_main线程函数

5.4、driver函数

5.5、do_neword函数

5.6、neword函数

6、TPCC一致性校验SQL

        TPCC是数据库最出名的测试基准，本人很好奇里面的事务模型、工具的压测逻辑，所以决定通过源码了解。

1、关于Tpcc-MySQL

        TPC-C 是一个非常严苛的基准测试模型，考验的是一个完备的关系数据库系统全链路的能力。这也是为什么在 TPC-C 的榜单前列，出现的永远只是大家熟知的那几家在业界有着几十年积累、从关系数据库理论开始发展就差不多同步出现的数据库公司。

         TPC-C 中的事务模型，主要分为五种事务，订单创建、订单支付、订单查询、订单发货以及库存查询，这五种事务按照一定的比例发生，测试最终衡量的是每分钟订单创建事务的执行个数。

        Tpcc-MySQL是percona基于TPC-C衍生出来的产品，专用于MySQL基准测试。用来测试数据库的压力工具，模拟一个电商的业务，主要的业务有新增订单，库存查询，发货，支付等模块的测试。

2、源码地址

GitHub - Percona-Lab/tpcc-mysql

3、全球数据库TPCC排名

https://www.tpc.org/tpcc/results/tpcc_results5.asp?print=false&orderby=tpm&sortby=desc

关于OceanBase在2020年第二次上榜TPCC的解读

关于OceanBase在2020年第二次上榜TPCC的解读 | Oceanbase列传

4、TPCC相关表

 5、TPCC-MYSQL源码分析

5.1、源码文件

文件很少，基本可以通过文件名判断功能

# tree
.
├── delivery.c
├── driver.c
├── load.c
├── main.c
├── Makefile
├── neword.c
├── ordstat.c
├── parse_port.h
├── payment.c
├── rthist.c
├── rthist.h
├── sb_percentile.c
├── sb_percentile.h
├── sequence.c
├── sequence.h
├── slev.c
├── spt_proc.c
├── spt_proc.h
├── support.c
├── tpc.h
└── trans_if.h

5.2、main函数

主函数main调用多线程thread_main函数，并行压测

int main( int argc, char *argv[] )
{……
/* EXEC SQL WHENEVER SQLERROR GOTO sqlerr; */for( t_num=0; t_num < num_conn; t_num++ ){thd_arg[t_num].port= port;thd_arg[t_num].number= t_num;//创建thread_main多线程，进行压测pthread_create( &t[t_num], NULL, (void *)thread_main, (void *)&(thd_arg[t_num]) );}……
}

5.3、thread_main线程函数

thread_main线程函数显示了所有压测的相关SQL。想通过表分片键选择、索引优化来提升性能，就必须了解下面35条SQL的业务逻辑。

int thread_main (thread_arg* arg)
{……//建立MYSQL连接if(is_local==1){/* exec sql connect :connect_string; */resp = mysql_real_connect(ctx[t_num], "localhost", db_user, db_password, db_string_full, port, db_socket, 0);}else{/* exec sql connect :connect_string USING :db_string; */resp = mysql_real_connect(ctx[t_num], connect_string, db_user, db_password, db_string_full, port, db_socket, 0);}//每个会话设置35个prepare stmt
for(i=0;i<40;i++){stmt[t_num][i] = mysql_stmt_init(ctx[t_num]);if(!stmt[t_num][i]) goto sqlerr;}/* Prepare ALL of SQLs */if( mysql_stmt_prepare(stmt[t_num][0], "SELECT c_discount, c_last, c_credit, w_tax FROM customer, warehouse WHERE w_id = ? AND c_w_id = w_id AND c_d_id = ? AND c_id = ?", 128) ) goto sqlerr;if( mysql_stmt_prepare(stmt[t_num][1], "SELECT d_next_o_id, d_tax FROM district WHERE d_id = ? AND d_w_id = ? FOR UPDATE", 80) ) goto sqlerr;if( mysql_stmt_prepare(stmt[t_num][2], "UPDATE district SET d_next_o_id = ? + 1 WHERE d_id = ? AND d_w_id = ?", 69) ) goto sqlerr;if( mysql_stmt_prepare(stmt[t_num][3], "INSERT INTO orders (o_id, o_d_id, o_w_id, o_c_id, o_entry_d, o_ol_cnt, o_all_local) VALUES(?, ?, ?, ?, ?, ?, ?)", 111) ) goto sqlerr;if( mysql_stmt_prepare(stmt[t_num][4], "INSERT INTO new_orders (no_o_id, no_d_id, no_w_id) VALUES (?,?,?)", 65) ) goto sqlerr;if( mysql_stmt_prepare(stmt[t_num][5], "SELECT i_price, i_name, i_data FROM item WHERE i_id = ?", 55) ) goto sqlerr;if( mysql_stmt_prepare(stmt[t_num][6], "SELECT s_quantity, s_data, s_dist_01, s_dist_02, s_dist_03, s_dist_04, s_dist_05, s_dist_06, s_dist_07, s_dist_08, s_dist_09, s_dist_10 FROM stock WHERE s_i_id = ? AND s_w_id = ? FOR UPDATE", 189) ) goto sqlerr;if( mysql_stmt_prepare(stmt[t_num][7], "UPDATE stock SET s_quantity = ? WHERE s_i_id = ? AND s_w_id = ?", 63) ) goto sqlerr;if( mysql_stmt_prepare(stmt[t_num][8], "INSERT INTO order_line (ol_o_id, ol_d_id, ol_w_id, ol_number, ol_i_id, ol_supply_w_id, ol_quantity, ol_amount, ol_dist_info) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)", 159) ) goto sqlerr;if( mysql_stmt_prepare(stmt[t_num][9], "UPDATE warehouse SET w_ytd = w_ytd + ? WHERE w_id = ?", 53) ) goto sqlerr;if( mysql_stmt_prepare(stmt[t_num][10], "SELECT w_street_1, w_street_2, w_city, w_state, w_zip, w_name FROM warehouse WHERE w_id = ?", 91) ) goto sqlerr;if( mysql_stmt_prepare(stmt[t_num][11], "UPDATE district SET d_ytd = d_ytd + ? WHERE d_w_id = ? AND d_id = ?", 67) ) goto sqlerr;if( mysql_stmt_prepare(stmt[t_num][12], "SELECT d_street_1, d_street_2, d_city, d_state, d_zip, d_name FROM district WHERE d_w_id = ? AND d_id = ?", 105) ) goto sqlerr;if( mysql_stmt_prepare(stmt[t_num][13], "SELECT count(c_id) FROM customer WHERE c_w_id = ? AND c_d_id = ? AND c_last = ?", 79) ) goto sqlerr;if( mysql_stmt_prepare(stmt[t_num][14], "SELECT c_id FROM customer WHERE c_w_id = ? AND c_d_id = ? AND c_last = ? ORDER BY c_first", 89) ) goto sqlerr;if( mysql_stmt_prepare(stmt[t_num][15], "SELECT c_first, c_middle, c_last, c_street_1, c_street_2, c_city, c_state, c_zip, c_phone, c_credit, c_credit_lim, c_discount, c_balance, c_since FROM customer WHERE c_w_id = ? AND c_d_id = ? AND c_id = ? FOR UPDATE", 215) ) goto sqlerr;if( mysql_stmt_prepare(stmt[t_num][16], "SELECT c_data FROM customer WHERE c_w_id = ? AND c_d_id = ? AND c_id = ?", 72) ) goto sqlerr;if( mysql_stmt_prepare(stmt[t_num][17], "UPDATE customer SET c_balance = ?, c_data = ? WHERE c_w_id = ? AND c_d_id = ? AND c_id = ?", 90) ) goto sqlerr;if( mysql_stmt_prepare(stmt[t_num][18], "UPDATE customer SET c_balance = ? WHERE c_w_id = ? AND c_d_id = ? AND c_id = ?", 78) ) goto sqlerr;if( mysql_stmt_prepare(stmt[t_num][19], "INSERT INTO history(h_c_d_id, h_c_w_id, h_c_id, h_d_id, h_w_id, h_date, h_amount, h_data) VALUES(?, ?, ?, ?, ?, ?, ?, ?)", 120) ) goto sqlerr;if( mysql_stmt_prepare(stmt[t_num][20], "SELECT count(c_id) FROM customer WHERE c_w_id = ? AND c_d_id = ? AND c_last = ?", 79) ) goto sqlerr;if( mysql_stmt_prepare(stmt[t_num][21], "SELECT c_balance, c_first, c_middle, c_last FROM customer WHERE c_w_id = ? AND c_d_id = ? AND c_last = ? ORDER BY c_first", 121) ) goto sqlerr;if( mysql_stmt_prepare(stmt[t_num][22], "SELECT c_balance, c_first, c_middle, c_last FROM customer WHERE c_w_id = ? AND c_d_id = ? AND c_id = ?", 102) ) goto sqlerr;if( mysql_stmt_prepare(stmt[t_num][23], "SELECT o_id, o_entry_d, COALESCE(o_carrier_id,0) FROM orders WHERE o_w_id = ? AND o_d_id = ? AND o_c_id = ? AND o_id = (SELECT MAX(o_id) FROM orders WHERE o_w_id = ? AND o_d_id = ? AND o_c_id = ?)", 196) ) goto sqlerr;if( mysql_stmt_prepare(stmt[t_num][24], "SELECT ol_i_id, ol_supply_w_id, ol_quantity, ol_amount, ol_delivery_d FROM order_line WHERE ol_w_id = ? AND ol_d_id = ? AND ol_o_id = ?", 135) ) goto sqlerr;if( mysql_stmt_prepare(stmt[t_num][25], "SELECT COALESCE(MIN(no_o_id),0) FROM new_orders WHERE no_d_id = ? AND no_w_id = ?", 81) ) goto sqlerr;if( mysql_stmt_prepare(stmt[t_num][26], "DELETE FROM new_orders WHERE no_o_id = ? AND no_d_id = ? AND no_w_id = ?", 72) ) goto sqlerr;if( mysql_stmt_prepare(stmt[t_num][27], "SELECT o_c_id FROM orders WHERE o_id = ? AND o_d_id = ? AND o_w_id = ?", 70) ) goto sqlerr;if( mysql_stmt_prepare(stmt[t_num][28], "UPDATE orders SET o_carrier_id = ? WHERE o_id = ? AND o_d_id = ? AND o_w_id = ?", 79) ) goto sqlerr;if( mysql_stmt_prepare(stmt[t_num][29], "UPDATE order_line SET ol_delivery_d = ? WHERE ol_o_id = ? AND ol_d_id = ? AND ol_w_id = ?", 89) ) goto sqlerr;if( mysql_stmt_prepare(stmt[t_num][30], "SELECT SUM(ol_amount) FROM order_line WHERE ol_o_id = ? AND ol_d_id = ? AND ol_w_id = ?", 87) ) goto sqlerr;if( mysql_stmt_prepare(stmt[t_num][31], "UPDATE customer SET c_balance = c_balance + ? , c_delivery_cnt = c_delivery_cnt + 1 WHERE c_id = ? AND c_d_id = ? AND c_w_id = ?", 128) ) goto sqlerr;if( mysql_stmt_prepare(stmt[t_num][32], "SELECT d_next_o_id FROM district WHERE d_id = ? AND d_w_id = ?", 62) ) goto sqlerr;if( mysql_stmt_prepare(stmt[t_num][33], "SELECT DISTINCT ol_i_id FROM order_line WHERE ol_w_id = ? AND ol_d_id = ? AND ol_o_id < ? AND ol_o_id >= (? - 20)", 113) ) goto sqlerr;if( mysql_stmt_prepare(stmt[t_num][34], "SELECT count(*) FROM stock WHERE s_w_id = ? AND s_i_id = ? AND s_quantity < ?", 77) ) goto sqlerr;//通过driver选择压测的事务r = driver(t_num);/* EXEC SQL COMMIT WORK; */if( mysql_commit(ctx[t_num]) ) goto sqlerr;    

注意：

MYSQL的参数max_prepared_stmt_count

max_prepared_stmt_count

int driver (int t_num)
{int i, j;/* Actually, WaitTimes are needed... */while( activate_transaction ){switch(seq_get()){case 0:do_neword(t_num);  //新增订单break;case 1:do_payment(t_num); //支付break;case 2:do_ordstat(t_num); //订单状态break;case 3:do_delivery(t_num); //发货break;case 4:do_slev(t_num);    //库存查询break;default:printf("Error - Unknown sequence.\n");}}return(0);}

/** prepare data and execute the new order transaction for one order* officially, this is supposed to be simulated terminal I/O*/
static int do_neword (int t_num)
{……for (i = 0; i < MAX_RETRY; i++) { //最大可以重试MAX_RETRY 2000次ret = neword(t_num, w_id, d_id, c_id, ol_cnt, all_local, itemid, supware, qty);clk2 = clock_gettime(CLOCK_MONOTONIC, &tbuf2 );if(ret){……return (1); /* end */}else{

/** the new order transaction*/
int neword( int t_num,int w_id_arg,		/* warehouse id */int d_id_arg,		/* district id */int c_id_arg,		/* customer id */int o_ol_cnt_arg,	        /* number of items */int o_all_local_arg,	/* are all order lines local */int itemid[],		/* ids of items to be ordered */int supware[],		/* warehouses supplying items */int qty[]		        /* quantity of each item */
)
{……/* EXEC SQL WHENEVER NOT FOUND GOTO sqlerr;*//* EXEC SQL WHENEVER SQLERROR GOTO sqlerr;*//*EXEC SQL CONTEXT USE :ctx[t_num];*/gettimestamp(datetime, STRFTIME_FORMAT, TIMESTAMP_LEN);clk_start = clock_gettime(CLOCK_REALTIME, &tbuf_start );proceed = 1;/*EXEC_SQL SELECT c_discount, c_last, c_credit, w_taxINTO :c_discount, :c_last, :c_credit, :w_taxFROM customer, warehouseWHERE w_id = :w_id AND c_w_id = w_id AND c_d_id = :d_id AND c_id = :c_id;*/mysql_stmt = stmt[t_num][0];……proceed = 2;/*EXEC_SQL SELECT d_next_o_id, d_tax INTO :d_next_o_id, :d_taxFROM districtWHERE d_id = :d_idAND d_w_id = :w_idFOR UPDATE;*/mysql_stmt = stmt[t_num][1];……proceed = 3;/*EXEC_SQL UPDATE district SET d_next_o_id = :d_next_o_id + 1WHERE d_id = :d_id AND d_w_id = :w_id;*/mysql_stmt = stmt[t_num][2];……proceed = 4;/*EXEC_SQL INSERT INTO orders (o_id, o_d_id, o_w_id, o_c_id,o_entry_d, o_ol_cnt, o_all_local)VALUES(:o_id, :d_id, :w_id, :c_id, :datetime,:o_ol_cnt, :o_all_local);*/mysql_stmt = stmt[t_num][3];……proceed = 5;/* EXEC_SQL INSERT INTO new_orders (no_o_id, no_d_id, no_w_id)VALUES (:o_id,:d_id,:w_id); */mysql_stmt = stmt[t_num][4];……for (ol_number = 1; ol_number <= o_ol_cnt; ol_number++) {ol_supply_w_id = supware[ol_num_seq[ol_number - 1]];ol_i_id = itemid[ol_num_seq[ol_number - 1]];ol_quantity = qty[ol_num_seq[ol_number - 1]];/* EXEC SQL WHENEVER NOT FOUND GOTO invaliditem; */proceed = 6;/*EXEC_SQL SELECT i_price, i_name, i_dataINTO :i_price, :i_name, :i_dataFROM itemWHERE i_id = :ol_i_id;*/mysql_stmt = stmt[t_num][5];……proceed = 7;/*EXEC_SQL SELECT s_quantity, s_data, s_dist_01, s_dist_02,s_dist_03, s_dist_04, s_dist_05, s_dist_06,s_dist_07, s_dist_08, s_dist_09, s_dist_10INTO :s_quantity, :s_data, :s_dist_01, :s_dist_02,:s_dist_03, :s_dist_04, :s_dist_05, :s_dist_06,:s_dist_07, :s_dist_08, :s_dist_09, :s_dist_10FROM stockWHERE s_i_id = :ol_i_id AND s_w_id = :ol_supply_w_idFOR UPDATE;*/mysql_stmt = stmt[t_num][6];……proceed = 8;/*EXEC_SQL UPDATE stock SET s_quantity = :s_quantityWHERE s_i_id = :ol_i_id AND s_w_id = :ol_supply_w_id;*/mysql_stmt = stmt[t_num][7];……proceed = 9;/*EXEC_SQL INSERT INTO order_line (ol_o_id, ol_d_id, ol_w_id, ol_number, ol_i_id, ol_supply_w_id, ol_quantity, ol_amount, ol_dist_info)VALUES (:o_id, :d_id, :w_id, :ol_number, :ol_i_id,:ol_supply_w_id, :ol_quantity, :ol_amount,:ol_dist_info);*/mysql_stmt = stmt[t_num][8];……}			/* End Order Lines */……/*EXEC_SQL COMMIT WORK;*/if( mysql_commit(ctx[t_num]) ) goto sqlerr;……
}

Select w_id, w_ytd from bmsql_warehouse where (w_id,w_ytd) not in (select d_w_id, sum(d_ytd) from bmsql_district group by d_w_id); 
Select d_w_id, d_id, D_NEXT_O_ID - 1 from bmsql_district  where (d_w_id,d_id,D_NEXT_O_ID - 1) not in (select o_w_id, o_d_id, max(o_id) from bmsql_oorder group by  o_w_id, o_d_id);
Select d_w_id, d_id, D_NEXT_O_ID - 1 from bmsql_district where (d_w_id, d_id, D_NEXT_O_ID - 1) not in (select no_w_id, no_d_id, max(no_o_id) from bmsql_new_order group by no_w_id, no_d_id);
select * from (select (count(no_o_id)-(max(no_o_id)-min(no_o_id)+1)) as diff from bmsql_new_order group by no_w_id, no_d_id) tmp where diff != 0;
-- select o_w_id, o_d_id,tot from (select o_w_id, o_d_id, sum(o_ol_cnt) tot from bmsql_oorder  group by o_w_id, o_d_id) tmp where ( o_w_id, o_d_id, tot) not in ();
drop table if exists check_test1;
drop table if exists check_test2;
create table check_test1(o_w_id int,o_d_id int,tot int);
create table check_test2(o_w_id int,o_d_id int,tot int);
insert into check_test1 select o_w_id, o_d_id, sum(o_ol_cnt) tot from bmsql_oorder  group by o_w_id, o_d_id;
insert into check_test2 select ol_w_id, ol_d_id, count(ol_o_id) from bmsql_order_line group by ol_w_id, ol_d_id;
select o_w_id, o_d_id,tot from check_test1 where (o_w_id, o_d_id,tot) not in (select o_w_id, o_d_id,tot from check_test2);select d_w_id,tot from (select d_w_id, sum(d_ytd) tot from bmsql_district group by d_w_id) tmp where (d_w_id,tot) not in (Select w_id, w_ytd from bmsql_warehouse);

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！