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 "Sundar Narayanaswamy (Updated) (JIRA)" <ji...@apache.org> on 2011/10/29 06:33:32 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 ]
Sundar Narayanaswamy updated DERBY-5487:
----------------------------------------
Attachment: DerbyInPlaceCompress.java
> 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).
> 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 -----------------
> ------------------------------------------------------
--
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