MySQL Forums
Forum List  »  Partitioning

Re: Lock issues with Table Partitioning
Posted by: Mattias Jonsson
Date: September 15, 2009 03:26AM

The problem is the locking it self, usually one only uses a couple of tables, i.e. lets say 10 table in a very big join. That results in 10 locks that first needs to be stored in the tables, and then they need to be sorted (MySQL's way to avoid deadlocks, by always locking in the same order). And then the locks need to be locked.

But if we use two partitioned tables with 200 partitions each, that results in 400 locks which take much more time to sort and to lock (even if only inserting one row, or a simple 'select * where primary_key=1' query). What we will try to do in 5.1 is to improve the lock sort algorithm and in a later version see if we can do the pruning before locking, and that way only lock the needed partitions. (Pruning would also need to support insert, update and delete...)

Regardless of what operation type, in 5.1 all partitions are locked at the same time (when opening the tables, before pruning takes place). The lock type depends on engine and operation type.

One can explain the locks on the partitions as if they instead of partitions where tables in a join.

Not lock related, but affects inserts:
For multi-row inserts (load date, insert ... select, insert (),(),();) the patch for bug#35845 will at least delay/decrease the resource usage (both cpu and memory) by only start bulk insert for a partition when it is actually receiving a row.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Lock issues with Table Partitioning
5857
September 15, 2009 03:26AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.