From: Matthias Leich Date: June 16 2011 8:53pm Subject: bzr push into mysql-5.5 branch (matthias.leich:3459 to 3460) List-Archive: http://lists.mysql.com/commits/139383 Message-Id: <20110616205348.56F226D538@five.local.lan> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============6223963604603863288==" --===============6223963604603863288== MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline 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; --===============6223963604603863288== MIME-Version: 1.0 Content-Type: text/bzr-bundle; charset="us-ascii"; name="bzr/matthias.leich@stripped" Content-Transfer-Encoding: 7bit Content-Disposition: inline # Bazaar merge directive format 2 (Bazaar 0.90) # revision_id: matthias.leich@stripped\ # ev4d4a0o1g78eaa5 # target_branch: file:///work2/repo/mysql-5.5/ # testament_sha1: 93e79605884ef064d7c0019f8960b9c90c2c194a # timestamp: 2011-06-16 22:53:48 +0200 # source_branch: file:///work2/repo/mysql-5.5/ # base_revision_id: dmitry.lenev@stripped\ # ppr6n3bspiktvij0 # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWUxf4IgABBnfgAAQWGf//3/n 3yC////wYAgNu58Bve6hBve472QXvcaPpR6tDFJkCNNTamJtBqGmg9IaHpMhpoyPUyAkkJoYTQU9 BT0U/VAZlD1HqDQ0aDBGgyKZipqPU8p5NJoNAAAAA0AADFMqm9PUTSPKNBk0AAABoDQBpoNqU0ZC TR6j1NG0I2kDI0GEaBoAMCSIJoIBiBNqGk9Jpoj1MmjZRo00BpNLHfV7BUypBJOAdzaF8XeScTnC zBafDFLGzdqYXBEIU241bmDAXkkJcFJyAIGlXSqpZKec6Zs1Y2O60U9o6Ub+YVKDJMyQzL++n8IC 2edKtimIVHx/dOrVxJGDekLtEtEbIkGU0lbK7DsELy3FU02h3DCynn2QL386jQI9LOVovDSMGKGN 1EiGp33ja8+hzymQcW1424cUx0HMXWaZRkiDaa7BeaBw+v2KFxDK2NwiGkDpg2xPuCkhrnIIwI4D QAa33XTrbV5XUfPzb2eB38+0sPXGop4B9Y2ZqfklNkByY8hDryzFH7L6+JqKKtFPsad4UMSx8wwx kkEjcwXAs0/G9bltKu5ERG0z9Z6+/s1bNPHwXtkFgIsLV02piCFpTC4hZVjJwoopw7AzVCTXuDbI QS0sYQHZIixIbI+4hGJYhQEDpklQQ/aoAnKSrEQTkQU6oHqiWzLp7CCSoM0GHk4yTokQKz+js/uj fab0tVpoEjlNaBAFirRpRDWwHdK2dQcgvJYZSYg2hHxqGcZBCTNtgYrx00hqlAJww8YARYIOWIR7 sVV87bVnjpNwYivc0RH+2obLOylunwSVCBid+Q/gm67DlbaWnLWG9ROo4bdB8gVlouPBn1Q3EiIQ gAOMkrCHm4LceZxJssLlrFSOnS+JrM6UMYxpwdxCSW2+8RIjcOvkantzsPZNjCGFd60CKYRmNud5 KlrkIJnBYWljV469wyY2FJzyq0Z875h9LhKLrQycLGKFicuwkW8SirVXGuIiRnpcOUK5mCKgVS+w KZI5iOBXuzzpsg80bGDEFLbWZilyyBpmJCcFSK3vpiPcX7JhuyeXemzvdva10HBuIyLuJBVSTAsD HuistaQbI0MiQxkX3GsRQuOSinBPB40+tjfKd3M2wKq87gUB5G2Aug25qMt2IfEx1udnkzC1PN8s LF2F8p+C1KGFsc2OBjbAFzuxKs4rdlHZ73DdpHjTiOgWpQwxfwM+5pkShoZDBvptEBWU1VHBcjuB SKCGegFIRiGJdk1A0sOtfxkXO8IEEVcL4CDEkEp9gHdfAyHCQad8iNXpnadxr35HFgvpFBILsodi jkMGmsuM0wMrCGMCWYZc8xKv/LOSyUb1ia6KA0j1bJSMx9FZPIq93oZEUz2rimTH26JxKcd9MTsg D03xK2TkSaKiQ9uRwLh3p1LVqTGDlG25hcEa1y4zHLYlF60ODQspZREUUkLgWHEWkQM+yBbAhVYQ pk9c4ECpa6IxnkAyn0UOh6mcZgGUcnEvZ4e8YR7QUJCPBySR8BmPI+4mYDAonr8Vb9RIvJER6v/A uORpcl6gbqH7O3Df0vwTTVfIJFkiTr3IUBhtzwcheLzRUHGHxPibpmgmPI/k8qlm9IiNGCPwSRJz /RkYhkdh56HuCObTyLzI0Oec6zmIzI5KUtmssG1MJeKqzqDiRTzaaXaCLNC5rarKWuhahDBowdFq qpHMbDuOQlt19RYKlgoQlJB1QKFhgWuSxSdTbmj57FqxV0d+y91PvFijp0lJmntKUBDsDBhoDZ6H N6jDciZP0B5wwINmw6iqE0u05gtIKp5nqvMTga5HQM1hGA5pRRnuml+T2tEFtzrUXBgxA2zrhMwm kQQCKB1d0rDfspqaiU0GG3A13DmTH2AMHeLE8VxBP1HbvMTWPu4+44Dh7hkSOm8L0dD/g8Q1hQoI whkx7Gr2EM5C88V9TAF/GkCYb1yVu7g0EwlyEQAM8FDqXSQk3tDahEC+3tEuA7d5EShu7bBAUOUj WnwMpmLgKASjjk8CR4h4jcTMyronbfjAwzJTx0q7Qk8TqDtTjYq+sKclafU+AjIMRDcErKlmrDs6 guSg4SCgwMCGAZOLeAVBE9e9ofQjz3dL7E6uuSrqBHAOtfE60MEMpg+Ytq8XZ70ICsNLIzEbB1DI elGC9pIgYYKC4h+sI9wTiBEiwExMnYUU6yMvEFqEadOc9+UOm5CLUc2HTC8nE5xmfM46LnL/sCLU rJhdBeb+2tQU9AuspqI+NkmQ4W8TyIgPsuukQ6XERVSp5trjErZcep+biuX0IgjSmSi/fsoFaJuz ZyO2kUiJ4LX3wR8FPhhvJFylLVNoKLDEU1rnW+93gSO4ghQFUm2zU5mOTDNphEuIHBk4VdjJVkAs F588EclrNLWiU3p35HibXHW2n0uNoEO9MXsEMxx+YFYUyhi/csaWHJOBmy9BEBTEp4WZwleWqZ4F gIpmzR2ERYwFC8S8VkU/rr2iO7ZLvV9lgasr6W2VVtQQeium+BvpiYsWxweFMxNZSeoJyFYoVkIp C6oh6Hl8z58jjJEvL/xdyRThQkExf4Ig --===============6223963604603863288==--