You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Kathey Marsden (Updated) (JIRA)" <ji...@apache.org> on 2012/02/16 15:03:00 UTC
[jira] [Updated] (DERBY-5487) Primary key disk pages not reclaimed
when using SYSCS_UTIL.SYSCS_COMPRESS_TABLE with just the purge_rows option
[ 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: (was: Newcomer)
Fix Version/s: (was: 10.8.1.2)
Labels: derby_triage10_8 (was: derby_triage10_8 patch)
Removing fix version and patch label as there is no patch yet and the issue is not yet fixed.
Could you please attach a stand-alone java reproduction for the issue?
> 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