MySQL Bugs: #68869: InnoDB's thread concurrency tickets might be released after a row is read

Bug #68869	InnoDB's thread concurrency tickets might be released after a row is read
Submitted:	4 Apr 2013 23:50	Modified:	15 May 2013 20:51
Reporter:	Davi Arnaut (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.5.30, 5.6	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	concurrency tickets, innodb, thread concurrency

Description:
If InnoDB's thread concurrency is enabled (innodb_thread_concurrency >
0), a thread executing a query that reads from an InnoDB table might be
susceptible to concurrency checks (and possible queuing) whenever it
tries to enter InnoDB (for example, to read a row), irregardless of the
number of "free tickets" it might have (innodb_concurrency_tickets).

The problem arises from the fact that whenever a row is added to the
result set (that is, to be sent to the client), MySQL calls (see
select_send::send_data) ha_release_temporary_latches to release a
possible search latch, which also ends up releasing any concurrency
tickets the thread might have.  These tickets were meant to be released
only at the end of the statement, not whenever a row is to be sent to a
client. This causes a concurrency check to be performed after each row
is read, causing significant slowdowns for queries that return a lot of
rows.

Incidentally, those tickets are not released if the row is being added
to a temporary table. So, if the query ends up using a temporary table
(e.g. SQL_BUFFER_RESULT) it might actually perform better than an
equivalent query that does not use a temporary table.

How to repeat:
mysql> set global innodb_thread_concurrency=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1;
393216 rows in set (3.46 sec)

mysql> set global innodb_thread_concurrency=250;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1;
393216 rows in set (4.04 sec)

Can the code be updated to be clever enough to only call ha_release_temporary_latches when the server really is writing to a socket and not "maybe" writing to one?

  /*
    We may be passing the control from mysqld to the client: release the
    InnoDB adaptive hash S-latch to avoid thread deadlocks if it was reserved
    by thd
  */
  ha_release_temporary_latches(thd);

Verified upon the inspection of the code.

this bug seems only affect 5.5. 

quoted code In 5.6
innobase_release_temporary_latches(
/*===============================*/
        handlerton*     hton,   /*!< in: handlerton */
        THD*            thd)    /*!< in: MySQL thread */
{
        DBUG_ASSERT(hton == innodb_hton_ptr);

        if (!innodb_inited) {

                return(0);
        }

        trx_t*  trx = thd_to_trx(thd);

        if (trx != NULL) {
                trx_search_latch_release_if_reserved(trx);
        }

        return(0);
}

quoted code in 5.5
static
int
innobase_release_temporary_latches(
/*===============================*/
        handlerton*     hton,   /*!< in: handlerton */
        THD*            thd)    /*!< in: MySQL thread */
{
        trx_t*  trx;

        DBUG_ASSERT(hton == innodb_hton_ptr);

        if (!innodb_inited) {

                return(0);
        }

        trx = thd_to_trx(thd);

        if (trx) {
                innobase_release_stat_resources(trx);
        }
        return(0);
}

Added a changelog entry for 5.5.33, 5.6.13, 5.7.2:

"When InnoDB thread concurrency is enabled, there was a
possibility that thread concurrency tickets would be released
after each row is read resulting in a concurrency check after
each read. A concurrency check after each read could impact
performance of queries that return a large number of rows."

There are two ways you might notice this on upgrade from 5.5 to 5.6:

1. An increase in system CPU usage in 5.6 compared to 5.5 due to the extra mutex checking.

2. The performance effect.

James Day, MySQL Senior Principal Support Engineer, Oracle.