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