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:  marcin        |                        Owner:  tmark
                Type:  defect        |                       Status:
            Priority:  high          |  assigned
           Component:  dhcp4         |                    Milestone:
            Keywords:                |  Sprint-DHCP-20130214
           Sensitive:  0             |                   Resolution:
         Sub-Project:  DHCP          |                 CVSS Scoring:
Estimated Difficulty:  0             |              Defect Severity:
         Total Hours:  0             |  Medium
                                     |  Feature Depending on Ticket:
                                     |          Add Hours to Ticket:  0
                                     |                    Internal?:  0
-------------------------------------+-------------------------------------

Comment (by tmark):

 Create index statements were added for both the lease4 and for the lease6
 (see #2703) tables.
 Running simple clients demonstrated no issues creating, locating leases
 for renewal.

 A bit more digging innodb_flush_log_at_trx_commit shows that is can be set
 in mysql startup configuration file (my.cnf) it also indicates that it
 impacts the entire instance of mysql and has fairly broad implications
 when it comes to ACID compliance, replication, etc.  It is certainly
 something that would need to examined by local DBAs.

 From MySQL documentation

 "A value of 1 is required for ACID compliance. You can achieve better
 performance by setting the value different from 1, but then you can lose
 at most one second worth of transactions in a crash. With a value of 0,
 any mysqld process crash can erase the last second of transactions. With a
 value of 2, then only an operating system crash or a power outage can
 erase the last second of transactions. However, InnoDB's crash recovery is
 not affected and thus crash recovery does work regardless of the value.
 Note

 For the greatest possible durability and consistency in a replication
 setup using InnoDB with transactions, use
 innodb_flush_log_at_trx_commit=1, sync_binlog=1, and innodb-safe-binlog in
 your master server my.cnf file.

 Caution

 Many operating systems and some disk hardware fool the flush-to-disk
 operation. They may tell mysqld that the flush has taken place, even
 though it has not. Then the durability of transactions is not guaranteed
 even with the setting 1, and in the worst case a power outage can even
 corrupt the InnoDB database. Using a battery-backed disk cache in the SCSI
 disk controller or in the disk itself speeds up file flushes, and makes
 the operation safer. You can also try using the Unix command hdparm to
 disable the caching of disk writes in hardware caches, or use some other
 command specific to the hardware vendor.
 "

 Note the cautionary statement. Realized performance increases would likely
 vary widely from setup to setup.

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