BIND 10 trac2040, updated. 6a212576107d4b72f31249850b634eb6f7fec5ce [2040] compiled statements in MySQL benchmarks added for INSERT and SELECT
BIND 10 source code commits
bind10-changes at lists.isc.org
Mon Aug 20 17:31:54 UTC 2012
The branch, trac2040 has been updated
via 6a212576107d4b72f31249850b634eb6f7fec5ce (commit)
from 5263aaafa559b202f2731da3a152b16bcf55bd74 (commit)
Those revisions listed above that are new to this repository have
not appeared on any other notification email; so we list those
revisions in full, below.
- Log -----------------------------------------------------------------
commit 6a212576107d4b72f31249850b634eb6f7fec5ce
Author: Tomek Mrugalski <tomasz at isc.org>
Date: Mon Aug 20 19:31:28 2012 +0200
[2040] compiled statements in MySQL benchmarks added for INSERT and SELECT
-----------------------------------------------------------------------
Summary of changes:
tests/tools/dhcp-ubench/benchmark.cc | 11 +-
tests/tools/dhcp-ubench/benchmark.h | 3 +
tests/tools/dhcp-ubench/dhcp-perf-guide.xml | 1 +
tests/tools/dhcp-ubench/mysql_ubench.cc | 390 ++++++++++++++++++++++-----
4 files changed, 338 insertions(+), 67 deletions(-)
-----------------------------------------------------------------------
diff --git a/tests/tools/dhcp-ubench/benchmark.cc b/tests/tools/dhcp-ubench/benchmark.cc
index 66765f5..50f3aaa 100644
--- a/tests/tools/dhcp-ubench/benchmark.cc
+++ b/tests/tools/dhcp-ubench/benchmark.cc
@@ -26,8 +26,11 @@ uBenchmark::uBenchmark(uint32_t iterations, const std::string& dbname,
const std::string& user /* = "" */,
const std::string& pass /* = "" */)
:num_(iterations), sync_(sync), verbose_(verbose),
- hostname_(host), user_(user), passwd_(pass), dbname_(dbname)
+ hostname_(host), user_(user), passwd_(pass), dbname_(dbname),
+ compiled_stmt_(true)
{
+ /// @todo: make compiled statements a configurable parameter
+
/// @todo: convert this to user-configurable parameter
hitratio_ = 0.9f;
@@ -55,7 +58,7 @@ void uBenchmark::usage() {
void uBenchmark::parseCmdline(int argc, char* const argv[]) {
int ch;
- while ((ch = getopt(argc, argv, "hm:u:p:f:n:s:v:")) != -1) {
+ while ((ch = getopt(argc, argv, "hm:u:p:f:n:s:v:c:")) != -1) {
switch (ch) {
case 'h':
usage();
@@ -79,6 +82,9 @@ void uBenchmark::parseCmdline(int argc, char* const argv[]) {
usage();
}
break;
+ case 'c':
+ compiled_stmt_ = !strcasecmp(optarg, "yes") || !strcmp(optarg, "1");
+ break;
case 's':
sync_ = !strcasecmp(optarg, "yes") || !strcmp(optarg, "1");
break;
@@ -122,6 +128,7 @@ int uBenchmark::run() {
<< "Number of iterations : " << num_ << endl
<< "Sync/async : " << (sync_ ? "sync" : "async") << endl
<< "Verbose : " << (verbose_ ? "verbose" : "quiet") << endl
+ << "Compiled statements : " << (compiled_stmt_ ? "yes": "no") << endl
<< "Database name : " << dbname_ << endl
<< "MySQL hostname : " << hostname_ << endl
<< "MySQL username : " << user_ << endl
diff --git a/tests/tools/dhcp-ubench/benchmark.h b/tests/tools/dhcp-ubench/benchmark.h
index 33202e5..c126de3 100644
--- a/tests/tools/dhcp-ubench/benchmark.h
+++ b/tests/tools/dhcp-ubench/benchmark.h
@@ -199,6 +199,9 @@ protected:
/// five timestamps (1 at the beginning and 4 after each step)
struct timespec ts_[5];
+
+ /// should compiled statements be used?
+ bool compiled_stmt_;
};
#endif
diff --git a/tests/tools/dhcp-ubench/dhcp-perf-guide.xml b/tests/tools/dhcp-ubench/dhcp-perf-guide.xml
index 80c4148..6edc5d9 100644
--- a/tests/tools/dhcp-ubench/dhcp-perf-guide.xml
+++ b/tests/tools/dhcp-ubench/dhcp-perf-guide.xml
@@ -226,6 +226,7 @@ Possible command-line parameters:
-n integer - number of test repetitions (MySQL, SQLite and memfile)
-s yes|no - synchronous/asynchronous operation (MySQL, SQLite and memfile)
-v yes|no - verbose mode (MySQL, SQLite and memfile)
+ -c yes|no - should compiled statements be used (MySQL only)
</screen>
</para>
diff --git a/tests/tools/dhcp-ubench/mysql_ubench.cc b/tests/tools/dhcp-ubench/mysql_ubench.cc
index 9fdbd90..1a70bd7 100644
--- a/tests/tools/dhcp-ubench/mysql_ubench.cc
+++ b/tests/tools/dhcp-ubench/mysql_ubench.cc
@@ -89,16 +89,25 @@ void MySQL_uBenchmark::createLease4Test() {
uint32_t addr = BASE_ADDR4; // Let's start with 1.0.0.0 address
char hwaddr[20];
- uint8_t hwaddr_len = 20; // Not a real field
+ size_t hwaddr_len = 20; // Not a real field
char client_id[128];
- uint8_t client_id_len = 128;
+ size_t client_id_len = 128;
uint32_t valid_lft = 1000; // We can use the same value for all leases
- uint32_t recycle_time = 0; // not supported in any foresable future,
- // so keep this as 0
- string cltt = "now()"; // Timestamp
- uint32_t pool_id = 0; // Let's use pools 0-99
+ uint32_t recycle_time = 7; // not supported in any foresable future,
+
+ char cltt[48]; // timestamp (specified as text)
+ size_t cltt_len;
+
+ sprintf(cltt, "2012-07-11 15:43:00");
+ cltt_len = strlen(cltt);
+
+ uint32_t pool_id = 1000; // Let's use pools 0-99
bool fixed = false;
- string hostname("foo"); // Will generate it dynamically
+
+ char hostname[] = "foo"; // Will generate it dynamically
+ size_t hostname_len;
+ hostname_len = strlen(hostname);
+
bool fqdn_fwd = true; // Let's pretend to do AAAA update
bool fqdn_rev = true; // Let's pretend to do PTR update
@@ -107,45 +116,162 @@ void MySQL_uBenchmark::createLease4Test() {
for (uint8_t i = 0; i < 20; i++) {
hwaddr[i] = 65 + i;
}
+ hwaddr[19] = 0;
for (uint8_t i = 0; i < 128; i++) {
client_id[i] = 33 + i;
}
+ client_id[127] = 0;
+
+ MYSQL_STMT * stmt = NULL;
+ MYSQL_BIND bind[11]; // 11 parameters in the insert statement
+
+ if (compiled_stmt_) {
+ // create a statement once
+ stmt = mysql_stmt_init(Conn_);
+ if (!stmt) {
+ failure("Unable to create compiled statement, mysql_stmt_init() failed");
+ }
+
+ const char * statement = "INSERT INTO lease4(addr,hwaddr,client_id,"
+ "valid_lft,recycle_time,cltt,pool_id,fixed,hostname,"
+ "fqdn_fwd,fqdn_rev) VALUES(?,?,?,?,?,?,?,?,?,?,?)";
+
+ if (mysql_stmt_prepare(stmt, statement, strlen(statement) )) {
+ failure("Failed to prepare statement, mysql_stmt_prepare() returned non-zero");
+ }
+ int param_cnt = mysql_stmt_param_count(stmt);
+ if (param_cnt != 11) {
+ failure("Parameter count sanity check failed.");
+ }
+
+ memset(bind, 0, sizeof(bind));
+
+ // 1st parameter: IPv4 address
+ bind[0].buffer_type = MYSQL_TYPE_LONG;
+ bind[0].buffer = (&addr);
+ bind[0].is_null = 0;
+ bind[0].length = 0;
+
+ // 2nd parameter: Hardware address
+ bind[1].buffer_type = MYSQL_TYPE_VARCHAR;
+ bind[1].buffer = hwaddr;
+ bind[1].buffer_length = hwaddr_len;
+ bind[1].is_null = 0;
+ bind[1].length = &hwaddr_len;
+
+ // 3rd parameter: Client-id
+ bind[2].buffer_type = MYSQL_TYPE_VARCHAR;
+ bind[2].buffer = client_id;
+ bind[2].buffer_length = client_id_len;
+ bind[2].is_null = 0;
+ bind[2].length = &client_id_len;
+
+ // 4th parameter: valid-lifetime
+ bind[3].buffer_type = MYSQL_TYPE_LONG;
+ bind[3].buffer = (&valid_lft);
+ bind[3].is_null = 0;
+ bind[3].length = 0;
+
+ // 5th parameter: recycle-time
+ bind[4].buffer_type = MYSQL_TYPE_LONG;
+ bind[4].buffer = (&recycle_time);
+ bind[4].is_null = 0;
+ bind[4].length = 0;
+
+ // 6th parameter: cltt
+ bind[5].buffer_type = MYSQL_TYPE_TIMESTAMP;
+ bind[5].buffer = cltt;
+ bind[2].buffer_length = cltt_len;
+ bind[5].is_null = 0;
+ bind[5].length = &cltt_len;
+
+ // 7th parameter: pool-id
+ bind[6].buffer_type = MYSQL_TYPE_LONG;
+ bind[6].buffer = &pool_id;
+ bind[6].is_null = 0;
+ bind[6].length = 0;
+
+ // 8th parameter: fixed
+ bind[7].buffer_type = MYSQL_TYPE_TINY;
+ bind[7].buffer = &fixed;
+ bind[7].is_null = 0;
+ bind[7].length = 0;
+
+ // 9th parameter: hostname
+ bind[8].buffer_type = MYSQL_TYPE_VARCHAR;
+ bind[8].buffer = hostname;
+ bind[8].buffer_length = strlen(hostname);
+ bind[8].is_null = 0;
+ bind[8].length = &hostname_len;
+
+ // 10th parameter: fqdn_fwd
+ bind[9].buffer_type = MYSQL_TYPE_TINY;
+ bind[9].buffer = &fqdn_fwd;
+ bind[9].is_null = 0;
+ bind[9].length = 0;
+
+ // 11th parameter: fqdn_rev
+ bind[10].buffer_type = MYSQL_TYPE_TINY;
+ bind[10].buffer = &fqdn_rev;
+ bind[10].is_null = 0;
+ bind[10].length = 0;
+ }
for (uint32_t i = 0; i < num_; i++) {
- stringstream cltt;
- cltt << "2012-07-11 15:43:" << (i % 60);
+ sprintf(cltt, "2012-07-11 15:43:%02d", i % 60);
+
addr++;
- // the first address is 1.0.0.0.
- char query[2000], * end;
- strcpy(query, "INSERT INTO lease4(addr,hwaddr,client_id,"
- "valid_lft,recycle_time,cltt,pool_id,fixed,hostname,"
- "fqdn_fwd,fqdn_rev) VALUES(");
- end = query + strlen(query);
- end += sprintf(end, "%u,\'", addr);
- end += mysql_real_escape_string(Conn_, end, hwaddr, hwaddr_len);
- end += sprintf(end,"\',\'");
- end += mysql_real_escape_string(Conn_, end, client_id, client_id_len);
- end += sprintf(end, "\',%d,%d,'%s',%d,%s,\'%s\',%s,%s);",
- valid_lft, recycle_time, cltt.str().c_str(),
- pool_id, (fixed?"true":"false"), hostname.c_str(),
- (fqdn_fwd?"true":"false"), (fqdn_rev?"true":"false"));
- // lease_id field is set automatically
- // options and comments fields are not set
-
- unsigned int len = end - query;
- if (mysql_real_query(Conn_, query, len)) {
- // something failed.
- failure("INSERT query");
+ if (!compiled_stmt_) {
+ // the first address is 1.0.0.0.
+ char query[2000], * end;
+ strcpy(query, "INSERT INTO lease4(addr,hwaddr,client_id,"
+ "valid_lft,recycle_time,cltt,pool_id,fixed,hostname,"
+ "fqdn_fwd,fqdn_rev) VALUES(");
+ end = query + strlen(query);
+ end += sprintf(end, "%u,\'", addr);
+ end += mysql_real_escape_string(Conn_, end, hwaddr, hwaddr_len);
+ end += sprintf(end,"\',\'");
+ end += mysql_real_escape_string(Conn_, end, client_id, client_id_len);
+ end += sprintf(end, "\',%d,%d,'%s',%d,%s,\'%s\',%s,%s);",
+ valid_lft, recycle_time, cltt,
+ pool_id, (fixed?"true":"false"), hostname,
+ (fqdn_fwd?"true":"false"), (fqdn_rev?"true":"false"));
+ // lease_id field is set automatically
+ // options and comments fields are not set
+
+ unsigned int len = end - query;
+ if (mysql_real_query(Conn_, query, len)) {
+ // something failed.
+ failure("INSERT query");
+ }
} else {
- if (verbose_) {
- printf(".");
+ // compiled statement
+
+ if (mysql_stmt_bind_param(stmt, bind)) {
+ failure("Failed to bind parameters: mysql_stmt_bind_param() returned non-zero");
+ }
+
+ if (mysql_stmt_execute(stmt)) {
+ failure("Failed to execute statement: mysql_stmt_execute() returned non-zero");
}
- };
+
+ }
+
+ if (verbose_) {
+ printf(".");
+ }
+ }
+
+ if (compiled_stmt_) {
+ if (mysql_stmt_close(stmt)) {
+ failure("Failed to close compiled statement, mysql_stmt_close returned non-zero");
+ }
}
+
printf("\n");
}
@@ -154,56 +280,190 @@ void MySQL_uBenchmark::searchLease4Test() {
throw "Not connected to MySQL server.";
}
- /* cout << "range=" << int(Num_ / hitRatio) << " minAddr=" << hex
- << BASE_ADDR4 << " maxAddr=" << BASE_ADDR4 + int(Num_ / hitRatio)
- << dec << endl; */
-
printf("RETRIEVE: ");
+ uint32_t addr = 0;
+
+ MYSQL_STMT * stmt = NULL;
+ MYSQL_BIND bind[1]; // just a single element
+ if (compiled_stmt_) {
+ stmt = mysql_stmt_init(Conn_);
+ if (!stmt) {
+ failure("Unable to create compiled statement");
+ }
+ const char * statement = "SELECT lease_id,addr,hwaddr,client_id,"
+ "valid_lft, cltt,pool_id,fixed,hostname,fqdn_fwd,fqdn_rev "
+ "FROM lease4 where addr=?";
+ if (mysql_stmt_prepare(stmt, statement, strlen(statement))) {
+ failure("Failed to prepare statement, mysql_stmt_prepare() returned non-zero");
+ }
+ int param_cnt = mysql_stmt_param_count(stmt);
+ if (param_cnt != 1) {
+ failure("Parameter count sanity check failed.");
+ }
+
+ memset(bind, 0, sizeof(bind));
+
+ // 1st parameter: IPv4 address
+ bind[0].buffer_type = MYSQL_TYPE_LONG;
+ bind[0].buffer = (&addr);
+ bind[0].is_null = 0;
+ bind[0].length = 0;
+ }
for (uint32_t i = 0; i < num_; i++) {
- uint32_t x = BASE_ADDR4 + random() % int(num_ / hitratio_);
+ addr = BASE_ADDR4 + random() % int(num_ / hitratio_);
- char query[2000];
- sprintf(query, "SELECT lease_id,addr,hwaddr,client_id,valid_lft,"
- "cltt,pool_id,fixed,hostname,fqdn_fwd,fqdn_rev "
- "FROM lease4 where addr=%d", x);
- mysql_real_query(Conn_, query, strlen(query));
+ if (!compiled_stmt_) {
+ char query[512];
+ sprintf(query, "SELECT lease_id,addr,hwaddr,client_id,valid_lft,"
+ "cltt,pool_id,fixed,hostname,fqdn_fwd,fqdn_rev "
+ "FROM lease4 where addr=%d", addr);
+ mysql_real_query(Conn_, query, strlen(query));
- MYSQL_RES * result = mysql_store_result(Conn_);
+ MYSQL_RES * result = mysql_store_result(Conn_);
- int num_rows = mysql_num_rows(result);
- int num_fields = mysql_num_fields(result);
+ int num_rows = mysql_num_rows(result);
+ int num_fields = mysql_num_fields(result);
- if ( (num_rows > 1) ) {
- stringstream tmp;
- tmp << "Search: DB returned " << num_rows << " leases for address "
- << hex << x << dec;
- failure(tmp.str().c_str());
- }
+ if ( (num_rows > 1) ) {
+ stringstream tmp;
+ tmp << "Search: DB returned " << num_rows << " leases for address "
+ << hex << addr << dec;
+ failure(tmp.str().c_str());
+ }
+
+ if (num_rows) {
+ if (num_fields == 0) {
+ failure("Query returned empty set");
+ }
+
+ MYSQL_ROW row = mysql_fetch_row(result);
+
+ // pretend to do something with it
+ if (row[0] == NULL) {
+ failure("SELECT returned NULL data.");
+ }
+ mysql_free_result(result);
- if (num_rows) {
- if (num_fields == 0) {
- failure("Query returned empty set");
+ if (verbose_) {
+ printf("."); // hit
+ }
+
+ } else {
+ if (verbose_) {
+ printf("x"); // miss
+ }
}
+ } else {
+ // compiled statement
- MYSQL_ROW row = mysql_fetch_row(result);
+ if (mysql_stmt_bind_param(stmt, bind)) {
+ failure("Failed to bind parameters: mysql_stmt_bind_param() returned non-zero");
+ }
- // pretend to do something with it
- if (row[0] == NULL) {
- failure("SELECT returned NULL data.");
+ if (mysql_stmt_execute(stmt)) {
+ failure("Failed to execute statement: mysql_stmt_execute() returned non-zero");
}
- mysql_free_result(result);
- if (verbose_) {
- printf(".");
+ MYSQL_BIND response[11];
+
+ size_t length[11];
+ my_bool is_null[11];
+ my_bool error[11];
+
+ uint32_t lease_id;
+ uint32_t lease_addr;
+ char hwaddr[20];
+ char client_id[128];
+ uint32_t valid_lft; // We can use the same value for all leases
+ MYSQL_TIME cltt;
+ uint32_t pool_id;
+ my_bool fixed;
+ char hostname[255];
+ my_bool fqdn_fwd;
+ my_bool fqdn_rev;
+
+ for (int j = 0; j < 11; j++) {
+ response[j].is_null = &is_null[j];
+ response[j].length = &length[j];
+ response[j].error = &error[j];
}
- } else {
- if (verbose_) {
- printf("x");
+ // 1th parameter: lease_id
+ response[0].buffer_type = MYSQL_TYPE_LONG;
+ response[0].buffer = (&lease_id);
+
+ // 2nd parameter: IPv4 address
+ response[1].buffer_type = MYSQL_TYPE_LONG;
+ response[1].buffer = (&lease_addr);
+
+ // 3rd parameter: Hardware address
+ response[2].buffer_type = MYSQL_TYPE_STRING;
+ response[2].buffer = hwaddr;
+ response[2].buffer_length = sizeof(hwaddr);
+
+ // 4th parameter: Client-id
+ response[3].buffer_type = MYSQL_TYPE_STRING;
+ response[3].buffer = client_id;
+
+ // 5th parameter: valid-lifetime
+ response[4].buffer_type = MYSQL_TYPE_LONG;
+ response[4].buffer = &valid_lft;
+
+ // 6th parameter: cltt
+ response[5].buffer_type = MYSQL_TYPE_TIMESTAMP;
+ response[5].buffer = &cltt;
+
+ // 7th parameter: pool-id
+ response[6].buffer_type = MYSQL_TYPE_LONG;
+ response[6].buffer = &pool_id;
+
+ // 8th parameter: fixed
+ response[7].buffer_type = MYSQL_TYPE_TINY;
+ response[7].buffer = &fixed;
+
+ // 9th parameter: hostname
+ response[8].buffer_type = MYSQL_TYPE_STRING;
+ response[8].buffer = hostname;
+
+ // 10th parameter: fqdn_fwd
+ response[9].buffer_type = MYSQL_TYPE_TINY;
+ response[9].buffer = &fqdn_fwd;
+
+ // 11th parameter: fqdn_rev
+ response[10].buffer_type = MYSQL_TYPE_TINY;
+ response[10].buffer = &fqdn_rev;
+
+ if (mysql_stmt_bind_result(stmt, response))
+ {
+ printf("Error: %s\n", mysql_stmt_error(stmt));
+ failure("mysql_stmt_bind_result() failed");
+ }
+ int num_rows = 0;
+
+ if (!mysql_stmt_fetch(stmt)) {
+ if (lease_addr != addr) {
+ failure("Returned data is bogus!");
+ }
+ num_rows++;
+ }
+
+ // we could call mysql_stmt_fetch again to check that there are no
+ // other data for us. But there should be exactly one row of data
+ // with specified address.
+
+ if (num_rows) {
+ if (verbose_) {
+ printf("."); // hit
+ }
+ } else {
+ if (verbose_) {
+ printf("x"); // miss
+ }
}
+
}
}
More information about the bind10-changes
mailing list