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 "Andreas Korneliussen (JIRA)" <de...@db.apache.org> on 2006/02/28 15:40:43 UTC
[jira] Created: (DERBY-1068) change of store contract: online
compress operations should not share any locks with user transactions
change of store contract: online compress operations should not share any locks with user transactions
------------------------------------------------------------------------------------------------------
Key: DERBY-1068
URL: http://issues.apache.org/jira/browse/DERBY-1068
Project: Derby
Type: Improvement
Components: Store
Reporter: Andreas Korneliussen
Priority: Minor
Propose to add the following to the store contract:
* truncate and compress requires exclusive table locking
* the truncate, purge and compress operations do not share any locks with user transactions
Currently the store implementation allows users to share locks in truncate and possibly purge.
This request is driven by:
http://www.nabble.com/conflict-detection-strategies-t1120376.html#a2938142
and:
http://www.nabble.com/RowLocation-contract-from-storage-engine-t1142235.html#a2994156
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
Re: [jira] Created: (DERBY-1068) change of store contract: online
compress operations should not share any locks with user transactions
Posted by Andreas Korneliussen <An...@Sun.COM>.
Mike Matrigali wrote:
> Andreas, are you going to propose a patch for this or would you like
> me to take the first take on it?
>
Hi,
I would appreciate it if you would like to take the first take on it,
since I do currently not have any code on this issue.
Thanks
--Andreas
> Andreas Korneliussen (JIRA) wrote:
>
>> change of store contract: online compress operations should not share
>> any locks with user transactions
>> ------------------------------------------------------------------------------------------------------
>>
>>
>> Key: DERBY-1068
>> URL: http://issues.apache.org/jira/browse/DERBY-1068
>> Project: Derby
>> Type: Improvement
>> Components: Store Reporter: Andreas Korneliussen
>> Priority: Minor
>>
>>
>> Propose to add the following to the store contract:
>> * truncate and compress requires exclusive table locking
>> * the truncate, purge and compress operations do not share any locks
>> with user transactions
>> Currently the store implementation allows users to share locks in
>> truncate and possibly purge.
>>
>> This request is driven by:
>> http://www.nabble.com/conflict-detection-strategies-t1120376.html#a2938142
>>
>>
>> and:
>>
>> http://www.nabble.com/RowLocation-contract-from-storage-engine-t1142235.html#a2994156
>>
>>
>
Re: [jira] Created: (DERBY-1068) change of store contract: online
compress operations should not share any locks with user transactions
Posted by Mike Matrigali <mi...@sbcglobal.net>.
Andreas, are you going to propose a patch for this or would you like
me to take the first take on it?
Andreas Korneliussen (JIRA) wrote:
> change of store contract: online compress operations should not share any locks with user transactions
> ------------------------------------------------------------------------------------------------------
>
> Key: DERBY-1068
> URL: http://issues.apache.org/jira/browse/DERBY-1068
> Project: Derby
> Type: Improvement
> Components: Store
> Reporter: Andreas Korneliussen
> Priority: Minor
>
>
> Propose to add the following to the store contract:
> * truncate and compress requires exclusive table locking
> * the truncate, purge and compress operations do not share any locks with user transactions
>
> Currently the store implementation allows users to share locks in truncate and possibly purge.
>
> This request is driven by:
> http://www.nabble.com/conflict-detection-strategies-t1120376.html#a2938142
>
> and:
>
> http://www.nabble.com/RowLocation-contract-from-storage-engine-t1142235.html#a2994156
>
[jira] Commented: (DERBY-1068) change of store contract: online
compress operations should not share any locks with user transactions
Posted by "quartz (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-1068?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12557065#action_12557065 ]
quartz commented on DERBY-1068:
-------------------------------
> Derby does reuse space in the table when subsequent inserts come in.
> The level of reuse tends to be when a whole page of data becomes deleted.
This is not true. From the doc of SYSCS_UTIL.SYSCS_COMPRESS_TABLE, as is:
"Typically, unused allocated space exists when a large amount of data is deleted from a table, or indexes are updated. By default, Derby does not return unused space to the operating system."
Normal tests tables show the seg0/ folder takes about 40 MB.
The crashed customer where the compress isn't working had a seg0/ folder of about 480 MB!
I must call a compress periodically.
In place or not, it has to be completely safe, concurrent or not. What I meant is that issuing an insert while compressing shouldn't result in either of the statement failing.
> change of store contract: online compress operations should not share any locks with user transactions
> ------------------------------------------------------------------------------------------------------
>
> Key: DERBY-1068
> URL: https://issues.apache.org/jira/browse/DERBY-1068
> Project: Derby
> Issue Type: Improvement
> Components: Store
> Reporter: Andreas Korneliussen
> Assignee: Mike Matrigali
> Priority: Minor
>
> Propose to add the following to the store contract:
> * truncate and compress requires exclusive table locking
> * the truncate, purge and compress operations do not share any locks with user transactions
> Currently the store implementation allows users to share locks in truncate and possibly purge.
> This request is driven by:
> http://www.nabble.com/conflict-detection-strategies-t1120376.html#a2938142
> and:
> http://www.nabble.com/RowLocation-contract-from-storage-engine-t1142235.html#a2994156
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-1068) change of store contract: online
compress operations should not share any locks with user transactions
Posted by "quartz (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-1068?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12557470#action_12557470 ]
quartz commented on DERBY-1068:
-------------------------------
Thanks for the explanation.
The error message I pasted here says 'deadlock'.
That's is happening in our labs and at customers so that is not a fluke.
Our table is a "rolling" one, i.e. we have 1 record per 5 minutes, for 3 months.
That is 288*90 rows. Every hour we delete the excess of 90 days, i.e. generally 12 rows.
The compress of the 40 MB table takes less than 10 seconds.
Anyway, I just cannot understand how it got to 480 MB.... :-/
I will see if we can reproduce with simple code.
> change of store contract: online compress operations should not share any locks with user transactions
> ------------------------------------------------------------------------------------------------------
>
> Key: DERBY-1068
> URL: https://issues.apache.org/jira/browse/DERBY-1068
> Project: Derby
> Issue Type: Improvement
> Components: Store
> Reporter: Andreas Korneliussen
> Assignee: Mike Matrigali
> Priority: Minor
>
> Propose to add the following to the store contract:
> * truncate and compress requires exclusive table locking
> * the truncate, purge and compress operations do not share any locks with user transactions
> Currently the store implementation allows users to share locks in truncate and possibly purge.
> This request is driven by:
> http://www.nabble.com/conflict-detection-strategies-t1120376.html#a2938142
> and:
> http://www.nabble.com/RowLocation-contract-from-storage-engine-t1142235.html#a2994156
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-1068) change of store contract: online
compress operations should not share any locks with user transactions
Posted by "Mike Matrigali (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-1068?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mike Matrigali updated DERBY-1068:
----------------------------------
It is still not totally concurrent but the procedure meant to be used to allow concurrent access to the table and compressing the table at same time is:
SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE
The COMPRESS_TABLE function guarantees best compression, but will always do so by getting
exclusive access to the table, and internally creating brand new base tables and indexes and loading
those from the old table while holding the exclusive lock.
INPLACE_COMPRESS_TABLE is "more" online, but does still have one phase that requires
an exclusive lock.
Also wondering if maybe it is not necessary to run compress if you plan on inserting into table. Derby
does reuse space in the table when subsequent inserts come in. The level of reuse tends to be when a whole page of data becomes deleted.
> change of store contract: online compress operations should not share any locks with user transactions
> ------------------------------------------------------------------------------------------------------
>
> Key: DERBY-1068
> URL: https://issues.apache.org/jira/browse/DERBY-1068
> Project: Derby
> Issue Type: Improvement
> Components: Store
> Reporter: Andreas Korneliussen
> Assignee: Mike Matrigali
> Priority: Minor
>
> Propose to add the following to the store contract:
> * truncate and compress requires exclusive table locking
> * the truncate, purge and compress operations do not share any locks with user transactions
> Currently the store implementation allows users to share locks in truncate and possibly purge.
> This request is driven by:
> http://www.nabble.com/conflict-detection-strategies-t1120376.html#a2938142
> and:
> http://www.nabble.com/RowLocation-contract-from-storage-engine-t1142235.html#a2994156
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-1068) change of store contract: online
compress operations should not share any locks with user transactions
Posted by "Mike Matrigali (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-1068?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mike Matrigali updated DERBY-1068:
----------------------------------
The "rolling" data app is the paradigm that the existing derby algorithm should handle very well with no need to call compress.
Rows in derby in the base table tend to cluster by page in the time they were inserted. So it should be the case that whole pages
of rows will be deleted and then be available for insert. Assuming the rows are about the same size. If for instance one row is
480gb the rest are 100 bytes then compress is likely necessary once that one big row is deleted.
If everything is working right no need to ever call compress.
Definitely if
you can reproduce the deadlock report in a new jira - we are off topic in this JIRA. Note if compress on 40 gb table takes 10 seconds, it is likely compress of 480 gb table will at least take 100 seconds and probably a lot more as now data is probably
out of cache. I think default timeout is 60 seconds.
> change of store contract: online compress operations should not share any locks with user transactions
> ------------------------------------------------------------------------------------------------------
>
> Key: DERBY-1068
> URL: https://issues.apache.org/jira/browse/DERBY-1068
> Project: Derby
> Issue Type: Improvement
> Components: Store
> Reporter: Andreas Korneliussen
> Assignee: Mike Matrigali
> Priority: Minor
>
> Propose to add the following to the store contract:
> * truncate and compress requires exclusive table locking
> * the truncate, purge and compress operations do not share any locks with user transactions
> Currently the store implementation allows users to share locks in truncate and possibly purge.
> This request is driven by:
> http://www.nabble.com/conflict-detection-strategies-t1120376.html#a2938142
> and:
> http://www.nabble.com/RowLocation-contract-from-storage-engine-t1142235.html#a2994156
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-1068) change of store contract: online
compress operations should not share any locks with user transactions
Posted by "quartz (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-1068?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12555742#action_12555742 ]
quartz commented on DERBY-1068:
-------------------------------
I have had a serious problem with SYSCS_UTIL.SYSCS_COMPRESS_TABLE.
On separate connections and separate yet concurrent threads (obviously)
I try to pack my table while insert are ongoing, basically like this:
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'SAMPLE_PERIOD_TB', 1);
INSERT INTO sample_period_tb (start_time,end_time) VALUES (1192037400000,1192037700000);
The exception I get is this:
A lock could not be obtained due to a deadlock, cycle of locks and waiters is:
Lock : ROW, SYSCONGLOMERATES, (4,20)
Waiting XID : {21215745, S} , APP, INSERT INTO sample_period_tb (start_time,end_time) VALUES (1192037400000,1192037700000)
Granted XID : {18280645, S}
Lock : ROW, SYSCONGLOMERATES, (4,19)
Waiting XID : {18280645, X} , APP, alter table "APP"."SAMPLE_PERIOD_TB" compress sequential
Granted XID : {18280645, S} , {21215759, S} , {21215745, S}
. The selected victim is XID : 21215745.
This is horrible! I would have to stop all inserts or what?
If this improvement mean the problem would go away, let us know (and make it high priority!).
Otherwise, you may want to file a new bug!
> change of store contract: online compress operations should not share any locks with user transactions
> ------------------------------------------------------------------------------------------------------
>
> Key: DERBY-1068
> URL: https://issues.apache.org/jira/browse/DERBY-1068
> Project: Derby
> Issue Type: Improvement
> Components: Store
> Reporter: Andreas Korneliussen
> Assignee: Mike Matrigali
> Priority: Minor
>
> Propose to add the following to the store contract:
> * truncate and compress requires exclusive table locking
> * the truncate, purge and compress operations do not share any locks with user transactions
> Currently the store implementation allows users to share locks in truncate and possibly purge.
> This request is driven by:
> http://www.nabble.com/conflict-detection-strategies-t1120376.html#a2938142
> and:
> http://www.nabble.com/RowLocation-contract-from-storage-engine-t1142235.html#a2994156
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-1068) change of store contract: online
compress operations should not share any locks with user transactions
Posted by "Mike Matrigali (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-1068?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mike Matrigali updated DERBY-1068:
----------------------------------
What I was trying to say is that derby will use allocated space that has been "freed" by committed deletes in some cases for subsequent inserts. Derby does not return the space to the OS, but it tracks the deleted space in the table and often after deletes it can use existing space within the table without asking the OS for more space to executes
inserts. This tends to work well when the total amount of space for inserts is greater than the total amount of space of deletes. Once the space of deletes is greater than expected future inserts than
there is dead space that can only be returned to OS through one of the compress table calls. A worst case example is an app which inserts 500gb of data and then deletes it all, and subsequently has no plans to insert much more.
The lock deadlock error looks wierd, and probably should not happen.
A lock timeout error is likely expected behavior for competing inserts to the offline compress table operation depending on how big the table you are compressing, machine speed, disk speed and settings for lock timeout wait and lock deadlock wait. These timeouts are configurable and can be set to wait forever if you would rather not see a lock timeout error. In most
cases the defaults for these are reasonably high - but the time to compress a 500 GB table is likely much longer than most settings of lock timeout.
If you can come up with a reproducible case for the deadlock error (vs. timeout), please contribute it under a new JIRA entry.
> change of store contract: online compress operations should not share any locks with user transactions
> ------------------------------------------------------------------------------------------------------
>
> Key: DERBY-1068
> URL: https://issues.apache.org/jira/browse/DERBY-1068
> Project: Derby
> Issue Type: Improvement
> Components: Store
> Reporter: Andreas Korneliussen
> Assignee: Mike Matrigali
> Priority: Minor
>
> Propose to add the following to the store contract:
> * truncate and compress requires exclusive table locking
> * the truncate, purge and compress operations do not share any locks with user transactions
> Currently the store implementation allows users to share locks in truncate and possibly purge.
> This request is driven by:
> http://www.nabble.com/conflict-detection-strategies-t1120376.html#a2938142
> and:
> http://www.nabble.com/RowLocation-contract-from-storage-engine-t1142235.html#a2994156
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Assigned: (DERBY-1068) change of store contract: online
compress operations should not share any locks with user transactions
Posted by "Mike Matrigali (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1068?page=all ]
Mike Matrigali reassigned DERBY-1068:
-------------------------------------
Assign To: Mike Matrigali
> change of store contract: online compress operations should not share any locks with user transactions
> ------------------------------------------------------------------------------------------------------
>
> Key: DERBY-1068
> URL: http://issues.apache.org/jira/browse/DERBY-1068
> Project: Derby
> Type: Improvement
> Components: Store
> Reporter: Andreas Korneliussen
> Assignee: Mike Matrigali
> Priority: Minor
>
> Propose to add the following to the store contract:
> * truncate and compress requires exclusive table locking
> * the truncate, purge and compress operations do not share any locks with user transactions
> Currently the store implementation allows users to share locks in truncate and possibly purge.
> This request is driven by:
> http://www.nabble.com/conflict-detection-strategies-t1120376.html#a2938142
> and:
> http://www.nabble.com/RowLocation-contract-from-storage-engine-t1142235.html#a2994156
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira