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

Primary key disk pages not reclaimed when using SYSCS_UTIL.SYSCS_COMPRESS_TABLE with just the purge_rows option

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Open
    • Major
    • Resolution: Unresolved
    • 10.8.1.2
    • None
    • Store
    • Windows 7, Embedded Derby mode
    • Normal
    • Repro attached
    • Performance

    Description

      When I continuously insert data, delete the inserted data then compress with purge_rows option in a loop, space is not reclaimed from the primary key file. The inserts are committed every 10000 rows, deletes committed every 50000 rows. All the rows that were inserted are deleted. The primary key values continually increase (across the inserts) . All the activities occur on a single thread. Included below is the space table output after each iteration in the loop:

      As can be seen below and in the screenshot attached, the NumAllocatedpages for SQL111029001155930 is continuously increasing. This increase does not happen if the primary key values are reset after each iteration (ie, primary key values for new inserts are in the same range as deleted rows).

      Iteration: 0
      ConglomerateName IsIndex NumAllocatedPages NumFreePages NumUnFilledPages PageSize EstimSpaceSaving
      LOCATION 0 1 803 1 4096 3289088
      SQL111029003533400 1 238 31 179 4096 126976
      LOC_INDEX 1 211 185 119 4096 757760
      Database size: 12993 KB

      Iteration: 1
      ConglomerateName IsIndex NumAllocatedPages NumFreePages NumUnFilledPages PageSize EstimSpaceSaving
      LOCATION 0 1 816 1 4096 3342336
      SQL111029003533400 1 324 192 200 4096 786432
      LOC_INDEX 1 1 406 1 4096 1662976
      Database size: 17112 KB

      Iteration: 2
      ConglomerateName IsIndex NumAllocatedPages NumFreePages NumUnFilledPages PageSize EstimSpaceSaving
      LOCATION 0 7 810 1 4096 3317760
      SQL111029003533400 1 579 23 294 4096 94208
      LOC_INDEX 1 394 28 2 4096 114688
      Database size: 22821 KB

      Iteration: 3
      ConglomerateName IsIndex NumAllocatedPages NumFreePages NumUnFilledPages PageSize EstimSpaceSaving
      LOCATION 0 1 816 0 4096 3342336
      SQL111029003533400 1 631 227 451 4096 929792
      LOC_INDEX 1 5 437 3 4096 1789952
      Database size: 18054 KB

      Iteration: 4
      ConglomerateName IsIndex NumAllocatedPages NumFreePages NumUnFilledPages PageSize EstimSpaceSaving
      LOCATION 0 1 817 0 4096 3346432
      SQL111029003533400 1 735 174 460 4096 712704
      LOC_INDEX 1 1 441 1 4096 1806336
      Database size: 15632 KB

      Iteration: 5
      ConglomerateName IsIndex NumAllocatedPages NumFreePages NumUnFilledPages PageSize EstimSpaceSaving
      LOCATION 0 4 814 0 4096 3334144
      SQL111029003533400 1 992 21 690 4096 86016
      LOC_INDEX 1 378 64 127 4096 262144
      Database size: 25633 KB

      Iteration: 6
      ConglomerateName IsIndex NumAllocatedPages NumFreePages NumUnFilledPages PageSize EstimSpaceSaving
      LOCATION 0 1 817 0 4096 3346432
      SQL111029003533400 1 1071 199 744 4096 815104
      LOC_INDEX 1 5 437 3 4096 1789952
      Database size: 19763 KB

      Iteration: 7
      ConglomerateName IsIndex NumAllocatedPages NumFreePages NumUnFilledPages PageSize EstimSpaceSaving
      LOCATION 0 1 817 0 4096 3346432
      SQL111029003533400 1 1320 29 932 4096 118784
      LOC_INDEX 1 395 47 11 4096 192512
      Database size: 26911 KB

      Iteration: 8
      ConglomerateName IsIndex NumAllocatedPages NumFreePages NumUnFilledPages PageSize EstimSpaceSaving
      LOCATION 0 1 817 0 4096 3346432
      SQL111029003533400 1 1421 177 1014 4096 724992
      LOC_INDEX 1 1 441 1 4096 1806336
      Database size: 21003 KB

      Iteration: 9
      ConglomerateName IsIndex NumAllocatedPages NumFreePages NumUnFilledPages PageSize EstimSpaceSaving
      LOCATION 0 1 817 0 4096 3346432
      SQL111029003533400 1 1593 106 1063 4096 434176
      LOC_INDEX 1 1 441 1 4096 1806336
      Database size: 29662 KB

      Iteration: 10
      ConglomerateName IsIndex NumAllocatedPages NumFreePages NumUnFilledPages PageSize EstimSpaceSaving
      LOCATION 0 1 817 0 4096 3346432
      SQL111029003533400 1 1736 135 1083 4096 552960
      LOC_INDEX 1 3 439 2 4096 1798144
      Database size: 22035 KB

      ------------------ Java Information ------------------
      Java Version: 1.6.0_21
      Java Vendor: Sun Microsystems Inc.
      Java home: c:\development\
      Java classpath: .;derby-10.8.1.2.jar
      OS name: Windows 7
      OS architecture: x86
      OS version: 6.1
      Java user name: testuser
      Java user home: C:\Users\testuser
      Java user dir: C:\temp\Derby
      java.specification.name: Java Platform API Specification
      java.specification.version: 1.6
      java.runtime.version: 1.6.0_21-b06
      --------- Derby Information --------
      JRE - JDBC: Java SE 6 - JDBC 4.0
      [C:\temp\Derby\derby-10.8.1.2.jar] 10.8.1.2 - (1095077)
      ------------------------------------------------------
      ----------------- Locale Information -----------------
      ------------------------------------------------------

      Attachments

        1. ASF.LICENSE.NOT.GRANTED--screenshot-1.jpg
          159 kB
          Sundar Narayanaswamy
        2. DerbyInPlaceCompress.java
          7 kB
          Sundar Narayanaswamy

        Issue Links

          Activity

            People

              Unassigned Unassigned
              sundar007@yahoo.com Sundar Narayanaswamy
              Votes:
              3 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated: