[ https://issues.apache.org/jira/browse/DERBY-5487?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dimuthu Wickramanayake updated DERBY-5487: ------------------------------------------ Priority: Minor (was: Major) > 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 > Priority: Minor > Labels: derby_triage10_8 > Attachments: ASF.LICENSE.NOT.GRANTED--screenshot-1.jpg, > 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 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 was sent by Atlassian JIRA (v6.4.14#64029)