[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 ] Dimuthu Wickramanayake updated DERBY-5487: -- Priority: Major (was: Minor) > 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: 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 1 rows, deletes committed every > 5 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 > ConglomerateNameIsIndex NumAllocatedPages NumFreePages > NumUnFilledPagesPageSize EstimSpaceSaving > LOCATION0 1 8031 >4096 3289088 > SQL111029003533400 1 238 31 179 >4096 126976 > LOC_INDEX 1 211 185119 >4096 757760 > Database size: 12993 KB > Iteration: 1 > ConglomerateNameIsIndex NumAllocatedPages NumFreePages > NumUnFilledPagesPageSize EstimSpaceSaving > LOCATION0 1 8161 >4096 3342336 > SQL111029003533400 1 324 192200 >4096 786432 > LOC_INDEX 1 1 4061 >4096 1662976 > Database size: 17112 KB > Iteration: 2 > ConglomerateNameIsIndex NumAllocatedPages NumFreePages > NumUnFilledPagesPageSize EstimSpaceSaving > LOCATION0 7 8101 >4096 3317760 > SQL111029003533400 1 579 23 294 >4096 94208 > LOC_INDEX 1 394 28 2 >4096 114688 > Database size: 22821 KB > Iteration: 3 > ConglomerateNameIsIndex NumAllocatedPages NumFreePages > NumUnFilledPagesPageSize EstimSpaceSaving > LOCATION0 1 8160 >4096 3342336 > SQL111029003533400 1 631 227451 >4096 929792 > LOC_INDEX 1 5 4373 >4096 1789952 > Database size: 18054 KB > Iteration: 4 > ConglomerateNameIsIndex NumAllocatedPages NumFreePages > NumUnFilledPagesPageSize EstimSpaceSaving > LOCATION0 1 8170 >4096 3346432 > SQL111029003533400 1 735 174460 >4096 712704 > LOC_INDEX 1 1 4411 >4096 1806336 > Database size: 15632 KB > Iteration: 5 > ConglomerateNameIsIndex NumAllocatedPages NumFreePages > NumUnFilledPagesPageSize EstimSpaceSaving > LOCATION0 4 8140 >4096 3334144 > SQL111029003533400 1 992 21 690 >4096 86016 > LOC_INDEX 1 378 64 127 >4096 262144 > Database size: 25633 KB > Iteration: 6 > ConglomerateNameIsIndex NumAllocatedPages NumFreePages > NumUnFilledPagesPageSize
[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 ] 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 1 rows, deletes committed every > 5 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 > ConglomerateNameIsIndex NumAllocatedPages NumFreePages > NumUnFilledPagesPageSize EstimSpaceSaving > LOCATION0 1 8031 >4096 3289088 > SQL111029003533400 1 238 31 179 >4096 126976 > LOC_INDEX 1 211 185119 >4096 757760 > Database size: 12993 KB > Iteration: 1 > ConglomerateNameIsIndex NumAllocatedPages NumFreePages > NumUnFilledPagesPageSize EstimSpaceSaving > LOCATION0 1 8161 >4096 3342336 > SQL111029003533400 1 324 192200 >4096 786432 > LOC_INDEX 1 1 4061 >4096 1662976 > Database size: 17112 KB > Iteration: 2 > ConglomerateNameIsIndex NumAllocatedPages NumFreePages > NumUnFilledPagesPageSize EstimSpaceSaving > LOCATION0 7 8101 >4096 3317760 > SQL111029003533400 1 579 23 294 >4096 94208 > LOC_INDEX 1 394 28 2 >4096 114688 > Database size: 22821 KB > Iteration: 3 > ConglomerateNameIsIndex NumAllocatedPages NumFreePages > NumUnFilledPagesPageSize EstimSpaceSaving > LOCATION0 1 8160 >4096 3342336 > SQL111029003533400 1 631 227451 >4096 929792 > LOC_INDEX 1 5 4373 >4096 1789952 > Database size: 18054 KB > Iteration: 4 > ConglomerateNameIsIndex NumAllocatedPages NumFreePages > NumUnFilledPagesPageSize EstimSpaceSaving > LOCATION0 1 8170 >4096 3346432 > SQL111029003533400 1 735 174460 >4096 712704 > LOC_INDEX 1 1 4411 >4096 1806336 > Database size: 15632 KB > Iteration: 5 > ConglomerateNameIsIndex NumAllocatedPages NumFreePages > NumUnFilledPagesPageSize EstimSpaceSaving > LOCATION0 4 8140 >4096 3334144 > SQL111029003533400 1 992 21 690 >4096 86016 > LOC_INDEX 1 378 64 127 >4096 262144 > Database size: 25633 KB > Iteration: 6 > ConglomerateNameIsIndex NumAllocatedPages NumFreePages >
[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: Repro attached 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 1 rows, deletes committed every 5 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 ConglomerateNameIsIndex NumAllocatedPages NumFreePages NumUnFilledPagesPageSize EstimSpaceSaving LOCATION0 1 8031 4096 3289088 SQL111029003533400 1 238 31 179 4096 126976 LOC_INDEX 1 211 185119 4096 757760 Database size: 12993 KB Iteration: 1 ConglomerateNameIsIndex NumAllocatedPages NumFreePages NumUnFilledPagesPageSize EstimSpaceSaving LOCATION0 1 8161 4096 3342336 SQL111029003533400 1 324 192200 4096 786432 LOC_INDEX 1 1 4061 4096 1662976 Database size: 17112 KB Iteration: 2 ConglomerateNameIsIndex NumAllocatedPages NumFreePages NumUnFilledPagesPageSize EstimSpaceSaving LOCATION0 7 8101 4096 3317760 SQL111029003533400 1 579 23 294 4096 94208 LOC_INDEX 1 394 28 2 4096 114688 Database size: 22821 KB Iteration: 3 ConglomerateNameIsIndex NumAllocatedPages NumFreePages NumUnFilledPagesPageSize EstimSpaceSaving LOCATION0 1 8160 4096 3342336 SQL111029003533400 1 631 227451 4096 929792 LOC_INDEX 1 5 4373 4096 1789952 Database size: 18054 KB Iteration: 4 ConglomerateNameIsIndex NumAllocatedPages NumFreePages NumUnFilledPagesPageSize EstimSpaceSaving LOCATION0 1 8170 4096 3346432 SQL111029003533400 1 735 174460 4096 712704 LOC_INDEX 1 1 4411 4096 1806336 Database size: 15632 KB Iteration: 5 ConglomerateNameIsIndex NumAllocatedPages NumFreePages NumUnFilledPagesPageSize EstimSpaceSaving LOCATION0 4 8140 4096 3334144 SQL111029003533400 1 992 21 690 4096 86016 LOC_INDEX 1 378 64 127 4096 262144 Database size: 25633 KB Iteration: 6 ConglomerateNameIsIndex NumAllocatedPages NumFreePages NumUnFilledPagesPageSize EstimSpaceSaving LOCATION0 1 8170 4096
[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 1 rows, deletes committed every 5 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 ConglomerateNameIsIndex NumAllocatedPages NumFreePages NumUnFilledPagesPageSize EstimSpaceSaving LOCATION0 1 8031 4096 3289088 SQL111029003533400 1 238 31 179 4096 126976 LOC_INDEX 1 211 185119 4096 757760 Database size: 12993 KB Iteration: 1 ConglomerateNameIsIndex NumAllocatedPages NumFreePages NumUnFilledPagesPageSize EstimSpaceSaving LOCATION0 1 8161 4096 3342336 SQL111029003533400 1 324 192200 4096 786432 LOC_INDEX 1 1 4061 4096 1662976 Database size: 17112 KB Iteration: 2 ConglomerateNameIsIndex NumAllocatedPages NumFreePages NumUnFilledPagesPageSize EstimSpaceSaving LOCATION0 7 8101 4096 3317760 SQL111029003533400 1 579 23 294 4096 94208 LOC_INDEX 1 394 28 2 4096 114688 Database size: 22821 KB Iteration: 3 ConglomerateNameIsIndex NumAllocatedPages NumFreePages NumUnFilledPagesPageSize EstimSpaceSaving LOCATION0 1 8160 4096 3342336 SQL111029003533400 1 631 227451 4096 929792 LOC_INDEX 1 5 4373 4096 1789952 Database size: 18054 KB Iteration: 4 ConglomerateNameIsIndex NumAllocatedPages NumFreePages NumUnFilledPagesPageSize EstimSpaceSaving LOCATION0 1 8170 4096 3346432 SQL111029003533400 1 735 174460 4096 712704 LOC_INDEX 1 1 4411 4096 1806336 Database size: 15632 KB Iteration: 5 ConglomerateNameIsIndex NumAllocatedPages NumFreePages NumUnFilledPagesPageSize EstimSpaceSaving LOCATION0 4 8140 4096 3334144 SQL111029003533400 1 992 21 690 4096 86016 LOC_INDEX 1 378 64 127
[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 1 rows, deletes committed every 5 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 ConglomerateNameIsIndex NumAllocatedPages NumFreePages NumUnFilledPagesPageSize EstimSpaceSaving LOCATION 0 1 803 1 4096 3289088 SQL111029001155930 1 153 116 39 4096 475136 LOC_INDEX 1 1 397 1 40961626112 Database size: 13447 KB Iteration: 1 ConglomerateNameIsIndex NumAllocatedPages NumFreePages NumUnFilledPagesPageSize EstimSpaceSaving LOCATION 0 1 816 1 4096 3342336 SQL111029001155930 1 400 31 284 4096 126976 LOC_INDEX1 103 318 84 4096 1302528 Database size: 23777 KB Iteration: 2 ConglomerateNameIsIndex NumAllocatedPages NumFreePages NumUnFilledPagesPageSize EstimSpaceSaving LOCATION0 1 816 0 4096 3342336 SQL111029001155930 1 452 226 2944096 925696 LOC_INDEX 1 3 418 2 4096 1712128 Database size: 17094 KB Iteration: 3 ConglomerateNameIsIndex NumAllocatedPages NumFreePages NumUnFilledPagesPageSize EstimSpaceSaving LOCATION 0 1 817 0 4096 3346432 SQL111029001155930 1 504 226 331 4096 925696 LOC_INDEX1 1 435 1 4096 1781760 Database size: 25802 KB Iteration: 4 ConglomerateNameIsIndex NumAllocatedPages NumFreePages NumUnFilledPagesPageSize EstimSpaceSaving LOCATION 0 1 817 0 4096 3346432 SQL111029001155930 1 691 91 367 4096 372736 LOC_INDEX 1 3 433 24096 1773568 Database size: 17636 KB Iteration: 5 ConglomerateNameIsIndex NumAllocatedPages NumFreePages NumUnFilledPagesPageSize EstimSpaceSaving LOCATION
[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: (was: 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 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 1 rows, deletes committed every 5 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 ConglomerateNameIsIndex NumAllocatedPages NumFreePages NumUnFilledPagesPageSize EstimSpaceSaving LOCATION 0 1 803 1 4096 3289088 SQL111029001155930 1 153 116 39 4096 475136 LOC_INDEX 1 1 397 1 40961626112 Database size: 13447 KB Iteration: 1 ConglomerateNameIsIndex NumAllocatedPages NumFreePages NumUnFilledPagesPageSize EstimSpaceSaving LOCATION 0 1 816 1 4096 3342336 SQL111029001155930 1 400 31 284 4096 126976 LOC_INDEX1 103 318 84 4096 1302528 Database size: 23777 KB Iteration: 2 ConglomerateNameIsIndex NumAllocatedPages NumFreePages NumUnFilledPagesPageSize EstimSpaceSaving LOCATION0 1 816 0 4096 3342336 SQL111029001155930 1 452 226 2944096 925696 LOC_INDEX 1 3 418 2 4096 1712128 Database size: 17094 KB Iteration: 3 ConglomerateNameIsIndex NumAllocatedPages NumFreePages NumUnFilledPagesPageSize EstimSpaceSaving LOCATION 0 1 817 0 4096 3346432 SQL111029001155930 1 504 226 331 4096 925696 LOC_INDEX1 1 435 1 4096 1781760 Database size: 25802 KB Iteration: 4 ConglomerateNameIsIndex NumAllocatedPages NumFreePages NumUnFilledPagesPageSize EstimSpaceSaving LOCATION 0 1 817 0 4096 3346432 SQL111029001155930 1 691 91 367 4096 372736 LOC_INDEX 1 3 433 24096 1773568 Database size: 17636 KB Iteration: 5 ConglomerateNameIsIndex NumAllocatedPages NumFreePages NumUnFilledPagesPageSize EstimSpaceSaving LOCATION 0 2
[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 1 rows, deletes committed every 5 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 ConglomerateNameIsIndex NumAllocatedPages NumFreePages NumUnFilledPagesPageSize EstimSpaceSaving LOCATION 0 1 803 1 4096 3289088 SQL111029001155930 1 153 116 39 4096 475136 LOC_INDEX 1 1 397 1 40961626112 Database size: 13447 KB Iteration: 1 ConglomerateNameIsIndex NumAllocatedPages NumFreePages NumUnFilledPagesPageSize EstimSpaceSaving LOCATION 0 1 816 1 4096 3342336 SQL111029001155930 1 400 31 284 4096 126976 LOC_INDEX1 103 318 84 4096 1302528 Database size: 23777 KB Iteration: 2 ConglomerateNameIsIndex NumAllocatedPages NumFreePages NumUnFilledPagesPageSize EstimSpaceSaving LOCATION0 1 816 0 4096 3342336 SQL111029001155930 1 452 226 2944096 925696 LOC_INDEX 1 3 418 2 4096 1712128 Database size: 17094 KB Iteration: 3 ConglomerateNameIsIndex NumAllocatedPages NumFreePages NumUnFilledPagesPageSize EstimSpaceSaving LOCATION 0 1 817 0 4096 3346432 SQL111029001155930 1 504 226 331 4096 925696 LOC_INDEX1 1 435 1 4096 1781760 Database size: 25802 KB Iteration: 4 ConglomerateNameIsIndex NumAllocatedPages NumFreePages NumUnFilledPagesPageSize EstimSpaceSaving LOCATION 0 1 817 0 4096 3346432 SQL111029001155930 1 691 91 367 4096 372736 LOC_INDEX 1 3 433 24096 1773568 Database size: 17636 KB Iteration: 5 ConglomerateNameIsIndex NumAllocatedPages NumFreePages NumUnFilledPagesPageSize EstimSpaceSaving LOCATION
[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: 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 1 rows, deletes committed every 5 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). pre Iteration: 0 ConglomerateNameIsIndex NumAllocatedPages NumFreePages NumUnFilledPagesPageSize EstimSpaceSaving LOCATION 0 1 803 1 4096 3289088 SQL111029001155930 1 153 116 39 4096 475136 LOC_INDEX 1 1 397 1 40961626112 Database size: 13447 KB /pre Iteration: 1 ConglomerateNameIsIndex NumAllocatedPages NumFreePages NumUnFilledPagesPageSize EstimSpaceSaving LOCATION 0 1 816 1 4096 3342336 SQL111029001155930 1 400 31 284 4096 126976 LOC_INDEX1 103 318 84 4096 1302528 Database size: 23777 KB Iteration: 2 ConglomerateNameIsIndex NumAllocatedPages NumFreePages NumUnFilledPagesPageSize EstimSpaceSaving LOCATION0 1 816 0 4096 3342336 SQL111029001155930 1 452 226 2944096 925696 LOC_INDEX 1 3 418 2 4096 1712128 Database size: 17094 KB Iteration: 3 ConglomerateNameIsIndex NumAllocatedPages NumFreePages NumUnFilledPagesPageSize EstimSpaceSaving LOCATION 0 1 817 0 4096 3346432 SQL111029001155930 1 504 226 331 4096 925696 LOC_INDEX1 1 435 1 4096 1781760 Database size: 25802 KB Iteration: 4 ConglomerateNameIsIndex NumAllocatedPages NumFreePages NumUnFilledPagesPageSize EstimSpaceSaving LOCATION 0 1 817 0 4096 3346432 SQL111029001155930 1 691 91 367 4096 372736 LOC_INDEX 1 3 433 24096 1773568 Database size: 17636 KB Iteration: 5 ConglomerateNameIsIndex NumAllocatedPages NumFreePages NumUnFilledPagesPageSize EstimSpaceSaving LOCATION 0 2 816 0 4096 3342336 SQL111029001155930 1 944 25 546 4096 102400 LOC_INDEX 1 311125 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:
[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: 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 1 rows, deletes committed every 5 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 NumUnFilledPagesPageSize EstimSpaceSaving LOCATION--0 1 803 1 4096 3289088 SQL111029001155930 1 153 116 39 4096 475136 LOC_INDEX 1 1 397 1 40961626112 Database size: 13447 KB Iteration: 1 ConglomerateNameIsIndex NumAllocatedPages NumFreePages NumUnFilledPagesPageSize EstimSpaceSaving LOCATION 0 1 816 1 4096 3342336 SQL111029001155930 1 400 31 284 4096 126976 LOC_INDEX1 103 318 84 4096 1302528 Database size: 23777 KB Iteration: 2 ConglomerateNameIsIndex NumAllocatedPages NumFreePages NumUnFilledPagesPageSize EstimSpaceSaving LOCATION0 1 816 0 4096 3342336 SQL111029001155930 1 452 226 2944096 925696 LOC_INDEX 1 3 418 2 4096 1712128 Database size: 17094 KB Iteration: 3 ConglomerateNameIsIndex NumAllocatedPages NumFreePages NumUnFilledPagesPageSize EstimSpaceSaving LOCATION 0 1 817 0 4096 3346432 SQL111029001155930 1 504 226 331 4096 925696 LOC_INDEX1 1 435 1 4096 1781760 Database size: 25802 KB Iteration: 4 ConglomerateNameIsIndex NumAllocatedPages NumFreePages NumUnFilledPagesPageSize EstimSpaceSaving LOCATION 0 1 817 0 4096 3346432 SQL111029001155930 1 691 91 367 4096 372736 LOC_INDEX 1 3 433 24096 1773568 Database size: 17636 KB Iteration: 5 ConglomerateNameIsIndex NumAllocatedPages NumFreePages NumUnFilledPagesPageSize EstimSpaceSaving LOCATION 0 2 816 0 4096 3342336 SQL111029001155930 1 944 25 546 4096 102400 LOC_INDEX 1 311125 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
[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: screenshot-1.jpg 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 1 rows, deletes committed every 5 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 NumUnFilledPagesPageSize EstimSpaceSaving LOCATION--0 1 803 1 4096 3289088 SQL111029001155930 1 153 116 39 4096 475136 LOC_INDEX 1 1 397 1 40961626112 Database size: 13447 KB Iteration: 1 ConglomerateNameIsIndex NumAllocatedPages NumFreePages NumUnFilledPagesPageSize EstimSpaceSaving LOCATION 0 1 816 1 4096 3342336 SQL111029001155930 1 400 31 284 4096 126976 LOC_INDEX1 103 318 84 4096 1302528 Database size: 23777 KB Iteration: 2 ConglomerateNameIsIndex NumAllocatedPages NumFreePages NumUnFilledPagesPageSize EstimSpaceSaving LOCATION0 1 816 0 4096 3342336 SQL111029001155930 1 452 226 2944096 925696 LOC_INDEX 1 3 418 2 4096 1712128 Database size: 17094 KB Iteration: 3 ConglomerateNameIsIndex NumAllocatedPages NumFreePages NumUnFilledPagesPageSize EstimSpaceSaving LOCATION 0 1 817 0 4096 3346432 SQL111029001155930 1 504 226 331 4096 925696 LOC_INDEX1 1 435 1 4096 1781760 Database size: 25802 KB Iteration: 4 ConglomerateNameIsIndex NumAllocatedPages NumFreePages NumUnFilledPagesPageSize EstimSpaceSaving LOCATION 0 1 817 0 4096 3346432 SQL111029001155930 1 691 91 367 4096 372736 LOC_INDEX 1 3 433 24096 1773568 Database size: 17636 KB Iteration: 5 ConglomerateNameIsIndex NumAllocatedPages NumFreePages NumUnFilledPagesPageSize EstimSpaceSaving
[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: 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 1 rows, deletes committed every 5 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 1 rows, deletes committed every 5 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 NumUnFilledPagesPageSize EstimSpaceSaving LOCATION--0 1 803 1 4096 3289088 SQL111029001155930 1 153 116 39 4096 475136 LOC_INDEX 1 1 397 1 40961626112 Database size: 13447 KB Iteration: 1 ConglomerateNameIsIndex NumAllocatedPages NumFreePages NumUnFilledPagesPageSize EstimSpaceSaving LOCATION 0 1 816 1 4096 3342336 SQL111029001155930 1 400 31 284 4096 126976 LOC_INDEX1 103 318 84 4096 1302528 Database size: 23777 KB Iteration: 2 ConglomerateNameIsIndex NumAllocatedPages NumFreePages NumUnFilledPagesPageSize EstimSpaceSaving LOCATION0 1 816 0 4096 3342336 SQL111029001155930 1 452 226 2944096 925696 LOC_INDEX 1 3 418 2 4096 1712128 Database size: 17094 KB Iteration: 3 ConglomerateNameIsIndex NumAllocatedPages NumFreePages NumUnFilledPagesPageSize EstimSpaceSaving LOCATION 0 1 817 0 4096 3346432 SQL111029001155930 1 504 226 331 4096 925696 LOC_INDEX1 1 435 1 4096 1781760 Database size: 25802 KB
[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: 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 1 rows, deletes committed every 5 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 ConglomerateNameIsIndex NumAllocatedPages NumFreePages NumUnFilledPagesPageSize EstimSpaceSaving LOCATION0 1 8031 4096 3289088 SQL111029003533400 1 238 31 179 4096 126976 LOC_INDEX 1 211 185119 4096 757760 Database size: 12993 KB Iteration: 1 ConglomerateNameIsIndex NumAllocatedPages NumFreePages NumUnFilledPagesPageSize EstimSpaceSaving LOCATION0 1 8161 4096 3342336 SQL111029003533400 1 324 192200 4096 786432 LOC_INDEX 1 1 4061 4096 1662976 Database size: 17112 KB Iteration: 2 ConglomerateNameIsIndex NumAllocatedPages NumFreePages NumUnFilledPagesPageSize EstimSpaceSaving LOCATION0 7 8101 4096 3317760 SQL111029003533400 1 579 23 294 4096 94208 LOC_INDEX 1 394 28 2 4096 114688 Database size: 22821 KB Iteration: 3 ConglomerateNameIsIndex NumAllocatedPages NumFreePages NumUnFilledPagesPageSize EstimSpaceSaving LOCATION0 1 8160 4096 3342336 SQL111029003533400 1 631 227451 4096 929792 LOC_INDEX 1 5 4373 4096 1789952 Database size: 18054 KB Iteration: 4 ConglomerateNameIsIndex NumAllocatedPages NumFreePages NumUnFilledPagesPageSize EstimSpaceSaving LOCATION0 1 8170 4096 3346432 SQL111029003533400 1 735 174460 4096 712704 LOC_INDEX 1 1 4411 4096 1806336 Database size: 15632 KB Iteration: 5 ConglomerateNameIsIndex NumAllocatedPages NumFreePages NumUnFilledPagesPageSize EstimSpaceSaving LOCATION0 4 8140 4096 3334144 SQL111029003533400 1 992 21 690 4096 86016 LOC_INDEX 1 378 64 127 4096 262144 Database size: 25633 KB Iteration: 6 ConglomerateNameIsIndex NumAllocatedPages NumFreePages NumUnFilledPagesPageSize EstimSpaceSaving LOCATION0 1 8170 4096 3346432 SQL111029003533400 1 1071199744 4096 815104 LOC_INDEX 1 5 4373 4096 1789952 Database size: 19763 KB Iteration: 7 ConglomerateNameIsIndex NumAllocatedPages NumFreePages NumUnFilledPagesPageSize EstimSpaceSaving LOCATION0 1 8170 4096 3346432 SQL111029003533400 1 132029 932 4096 118784 LOC_INDEX 1 395 47 11 4096
[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: 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 1 rows, deletes committed every 5 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 ConglomerateNameIsIndex NumAllocatedPages NumFreePages NumUnFilledPagesPageSize EstimSpaceSaving LOCATION0 1 8031 4096 3289088 SQL111029003533400 1 238 31 179 4096 126976 LOC_INDEX 1 211 185119 4096 757760 Database size: 12993 KB Iteration: 1 ConglomerateNameIsIndex NumAllocatedPages NumFreePages NumUnFilledPagesPageSize EstimSpaceSaving LOCATION0 1 8161 4096 3342336 SQL111029003533400 1 324 192200 4096 786432 LOC_INDEX 1 1 4061 4096 1662976 Database size: 17112 KB Iteration: 2 ConglomerateNameIsIndex NumAllocatedPages NumFreePages NumUnFilledPagesPageSize EstimSpaceSaving LOCATION0 7 8101 4096 3317760 SQL111029003533400 1 579 23 294 4096 94208 LOC_INDEX 1 394 28 2 4096 114688 Database size: 22821 KB Iteration: 3 ConglomerateNameIsIndex NumAllocatedPages NumFreePages NumUnFilledPagesPageSize EstimSpaceSaving LOCATION0 1 8160 4096 3342336 SQL111029003533400 1 631 227451 4096 929792 LOC_INDEX 1 5 4373 4096 1789952 Database size: 18054 KB Iteration: 4 ConglomerateNameIsIndex NumAllocatedPages NumFreePages NumUnFilledPagesPageSize EstimSpaceSaving LOCATION0 1 8170 4096 3346432 SQL111029003533400 1 735 174460 4096 712704 LOC_INDEX 1 1 4411 4096 1806336 Database size: 15632 KB Iteration: 5 ConglomerateNameIsIndex NumAllocatedPages NumFreePages NumUnFilledPagesPageSize EstimSpaceSaving LOCATION0 4 8140 4096 3334144 SQL111029003533400 1 992 21 690 4096 86016 LOC_INDEX 1 378 64 127 4096 262144 Database size: 25633 KB Iteration: 6 ConglomerateNameIsIndex NumAllocatedPages NumFreePages NumUnFilledPagesPageSize EstimSpaceSaving LOCATION0 1 8170 4096 3346432 SQL111029003533400 1 1071199744 4096 815104 LOC_INDEX 1 5 4373 4096 1789952 Database size: 19763 KB Iteration: 7 ConglomerateNameIsIndex NumAllocatedPages NumFreePages NumUnFilledPagesPageSize EstimSpaceSaving LOCATION0 1 8170 4096 3346432 SQL111029003533400 1 132029 932 4096 118784 LOC_INDEX 1 395 47 11 4096
