1
/* Copyright (C) 2004 MySQL AB
3
This program is free software; you can redistribute it and/or modify
4
it under the terms of the GNU General Public License as published by
5
the Free Software Foundation; either version 2 of the License, or
6
(at your option) any later version.
8
This program is distributed in the hope that it will be useful,
9
but WITHOUT ANY WARRANTY; without even the implied warranty of
10
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
11
GNU General Public License for more details.
13
You should have received a copy of the GNU General Public License
14
along with this program; if not, write to the Free Software
15
Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
23
#include "db_driver.h"
26
#define GET_RANDOM_ID() ((*rnd_func)())
28
/* How many rows to insert in a single query (used for test DB creation) */
29
#define INSERT_ROWS 10000
31
/* How many rows to insert before COMMITs (used for test DB creation) */
32
#define ROWS_BEFORE_COMMIT 1000
34
/* Maximum query length */
35
#define MAX_QUERY_LEN 1024
37
/* Large prime number to generate unique set of random numbers in delete test */
38
#define LARGE_PRIME 2147483647
40
/* Command line arguments definition */
41
static sb_arg_t oltp_args[] =
43
{"oltp-test-mode", "test type to use {simple,complex,nontrx,sp}", SB_ARG_TYPE_STRING, "complex"},
44
{"oltp-sp-name", "name of store procedure to call in SP test mode", SB_ARG_TYPE_STRING, ""},
45
{"oltp-read-only", "generate only 'read' queries (do not modify database)", SB_ARG_TYPE_FLAG, "off"},
46
{"oltp-skip-trx", "skip BEGIN/COMMIT statements", SB_ARG_TYPE_FLAG, "off"},
47
{"oltp-range-size", "range size for range queries", SB_ARG_TYPE_INT, "100"},
48
{"oltp-point-selects", "number of point selects", SB_ARG_TYPE_INT, "10"},
49
{"oltp-simple-ranges", "number of simple ranges", SB_ARG_TYPE_INT, "1"},
50
{"oltp-sum-ranges", "number of sum ranges", SB_ARG_TYPE_INT, "1"},
51
{"oltp-order-ranges", "number of ordered ranges", SB_ARG_TYPE_INT, "1"},
52
{"oltp-distinct-ranges", "number of distinct ranges", SB_ARG_TYPE_INT, "1"},
53
{"oltp-index-updates", "number of index update", SB_ARG_TYPE_INT, "1"},
54
{"oltp-non-index-updates", "number of non-index updates", SB_ARG_TYPE_INT, "1"},
56
"mode for non-transactional test {select, update_key, update_nokey, insert, delete}",
57
SB_ARG_TYPE_STRING, "select"},
58
{"oltp-auto-inc", "whether AUTO_INCREMENT (or equivalent) should be used on id column",
59
SB_ARG_TYPE_FLAG, "on"},
60
{"oltp-connect-delay", "time in microseconds to sleep after connection to database", SB_ARG_TYPE_INT,
62
{"oltp-user-delay-min", "minimum time in microseconds to sleep after each request",
63
SB_ARG_TYPE_INT, "0"},
64
{"oltp-user-delay-max", "maximum time in microseconds to sleep after each request",
65
SB_ARG_TYPE_INT, "0"},
66
{"oltp-table-name", "name of test table", SB_ARG_TYPE_STRING, "sbtest"},
67
{"oltp-table-size", "number of records in test table", SB_ARG_TYPE_INT, "10000"},
69
{"oltp-dist-type", "random numbers distribution {uniform,gaussian,special}", SB_ARG_TYPE_STRING,
71
{"oltp-dist-iter", "number of iterations used for numbers generation", SB_ARG_TYPE_INT, "12"},
72
{"oltp-dist-pct", "percentage of values to be treated as 'special' (for special distribution)",
73
SB_ARG_TYPE_INT, "1"},
74
{"oltp-dist-res", "percentage of 'special' values to use (for special distribution)",
75
SB_ARG_TYPE_INT, "75"},
77
{NULL, NULL, SB_ARG_TYPE_NULL, NULL}
89
/* Modes for 'non-transactional' test */
93
NONTRX_MODE_UPDATE_KEY,
94
NONTRX_MODE_UPDATE_NOKEY,
99
/* Random numbers distributions */
109
oltp_mode_t test_mode;
110
unsigned int read_only;
111
unsigned int skip_trx;
112
unsigned int auto_inc;
113
unsigned int range_size;
114
unsigned int point_selects;
115
unsigned int simple_ranges;
116
unsigned int sum_ranges;
117
unsigned int order_ranges;
118
unsigned int distinct_ranges;
119
unsigned int index_updates;
120
unsigned int non_index_updates;
121
nontrx_mode_t nontrx_mode;
122
unsigned int connect_delay;
123
unsigned int user_delay_min;
124
unsigned int user_delay_max;
127
unsigned int table_size;
128
oltp_dist_t dist_type;
129
unsigned int dist_iter;
130
unsigned int dist_pct;
131
unsigned int dist_res;
134
/* Test statements structure */
142
db_stmt_t *range_sum;
143
db_stmt_t *range_order;
144
db_stmt_t *range_distinct;
145
db_stmt_t *update_index;
146
db_stmt_t *update_non_index;
151
/* Bind buffers for statements */
154
sb_sql_query_point_t point;
155
sb_sql_query_range_t range;
156
sb_sql_query_range_t range_sum;
157
sb_sql_query_range_t range_order;
158
sb_sql_query_range_t range_distinct;
159
sb_sql_query_update_t update_index;
160
sb_sql_query_update_t update_non_index;
161
sb_sql_query_delete_t delete;
162
sb_sql_query_insert_t insert;
163
sb_sql_query_call_t call;
164
/* Buffer for the 'c' table field in update_non_index and insert queries */
167
/* Buffer for the 'pad' table field in insert query */
169
unsigned long pad_len;
172
/* OLTP test commands */
173
static int oltp_cmd_help(void);
174
static int oltp_cmd_prepare(void);
175
static int oltp_cmd_cleanup(void);
177
/* OLTP test operations */
178
static int oltp_init(void);
179
static void oltp_print_mode(void);
180
static sb_request_t oltp_get_request(void);
181
static int oltp_execute_request(sb_request_t *, int);
182
static void oltp_print_stats(void);
183
static db_conn_t *oltp_connect(void);
184
static int oltp_disconnect(db_conn_t *);
185
static int oltp_done(void);
187
static sb_test_t oltp_test =
190
.lname = "OLTP test",
197
.print_mode = oltp_print_mode,
198
.get_request = oltp_get_request,
199
.execute_request = oltp_execute_request,
200
.print_stats = oltp_print_stats,
204
.help = oltp_cmd_help,
205
.prepare = oltp_cmd_prepare,
207
.cleanup = oltp_cmd_cleanup
213
/* Global variables */
214
static oltp_args_t args; /* test args */
215
static unsigned int (*rnd_func)(void); /* pointer to random numbers generator */
216
static unsigned int req_performed; /* number of requests done */
217
static db_conn_t **connections; /* database connection pool */
218
static oltp_stmt_set_t *statements; /* prepared statements pool */
219
static oltp_bind_set_t *bind_bufs; /* bind buffers pool */
220
static db_driver_t *driver; /* current database driver */
221
static drv_caps_t driver_caps; /* driver capabilities */
223
/* Statistic counters */
225
static int write_ops;
226
static int other_ops;
227
static int transactions;
228
static int deadlocks;
230
static sb_timer_t *exec_timers;
231
static sb_timer_t *fetch_timers;
233
/* Random seed used to generate unique random numbers */
234
static unsigned long long rnd_seed;
235
/* Mutex to protect random seed */
236
static pthread_mutex_t rnd_mutex;
238
/* Variable to pass is_null flag to drivers */
240
static char oltp_is_null = 1;
242
/* Parse command line arguments */
243
static int parse_arguments(void);
245
/* Random number generators */
246
static unsigned int rnd_func_uniform(void);
247
static unsigned int rnd_func_gaussian(void);
248
static unsigned int rnd_func_special(void);
249
static unsigned int get_unique_random_id(void);
251
/* SQL request generators */
252
static sb_request_t get_request_simple(void);
253
static sb_request_t get_request_complex(void);
254
static sb_request_t get_request_nontrx(void);
255
static sb_request_t get_request_sp(void);
257
/* Get random 'user think' time */
258
static int get_think_time(void);
260
/* Generate SQL statement from query */
261
static db_stmt_t *get_sql_statement(sb_sql_query_t *, int);
262
static db_stmt_t *get_sql_statement_trx(sb_sql_query_t *, int);
263
static db_stmt_t *get_sql_statement_nontrx(sb_sql_query_t *, int);
264
static db_stmt_t *get_sql_statement_sp(sb_sql_query_t *, int);
266
/* Prepare a set of statements for test */
267
static int prepare_stmt_set(oltp_stmt_set_t *, oltp_bind_set_t *, db_conn_t *);
268
static int prepare_stmt_set_trx(oltp_stmt_set_t *, oltp_bind_set_t *, db_conn_t *);
269
static int prepare_stmt_set_nontrx(oltp_stmt_set_t *, oltp_bind_set_t *, db_conn_t *);
270
static int prepare_stmt_set_sp(oltp_stmt_set_t *, oltp_bind_set_t *, db_conn_t *);
272
/* Close a set of statements */
273
void close_stmt_set(oltp_stmt_set_t *set);
275
int register_test_oltp(sb_list_t *tests)
277
/* Register database API */
281
/* Register OLTP test */
282
SB_LIST_ADD_TAIL(&oltp_test.listitem, tests);
288
int oltp_cmd_help(void)
296
int oltp_cmd_prepare(void)
300
unsigned int query_len;
305
unsigned long commit_cntr = 0;
306
char insert_str[MAX_QUERY_LEN];
308
char *table_options_str;
310
if (parse_arguments())
313
/* Get database capabilites */
314
if (db_describe(driver, &driver_caps, NULL))
316
log_text(LOG_FATAL, "failed to get database capabilities!");
320
/* Create database connection */
321
con = oltp_connect();
325
/* Determine if database supports multiple row inserts */
326
if (driver_caps.multi_rows_insert)
331
/* Prepare statement buffer */
333
snprintf(insert_str, sizeof(insert_str),
334
"(0,' ','qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt')");
336
snprintf(insert_str, sizeof(insert_str),
337
"(%d,0,' ','qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt')",
340
query_len = MAX_QUERY_LEN + nrows * (strlen(insert_str) + 1);
341
query = (char *)malloc(query_len);
344
log_text(LOG_FATAL, "memory allocation failure!");
348
/* Create test table */
349
log_text(LOG_NOTICE, "Creating table '%s'...", args.table_name);
350
table_options_str = driver_caps.table_options_str;
351
snprintf(query, query_len,
353
"id %s %s NOT NULL %s, "
354
"k integer %s DEFAULT '0' NOT NULL, "
355
"c char(120) DEFAULT '' NOT NULL, "
356
"pad char(60) DEFAULT '' NOT NULL, "
360
(args.auto_inc && driver_caps.serial) ? "SERIAL" : "INTEGER",
361
driver_caps.unsigned_int ? "UNSIGNED" : "",
362
(args.auto_inc && driver_caps.auto_increment) ? "AUTO_INCREMENT" : "",
363
driver_caps.unsigned_int ? "UNSIGNED" : "",
364
(table_options_str != NULL) ? table_options_str : ""
366
if (db_query(con, query) == NULL)
368
log_text(LOG_FATAL, "failed to create test table");
372
if (args.auto_inc && !driver_caps.serial && !driver_caps.auto_increment)
374
if (db_query(con, "CREATE SEQUENCE sbtest_seq") == NULL ||
376
"CREATE TRIGGER sbtest_trig BEFORE INSERT ON sbtest "
378
"BEGIN SELECT sbtest_seq.nextval INTO :new.id FROM DUAL; "
382
log_text(LOG_FATAL, "failed to create test table");
387
/* Create secondary index on 'k' */
388
snprintf(query, query_len,
389
"CREATE INDEX k on %s(k)",
391
if (db_query(con, query) == NULL)
393
log_text(LOG_FATAL, "failed to create secondary index on table!");
396
/* Fill test table with data */
397
log_text(LOG_NOTICE, "Creating %d records in table '%s'...", args.table_size,
400
for (i = 0; i < args.table_size; i += nrows)
404
n = snprintf(query, query_len, "INSERT INTO %s(k, c, pad) VALUES ",
407
n = snprintf(query, query_len, "INSERT INTO %s(id, k, c, pad) VALUES ",
411
log_text(LOG_FATAL, "query is too long!");
415
for (j = 0; j < nrows; j++)
417
if ((unsigned)(pos - query) >= query_len)
419
log_text(LOG_FATAL, "query is too long!");
423
/* Form the values string when if are not using auto_inc */
425
snprintf(insert_str, sizeof(insert_str),
426
"(%d,0,' ','qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt')",
429
if (j == nrows - 1 || i+j == args.table_size -1)
430
n = snprintf(pos, query_len - (pos - query), "%s", insert_str);
432
n = snprintf(pos, query_len - (pos - query), "%s,", insert_str);
433
if (n >= query_len - (pos - query))
435
log_text(LOG_FATAL, "query is too long!");
438
if (i+j == args.table_size - 1)
444
if (db_query(con, query) == NULL)
446
log_text(LOG_FATAL, "failed to create test table!");
450
if (driver_caps.needs_commit)
452
commit_cntr += nrows;
453
if (commit_cntr >= ROWS_BEFORE_COMMIT)
455
if (db_query(con, "COMMIT") == NULL)
457
log_text(LOG_FATAL, "failed to commit inserted rows!");
460
commit_cntr -= ROWS_BEFORE_COMMIT;
465
if (driver_caps.needs_commit && db_query(con, "COMMIT") == NULL)
467
if (db_query(con, "COMMIT") == NULL)
469
log_text(LOG_FATAL, "failed to commit inserted rows!");
474
oltp_disconnect(con);
479
oltp_disconnect(con);
486
int oltp_cmd_cleanup(void)
491
if (parse_arguments())
494
/* Get database capabilites */
495
if (db_describe(driver, &driver_caps, NULL))
497
log_text(LOG_FATAL, "failed to get database capabilities!");
501
/* Create database connection */
502
con = oltp_connect();
506
/* Drop the test table */
507
log_text(LOG_NOTICE, "Dropping table '%s'...", args.table_name);
508
snprintf(query, sizeof(query), "DROP TABLE %s", args.table_name);
509
if (db_query(con, query) == NULL)
511
oltp_disconnect(con);
515
oltp_disconnect(con);
516
log_text(LOG_INFO, "Done.");
524
unsigned int thread_id;
525
char query[MAX_QUERY_LEN];
527
if (parse_arguments())
530
/* Get database capabilites */
531
if (db_describe(driver, &driver_caps, args.table_name))
533
log_text(LOG_FATAL, "failed to get database capabilities!");
537
/* Truncate table in case of nontrx INSERT test */
538
if (args.test_mode == TEST_MODE_NONTRX && args.nontrx_mode == NONTRX_MODE_INSERT)
540
con = oltp_connect();
543
snprintf(query, sizeof(query), "TRUNCATE TABLE %s", args.table_name);
544
if (db_query(con, query) == NULL)
546
oltp_disconnect(con);
549
/* Allocate database connection pool */
550
connections = (db_conn_t **)malloc(sb_globals.num_threads * sizeof(db_conn_t *));
551
if (connections == NULL)
553
log_text(LOG_FATAL, "failed to allocate DB connection pool!");
557
/* Create database connections */
558
for (thread_id = 0; thread_id < sb_globals.num_threads; thread_id++)
560
connections[thread_id] = oltp_connect();
561
if (connections[thread_id] == NULL)
563
log_text(LOG_FATAL, "thread#%d: failed to connect to database server, aborting...",
569
/* Allocate statements pool */
570
statements = (oltp_stmt_set_t *)calloc(sb_globals.num_threads,
571
sizeof(oltp_stmt_set_t));
572
if (statements == NULL)
574
log_text(LOG_FATAL, "failed to allocate statements pool!");
578
/* Allocate bind buffers for each thread */
579
bind_bufs = (oltp_bind_set_t *)calloc(sb_globals.num_threads,
580
sizeof(oltp_bind_set_t));
581
/* Prepare statements for each thread */
582
for (thread_id = 0; thread_id < sb_globals.num_threads; thread_id++)
584
if (prepare_stmt_set(statements + thread_id, bind_bufs + thread_id,
585
connections[thread_id]))
587
log_text(LOG_FATAL, "thread#%d: failed to prepare statements for test",
593
/* Initialize random seed for non-transactional delete test */
594
if (args.test_mode == TEST_MODE_NONTRX)
596
rnd_seed = LARGE_PRIME;
597
pthread_mutex_init(&rnd_mutex, NULL);
600
/* Initialize internal timers if we are in the debug mode */
601
if (sb_globals.debug)
603
exec_timers = (sb_timer_t *)malloc(sb_globals.num_threads * sizeof(sb_timer_t));
604
fetch_timers = (sb_timer_t *)malloc(sb_globals.num_threads * sizeof(sb_timer_t));
605
for (thread_id = 0; thread_id < sb_globals.num_threads; thread_id++)
607
sb_timer_init(exec_timers + thread_id);
608
sb_timer_init(fetch_timers + thread_id);
618
unsigned int thread_id;
620
if (args.test_mode == TEST_MODE_NONTRX)
621
pthread_mutex_destroy(&rnd_mutex);
623
/* Close statements and database connections */
624
for (thread_id = 0; thread_id < sb_globals.num_threads; thread_id++)
626
close_stmt_set(statements + thread_id);
627
oltp_disconnect(connections[thread_id]);
630
/* Deallocate connection pool */
640
void oltp_print_mode(void)
642
log_text(LOG_NOTICE, "Doing OLTP test.");
644
switch (args.test_mode) {
645
case TEST_MODE_SIMPLE:
646
log_text(LOG_NOTICE, "Running simple OLTP test");
648
case TEST_MODE_COMPLEX:
649
log_text(LOG_NOTICE, "Running mixed OLTP test");
651
case TEST_MODE_NONTRX:
652
log_text(LOG_NOTICE, "Running non-transactional test");
655
log_text(LOG_NOTICE, "Running stored procedure test");
659
log_text(LOG_WARNING, "Unknown OLTP test mode!");
664
log_text(LOG_NOTICE, "Doing read-only test");
666
switch (args.dist_type) {
667
case DIST_TYPE_UNIFORM:
668
log_text(LOG_NOTICE, "Using Uniform distribution");
670
case DIST_TYPE_GAUSSIAN:
671
log_text(LOG_NOTICE, "Using Normal distribution (%d iterations)",
674
case DIST_TYPE_SPECIAL:
675
log_text(LOG_NOTICE, "Using Special distribution (%d iterations, "
676
"%d pct of values are returned in %d pct cases)",
677
args.dist_iter, args.dist_pct, args.dist_res);
680
log_text(LOG_WARNING, "Unknown distribution!");
685
log_text(LOG_NOTICE, "Skipping BEGIN/COMMIT");
687
log_text(LOG_NOTICE, "Using \"%s%s\" for starting transactions",
688
driver_caps.transactions ? "BEGIN" : "LOCK TABLES",
689
(driver_caps.transactions) ? "" :
690
((args.read_only) ? " READ" : " WRITE"));
693
log_text(LOG_NOTICE, "Using auto_inc on the id column");
695
log_text(LOG_NOTICE, "Not using auto_inc on the id column");
697
if (sb_globals.max_requests > 0)
699
"Maximum number of requests for OLTP test is limited to %d",
700
sb_globals.max_requests);
701
if (sb_globals.validate)
702
log_text(LOG_NOTICE, "Validation mode enabled");
706
sb_request_t oltp_get_request(void)
710
if (sb_globals.max_requests > 0 && req_performed >= sb_globals.max_requests)
712
sb_req.type = SB_REQ_TYPE_NULL;
716
switch (args.test_mode) {
717
case TEST_MODE_SIMPLE:
718
return get_request_simple();
719
case TEST_MODE_COMPLEX:
720
return get_request_complex();
721
case TEST_MODE_NONTRX:
722
return get_request_nontrx();
724
return get_request_sp();
726
log_text(LOG_FATAL, "unknown test mode: %d!", args.test_mode);
727
sb_req.type = SB_REQ_TYPE_NULL;
734
sb_request_t get_request_sp(void)
737
sb_sql_request_t *sql_req = &sb_req.u.sql_request;
738
sb_sql_query_t *query;
740
sb_req.type = SB_REQ_TYPE_SQL;
742
sql_req->queries = (sb_list_t *)malloc(sizeof(sb_list_t));
743
query = (sb_sql_query_t *)malloc(sizeof(sb_sql_query_t));
744
if (sql_req->queries == NULL || query == NULL)
746
log_text(LOG_FATAL, "cannot allocate SQL query!");
747
sb_req.type = SB_REQ_TYPE_NULL;
751
SB_LIST_INIT(sql_req->queries);
752
query->num_times = 1;
753
query->think_time = get_think_time();
754
query->type = SB_SQL_QUERY_CALL;
756
SB_LIST_ADD_TAIL(&query->listitem, sql_req->queries);
764
sb_request_t get_request_simple(void)
767
sb_sql_request_t *sql_req = &sb_req.u.sql_request;
768
sb_sql_query_t *query;
770
sb_req.type = SB_REQ_TYPE_SQL;
772
sql_req->queries = (sb_list_t *)malloc(sizeof(sb_list_t));
773
query = (sb_sql_query_t *)malloc(sizeof(sb_sql_query_t));
774
if (sql_req->queries == NULL || query == NULL)
776
log_text(LOG_FATAL, "cannot allocate SQL query!");
777
sb_req.type = SB_REQ_TYPE_NULL;
781
SB_LIST_INIT(sql_req->queries);
782
query->num_times = 1;
783
query->think_time = get_think_time();
784
query->type = SB_SQL_QUERY_POINT;
785
query->u.point_query.id = GET_RANDOM_ID();
787
SB_LIST_ADD_TAIL(&query->listitem, sql_req->queries);
795
sb_request_t get_request_complex(void)
798
sb_sql_request_t *sql_req = &sb_req.u.sql_request;
799
sb_sql_query_t *query;
805
sb_req.type = SB_REQ_TYPE_SQL;
807
sql_req->queries = (sb_list_t *)malloc(sizeof(sb_list_t));
808
if (sql_req->queries == NULL)
810
log_text(LOG_FATAL, "cannot allocate SQL query!");
811
sb_req.type = SB_REQ_TYPE_NULL;
814
SB_LIST_INIT(sql_req->queries);
818
/* Generate BEGIN statement */
819
query = (sb_sql_query_t *)malloc(sizeof(sb_sql_query_t));
822
query->type = SB_SQL_QUERY_LOCK;
823
query->num_times = 1;
824
query->think_time = 0;
825
SB_LIST_ADD_TAIL(&query->listitem, sql_req->queries);
828
/* Generate set of point selects */
829
for(i = 0; i < args.point_selects; i++)
831
query = (sb_sql_query_t *)malloc(sizeof(sb_sql_query_t));
834
query->num_times = 1;
835
query->think_time = get_think_time();
836
query->type = SB_SQL_QUERY_POINT;
837
query->u.point_query.id = GET_RANDOM_ID();
839
SB_LIST_ADD_TAIL(&query->listitem, sql_req->queries);
842
/* Generate range queries */
843
for(i = 0; i < args.simple_ranges; i++)
845
query = (sb_sql_query_t *)malloc(sizeof(sb_sql_query_t));
848
query->num_times = 1;
849
query->think_time = get_think_time();
850
query->type = SB_SQL_QUERY_RANGE;
851
range = GET_RANDOM_ID();
852
if (range + args.range_size > args.table_size)
853
range = args.table_size - args.range_size;
856
query->u.range_query.from = range;
857
query->u.range_query.to = range + args.range_size - 1;
858
query->nrows = args.range_size;
859
SB_LIST_ADD_TAIL(&query->listitem, sql_req->queries);
862
/* Generate sum range queries */
863
for(i = 0; i < args.sum_ranges; i++)
865
query = (sb_sql_query_t *)malloc(sizeof(sb_sql_query_t));
868
query->num_times = 1;
869
query->think_time = get_think_time();
870
query->type = SB_SQL_QUERY_RANGE_SUM;
871
range = GET_RANDOM_ID();
872
if (range + args.range_size > args.table_size)
873
range = args.table_size - args.range_size;
876
query->u.range_query.from = range;
877
query->u.range_query.to = range + args.range_size - 1;
879
SB_LIST_ADD_TAIL(&query->listitem, sql_req->queries);
882
/* Generate ordered range queries */
883
for(i = 0; i < args.order_ranges; i++)
885
query = (sb_sql_query_t *)malloc(sizeof(sb_sql_query_t));
888
query->num_times = 1;
889
query->think_time = get_think_time();
890
query->type = SB_SQL_QUERY_RANGE_ORDER;
891
range = GET_RANDOM_ID();
892
if (range + args.range_size > args.table_size)
893
range = args.table_size - args.range_size;
896
query->u.range_query.from = range;
897
query->u.range_query.to = range + args.range_size - 1;
898
query->nrows = args.range_size;
899
SB_LIST_ADD_TAIL(&query->listitem, sql_req->queries);
902
/* Generate distinct range queries */
903
for(i = 0; i < args.distinct_ranges; i++)
905
query = (sb_sql_query_t *)malloc(sizeof(sb_sql_query_t));
908
query->num_times = 1;
909
query->think_time = get_think_time();
910
query->type = SB_SQL_QUERY_RANGE_DISTINCT;
911
range = GET_RANDOM_ID();
912
if (range + args.range_size > args.table_size)
913
range = args.table_size - args.range_size;
916
query->u.range_query.from = range;
917
query->u.range_query.to = range + args.range_size;
919
SB_LIST_ADD_TAIL(&query->listitem, sql_req->queries);
922
/* Skip all write queries for read-only test mode */
926
/* Generate index update */
927
for (i = 0; i < args.index_updates; i++)
929
query = (sb_sql_query_t *)malloc(sizeof(sb_sql_query_t));
932
query->num_times = 1;
933
query->think_time = get_think_time();
934
query->type = SB_SQL_QUERY_UPDATE_INDEX;
935
query->u.update_query.id = GET_RANDOM_ID();
936
SB_LIST_ADD_TAIL(&query->listitem, sql_req->queries);
939
/* Generate non-index update */
940
for (i = 0; i < args.non_index_updates; i++)
942
query = (sb_sql_query_t *)malloc(sizeof(sb_sql_query_t));
945
query->num_times = 1;
946
query->think_time = get_think_time();
947
query->type = SB_SQL_QUERY_UPDATE_NON_INDEX;
948
query->u.update_query.id = GET_RANDOM_ID();
949
SB_LIST_ADD_TAIL(&query->listitem, sql_req->queries);
952
/* FIXME: generate one more UPDATE with the same ID as DELETE/INSERT to make
954
query = (sb_sql_query_t *)malloc(sizeof(sb_sql_query_t));
957
query->num_times = 1;
958
query->think_time = get_think_time();
959
query->type = SB_SQL_QUERY_UPDATE_INDEX;
960
range = GET_RANDOM_ID();
961
query->u.update_query.id = range;
962
SB_LIST_ADD_TAIL(&query->listitem, sql_req->queries);
964
/* Generate delete */
965
query = (sb_sql_query_t *)malloc(sizeof(sb_sql_query_t));
968
query->num_times = 1;
969
query->think_time = get_think_time();
970
query->type = SB_SQL_QUERY_DELETE;
971
/* FIXME range = GET_RANDOM_ID(); */
972
query->u.delete_query.id = range;
973
SB_LIST_ADD_TAIL(&query->listitem, sql_req->queries);
975
/* Generate insert with same value */
976
query = (sb_sql_query_t *)malloc(sizeof(sb_sql_query_t));
979
query->num_times = 1;
980
query->think_time = get_think_time();
981
query->type = SB_SQL_QUERY_INSERT;
982
query->u.insert_query.id = range;
983
SB_LIST_ADD_TAIL(&query->listitem, sql_req->queries);
989
/* Generate commit */
990
query = (sb_sql_query_t *)malloc(sizeof(sb_sql_query_t));
993
query->type = SB_SQL_QUERY_UNLOCK;
994
query->num_times = 1;
995
query->think_time = 0;
996
SB_LIST_ADD_TAIL(&query->listitem, sql_req->queries);
1003
/* Handle memory allocation failures */
1005
log_text(LOG_FATAL, "cannot allocate SQL query!");
1006
SB_LIST_FOR_EACH_SAFE(pos, tmp, sql_req->queries)
1008
query = SB_LIST_ENTRY(pos, sb_sql_query_t, listitem);
1011
free(sql_req->queries);
1012
sb_req.type = SB_REQ_TYPE_NULL;
1017
sb_request_t get_request_nontrx(void)
1019
sb_request_t sb_req;
1020
sb_sql_request_t *sql_req = &sb_req.u.sql_request;
1021
sb_sql_query_t *query;
1023
sb_req.type = SB_REQ_TYPE_SQL;
1025
sql_req->queries = (sb_list_t *)malloc(sizeof(sb_list_t));
1026
if (sql_req->queries == NULL)
1028
log_text(LOG_FATAL, "cannot allocate SQL query!");
1029
sb_req.type = SB_REQ_TYPE_NULL;
1032
SB_LIST_INIT(sql_req->queries);
1034
query = (sb_sql_query_t *)malloc(sizeof(sb_sql_query_t));
1037
query->num_times = 1;
1038
query->think_time = get_think_time();
1040
switch (args.nontrx_mode) {
1041
case NONTRX_MODE_SELECT:
1042
query->type = SB_SQL_QUERY_POINT;
1043
query->u.point_query.id = GET_RANDOM_ID();
1046
case NONTRX_MODE_UPDATE_KEY:
1047
query->type = SB_SQL_QUERY_UPDATE_INDEX;
1048
query->u.update_query.id = GET_RANDOM_ID();
1050
case NONTRX_MODE_UPDATE_NOKEY:
1051
query->type = SB_SQL_QUERY_UPDATE_NON_INDEX;
1052
query->u.update_query.id = GET_RANDOM_ID();
1054
case NONTRX_MODE_INSERT:
1055
query->type = SB_SQL_QUERY_INSERT;
1056
query->u.update_query.id = GET_RANDOM_ID();
1058
case NONTRX_MODE_DELETE:
1059
query->type = SB_SQL_QUERY_DELETE;
1060
query->u.delete_query.id = get_unique_random_id();
1063
log_text(LOG_FATAL, "unknown mode for non-transactional test!");
1065
sb_req.type = SB_REQ_TYPE_NULL;
1069
SB_LIST_ADD_TAIL(&query->listitem, sql_req->queries);
1071
/* return request */
1075
/* Handle memory allocation failures */
1077
log_text(LOG_FATAL, "cannot allocate SQL query!");
1080
free(sql_req->queries);
1081
sb_req.type = SB_REQ_TYPE_NULL;
1087
* We measure read operations, write operations and transactions
1088
* performance. The time is counted for atomic operations as user might sleep
1089
* before some of them.
1093
int oltp_execute_request(sb_request_t *sb_req, int thread_id)
1096
sb_sql_request_t *sql_req = &sb_req->u.sql_request;
1098
db_result_set_t *rs;
1099
sb_list_item_t *pos;
1100
sb_list_item_t *tmp;
1101
sb_sql_query_t *query;
1103
unsigned int local_read_ops=0;
1104
unsigned int local_write_ops=0;
1105
unsigned int local_other_ops=0;
1106
unsigned int local_deadlocks=0;
1109
log_msg_oper_t op_msg;
1110
unsigned long long nrows;
1112
/* Prepare log message */
1113
msg.type = LOG_MSG_TYPE_OPER;
1116
/* measure the time for transaction */
1117
LOG_EVENT_START(msg, thread_id);
1119
do /* deadlock handling */
1122
SB_LIST_FOR_EACH(pos, sql_req->queries)
1124
query = SB_LIST_ENTRY(pos, sb_sql_query_t, listitem);
1126
for(i = 0; i < query->num_times; i++)
1128
/* emulate user thinking */
1129
if (query->think_time > 0)
1130
usleep(query->think_time);
1132
/* find prepared statement */
1133
stmt = get_sql_statement(query, thread_id);
1136
log_text(LOG_FATAL, "unknown SQL query type: %d!", query->type);
1137
sb_globals.error = 1;
1141
if (sb_globals.debug)
1142
sb_timer_start(exec_timers + thread_id);
1144
rs = db_execute(stmt);
1146
if (sb_globals.debug)
1147
sb_timer_stop(exec_timers + thread_id);
1151
rc = db_errno(connections[thread_id]);
1152
if (rc != SB_DB_ERROR_DEADLOCK)
1154
log_text(LOG_FATAL, "database error, exiting...");
1155
/* exiting, forget about allocated memory */
1156
sb_globals.error = 1;
1168
if (query->type >= SB_SQL_QUERY_POINT &&
1169
query->type <= SB_SQL_QUERY_RANGE_DISTINCT) /* select query */
1171
if (sb_globals.debug)
1172
sb_timer_start(fetch_timers + thread_id);
1174
rc = db_store_results(rs);
1176
if (sb_globals.debug)
1177
sb_timer_stop(fetch_timers + thread_id);
1180
if (rc == SB_DB_ERROR_DEADLOCK)
1182
db_free_results(rs);
1187
else if (rc != SB_DB_ERROR_NONE)
1189
log_text(LOG_FATAL, "Error fetching result: `%s`", stmt);
1190
/* exiting, forget about allocated memory */
1191
sb_globals.error = 1;
1195
/* Validate the result set if requested */
1196
if (sb_globals.validate && query->nrows > 0)
1198
nrows = db_num_rows(rs);
1199
if (nrows != query->nrows)
1200
log_text(LOG_WARNING,
1201
"Number of received rows mismatch, expected: %ld, actual: %ld",
1202
(long )query->nrows, (long)nrows);
1206
db_free_results(rs);
1209
/* count operation statistics */
1210
switch(query->type) {
1211
case SB_SQL_QUERY_POINT:
1212
case SB_SQL_QUERY_RANGE:
1213
case SB_SQL_QUERY_RANGE_SUM:
1214
case SB_SQL_QUERY_RANGE_ORDER:
1215
case SB_SQL_QUERY_RANGE_DISTINCT:
1216
local_read_ops += query->num_times;
1218
case SB_SQL_QUERY_UPDATE_INDEX:
1219
case SB_SQL_QUERY_UPDATE_NON_INDEX:
1220
case SB_SQL_QUERY_DELETE:
1221
case SB_SQL_QUERY_INSERT:
1222
local_write_ops += query->num_times;
1225
local_other_ops += query->num_times;
1228
break; /* break transaction execution if deadlock */
1230
} while(retry); /* retry transaction in case of deadlock */
1232
LOG_EVENT_STOP(msg, thread_id);
1234
SB_THREAD_MUTEX_LOCK();
1235
read_ops += local_read_ops;
1236
write_ops += local_write_ops;
1237
other_ops += local_other_ops;
1239
deadlocks += local_deadlocks;
1240
SB_THREAD_MUTEX_UNLOCK();
1242
/* Free list of queries */
1243
SB_LIST_FOR_EACH_SAFE(pos, tmp, sql_req->queries)
1245
query = SB_LIST_ENTRY(pos, sb_sql_query_t, listitem);
1248
free(sql_req->queries);
1254
void oltp_print_stats(void)
1258
sb_timer_t exec_timer;
1259
sb_timer_t fetch_timer;
1261
total_time = NS2SEC(sb_timer_value(&sb_globals.exec_timer));
1263
log_text(LOG_NOTICE, "OLTP test statistics:");
1264
log_text(LOG_NOTICE, " queries performed:");
1265
log_text(LOG_NOTICE, " read: %d",
1267
log_text(LOG_NOTICE, " write: %d",
1269
log_text(LOG_NOTICE, " other: %d",
1271
log_text(LOG_NOTICE, " total: %d",
1272
read_ops + write_ops + other_ops);
1273
log_text(LOG_NOTICE, " transactions: %-6d"
1274
" (%.2f per sec.)", transactions, transactions / total_time);
1275
log_text(LOG_NOTICE, " deadlocks: %-6d"
1276
" (%.2f per sec.)", deadlocks, deadlocks / total_time);
1277
log_text(LOG_NOTICE, " read/write requests: %-6d"
1278
" (%.2f per sec.)", read_ops + write_ops,
1279
(read_ops + write_ops) / total_time);
1280
log_text(LOG_NOTICE, " other operations: %-6d"
1281
" (%.2f per sec.)", other_ops, other_ops / total_time);
1283
if (sb_globals.debug)
1285
sb_timer_init(&exec_timer);
1286
sb_timer_init(&fetch_timer);
1288
for (i = 0; i < sb_globals.num_threads; i++)
1290
exec_timer = merge_timers(&exec_timer, exec_timers + i);
1291
fetch_timer = merge_timers(&fetch_timer, fetch_timers + i);
1294
log_text(LOG_DEBUG, "");
1295
log_text(LOG_DEBUG, "Query execution statistics:");
1296
log_text(LOG_DEBUG, " min: %.4fs",
1297
NS2SEC(get_min_time(&exec_timer)));
1298
log_text(LOG_DEBUG, " avg: %.4fs",
1299
NS2SEC(get_avg_time(&exec_timer)));
1300
log_text(LOG_DEBUG, " max: %.4fs",
1301
NS2SEC(get_max_time(&exec_timer)));
1302
log_text(LOG_DEBUG, " total: %.4fs",
1303
NS2SEC(get_sum_time(&exec_timer)));
1305
log_text(LOG_DEBUG, "Results fetching statistics:");
1306
log_text(LOG_DEBUG, " min: %.4fs",
1307
NS2SEC(get_min_time(&fetch_timer)));
1308
log_text(LOG_DEBUG, " avg: %.4fs",
1309
NS2SEC(get_avg_time(&fetch_timer)));
1310
log_text(LOG_DEBUG, " max: %.4fs",
1311
NS2SEC(get_max_time(&fetch_timer)));
1312
log_text(LOG_DEBUG, " total: %.4fs",
1313
NS2SEC(get_sum_time(&fetch_timer)));
1318
db_conn_t *oltp_connect(void)
1322
con = db_connect(driver);
1325
log_text(LOG_FATAL, "failed to connect to database server!");
1329
if (args.connect_delay > 0)
1330
usleep(args.connect_delay);
1336
int oltp_disconnect(db_conn_t *con)
1338
return db_disconnect(con);
1342
/* Parse command line arguments */
1345
int parse_arguments(void)
1349
s = sb_get_value_string("oltp-test-mode");
1350
if (!strcmp(s, "simple"))
1351
args.test_mode = TEST_MODE_SIMPLE;
1352
else if (!strcmp(s, "complex"))
1353
args.test_mode = TEST_MODE_COMPLEX;
1354
else if (!strcmp(s, "nontrx"))
1355
args.test_mode = TEST_MODE_NONTRX;
1356
else if (!strcmp(s, "sp"))
1357
args.test_mode = TEST_MODE_SP;
1360
log_text(LOG_FATAL, "Invalid OLTP test mode: %s.", s);
1364
args.sp_name = sb_get_value_string("oltp-sp-name");
1365
if (args.test_mode == TEST_MODE_SP && args.sp_name == NULL)
1367
log_text(LOG_FATAL, "Name of stored procedure must be specified with --oltp-sp-name "
1372
args.read_only = sb_get_value_flag("oltp-read-only");
1373
args.skip_trx = sb_get_value_flag("oltp-skip-trx");
1374
args.auto_inc = sb_get_value_flag("oltp-auto-inc");
1375
args.range_size = sb_get_value_int("oltp-range-size");
1376
args.point_selects = sb_get_value_int("oltp-point-selects");
1377
args.simple_ranges = sb_get_value_int("oltp-simple-ranges");
1378
args.sum_ranges = sb_get_value_int("oltp-sum-ranges");
1379
args.order_ranges = sb_get_value_int("oltp-order-ranges");
1380
args.distinct_ranges = sb_get_value_int("oltp-distinct-ranges");
1381
args.index_updates = sb_get_value_int("oltp-index-updates");
1382
args.non_index_updates = sb_get_value_int("oltp-non-index-updates");
1384
s = sb_get_value_string("oltp-nontrx-mode");
1385
if (!strcmp(s, "select"))
1386
args.nontrx_mode = NONTRX_MODE_SELECT;
1387
else if (!strcmp(s, "update_key"))
1388
args.nontrx_mode = NONTRX_MODE_UPDATE_KEY;
1389
else if (!strcmp(s, "update_nokey"))
1390
args.nontrx_mode = NONTRX_MODE_UPDATE_NOKEY;
1391
else if (!strcmp(s, "insert"))
1392
args.nontrx_mode = NONTRX_MODE_INSERT;
1393
else if (!strcmp(s, "delete"))
1394
args.nontrx_mode = NONTRX_MODE_DELETE;
1397
log_text(LOG_FATAL, "Invalid value of oltp-nontrx-mode: %s", s);
1401
args.connect_delay = sb_get_value_int("oltp-connect-delay");
1402
args.user_delay_min = sb_get_value_int("oltp-user-delay-min");
1403
args.user_delay_max = sb_get_value_int("oltp-user-delay-max");
1404
args.table_name = sb_get_value_string("oltp-table-name");
1405
args.table_size = sb_get_value_int("oltp-table-size");
1407
s = sb_get_value_string("oltp-dist-type");
1408
if (!strcmp(s, "uniform"))
1410
args.dist_type = DIST_TYPE_UNIFORM;
1411
rnd_func = &rnd_func_uniform;
1413
else if (!strcmp(s, "gaussian"))
1415
args.dist_type = DIST_TYPE_GAUSSIAN;
1416
rnd_func = &rnd_func_gaussian;
1418
else if (!strcmp(s, "special"))
1420
args.dist_type = DIST_TYPE_SPECIAL;
1421
rnd_func = &rnd_func_special;
1425
log_text(LOG_FATAL, "Invalid random numbers distribution: %s.", s);
1429
args.dist_iter = sb_get_value_int("oltp-dist-iter");
1430
args.dist_pct = sb_get_value_int("oltp-dist-pct");
1431
args.dist_res = sb_get_value_int("oltp-dist-res");
1433
/* Select driver according to command line arguments */
1434
driver = db_init(NULL);
1437
log_text(LOG_FATAL, "failed to initialize database driver!");
1445
/* Prepare a set of statements for the test */
1448
int prepare_stmt_set(oltp_stmt_set_t *set, oltp_bind_set_t *bufs, db_conn_t *conn)
1450
if (args.test_mode == TEST_MODE_NONTRX)
1451
return prepare_stmt_set_nontrx(set, bufs, conn);
1452
else if (args.test_mode == TEST_MODE_COMPLEX ||
1453
args.test_mode == TEST_MODE_SIMPLE)
1454
return prepare_stmt_set_trx(set, bufs, conn);
1456
return prepare_stmt_set_sp(set, bufs, conn);
1460
/* Close a set of statements for the test */
1462
void close_stmt_set(oltp_stmt_set_t *set)
1464
db_close(set->lock);
1465
db_close(set->unlock);
1466
db_close(set->point);
1467
db_close(set->call);
1468
db_close(set->range);
1469
db_close(set->range_sum);
1470
db_close(set->range_order);
1471
db_close(set->range_distinct);
1472
db_close(set->update_index);
1473
db_close(set->update_non_index);
1474
db_close(set->delete);
1475
db_close(set->insert);
1479
/* Generate SQL statement from query */
1482
db_stmt_t *get_sql_statement(sb_sql_query_t *query, int thread_id)
1484
if (args.test_mode == TEST_MODE_NONTRX)
1485
return get_sql_statement_nontrx(query, thread_id);
1486
else if (args.test_mode == TEST_MODE_COMPLEX ||
1487
args.test_mode == TEST_MODE_SIMPLE)
1488
return get_sql_statement_trx(query, thread_id);
1490
return get_sql_statement_sp(query, thread_id);
1494
/* Prepare a set of statements for SP test */
1497
int prepare_stmt_set_sp(oltp_stmt_set_t *set, oltp_bind_set_t *bufs, db_conn_t *conn)
1499
db_bind_t params[2];
1500
char query[MAX_QUERY_LEN];
1502
/* Prepare CALL statement */
1503
snprintf(query, MAX_QUERY_LEN, "CALL %s(?,?)", args.sp_name);
1504
set->call = db_prepare(conn, query);
1505
if (set->call == NULL)
1507
params[0].type = DB_TYPE_INT;
1508
params[0].buffer = &bufs->call.thread_id;
1509
params[0].is_null = 0;
1510
params[0].data_len = 0;
1511
params[1].type = DB_TYPE_INT;
1512
params[1].buffer = &bufs->call.nthreads;
1513
params[1].is_null = 0;
1514
params[1].data_len = 0;
1515
if (db_bind_param(set->call, params, 2))
1520
/* Prepare a set of statements for transactional test */
1523
int prepare_stmt_set_trx(oltp_stmt_set_t *set, oltp_bind_set_t *bufs, db_conn_t *conn)
1525
db_bind_t binds[11];
1526
char query[MAX_QUERY_LEN];
1528
/* Prepare the point statement */
1529
snprintf(query, MAX_QUERY_LEN, "SELECT c from %s where id=?",
1531
set->point = db_prepare(conn, query);
1532
if (set->point == NULL)
1534
binds[0].type = DB_TYPE_INT;
1535
binds[0].buffer = &bufs->point.id;
1536
binds[0].is_null = 0;
1537
binds[0].data_len = 0;
1538
if (db_bind_param(set->point, binds, 1))
1541
/* Prepare the range statement */
1542
snprintf(query, MAX_QUERY_LEN, "SELECT c from %s where id between ? and ?",
1544
set->range = db_prepare(conn, query);
1545
if (set->range == NULL)
1547
binds[0].type = DB_TYPE_INT;
1548
binds[0].buffer = &bufs->range.from;
1549
binds[0].is_null = 0;
1550
binds[0].data_len = 0;
1551
binds[1].type = DB_TYPE_INT;
1552
binds[1].buffer = &bufs->range.to;
1553
binds[1].is_null = 0;
1554
binds[1].data_len = 0;
1555
if (db_bind_param(set->range, binds, 2))
1558
/* Prepare the range_sum statement */
1559
snprintf(query, MAX_QUERY_LEN,
1560
"SELECT SUM(K) from %s where id between ? and ?", args.table_name);
1561
set->range_sum = db_prepare(conn, query);
1562
if (set->range_sum == NULL)
1564
binds[0].type = DB_TYPE_INT;
1565
binds[0].buffer = &bufs->range_sum.from;
1566
binds[0].is_null = 0;
1567
binds[0].data_len = 0;
1568
binds[1].type = DB_TYPE_INT;
1569
binds[1].buffer = &bufs->range_sum.to;
1570
binds[1].is_null = 0;
1571
binds[1].data_len = 0;
1572
if (db_bind_param(set->range_sum, binds, 2))
1575
/* Prepare the range_order statement */
1576
snprintf(query, MAX_QUERY_LEN,
1577
"SELECT c from %s where id between ? and ? order by c",
1579
set->range_order = db_prepare(conn, query);
1580
if (set->range_order == NULL)
1582
binds[0].type = DB_TYPE_INT;
1583
binds[0].buffer = &bufs->range_order.from;
1584
binds[0].is_null = 0;
1585
binds[0].data_len = 0;
1586
binds[1].type = DB_TYPE_INT;
1587
binds[1].buffer = &bufs->range_order.to;
1588
binds[1].is_null = 0;
1589
binds[1].data_len = 0;
1590
if (db_bind_param(set->range_order, binds, 2))
1593
/* Prepare the range_distinct statement */
1594
snprintf(query, MAX_QUERY_LEN,
1595
"SELECT DISTINCT c from %s where id between ? and ? order by c",
1597
set->range_distinct = db_prepare(conn, query);
1598
if (set->range_distinct == NULL)
1600
binds[0].type = DB_TYPE_INT;
1601
binds[0].buffer = &bufs->range_distinct.from;
1602
binds[0].is_null = 0;
1603
binds[0].data_len = 0;
1604
binds[1].type = DB_TYPE_INT;
1605
binds[1].buffer = &bufs->range_distinct.to;
1606
binds[1].is_null = 0;
1607
binds[1].data_len = 0;
1608
if (db_bind_param(set->range_distinct, binds, 2))
1611
/* Prepare the update_index statement */
1612
snprintf(query, MAX_QUERY_LEN, "UPDATE %s set k=k+1 where id=?",
1614
set->update_index = db_prepare(conn, query);
1615
if (set->update_index == NULL)
1617
binds[0].type = DB_TYPE_INT;
1618
binds[0].buffer = &bufs->update_index.id;
1619
binds[0].is_null = 0;
1620
binds[0].data_len = 0;
1621
if (db_bind_param(set->update_index, binds, 1))
1624
/* Prepare the update_non_index statement */
1625
snprintf(query, MAX_QUERY_LEN,
1626
"UPDATE %s set c=? where id=?",
1628
set->update_non_index = db_prepare(conn, query);
1629
if (set->update_non_index == NULL)
1632
Non-index update statement is re-bound each time because of the string
1636
/* Prepare the delete statement */
1637
snprintf(query, MAX_QUERY_LEN, "DELETE from %s where id=?",
1639
set->delete = db_prepare(conn, query);
1640
if (set->delete == NULL)
1642
binds[0].type = DB_TYPE_INT;
1643
binds[0].buffer = &bufs->delete.id;
1644
binds[0].is_null = 0;
1645
binds[0].data_len = 0;
1646
if (db_bind_param(set->delete, binds, 1))
1649
/* Prepare the insert statement */
1650
snprintf(query, MAX_QUERY_LEN, "INSERT INTO %s values(?,0,' ',"
1651
"'aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy')",
1653
set->insert = db_prepare(conn, query);
1654
if (set->insert == NULL)
1656
binds[0].type = DB_TYPE_INT;
1657
binds[0].buffer = &bufs->insert.id;
1658
binds[0].is_null = 0;
1659
binds[0].data_len = 0;
1660
if (db_bind_param(set->insert, binds, 1))
1666
/* Prepare the lock statement */
1667
if (driver_caps.transactions)
1668
strncpy(query, "BEGIN", MAX_QUERY_LEN);
1672
snprintf(query, MAX_QUERY_LEN, "LOCK TABLES %s READ", args.table_name);
1674
snprintf(query, MAX_QUERY_LEN, "LOCK TABLES %s WRITE", args.table_name);
1676
set->lock = db_prepare(conn, query);
1677
if (set->lock == NULL)
1680
/* Prepare the unlock statement */
1681
if (driver_caps.transactions)
1682
strncpy(query, "COMMIT", MAX_QUERY_LEN);
1684
strncpy(query, "UNLOCK TABLES", MAX_QUERY_LEN);
1685
set->unlock = db_prepare(conn, query);
1686
if (set->unlock == NULL)
1693
/* Prepare a set of statements for non-transactional test */
1696
int prepare_stmt_set_nontrx(oltp_stmt_set_t *set, oltp_bind_set_t *bufs, db_conn_t *conn)
1698
db_bind_t binds[11];
1699
char query[MAX_QUERY_LEN];
1701
/* Prepare the point statement */
1702
snprintf(query, MAX_QUERY_LEN, "SELECT pad from %s where id=?",
1704
set->point = db_prepare(conn, query);
1705
if (set->point == NULL)
1707
binds[0].type = DB_TYPE_INT;
1708
binds[0].buffer = &bufs->point.id;
1709
binds[0].is_null = 0;
1710
binds[0].data_len = 0;
1711
if (db_bind_param(set->point, binds, 1))
1714
/* Prepare the update_index statement */
1715
snprintf(query, MAX_QUERY_LEN, "UPDATE %s set k=k+1 where id=?",
1717
set->update_index = db_prepare(conn, query);
1718
if (set->update_index == NULL)
1720
binds[0].type = DB_TYPE_INT;
1721
binds[0].buffer = &bufs->update_index.id;
1722
binds[0].is_null = 0;
1723
binds[0].data_len = 0;
1724
if (db_bind_param(set->update_index, binds, 1))
1727
/* Prepare the update_non_index statement */
1728
snprintf(query, MAX_QUERY_LEN,
1729
"UPDATE %s set c=? where id=?",
1731
set->update_non_index = db_prepare(conn, query);
1732
if (set->update_non_index == NULL)
1735
Non-index update statement is re-bound each time because of the string
1739
/* Prepare the delete statement */
1740
snprintf(query, MAX_QUERY_LEN, "DELETE from %s where id=?",
1742
set->delete = db_prepare(conn, query);
1743
if (set->delete == NULL)
1745
binds[0].type = DB_TYPE_INT;
1746
binds[0].buffer = &bufs->delete.id;
1747
binds[0].is_null = 0;
1748
binds[0].data_len = 0;
1749
if (db_bind_param(set->delete, binds, 1))
1752
/* Prepare the insert statement */
1753
snprintf(query, MAX_QUERY_LEN, "INSERT INTO %s values(?,?,?,?)",
1755
set->insert = db_prepare(conn, query);
1757
Insert statement is re-bound each time because of the string
1765
/* Generate SQL statement from query for SP test */
1768
db_stmt_t *get_sql_statement_sp(sb_sql_query_t *query, int thread_id)
1771
oltp_bind_set_t *buf = bind_bufs + thread_id;
1773
(void) query; /* unused */
1775
stmt = statements[thread_id].call;
1776
buf->call.thread_id = thread_id;
1777
buf->call.nthreads = sb_globals.num_threads;
1783
/* Generate SQL statement from query for transactional test */
1786
db_stmt_t *get_sql_statement_trx(sb_sql_query_t *query, int thread_id)
1788
db_stmt_t *stmt = NULL;
1790
oltp_bind_set_t *buf = bind_bufs + thread_id;
1792
switch (query->type) {
1793
case SB_SQL_QUERY_LOCK:
1794
stmt = statements[thread_id].lock;
1797
case SB_SQL_QUERY_UNLOCK:
1798
stmt = statements[thread_id].unlock;
1801
case SB_SQL_QUERY_POINT:
1802
stmt = statements[thread_id].point;
1803
buf->point.id = query->u.point_query.id;
1806
case SB_SQL_QUERY_RANGE:
1807
stmt = statements[thread_id].range;
1808
buf->range.from = query->u.range_query.from;
1809
buf->range.to = query->u.range_query.to;
1812
case SB_SQL_QUERY_RANGE_SUM:
1813
stmt = statements[thread_id].range_sum;
1814
buf->range_sum.from = query->u.range_query.from;
1815
buf->range_sum.to = query->u.range_query.to;
1818
case SB_SQL_QUERY_RANGE_ORDER:
1819
stmt = statements[thread_id].range_order;
1820
buf->range_order.from = query->u.range_query.from;
1821
buf->range_order.to = query->u.range_query.to;
1824
case SB_SQL_QUERY_RANGE_DISTINCT:
1825
stmt = statements[thread_id].range_distinct;
1826
buf->range_distinct.from = query->u.range_query.from;
1827
buf->range_distinct.to = query->u.range_query.to;
1830
case SB_SQL_QUERY_UPDATE_INDEX:
1831
stmt = statements[thread_id].update_index;
1832
buf->update_index.id = query->u.update_query.id;
1835
case SB_SQL_QUERY_UPDATE_NON_INDEX:
1836
stmt = statements[thread_id].update_non_index;
1838
We have to bind non-index update data each time
1839
because of string parameter
1841
snprintf(buf->c, 120, "%d-%d-%d-%d-%d-%d-%d-%d-%d-%d",
1842
sb_rnd(), sb_rnd(), sb_rnd(), sb_rnd(), sb_rnd(), sb_rnd(),
1843
sb_rnd(), sb_rnd(), sb_rnd(), sb_rnd());
1844
buf->update_non_index.id = query->u.update_query.id;
1845
buf->c_len = strlen(buf->c);
1846
binds[0].type = DB_TYPE_CHAR;
1847
binds[0].buffer = buf->c;
1848
binds[0].data_len = &buf->c_len;
1849
binds[0].is_null = 0;
1850
binds[0].max_len = 120;
1851
binds[1].type = DB_TYPE_INT;
1852
binds[1].buffer = &buf->update_non_index.id;
1853
binds[1].data_len = 0;
1854
binds[1].is_null = 0;
1855
if (db_bind_param(statements[thread_id].update_non_index, binds, 2))
1859
case SB_SQL_QUERY_DELETE:
1860
stmt = statements[thread_id].delete;
1861
buf->delete.id = query->u.delete_query.id;
1864
case SB_SQL_QUERY_INSERT:
1865
stmt = statements[thread_id].insert;
1866
buf->insert.id = query->u.insert_query.id;
1877
/* Generate SQL statement from query for non-transactional test */
1880
db_stmt_t *get_sql_statement_nontrx(sb_sql_query_t *query, int thread_id)
1882
db_stmt_t *stmt = NULL;
1884
oltp_bind_set_t *buf = bind_bufs + thread_id;
1886
switch (query->type) {
1887
case SB_SQL_QUERY_POINT:
1888
stmt = statements[thread_id].point;
1889
buf->point.id = query->u.point_query.id;
1892
case SB_SQL_QUERY_UPDATE_INDEX:
1893
stmt = statements[thread_id].update_index;
1894
buf->update_index.id = query->u.update_query.id;
1897
case SB_SQL_QUERY_UPDATE_NON_INDEX:
1898
stmt = statements[thread_id].update_non_index;
1900
We have to bind non-index update data each time
1901
because of string parameter
1903
snprintf(buf->c, 120, "%d-%d-%d-%d-%d-%d-%d-%d-%d-%d",
1904
sb_rnd(), sb_rnd(), sb_rnd(), sb_rnd(), sb_rnd(), sb_rnd(),
1905
sb_rnd(), sb_rnd(), sb_rnd(), sb_rnd());
1906
buf->update_non_index.id = query->u.update_query.id;
1907
buf->c_len = strlen(buf->c);
1909
binds[0].type = DB_TYPE_CHAR;
1910
binds[0].buffer = buf->c;
1911
binds[0].data_len = &buf->c_len;
1912
binds[0].is_null = 0;
1913
binds[0].max_len = 120;
1915
binds[1].type = DB_TYPE_INT;
1916
binds[1].buffer = &buf->update_non_index.id;
1917
binds[1].data_len = 0;
1918
binds[1].is_null = 0;
1920
if (db_bind_param(statements[thread_id].update_non_index, binds, 2))
1924
case SB_SQL_QUERY_DELETE:
1925
stmt = statements[thread_id].delete;
1926
buf->delete.id = query->u.delete_query.id;
1929
case SB_SQL_QUERY_INSERT:
1930
stmt = statements[thread_id].insert;
1932
We have to bind insert data each time
1933
because of string parameters
1935
buf->range.to = query->u.insert_query.id;
1936
snprintf(buf->c, 120, "%d-%d-%d-%d-%d-%d-%d-%d-%d-%d",
1937
sb_rnd(), sb_rnd(), sb_rnd(), sb_rnd(), sb_rnd(), sb_rnd(),
1938
sb_rnd(), sb_rnd(), sb_rnd(), sb_rnd());
1939
buf->c_len = strlen(buf->c);
1940
snprintf(buf->pad, 60, "%d-%d-%d-%d-%d",
1941
sb_rnd(), sb_rnd(), sb_rnd(), sb_rnd(), sb_rnd());
1942
buf->pad_len = strlen(buf->pad);
1944
/* Use NULL is AUTO_INCREMENT is used, unique id otherwise */
1947
binds[0].is_null = &oltp_is_null;
1951
buf->range.from = get_unique_random_id();
1952
binds[0].buffer = &buf->range.from;
1953
binds[0].is_null = 0;
1956
binds[0].type = DB_TYPE_INT;
1957
binds[0].data_len = 0;
1959
binds[1].type = DB_TYPE_INT;
1960
binds[1].buffer = &buf->range.to;
1961
binds[1].data_len = 0;
1962
binds[1].is_null = 0;
1964
binds[2].type = DB_TYPE_CHAR;
1965
binds[2].buffer = buf->c;
1966
binds[2].data_len = &buf->c_len;
1967
binds[2].is_null = 0;
1968
binds[2].max_len = 120;
1970
binds[3].type = DB_TYPE_CHAR;
1971
binds[3].buffer = buf->pad;
1972
binds[3].data_len = &buf->pad_len;
1973
binds[3].is_null = 0;
1974
binds[3].max_len = 60;
1976
if (db_bind_param(statements[thread_id].insert, binds, 4))
1989
/* uniform distribution */
1992
unsigned int rnd_func_uniform(void)
1994
return 1 + sb_rnd() % args.table_size;
1998
/* gaussian distribution */
2001
unsigned int rnd_func_gaussian(void)
2006
for(i=0, sum=0; i < args.dist_iter; i++)
2007
sum += (1 + sb_rnd() % args.table_size);
2009
return sum / args.dist_iter;
2013
/* 'special' distribution */
2016
unsigned int rnd_func_special(void)
2022
unsigned int range_size;
2024
if (args.table_size == 0)
2027
/* Increase range size for special values. */
2028
range_size = args.table_size * (100 / (100 - args.dist_res));
2030
/* Generate evenly distributed one at this stage */
2031
res = (1 + sb_rnd() % range_size);
2033
/* For first part use gaussian distribution */
2034
if (res <= args.table_size)
2036
for(i = 0; i < args.dist_iter; i++)
2038
sum += (1 + sb_rnd() % args.table_size);
2040
return sum / args.dist_iter;
2044
* For second part use even distribution mapped to few items
2045
* We shall distribute other values near by the center
2047
d = args.table_size * args.dist_pct / 100;
2052
/* Now we have res values in SPECIAL_PCT range of the data */
2053
res += (args.table_size / 2 - args.table_size * args.dist_pct / (100 * 2));
2059
/* Generate unique random id */
2062
unsigned int get_unique_random_id(void)
2066
pthread_mutex_lock(&rnd_mutex);
2067
res = (unsigned int) (rnd_seed % args.table_size) + 1;
2068
rnd_seed += LARGE_PRIME;
2069
pthread_mutex_unlock(&rnd_mutex);
2075
int get_think_time(void)
2077
int t = args.user_delay_min;
2079
if (args.user_delay_min < args.user_delay_max)
2080
t += sb_rnd() % (args.user_delay_max - args.user_delay_min);