bzr push into mysql-5.5 branch (matthias.leich:3459 to 3460)

[Matthias Leich]

 3460 Matthias Leich	2011-06-16
      Fix for
      Bug 12430414 - THE TEST PERFSCHEMA.SELECTS.TEST CAN AFFECT SUCCEEDING TESTS
      Bug 12430599 - THE TEST PERFSCHEMA.ONE_THREAD_PER_CON. CAN AFFECT SUCCEEDING TESTS
      Bug 12431153 - THE TEST PERFSCHEMA.PFS_UPGRADE CAN AFFECT SUCCEEDING TEST

    modified:
      mysql-test/suite/perfschema/include/cleanup_helper.inc
      mysql-test/suite/perfschema/include/upgrade_check.inc
      mysql-test/suite/perfschema/r/selects.result
      mysql-test/suite/perfschema/t/selects.test
 3459 Dmitry Lenev	2011-06-16
      Fix for bug #12641342 - "61401: UPDATE PERFORMANCE DEGRADES
      GRADUALLY IF A TRIGGER EXISTS".
      
      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, durations of all metadata locks
      acquired so far were changed to EXPLICIT, to prevent 
      substatements from releasing these locks. When prelocked mode
      was left, durations of metadata locks were changed to
      TRANSACTIONAL (with a few exceptions) so they can be properly
      released at the end of transaction. 
      Unfortunately, this meant that the global IX lock blocking
      FTWRL with STATEMENT duration was moved to TRANSACTIONAL
      duration after execution of statement requiring prelocking.
      
      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 led to unwarranted growth of
      number of tickets with TRANSACITONAL duration in this
      connection's MDL_context. As result searching for other
      tickets in it became slow and acquisition of other metadata
      locks by this transaction started to hog CPU.
      
      Moreover, this also meant that after execution of statement
      requiring prelocking concurrent FTWRL was blocked
      until the end of transaction instead of end of statement.
      
      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, the global IX lock blocking FTWRL keeps its
      STATEMENT 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/mdl.h
        Added comment describing various types of metadata lock
        duration.
     @ 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 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 as
        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.

    modified:
      mysql-test/r/flush.result
      mysql-test/t/flush.test
      sql/mdl.h
      sql/sql_class.cc
      sql/sql_class.h
      sql/sql_parse.cc
=== modified file 'mysql-test/suite/perfschema/include/cleanup_helper.inc'
--- a/mysql-test/suite/perfschema/include/cleanup_helper.inc	2010-11-03 15:42:33 +0000
+++ b/mysql-test/suite/perfschema/include/cleanup_helper.inc	2011-06-16 20:35:01 +0000
@@ -1,25 +1,33 @@
-# Copyright (C) 2009 Sun Microsystems, Inc
+# Copyright (c) 2009, 2011, Oracle and/or its affiliates. All rights reserved.
 #
-# This program is free software; you can redistribute it and/or modify
-# it under the terms of the GNU General Public License as published by
-# the Free Software Foundation; version 2 of the License.
+# This program is free software; you can redistribute it and/or
+# modify it under the terms of the GNU General Public License
+# as published by the Free Software Foundation; version 2 of
+# the License.
 #
 # This program is distributed in the hope that it will be useful,
 # but WITHOUT ANY WARRANTY; without even the implied warranty of
-# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
 # GNU General Public License for more details.
 #
 # You should have received a copy of the GNU General Public License
 # along with this program; if not, write to the Free Software
-# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
+# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA
+# 02110-1301  USA
 
 # Tests for PERFORMANCE_SCHEMA
 
 update performance_schema.setup_instruments set enabled='YES';
 
+connection con1;
 disconnect con1;
+--source include/wait_until_disconnected.inc
+connection con2;
 disconnect con2;
+--source include/wait_until_disconnected.inc
+connection con3;
 disconnect con3;
+--source include/wait_until_disconnected.inc
 
 connection default;
 

=== modified file 'mysql-test/suite/perfschema/include/upgrade_check.inc'
--- a/mysql-test/suite/perfschema/include/upgrade_check.inc	2010-09-13 23:19:39 +0000
+++ b/mysql-test/suite/perfschema/include/upgrade_check.inc	2011-06-16 20:35:01 +0000
@@ -17,8 +17,10 @@
 # $out_file and $err_file must be set within pfs_upgrade.test.
 #
 
+--source include/count_sessions.inc
 --error 1
 --exec $MYSQL_UPGRADE --skip-verbose --force > $out_file 2> $err_file
+--source include/wait_until_count_sessions.inc
 
 # Verify that mysql_upgrade complained about the performance_schema
 --cat_file $err_file

=== modified file 'mysql-test/suite/perfschema/r/selects.result'
--- a/mysql-test/suite/perfschema/r/selects.result	2010-11-03 15:42:33 +0000
+++ b/mysql-test/suite/perfschema/r/selects.result	2011-06-16 20:35:01 +0000
@@ -62,6 +62,7 @@ SELECT EVENT_ID FROM performance_schema.
 WHERE 1 = 2;
 CREATE EVENT t_ps_event
 ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 SECOND
+ON COMPLETION PRESERVE
 DO INSERT INTO t_event
 SELECT DISTINCT EVENT_ID
 FROM performance_schema.events_waits_current
@@ -106,5 +107,6 @@ EVENT_ID
 [EVENT_ID]
 DROP PROCEDURE t_ps_proc;
 DROP FUNCTION t_ps_func;
+DROP EVENT t_ps_event;
 DROP TABLE t1;
 DROP TABLE t_event;

=== modified file 'mysql-test/suite/perfschema/t/selects.test'
--- a/mysql-test/suite/perfschema/t/selects.test	2010-11-03 15:42:33 +0000
+++ b/mysql-test/suite/perfschema/t/selects.test	2011-06-16 20:35:01 +0000
@@ -1,4 +1,4 @@
-# Copyright (c) 2008, 2010, Oracle and/or its affiliates. All rights reserved.
+# Copyright (c) 2008, 2011, Oracle and/or its affiliates. All rights reserved.
 #
 # This program is free software; you can redistribute it and/or modify
 # it under the terms of the GNU General Public License as published by
@@ -97,6 +97,7 @@ SELECT EVENT_ID FROM performance_schema.
 WHERE 1 = 2;
 CREATE EVENT t_ps_event
 ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 SECOND
+ON COMPLETION PRESERVE
 DO INSERT INTO t_event
    SELECT DISTINCT EVENT_ID
    FROM performance_schema.events_waits_current
@@ -168,7 +169,7 @@ delimiter ;|
 SELECT t_ps_func(connection_id()) = @p_id;
 
 # We might reach this point too early which means the event scheduler has not
-# execute our "t_ps_event". Therefore we poll till the record was inserted
+# executed our "t_ps_event". Therefore we poll till the record was inserted
 # and run our test statement afterwards.
 let $wait_timeout= 20;
 let $wait_condition= SELECT COUNT(*) = 1 FROM t_event;
@@ -179,5 +180,6 @@ SELECT * FROM t_event;
 # Clean up
 DROP PROCEDURE t_ps_proc;
 DROP FUNCTION t_ps_func;
+DROP EVENT t_ps_event;
 DROP TABLE t1;
 DROP TABLE t_event;


Attachment: [text/bzr-bundle] bzr/[e-mail]