Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-132

in place table/index compress which returns space to OS



    • Improvement
    • Status: Closed
    • Major
    • Resolution: Fixed
    • Store
    • None


      Each derby table or index is stored in a separate file. Space from
      deleted rows is eventually reclaimed within the file as is used for
      subsequent inserts into the same file. That space is not returned to
      the OS unless the user calls the SYSCS_UTIL.SYSCS_COMPRESS_TABLE
      system procedure. That procedure will return the unused space in
      the tables and indexes to the OS. It gets an exclusive lock on the
      table, copies all rows in the indexes and the base table into new
      compressed files and delete the old files. Prior to jdk 1.4 this was
      the only way to return space from a file to the OS.

      In jdk 1.4 RandomAccessFile was enhanced to allow the truncation of a
      file, which would return the space at the "end" of the file back to
      the OS. In order to take advantage of this new feature a new
      compress feature is needed in derby.

      The assumption is that this work will be used in future work which will
      automatically schedule this job and others in background, with no
      interaction needed from the dba. The 1st phase of this work will
      simply build a procedure that will do the work. The 2nd phase will
      be to look into scheduling the procedure automatically as part of
      the current background post commit processing. Longer term it would
      be best if this fit into a new background task monitor, which could
      schedule larger background tasks balanced against the other priorities
      of the system. These tasks might include: this new online compress,
      automatic statistics gathering, more proactive deleted row reclamation, ....

      The proposed feature will reorganize base tables and indexes, moving
      empty pages to the "end". It will release space back to the operating
      system when it has created a chunk of empty pages at the end of the
      file. It will be designed to run in background, and will lock resources
      of the table for as short a time as possible so that it can iteratively
      process the table.

      To reclaim space in the heap, it will scan the heap in page reverse order.
      It will get a short term table lock, process all the rows on a page, and
      then commit that transaction releasing the lock. The commit will be
      optimized like other internal transactions, and will not need to wait
      for a synchonized write. Each row moved, will require all the index
      entries for that row to also be updated. While doing the processing it
      will also take care of processing committed deleted rows. When space
      is free at the end of the table it will be freed back to the operating
      system, using the RandomAccessFile.setLength() interface.

      To reclaim space in the btree, data on pages will be moved rather than
      rows. Data from pages at the end of the file will be moved to free
      smaller numbered pages. Again short term table locks will be required,
      and the operation will look similar to the current btree merge operations
      already implemented. Again when a chunk of pages is free at the end of
      the file, they will be returned to the OS using the same mechanism as
      the heap.




            mikem Mike Matrigali
            mikem Mike Matrigali
            2 Vote for this issue
            0 Start watching this issue