Speeding up the Check for Orphaned IP Addresses


  Checking for orphaned IP addresses in NetDB_Network.verify_and_log()
  is slow.  On our current hardware with 150,000 IP addresses and 1000
  IP address spaces it takes around 12 seconds.  [DHCP generation uses
  the same "where IPaddress is address and broadcast" clause and takes
  the same amount of time.]

  We think part of the slowness is Oracle checking every address space
  against every address.  The database doesn't know that we ensure that
  address spaces don't overlap, so an address is in one or zero address
  spaces.  If it did, it could quit searching after finding a match.  We
  don't know how to communicate this to the database; perhaps there is a
  way and we'll figure it out one day.

  In the meantime, we've investigated de-normalizing by putting (another)
  IPaddressSpace reference into the IPaddress table, ala:

    alter table IPaddress
      add subnet int references IPaddressSpace on delete  SET NULL;

    update IPaddress
      set subnet = (select id from IPaddressSpace
                     where IPaddress.IPaddress between address and broadcast);

    create index ipaddress_subnet_idx on IPaddress (subnet);

  We could maintain that column with a set of insert and update triggers
  on the IPaddressSpace table.  Then NetDB_Network.verify_and_log() would
  only have to look at IP address "where subnet is null".

  Testing shows that this would reduce the current 12 second check for
  orphaned addresses to around 3 seconds - a significant reduction.
  DHCP generation testing shows only a 2-3 second improvement from a
  nominal 15 seconds.  The smaller improvement is because we always use
  all the data for DHCP generation.

  So, is it worth it to de-normalize?

  We don't think so.  Not right now, anyway.

  Why not?

  The set of triggers on IPaddressSpace to maintain IPaddress.subnet
  would be mildly complex (the "mutating table" thing).  We don't think
  saving a few seconds saving network modifications (not a common event)
  is worth that amount of complexity.  Plus we continue to hope for a
  better solution.


                          Better Solution

  Joel sped up the orphan check by walking through the IP address table
  while keeping track of whether the current address is within an existing
  address space.  That was a great improvement and is good enough for now.
  We still haven't updated the DHCP query though - we should someday.


                          appendix:  sample test

    update IPaddress set subnet=null where mod(subnet,47) = 0;

    select count(*) from IPaddress where subnet is null;

    set timing on;

    update IPaddress
       set subnet = (select id from IPaddressSpace
                      where IPaddress.IPaddress between address and broadcast)
     where subnet is null;