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-20120903
  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      |
-------------------------------------+-------------------------------------

Comment (by tomek):

 Replying to [comment:7 stephen]:
 > 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.

 Support for compiled statements in MySQL has been added in commits:
 6a212576107d4b72f31249850b634eb6f7fec5ce
 8c3cf6602f8977639b544fefbc6eb869b01eab07

 I was disappointed at the results. I haven't run extensive tests, but
 quick runs should around 10% improvement with compiled statement. It is
 paid for with increased code complexity.

 Both query types ("classic" and compiled statements) has been implemented
 within the same methods, e.g. createLease4Test() with several "if
 (compiled_stmt_) {...}" clauses. It would be cleaner to implement them as
 separate methods, but then it would be difficult to maintain the same
 parameters in both approaches.

 Ok, now on to the last thing: compiled statements in SQLite.

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