Bug #33191 | Threads 'serialize' when multy-query delete trans. w/trig are run againt Falcon | ||
---|---|---|---|
Submitted: | 13 Dec 2007 1:21 | Modified: | 2 May 2008 23:30 |
Reporter: | Omer Barnir (OCA) | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Falcon storage engine | Severity: | S2 (Serious) |
Version: | 6.0.4 | OS: | Any |
Assigned to: | Kevin Lewis | CPU Architecture: | Any |
[13 Dec 2007 1:21]
Omer Barnir
[13 Dec 2007 1:23]
Omer Barnir
For test case, see files attached to bug#33072 at entry [7 Dec 20:35] Omer BarNir
[23 Dec 2007 8:22]
Philip Stoev
If one issues a SELECT in a separate connection, it takes several minutes to complete (CPU usage being zero all the time).
[23 Dec 2007 18:07]
Philip Stoev
Some relevant comments from the parent bug: * As Omer observed, statements become serialized, running at 1 statement each falcon_lock_timeout milliseconds. The stored procedure in question runs 100 such statements and completes in 100 * falcon_lock_timeout * number_of_threads (50) msec. No CPU activity takes place during the falcon_lock_timeout period. * SELECT statements on the same table started in a separate connection take anywhere from 10 seconds to 2 minutes to run. It appears to me this means both that they are affected by the timeout (was 10 seconds at the time of observation), that writes block reads and that writes starve reads (cpu usage is very low all the time). SELECTS are masssively delayed during the deadlock: mysql> select * from test.tbl_a; ... 738 rows in set (13 min 13.45 sec)
[28 Dec 2007 5:59]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/40446 ChangeSet@1.2766, 2007-12-27 23:58:44-06:00, klewis@klewis-mysql. +1 -0 Bug#33191 - Serialization of delete operations where trigers are involved occured in the MySQL server. StorageInterface::store_lock() was not changing the lock_type to TL_WRITE_ALLOW_WRITE because the conditional statement was incorrect. This lower lock type allows multiple threads to do triggered deletes in Falcon concurrently. The condition is now like InnoDB except for allowing concurrent truncates, which Falcon can do.
[28 Dec 2007 6:08]
Kevin Lewis
This test is designed to create deadlocks. When the stored procedure is run against InnoDB with 2 or more clients concurrently, InnoDB recognizes deadlocks and automatically does a rollback on the transaction where it is detected. Falcon however, cannot detect the deadlock because the threads representing the other transactions are being serialized in the MySQL server. This happens in sql_delete.cc, mysql_delete() when it calls open_and_lock_tables(). The lock type used for Falcon is TL_WRITE. So even though there are multiple threads using Falcon during this test, they are mostly being serialized, at least for the deletes. This is why Falcon can not detect the deadlock. There is never more than one thread involved in this deadlock waiting in Falcon. InnoDB can detect the deadlock because the lock_type has been reduced to TL_WRITE_ALLOW_WRITE. This is done in a handler function called ha_innobase::store_lock(). Falcon also has one of these handler functions; StorageInterface::store_lock(). In fact, Falcon attempts to lower the priority to TL_WRITE_ALLOW_WRITE also, but with conditions different than InnoDB. If the same conditions are used in Falcon as what is used in InnoDB, then the Stored Procedure in this test can run concurrently within Falcon, the lock type is set to TL_WRITE_ALLOW_WRITE, and unlike InnoDB, there are no deadlocks detected. And most importantly, the performance drop seems to go away.
[11 Feb 2008 20:40]
Kevin Lewis
Patch is in mysql-6.0-falcon-team and mysql-6.0-release version 6.0.4
[12 Mar 2008 23:02]
Bugs System
Pushed into 6.0.4-alpha
[2 May 2008 23:30]
Paul DuBois
Note in 6.0.4 changelog. Multiple concurrent delete operations on a Falcon table were serialized rather than executing concurrently, resulting in poor performance.