db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Kathey Marsden (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (DERBY-5487) Primary key disk pages not reclaimed when using SYSCS_UTIL.SYSCS_COMPRESS_TABLE with just the purge_rows option
Date Wed, 20 Jun 2012 22:43:42 GMT

     [ https://issues.apache.org/jira/browse/DERBY-5487?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Kathey Marsden updated DERBY-5487:
----------------------------------

    Issue & fix info: Repro attached
    
> Primary key disk pages not reclaimed when using SYSCS_UTIL.SYSCS_COMPRESS_TABLE with
just the purge_rows option
> ---------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-5487
>                 URL: https://issues.apache.org/jira/browse/DERBY-5487
>             Project: Derby
>          Issue Type: Bug
>          Components: Store
>    Affects Versions: 10.8.1.2
>         Environment: Windows 7, Embedded Derby mode
>            Reporter: Sundar Narayanaswamy
>              Labels: derby_triage10_8
>         Attachments: DerbyInPlaceCompress.java, screenshot-1.jpg
>
>
> 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 -----------------
> ------------------------------------------------------

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

Mime
View raw message