BIND 10 #2042: Prototype backend performance microbenchmark: MySQL

[BIND 10 Development]

#2042: Prototype backend performance microbenchmark: MySQL
-------------------------------------+-------------------------------------
                   Reporter:  tomek  |                 Owner:  tomek
                       Type:         |                Status:  reviewing
  enhancement                        |             Milestone:  Sprint-
                   Priority:         |  DHCP-20120730
  medium                             |            Resolution:
                  Component:         |             Sensitive:  0
  dhcpdb                             |           Sub-Project:  DHCP
                   Keywords:         |  Estimated Difficulty:  0
            Defect Severity:  N/A    |           Total Hours:  0
Feature Depending on Ticket:         |
        Add Hours to Ticket:  0      |
                  Internal?:  0      |
-------------------------------------+-------------------------------------
Changes (by stephen):

 * owner:  stephen => tomek


Comment:

 Reviewed commit 6b80e1e57145e8b0796d872f51ca39a0f4bbc17a

 Note: a number of small changes (mainly to comments and word spacing) have
 been made and the results pushed to the repository.

 '''General'''[[BR]]
 If these benchmarks are going to form part of the BIND-10 distribution,
 then the source files do need comments and the methods need unit tests.
 (The absence of unit tests was agreed on the assumption that they were not
 going to be distributed.)

 It is OK that they do not form part of the overall BIND 10 build - they
 are completely separate and !MySql is not likely to be installed on all
 systems.

 The most important change needed to get realistic results is to try
 prepared statements in the database measurements.


 '''tests/tools/dhcp-ubench/Makefile'''[[BR]]
 When looking at it, my editor complained about the lack of a newline at
 the end of the file, so I added one.  I've also corrected a couple of
 minor errors concerning which flags are on which command line.

 '''tests/tools/dhcp-ubench/README'''[[BR]]
 OK, but I think there could perhaps be a fuller explanation if this will
 be part of the distributed code.


 '''tests/tools/dhcp-ubench/benchmark.cc'''[[BR]]
 When parsing an integer, suggest that boost::lexical_cast is used.  The
 problem with strtol() is that it ignores trailing invalid characters in a
 number (although they can be detected by checking the value returned in
 the endptr variable).

 When parsing command switches 's' and 'v', the "if" block could be
 replaced by:
 {{{
 <variable> = !strcasecmp(optarg, "yes") || !strcmp(optarg, "1");
 }}}

 There is no need for the "case ':'" immediately before "default" - the
 latter covers it.

 '''tests/tools/dhcp-ubench/benchmark.h'''[[BR]]
 Member variable names should start with a lower-case letter.

 Variable "ts" should be named "ts_" as it is a member variable.


 '''tests/tools/dhcp-ubench/dhcp-perf-guide.{html,xml}'''[[BR]]
 Good start.  We'll do an editing pass on it once there is more there.

 '''tests/tools/dhcp-ubench/memfile_ubench.cc'''[[BR]]
 Member variable names should start with a lower-case letter.

 '''tests/tools/dhcp-ubench/memfile_ubench.h'''[[BR]]
 Member variable names should start with a lower-case letter.

 Members of Lease4 structure should include inline comments.

 getLease(): "if" block should be enclosed in braces.

 '''tests/tools/dhcp-ubench/mysql_ubench.cc'''[[BR]]
 The variables initialized in createLease4Test() look a lot like those in
 the memory benchmark: they could perhaps be member variables in the base
 class and initialized in the constructor.

 The benchmark does not make optimum use of !MySql.  Creating an SQL
 statement via sprintf() and and passing it to mysql_real_query() incurs
 overhead in formatting the binary data, as well as overhead in repeatedly
 parsing the statement and preparing the execution plan.  Faster is to use
 mysql_stmt_init() and mysql_stmt_prepare() to prepare the statement,
 mysql_stmt_bind_param() to bind parameters to it and mysql_stmt_execute()
 to execute it.  The first two calls need only be done once for the loop.
 See http://dev.mysql.com/doc/refman/5.5/en/c-api-prepared-statements.html
 for more details.

 hitRatio is 0.5 for the memory benchmark and 0.9 for the !MySql one.  To
 eliminate some uncertainty in the comparison, they should be the same for
 each.  (In fact, the overall logic of the benchmark could be in the base
 class, with the access to the underlying database abstracted into methods
 in the database specialization classes).

 '''tests/tools/dhcp-ubench/mysql_ubench.h'''[[BR]]
 Member variable names should start with a lower-case letter.

 '''tests/tools/dhcp-ubench/sqlite_ubench.cc'''[[BR]]
 Same point concerning the use of prepared statements made above applies
 here: see http://www.sqlite.org/c3ref/prepare.html.

 Also applicable here are the points made in the mySql about createLease4()
 data initializations and the hitRatio value.

 '''tests/tools/dhcp-ubench/sqlite_ubench.h'''[[BR]]
 Member variable names should start with a lower-case letter.

-- 
Ticket URL: <http://bind10.isc.org/ticket/2042#comment:7>
BIND 10 Development <http://bind10.isc.org>
BIND 10 Development