db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Sundar Narayanaswamy (Updated) (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 Sat, 29 Oct 2011 04:41:32 GMT

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

Sundar Narayanaswamy updated DERBY-5487:
----------------------------------------

    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, 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). 

<pre>
Iteration: 0
ConglomerateName    IsIndex   NumAllocatedPages   NumFreePages   NumUnFilledPages    PageSize
 EstimSpaceSaving    
LOCATION                  0           1                               803                
    1                             4096         3289088             
SQL111029001155930  1           153                           116                      39
                          4096         475136              
LOC_INDEX                 1          1                               397                 
    1                             4096        1626112             
Database size: 13447 KB
</pre>
Iteration: 1
ConglomerateName    IsIndex   NumAllocatedPages   NumFreePages   NumUnFilledPages    PageSize
 EstimSpaceSaving    
LOCATION                  0           1                              816                 
  1                              4096          3342336             
SQL111029001155930  1           400                          31                      284 
                         4096         126976              
LOC_INDEX                1           103                           318                   
84                             4096         1302528             
Database size: 23777 KB

Iteration: 2
ConglomerateName    IsIndex   NumAllocatedPages   NumFreePages   NumUnFilledPages    PageSize
 EstimSpaceSaving    
LOCATION                    0         1                               816                
     0                           4096         3342336             
SQL111029001155930   1          452                           226                      294
                       4096         925696              
LOC_INDEX                 1          3                               418                 
    2                           4096         1712128             
Database size: 17094 KB
 
Iteration: 3
ConglomerateName    IsIndex   NumAllocatedPages   NumFreePages   NumUnFilledPages    PageSize
 EstimSpaceSaving    
LOCATION                   0         1                               817                 
    0                             4096         3346432             
SQL111029001155930  1          504                           226                      331
                         4096         925696              
LOC_INDEX                1          1                               435                  
   1                              4096         1781760             
Database size: 25802 KB

Iteration: 4
ConglomerateName    IsIndex   NumAllocatedPages   NumFreePages   NumUnFilledPages    PageSize
 EstimSpaceSaving    
LOCATION                   0          1                               817                
      0                           4096         3346432             
SQL111029001155930  1           691                           91                        367
                        4096         372736              
LOC_INDEX                 1           3                              433                 
     2                            4096         1773568             
Database size: 17636 KB

Iteration: 5  
ConglomerateName    IsIndex   NumAllocatedPages   NumFreePages   NumUnFilledPages    PageSize
 EstimSpaceSaving    
LOCATION                   0           2                               816               
   0                              4096         3342336             
SQL111029001155930   1           944                           25                     546
                          4096         102400              
LOC_INDEX                 1           311                            125                 
 80                             4096         512000              
Database size: 15316 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 -----------------
------------------------------------------------------



  was:
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, 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             
SQL111029001155930  1           153                           116                      39
                          4096         475136              
LOC_INDEX                 1          1                               397                 
    1                             4096        1626112             
Database size: 13447 KB

Iteration: 1
ConglomerateName    IsIndex   NumAllocatedPages   NumFreePages   NumUnFilledPages    PageSize
 EstimSpaceSaving    
LOCATION                  0           1                              816                 
  1                              4096          3342336             
SQL111029001155930  1           400                          31                      284 
                         4096         126976              
LOC_INDEX                1           103                           318                   
84                             4096         1302528             
Database size: 23777 KB

Iteration: 2
ConglomerateName    IsIndex   NumAllocatedPages   NumFreePages   NumUnFilledPages    PageSize
 EstimSpaceSaving    
LOCATION                    0         1                               816                
     0                           4096         3342336             
SQL111029001155930   1          452                           226                      294
                       4096         925696              
LOC_INDEX                 1          3                               418                 
    2                           4096         1712128             
Database size: 17094 KB
 
Iteration: 3
ConglomerateName    IsIndex   NumAllocatedPages   NumFreePages   NumUnFilledPages    PageSize
 EstimSpaceSaving    
