[
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 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
Iteration: 11
ConglomerateName IsIndex NumAllocatedPages NumFreePages
NumUnFilledPages PageSize EstimSpaceSaving
LOCATION 0 3 815 0
4096 3338240
SQL111029003533400 1 1967 48 1135
4096 196608
LOC_INDEX 1 393 49 3
4096 200704
Database size: 17909 KB
Iteration: 12
ConglomerateName IsIndex NumAllocatedPages NumFreePages
NumUnFilledPages PageSize EstimSpaceSaving
LOCATION 0 6 812 0
4096 3325952
SQL111029003533400 1 2194 51 1391
4096 208896
LOC_INDEX 1 396 46 108
4096 188416
Database size: 21620 KB
Iteration: 13
ConglomerateName IsIndex NumAllocatedPages NumFreePages
NumUnFilledPages PageSize EstimSpaceSaving
LOCATION 0 3 815 0
4096 3338240
SQL111029003533400 1 2445 27 1535
4096 110592
LOC_INDEX 1 318 124 107
4096 507904
Database size: 21314 KB
Iteration: 14
ConglomerateName IsIndex NumAllocatedPages NumFreePages
NumUnFilledPages PageSize EstimSpaceSaving
LOCATION 0 7 811 0
4096 3321856
SQL111029003533400 1 2672 51 1718
4096 208896
LOC_INDEX 1 410 32 100
4096 131072
Database size: 23232 KB
Iteration: 15
ConglomerateName IsIndex NumAllocatedPages NumFreePages
NumUnFilledPages PageSize EstimSpaceSaving
LOCATION 0 1 817 0
4096 3346432
SQL111029003533400 1 2794 156 1842
4096 638976
LOC_INDEX 1 1 441 1
4096 1806336
Database size: 20235 KB
Iteration: 16
ConglomerateName IsIndex NumAllocatedPages NumFreePages
NumUnFilledPages PageSize EstimSpaceSaving
LOCATION 0 1 817 0
4096 3346432
SQL111029003533400 1 2933 139 1863
4096 569344
LOC_INDEX 1 3 439 2
4096 1798144
Database size: 35124 KB
Iteration: 17
ConglomerateName IsIndex NumAllocatedPages NumFreePages
NumUnFilledPages PageSize EstimSpaceSaving
LOCATION 0 5 813 0
4096 3330048
SQL111029003533400 1 3177 34 2002
4096 139264
LOC_INDEX 1 385 57 15
4096 233472
Database size: 25820 KB
Iteration: 18
ConglomerateName IsIndex NumAllocatedPages NumFreePages
NumUnFilledPages PageSize EstimSpaceSaving
LOCATION 0 1 817 0
4096 3346432
SQL111029003533400 1 3254 202 2102
4096 827392
LOC_INDEX 1 3 439 2
4096 1798144
Database size: 22220 KB
Iteration: 19
ConglomerateName IsIndex NumAllocatedPages NumFreePages
NumUnFilledPages PageSize EstimSpaceSaving
LOCATION 0 2 816 0
4096 3342336
SQL111029003533400 1 3509 23 2339
4096 94208
LOC_INDEX 1 397 45 146
4096 184320
Database size: 35770 KB
Iteration: 20
ConglomerateName IsIndex NumAllocatedPages NumFreePages
NumUnFilledPages PageSize EstimSpaceSaving
LOCATION 0 2 816 0
4096 3342336
SQL111029003533400 1 3765 22 2555
4096 90112
LOC_INDEX 1 354 88 110
4096 360448
Database size: 28916 KB
Iteration: 21
ConglomerateName IsIndex NumAllocatedPages NumFreePages
NumUnFilledPages PageSize EstimSpaceSaving
LOCATION 0 1 817 0
4096 3346432
SQL111029003533400 1 3816 227 2608
4096 929792
LOC_INDEX 1 3 439 2
4096 1798144
Database size: 30791 KB
Iteration: 22
ConglomerateName IsIndex NumAllocatedPages NumFreePages
NumUnFilledPages PageSize EstimSpaceSaving
LOCATION 0 1 817 0
4096 3346432
SQL111029003533400 1 4076 18 2694
4096 73728
LOC_INDEX 1 352 90 23
4096 368640
Database size: 24658 KB
Iteration: 23
ConglomerateName IsIndex NumAllocatedPages NumFreePages
NumUnFilledPages PageSize EstimSpaceSaving
LOCATION 0 8 810 0
4096 3317760
SQL111029003533400 1 4292 62 2882
4096 253952
LOC_INDEX 1 415 27 62
4096 110592
Database size: 30294 KB
Iteration: 24
ConglomerateName IsIndex NumAllocatedPages NumFreePages
NumUnFilledPages PageSize EstimSpaceSaving
LOCATION 0 1 817 0
4096 3346432
SQL111029003533400 1 4340 230 2932
4096 942080
LOC_INDEX 1 1 444 1
4096 1818624
Database size: 30585 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 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 -----------------
------------------------------------------------------
> 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 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
> Iteration: 11
> ConglomerateName IsIndex NumAllocatedPages NumFreePages
> NumUnFilledPages PageSize EstimSpaceSaving
> LOCATION 0 3 815 0
> 4096 3338240
> SQL111029003533400 1 1967 48 1135
> 4096 196608
> LOC_INDEX 1 393 49 3
> 4096 200704
> Database size: 17909 KB
> Iteration: 12
> ConglomerateName IsIndex NumAllocatedPages NumFreePages
> NumUnFilledPages PageSize EstimSpaceSaving
> LOCATION 0 6 812 0
> 4096 3325952
> SQL111029003533400 1 2194 51 1391
> 4096 208896
> LOC_INDEX 1 396 46 108
> 4096 188416
> Database size: 21620 KB
> Iteration: 13
> ConglomerateName IsIndex NumAllocatedPages NumFreePages
> NumUnFilledPages PageSize EstimSpaceSaving
> LOCATION 0 3 815 0
> 4096 3338240
> SQL111029003533400 1 2445 27 1535
> 4096 110592
> LOC_INDEX 1 318 124 107
> 4096 507904
> Database size: 21314 KB
> Iteration: 14
> ConglomerateName IsIndex NumAllocatedPages NumFreePages
> NumUnFilledPages PageSize EstimSpaceSaving
> LOCATION 0 7 811 0
> 4096 3321856
> SQL111029003533400 1 2672 51 1718
> 4096 208896
> LOC_INDEX 1 410 32 100
> 4096 131072
> Database size: 23232 KB
> Iteration: 15
> ConglomerateName IsIndex NumAllocatedPages NumFreePages
> NumUnFilledPages PageSize EstimSpaceSaving
> LOCATION 0 1 817 0
> 4096 3346432
> SQL111029003533400 1 2794 156 1842
> 4096 638976
> LOC_INDEX 1 1 441 1
> 4096 1806336
> Database size: 20235 KB
> Iteration: 16
> ConglomerateName IsIndex NumAllocatedPages NumFreePages
> NumUnFilledPages PageSize EstimSpaceSaving
> LOCATION 0 1 817 0
> 4096 3346432
> SQL111029003533400 1 2933 139 1863
> 4096 569344
> LOC_INDEX 1 3 439 2
> 4096 1798144
> Database size: 35124 KB
> Iteration: 17
> ConglomerateName IsIndex NumAllocatedPages NumFreePages
> NumUnFilledPages PageSize EstimSpaceSaving
> LOCATION 0 5 813 0
> 4096 3330048
> SQL111029003533400 1 3177 34 2002
> 4096 139264
> LOC_INDEX 1 385 57 15
> 4096 233472
> Database size: 25820 KB
> Iteration: 18
> ConglomerateName IsIndex NumAllocatedPages NumFreePages
> NumUnFilledPages PageSize EstimSpaceSaving
> LOCATION 0 1 817 0
> 4096 3346432
> SQL111029003533400 1 3254 202 2102
> 4096 827392
> LOC_INDEX 1 3 439 2
> 4096 1798144
> Database size: 22220 KB
> Iteration: 19
> ConglomerateName IsIndex NumAllocatedPages NumFreePages
> NumUnFilledPages PageSize EstimSpaceSaving
> LOCATION 0 2 816 0
> 4096 3342336
> SQL111029003533400 1 3509 23 2339
> 4096 94208
> LOC_INDEX 1 397 45 146
> 4096 184320
> Database size: 35770 KB
> Iteration: 20
> ConglomerateName IsIndex NumAllocatedPages NumFreePages
> NumUnFilledPages PageSize EstimSpaceSaving
> LOCATION 0 2 816 0
> 4096 3342336
> SQL111029003533400 1 3765 22 2555
> 4096 90112
> LOC_INDEX 1 354 88 110
> 4096 360448
> Database size: 28916 KB
> Iteration: 21
> ConglomerateName IsIndex NumAllocatedPages NumFreePages
> NumUnFilledPages PageSize EstimSpaceSaving
> LOCATION 0 1 817 0
> 4096 3346432
> SQL111029003533400 1 3816 227 2608
> 4096 929792
> LOC_INDEX 1 3 439 2
> 4096 1798144
> Database size: 30791 KB
> Iteration: 22
> ConglomerateName IsIndex NumAllocatedPages NumFreePages
> NumUnFilledPages PageSize EstimSpaceSaving
> LOCATION 0 1 817 0
> 4096 3346432
> SQL111029003533400 1 4076 18 2694
> 4096 73728
> LOC_INDEX 1 352 90 23
> 4096 368640
> Database size: 24658 KB
> Iteration: 23
> ConglomerateName IsIndex NumAllocatedPages NumFreePages
> NumUnFilledPages PageSize EstimSpaceSaving
> LOCATION 0 8 810 0
> 4096 3317760
> SQL111029003533400 1 4292 62 2882
> 4096 253952
> LOC_INDEX 1 415 27 62
> 4096 110592
> Database size: 30294 KB
> Iteration: 24
> ConglomerateName IsIndex NumAllocatedPages NumFreePages
> NumUnFilledPages PageSize EstimSpaceSaving
> LOCATION 0 1 817 0
> 4096 3346432
> SQL111029003533400 1 4340 230 2932
> 4096 942080
> LOC_INDEX 1 1 444 1
> 4096 1818624
> Database size: 30585 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
