List:Commits« Previous MessageNext Message »
From:Jon Olav Hauglid Date:June 15 2011 3:59pm
Subject:Re: bzr commit into mysql-5.5 branch (Dmitry.Lenev:3441) Bug#12641342
View as plain text  
Hello,

On 06/13/2011 10:43 PM, Dmitry Lenev wrote:
>   3441 Dmitry Lenev	2011-06-14
>        Fix for bug #12641342 - "61401: UPDATE PERFORMANCE DEGRADES
>        GRADUALLY IF A TRIGGER EXISTS".

Looks good to me. Nice analysis of the problem. I also quite like that 
the patch in fact simplifies behavior, not only fixes the problem :-)

I would like to see a (future) trunk refactoring of
   enum enum_locked_tables_mode locked_tables_mode;
to avoid mixing prelocking and LOCK TABLES.

I've been trying to think of more we could do to prevent us from 
releasing of locks inside substatements (more than the assert you have 
added), but I don't have any bright ideas :-) I've looked at the current 
use of release_transactional_locks() and it looks OK.

Some nitpics about the commit message below:

>        This bug manifested itself in two ways:
>
>        - Firstly execution of any data-changing statement which
>          required prelocking (i.e. involved stored function or
>          trigger) as part of transaction slowed down a bit all
>          subsequent statements in this transaction. So performance
>          in transaction which periodically involved such statements
>          gradually degraded over time.
>        - Secondly execution of any data-changing statement which
>          required prelocking as part of transaction prevented
>          concurrent FLUSH TABLES WITH READ LOCK from proceeding
>          until the end of transaction instead of end of particular
>          statement.
>
>        The problem was caused by incorrect handling of metadata lock
>        used in FTWRL implementation for statements requiring prelocked
>        mode.
>        Each statement which changes data acquires global IX lock
>        with STATEMENT duration. This lock is supposed to block
>        concurrent FTWRL from proceeding until the statement ends.
>        When entering prelocked mode duration of all metadata locks
>        acquired so far was changed to EXPLICIT, to prevent

were

>        substatements from releasing these locks. When prelocked mode
>        was left duration of metadata lock was changed to TRANSACTIONAL

locks were

>        (with a few exceptions) so they can be properly released at
>        the end of transaction.
>        Unfortunately, this meant that global IX lock blocking FTWRL

the global

>        with STATEMENT duration was moved to TRANSACTIONAL duration
>        after execution of statement requiring prelocking. As result
>        concurrent FTWRL was blocked until the end of transaction
>        instead of end of statement in such a situation.

just drop "in such a situation"?

>        Moreover, since each subsequent statement that required
>        prelocking and tried to acquire global IX lock with STATEMENT
>        duration got a new instance of MDL_ticket, which was later
>        moved to TRANSACTIONAL duration, this also led to unwarranted
>        growth of number of tickets with TRANSACITONAL duration in
>        this MDL_context. As result searching for other tickets in

this connection's MDL_context?

>        it became slow and acquisition of other metadata locks by this
>        transaction started to hog CPU.
>
>        This patch solves this problem by not moving locks to EXPLICIT
>        duration when thread enters prelocked mode (unless it is a real
>        LOCK TABLES mode). This step turned out to be not really
>        necessary as substatements don't try to release metadata locks.
>        Consequently, global IX lock blocking FTWRL keeps its STATEMENT

the global

>        duration and is properly released at the end of statement and
>        the above issue goes away.
>       @ mysql-test/r/flush.result
>          Added test for bug #12641342 - "61401: UPDATE PERFORMANCE
>          DEGRADES GRADUALLY IF A TRIGGER EXISTS".
>       @ mysql-test/t/flush.test
>          Added test for bug #12641342 - "61401: UPDATE PERFORMANCE
>          DEGRADES GRADUALLY IF A TRIGGER EXISTS".
>       @ sql/sql_class.cc
>          Since we no longer change duration of metadata locks to EXPLICIT
>          when entering prelocked mode (unless it is a real LOCK TABLES)
>          there is no need to restore proper duration of the locks when
>          leaving it.

it => prelocked mode?

>       @ sql/sql_class.h
>          Do not change duration of metadata locks to EXPLICIT when
>          entering prelocking mode (unless it is a real LOCK TABLES).
>          This allows to avoid problems with restoring correct duration
>          when leaving this mode. It is possible to do this step as

drop "step"?

>          substatements won't release metadata locks in any case.
>       @ sql/sql_parse.cc
>          Added assert checking that we won't release metadata locks
>          when in substatement.

--- Jon Olav
Thread
bzr commit into mysql-5.5 branch (Dmitry.Lenev:3441) Bug#12641342Dmitry Lenev14 Jun
  • Re: bzr commit into mysql-5.5 branch (Dmitry.Lenev:3441) Bug#12641342Jon Olav Hauglid16 Jun
  • Re: bzr commit into mysql-5.5 branch (Dmitry.Lenev:3441) Bug#12641342Davi Arnaut16 Jun