MySQL Forums
Forum List  »  Performance

Re: slow overall perfomance
Posted by: N N
Date: August 08, 2011 09:58AM

yes, you are right we have discovered a regression:

Our application attempts to INSERT IGNORE the same row of data from many different connections to the same InnoDB table. In our test runs, we noticed that the 5.0 code created S locks while the 5.1 code created X locks for the same set of actions.

In the code paths for 5.0 and below, INSERT IGNORE processing would take
S-locks on any duplicate rows. The X-lock was reserved for only new rows
added to the table.

In 5.1 the locking logic was rewritten and all rows touched by INSERT IGNORE
are X-locked instead. This can turn a parallel data merge process into
essentially a single-threaded process because the connections that are not
actually adding a row to the table must wait for their X-lock which requires
the termination of the other locking thread(s).

the fault can be traced to this logic (5.1+):

if (allow_duplicates) {

/* If the SQL-query will update or replace
duplicate key we will take X-lock for
duplicates ( REPLACE, LOAD DATAFILE REPLACE,
INSERT ON DUPLICATE KEY UPDATE). */

err = row_ins_set_exclusive_rec_lock(
LOCK_ORDINARY, rec, index, offsets, thr);

This means that all INSERT IGNORE will take an X-lock because earlier this
flag was set:

case HA_EXTRA_IGNORE_DUP_KEY:
thd_to_trx(ha_thd())->duplicates |= TRX_DUP_IGNORE;
break;

Options: ReplyQuote


Subject
Views
Written By
Posted
3389
N N
May 20, 2011 03:53AM
1229
May 21, 2011 08:46AM
1354
N N
May 23, 2011 03:07AM
1129
May 23, 2011 09:49AM
Re: slow overall perfomance
1078
N N
August 08, 2011 09:58AM


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.