BIND 10 #2142: Design and Creation Script for SQL Lease Database

[BIND 10 Development]

#2142: Design and Creation Script for SQL Lease Database
-------------------------------------+-------------------------------------
                   Reporter:         |                 Owner:  stephen
  stephen                            |                Status:  reviewing
                       Type:  task   |             Milestone:  Sprint-
                   Priority:         |  DHCP-20121018
  medium                             |            Resolution:
                  Component:  dhcp   |             Sensitive:  0
                   Keywords:         |           Sub-Project:  DHCP
            Defect Severity:  N/A    |  Estimated Difficulty:  0
Feature Depending on Ticket:         |           Total Hours:  0
        Add Hours to Ticket:  0      |
                  Internal?:  0      |
-------------------------------------+-------------------------------------
Changes (by tomek):

 * owner:  tomek => stephen


Comment:

 Replying to [comment:5 stephen]:
 > > 3. There should be a comment about lack of indices. (That this is a
 design choice, because DHCP database is very dynamic and would require
 very frequent index regeneration, so the overhead is likely to outweigh
 read performance boosts that index provides).
 > There is, at the end of the file.
 >
 > In practice, the need for additional indexes will be determined by
 access pattern.  By default an index is created for the primary key
 (lookup by IP address).  An index on expiration time is probably not
 needed unless we are scanning for deleted leases very frequently (although
 when we do scan, all records will be read).
 Access pattern strongly correlates with user characteristics. It is very
 much different for fixed cable modems that get a lease and are almost
 never powered down and hotspot clients that come and go and even change
 locations frequently. It is ok to leave it as it is, without any indexes.
 I think once we grow Performance Guide, one day we will have a chapter
 about using indexes as a possible speed up.

 > > 4. There should be a comment about portability (this was explicitly
 requested). It should be pointed that the schema can be used as is, with
 the exception of engine specification, which is likely MySQL specific.
 > Added.
 Speaking about the portability, perhaps this script could be renamed to
 not explicitly have mysql in its name? We could have a generic script
 name. It already states in the script that it is for MySQL. That would
 give more "portable" feel. It's not a strong suggestion, but something you
 may consider.

 > > 5. InnoDB in its default configuration is fully synchronous, which is
 safe, but yields very low performance. There should at least be a
 discussion about
 > I don't think it is needed.  The MySQL database layer constructor turns
 off autocommit which makes all operations transaction-based.
 Ok.

 > > We should consider adding "CREATE database Kea;" at the beginning.
 > I'd rather not do this - there is always the possibility that the
 physical database is shared between two independent servers: in this case,
 it could be that two databases are used.
 Disagree for two reasons. What you say is possible, but rather unlikely.
 It is much more likely that the user will run the script and then get
 confused about lots of errors, because no database was created. The second
 point is that CREATE database does not remove existing database, if it
 exists.

 > Also, creating the database required logging in as a privileged user,
 whereas an unprivileged user with access to a database can always create
 extra tables.
 Your argument is partially valid for future schema tweaks, but not for the
 initial creation.  As far as I understand, MySQL offers fine granularity
 of access control, so regular users may have DB creation privileges.

 When you think about it, splitting database and table creation makes the
 initial setup more complicated. Think about this as an attempt to make
 initial deployment easier.

 > > 7. The database installation and setup must be documented somehow. It
 doesn't have to be done as part of this ticket. Probably MySQL backend
 ticket will be more appropriate for that, but this script should at least
 have 2 line instruction, how to use that schema. Something along the
 lines:
 > Done. The documentation will be added to the backend ticket.
 Ok, great.

 The ticket is back with you. Regardless if you choose to accept or reject
 my comments, I don't see a need to discuss them further. Please do as you
 think is right and merge the ticket. I don't need to review it again.

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