LOCATION                   0         1                               817                 
    0                             4096         3346432             
SQL111029001155930  1          504                           226                      331
                         4096         925696              
LOC_INDEX                1          1                               435                  
   1                              4096         1781760             
Database size: 25802 KB

Iteration: 4
ConglomerateName    IsIndex   NumAllocatedPages   NumFreePages   NumUnFilledPages    PageSize
 EstimSpaceSaving    
LOCATION                   0          1                               817                
      0                           4096         3346432             
SQL111029001155930  1           691                           91                        367
                        4096         372736              
LOC_INDEX                 1           3                              433                 
     2                            4096         1773568             
Database size: 17636 KB

Iteration: 5  
ConglomerateName    IsIndex   NumAllocatedPages   NumFreePages   NumUnFilledPages    PageSize
 EstimSpaceSaving    
LOCATION                   0           2                               816               
   0                              4096         3342336             
SQL111029001155930   1           944                           25                     546
                          4096         102400              
LOC_INDEX                 1           311                            125                 
 80                             4096         512000              
Database size: 15316 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 -----------------
------------------------------------------------------



    
> 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, patch
>             Fix For: 10.8.1.2
>
>         Attachments: DerbyInPlaceCompress.java
>
>
> 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, 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). 
> <pre>
> Iteration: 0
> ConglomerateName    IsIndex   NumAllocatedPages   NumFreePages   NumUnFilledPages   
PageSize  EstimSpaceSaving    
> LOCATION                  0           1                               803           
         1                             4096         3289088             
> SQL111029001155930  1           153                           116                   
  39                           4096         475136              
> LOC_INDEX                 1          1                               397            
         1                             4096        1626112             
> Database size: 13447 KB
> </pre>
> Iteration: 1
> ConglomerateName    IsIndex   NumAllocatedPages   NumFreePages   NumUnFilledPages   
PageSize  EstimSpaceSaving    
> LOCATION                  0           1                              816            
       1                              4096          3342336             
> SQL111029001155930  1           400                          31                     
284                           4096         126976              
> LOC_INDEX                1           103                           318              
     84                             4096         1302528             
> Database size: 23777 KB
> Iteration: 2
> ConglomerateName    IsIndex   NumAllocatedPages   NumFreePages   NumUnFilledPages   
PageSize  EstimSpaceSaving    
> LOCATION                    0         1                               816           
          0                           4096         3342336             
> SQL111029001155930   1          452                           226                   
  294                        4096         925696              
> LOC_INDEX                 1          3                               418            
         2                           4096         1712128             
> Database size: 17094 KB
>  
> Iteration: 3
> ConglomerateName    IsIndex   NumAllocatedPages   NumFreePages   NumUnFilledPages   
PageSize  EstimSpaceSaving    
> LOCATION                   0         1                               817            
         0                             4096         3346432             
> SQL111029001155930  1          504                           226                    
 331                          4096         925696              
> LOC_INDEX                1          1                               435             
        1                              4096         1781760             
> Database size: 25802 KB
> Iteration: 4
> ConglomerateName    IsIndex   NumAllocatedPages   NumFreePages   NumUnFilledPages   
PageSize  EstimSpaceSaving    
> LOCATION                   0          1                               817           
           0                           4096         3346432             
> SQL111029001155930  1           691                           91                    
   367                         4096         372736              
> LOC_INDEX                 1           3                              433            
          2                            4096         1773568             
> Database size: 17636 KB
> Iteration: 5  
> ConglomerateName    IsIndex   NumAllocatedPages   NumFreePages   NumUnFilledPages   
PageSize  EstimSpaceSaving    
> LOCATION                   0           2                               816          
        0                              4096         3342336             
> SQL111029001155930   1           944                           25                   
 546                           4096         102400              
> LOC_INDEX                 1           311                            125            
      80                             4096         512000              
> Database size: 15316 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