BIND 10 #2699: DHCP performance: use composite search indexes on the lease4 table to improve performance

[BIND 10 Development]

#2699: DHCP performance: use composite search indexes on the lease4 table to
improve performance
-------------------------------------+-------------------------------------
                   Reporter:         |                 Owner:
  marcin                             |                Status:  new
                       Type:         |             Milestone:
  defect                             |  Sprint-20130219
                   Priority:  high   |              Keywords:
                  Component:  dhcp4  |             Sensitive:  0
               CVSS Scoring:         |           Sub-Project:  DHCP
            Defect Severity:         |  Estimated Difficulty:  0
  Medium                             |           Total Hours:  0
Feature Depending on Ticket:         |
        Add Hours to Ticket:  0      |
                  Internal?:  0      |
-------------------------------------+-------------------------------------
 In the course of #2606 it was spotted that the performance of the DHCPv4
 server can be significantly improved: from around 300 leases/sec up to
 1100 leases/sec by the following:
 - Create composite search indexes when creating the '''lease4''' table
 that holds acquired leases. When the following indexes were created during
 the test: (hwaddr, subnet_id) and (client_id, subnet_id) the performance
 measures jumped from 300 to 1000 leases /sec
 - Set the innodb_flush_log_at_trx_commit MySQL InnoDB variable to '2'.
 With this setting the performance jumped from 1000 to 1100 leases/sec.
 (BTW, I presume that the variable can be only set when starting the mysqld
 so our effort my by just limited to update the performance guides to
 instruct users that this setting will improve the performance.)

-- 
Ticket URL: <http://bind10.isc.org/ticket/2699>
BIND 10 Development <http://bind10.isc.org>
BIND 10 Development