BIND 10 trac2041, updated. 7f37eed04092bb2f7babb50eadb10e28d3d776eb [2041] SQLite benchmark coding complete.
BIND 10 source code commits
bind10-changes at lists.isc.org
Mon Jul 16 10:59:09 UTC 2012
The branch, trac2041 has been updated
via 7f37eed04092bb2f7babb50eadb10e28d3d776eb (commit)
from 6c8b787f32ae2ab882af1a3d8479666e676cf996 (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 7f37eed04092bb2f7babb50eadb10e28d3d776eb
Author: Tomek Mrugalski <tomasz at isc.org>
Date: Mon Jul 16 12:58:34 2012 +0200
[2041] SQLite benchmark coding complete.
-----------------------------------------------------------------------
Summary of changes:
tests/tools/dhcp-ubench/Makefile | 2 +-
tests/tools/dhcp-ubench/README | 46 +++++++++++++++++++-
.../dhcp-ubench/{mysql-schema.sql => mysql.schema} | 0
tests/tools/dhcp-ubench/sqlite_ubench.cc | 11 ++++-
4 files changed, 54 insertions(+), 5 deletions(-)
rename tests/tools/dhcp-ubench/{mysql-schema.sql => mysql.schema} (100%)
-----------------------------------------------------------------------
diff --git a/tests/tools/dhcp-ubench/Makefile b/tests/tools/dhcp-ubench/Makefile
index 0ef6a32..5bc2f71 100644
--- a/tests/tools/dhcp-ubench/Makefile
+++ b/tests/tools/dhcp-ubench/Makefile
@@ -24,4 +24,4 @@ sqlite_ubench: sqlite_ubench.o benchmark.o
$(CXX) $< benchmark.o -o sqlite_ubench $(CFLAGS) $(SQLITE_CFLAGS) $(SQLITE_LDFLAGS)
clean:
- rm -f mysql_ubench *.o
+ rm -f mysql_ubench sqlite_ubench *.o
diff --git a/tests/tools/dhcp-ubench/README b/tests/tools/dhcp-ubench/README
index 463ec3e..6e48dc7 100644
--- a/tests/tools/dhcp-ubench/README
+++ b/tests/tools/dhcp-ubench/README
@@ -64,7 +64,7 @@ WARNING: It will drop existing Kea database. Do not run this on
your production server. Assuming your MySQL user is kea, you can
initialize your test database by:
-mysql -u kea -p < mysql-init.sql
+mysql -u kea -p < mysql.schema
After that step, you are ready to run the test:
@@ -72,7 +72,7 @@ After that step, you are ready to run the test:
or
-./mysql_ubench > results.txt
+./mysql_ubench > results-mysql.txt
Redirecting output to a file is important, because for each operation
there is a single character printed to show progress. If you have a slow
@@ -100,3 +100,45 @@ can tweak it by using the following command in mysql:
or
> alter table lease4 engine=InnoDB;
+
+ SQLite backend
+----------------
+SQLite backend requires both sqlite3 development and run-time package. Their
+names may vary from system to system, but on Ubuntu 12.04 they are called
+sqlite3 libsqlite3-dev. To install them, use the following command:
+
+sudo apt-get install sqlite3 libsqlite3-dev
+
+To run SQLite3 tests, first create the database:
+
+cat sqlite.schema | sqlite3 sqlite.db
+
+A new database called sqlite.db will be created. That is the default name used
+by sqlite_ubench test. If you prefer other name, make sure you update
+sqlite_ubench.cc accordingly. Once the database is created, you can run
+tests:
+
+./sqlite_ubench > results-sqlite.txt
+
+Make sure to tweak number of iterations in sqlite_ubench.cc. See num variable
+in main() function near the end of sqlite_ubench.cc file.
+
+To improve performance, asynchronous mode is used (PRAGMA synchronous = OFF).
+If you do not like it, modify SQLite_uBenchmark::connect() by commenting it out
+and suffer the pain of having around 10 inserts per seconds.
+
+ Performance optimizations
+---------------------------
+
+The following section is a collection of loose notes, ideas and comments that
+could be investigated to possibly improve (or thrash) performance. Please
+consider them as developer's scratchpad only. However, if you are experienced
+programmer or a DB admin, you are welcome to try some of them. ISC engineers
+are more than welcome to discuss pros and cons of various approaches on
+bind10-dev at lists.isc.org list.
+
+- SQLite: Excellent article that discusses performance optimizations:
+ - http://stackoverflow.com/questions/1711631/how-do-i-improve-the-performance-of-sqlite
+ - http://blog.quibb.org/2010/08/fast-bulk-inserts-into-sqlite/
+ - use prepared statements
+ - use journal mode in memory (or disable it completely)
diff --git a/tests/tools/dhcp-ubench/mysql-schema.sql b/tests/tools/dhcp-ubench/mysql-schema.sql
deleted file mode 100644
index ed01e46..0000000
--- a/tests/tools/dhcp-ubench/mysql-schema.sql
+++ /dev/null
@@ -1,86 +0,0 @@
-DROP DATABASE kea;
-CREATE DATABASE kea;
-
-CONNECT kea;
-
-CREATE TABLE lease4 (
-
- # Primary key (serial = BININT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE)
- lease_id SERIAL,
- addr INT UNSIGNED UNIQUE,
-
- # The largest hardware address is for Infiniband (20 bytes)
- hwaddr VARCHAR(20),
-
- # The largest client-id is DUID in DHCPv6 - up to 128 bytes
- client_id VARCHAR(128),
-
- # Expressed in seconds
- valid_lft INT,
-
- # Expressed in seconds,
- recycle_time INT DEFAULT 0,
-
- cltt TIMESTAMP,
-
- pool_id int,
-
- fixed BOOL,
-
- # DDNS stuff
- hostname VARCHAR(255),
- fqdn_fwd BOOL DEFAULT false,
- fqdn_rev BOOL DEFAULT false,
-
- options TEXT,
- comments TEXT
-);
-
-CREATE TABLE lease6 (
-
- # Primary key (serial = BININT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE)
- lease_id SERIAL,
- addr CHAR(16) BYTE UNIQUE,
-
- # The largest hardware address is for Infiniband (20 bytes)
- hwaddr VARCHAR(20),
-
- # The largest client-id is DUID in DHCPv6 - up to 128 bytes
- client_id VARCHAR(128),
-
- iaid int unsigned,
-
- # Used for IA_PD only (tinyint = 0..255)
- prefix_len TINYINT unsigned,
-
- # Expressed in seconds
- preferred_lft INT,
-
- # Expressed in seconds
- valid_lft INT,
-
- # Expressed in seconds,
- recycle_time INT DEFAULT 0,
-
- cltt TIMESTAMP,
-
- pool_id int,
-
- fixed BOOL DEFAULT false,
-
- hostname VARCHAR(255),
- fqdn_fwd BOOL DEFAULT false,
- fqdn_rev BOOL DEFAULT false,
-
- options TEXT,
- comments TEXT
-);
-
-CREATE TABLE host (
- address BIGINT NULL,
- address6 BIGINT NULL,
- prefix6 BIGINT NULL,
- hostname VARCHAR(255),
- options TEXT,
- comments TEXT
-);
\ No newline at end of file
diff --git a/tests/tools/dhcp-ubench/mysql.schema b/tests/tools/dhcp-ubench/mysql.schema
new file mode 100644
index 0000000..ed01e46
--- /dev/null
+++ b/tests/tools/dhcp-ubench/mysql.schema
@@ -0,0 +1,86 @@
+DROP DATABASE kea;
+CREATE DATABASE kea;
+
+CONNECT kea;
+
+CREATE TABLE lease4 (
+
+ # Primary key (serial = BININT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE)
+ lease_id SERIAL,
+ addr INT UNSIGNED UNIQUE,
+
+ # The largest hardware address is for Infiniband (20 bytes)
+ hwaddr VARCHAR(20),
+
+ # The largest client-id is DUID in DHCPv6 - up to 128 bytes
+ client_id VARCHAR(128),
+
+ # Expressed in seconds
+ valid_lft INT,
+
+ # Expressed in seconds,
+ recycle_time INT DEFAULT 0,
+
+ cltt TIMESTAMP,
+
+ pool_id int,
+
+ fixed BOOL,
+
+ # DDNS stuff
+ hostname VARCHAR(255),
+ fqdn_fwd BOOL DEFAULT false,
+ fqdn_rev BOOL DEFAULT false,
+
+ options TEXT,
+ comments TEXT
+);
+
+CREATE TABLE lease6 (
+
+ # Primary key (serial = BININT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE)
+ lease_id SERIAL,
+ addr CHAR(16) BYTE UNIQUE,
+
+ # The largest hardware address is for Infiniband (20 bytes)
+ hwaddr VARCHAR(20),
+
+ # The largest client-id is DUID in DHCPv6 - up to 128 bytes
+ client_id VARCHAR(128),
+
+ iaid int unsigned,
+
+ # Used for IA_PD only (tinyint = 0..255)
+ prefix_len TINYINT unsigned,
+
+ # Expressed in seconds
+ preferred_lft INT,
+
+ # Expressed in seconds
+ valid_lft INT,
+
+ # Expressed in seconds,
+ recycle_time INT DEFAULT 0,
+
+ cltt TIMESTAMP,
+
+ pool_id int,
+
+ fixed BOOL DEFAULT false,
+
+ hostname VARCHAR(255),
+ fqdn_fwd BOOL DEFAULT false,
+ fqdn_rev BOOL DEFAULT false,
+
+ options TEXT,
+ comments TEXT
+);
+
+CREATE TABLE host (
+ address BIGINT NULL,
+ address6 BIGINT NULL,
+ prefix6 BIGINT NULL,
+ hostname VARCHAR(255),
+ options TEXT,
+ comments TEXT
+);
\ No newline at end of file
diff --git a/tests/tools/dhcp-ubench/sqlite_ubench.cc b/tests/tools/dhcp-ubench/sqlite_ubench.cc
index fef1961..19a9e8a 100644
--- a/tests/tools/dhcp-ubench/sqlite_ubench.cc
+++ b/tests/tools/dhcp-ubench/sqlite_ubench.cc
@@ -40,7 +40,14 @@ void SQLite_uBenchmark::connect() {
failure("Failed to open DB file");
}
- sqlite3_exec(DB_, "DELETE FROM lease4", 0, 0, 0);
+ sqlite3_exec(DB_, "DELETE FROM lease4", NULL, NULL, NULL);
+
+ sqlite3_exec(DB_, "PRAGMA synchronous = OFF", NULL, NULL, NULL);
+
+ // see http://www.sqlite.org/pragma.html#pragma_journal_mode
+ // for detailed explanation. Available modes: DELETE, TRUNCATE,
+ // PERSIST, MEMORY, WAL, OFF
+ sqlite3_exec(DB_, "PRAGMA journal_mode = OFF", NULL, NULL, NULL);
}
void SQLite_uBenchmark::disconnect() {
@@ -235,7 +242,7 @@ void SQLite_uBenchmark::printInfo() {
int main(int argc, const char * argv[]) {
const char * filename = "sqlite.db";
- uint32_t num = 10;
+ uint32_t num = 10000;
SQLite_uBenchmark bench(filename, num);
More information about the bind10-changes
mailing list