From: MARK CALLAGHAN
Date: February 18 2010 6:56pm
Subject: Re: Finding the start of a statement
List-Archive: http://lists.mysql.com/internals/37725
Message-Id: <f0661f8c1002181056i11e51a5bjbc76c8b705f04076@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

On Thu, Feb 18, 2010 at 10:19 AM, MARK CALLAGHAN <mdcallag@stripped> wrote=
:

>
> http://bugs.mysql.com/bug.php?id=3D51285 has an example of a stack trace
> where there is a call from
> =A0JOIN::optimize -> ... -> ha_innodb::external_lock -> innobase_commit
> -> trx_commit_for_mysql
>
> This is not good. The trx functions lock kernel_mutex which is already
> too hot. Why must commit be done during query optimization? This is
> the comment from ha_innodb::external_lock()
>
> =A0 =A0 =A0 =A0/* If the MySQL lock count drops to zero we know that the =
current SQL
> =A0 =A0 =A0 =A0statement has ended */
>
> =A0 =A0 =A0 =A0if (trx->n_mysql_tables_in_use =3D=3D 0) {
>
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0trx->mysql_n_tables_locked =3D 0;
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0prebuilt->used_in_HANDLER =3D FALSE;
>
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0if (!thd_test_options(thd, OPTION_NOT_AUTO=
COMMIT |
> OPTION_BEGIN)) {
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0if (trx->active_trans !=3D=
 0) {
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0innobase_c=
ommit(ht, thd, TRUE);
>

I added more tracing and the early call to innobase_commit is OK. For
simple queries and simple joins, trx_commit_for_mysql is still called
once per statement during sysbench and something else must have caused
the perf regression post 5.1.38.

--=20
Mark Callaghan
mdcallag@stripped