Bug #70216 Unnecessary overhead from persistent adaptive hash index latches
Submitted: 2 Sep 2013 12:42 Modified: 14 Oct 2014 23:23
Reporter: Alexey Kopytov Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.1, 5.5, 5.6 OS:Any
Assigned to: CPU Architecture:Any

[2 Sep 2013 12:42] Alexey Kopytov
The server and InnoDB implement the following logic with respect to AHI latches acquired in row_search_for_mysql():

1. When row_search_for_mysql() decides that the AHI can be used to find the row, it acquires the S latch on AHI.
2. The latch is not normally released when InnoDB returns the control back to the server, i.e. is kept across multiple server calls into InnoDB.
3. The server may force InnoDB to release that latch later via ha_release_temporary_latches(), when it thinks it's going to do something that may take a long time, like sending results to the client, do filesort, or convert an internal temp. table from HEAP to MyISAM.
4. The latch is also released by InnoDB on table open/close or transaction start/commit/rollback.
5. The latch is also released by InnoDB if it detects there are X waiters on the latch.
6. In which case it also releases the S latch acquired by row_search_for_mysql() before return in the next 10000 calls to row_search_for_mysql(). After that many calls it assumes persistent latch will not result in X waiters starvation anymore and switches back to state #1.

So it's quite a lot of work to have a persistent S latch and avoid contention on it at the same time. The question is why do we need to have a persistent lock in the first place. Comments in ha_release_temporary_latches() provide the following explanation:

  This function should be called when MySQL sends rows of a SELECT result set
  or the EOF mark to the client. It releases a possible adaptive hash index
  S-latch held by thd in InnoDB and also releases a possible InnoDB query
  FIFO ticket to enter InnoDB. To save CPU time, InnoDB allows a thd to
  keep them over several calls of the InnoDB handler interface when a join
  is executed. But when we let the control to pass to the client they have
  to be released because if the application program uses mysql_use_result(),
  it may deadlock on the S-latch if the application on another connection
  performs another SQL query. In MySQL-4.1 this is even more important because
  there a connection can have several SELECT queries open at the same time.

It is unclear how much CPU time is saved in reality, given all the complications to avoid contention on the latch.

The relevant code seems to be rather old, probably implemented when locking an S latch was expensive even in the uncontented case. Did anyone tried recently to evaluate if it actually has any benefits?

How to repeat:
Read code in the ha_release_temporary_latches() code path and row_search_for_mysql().
[4 Sep 2013 15:15] Inaam Rana

All these extra push ups around search latch are likely legacy of times when InnoDB used pthread locks. Since plugin 5.1 InnoDB started using atomics based rw-locks and s-lock acquisition should not weigh heavily on performance. I believe all btr_search_latch acquisitions/releases should be pushed to btr0sea.cc.
[3 Oct 2013 13:06] MySQL Verification Team
Hello Alexey,

Thank you for the bug report.

[26 Sep 2014 12:08] Laurynas Biveinis
revno: 8709
committer: Yasufumi Kinoshita <yasufumi.kinoshita@oracle.com>
branch nick: mysql-trunk
timestamp: Thu 2014-08-28 13:44:16 +0900
  If INNODB_RW_LOCKS_USE_ATOMICS is enabled, rw_lock implementation is fast enough.
  So no need to keep btr_search_latch also when over 10000 times AHI searches per 1 transaction,
  because just might block the other AHI updates.
  Approved by Sunny in rb#6257
[14 Oct 2014 23:21] Daniel Price
Fixed as of the upcoming 5.7.6 release, and here's the changelog entry:

Under certain circumstances, adaptive hash index latches
("btr_search_latch") would persist. With "INNODB_RW_LOCKS_USE_ATOMICS"
enabled, persistent adpative hash index latches are unnecessary and may
block other adaptive hash index updates. 

Thank you for the bug report.
[14 Oct 2014 23:23] Daniel Price
Correction to my previous post. This bug is fixed in MySQL 5.7.5.
[6 Aug 2015 9:12] zhai weixiang
I guess this bug  is  "really" fixed in MySQL 5.7.8-rc after supporting multi btr_search_latch

In function row_search_mvcc , if shortcut search  is chosen,  the btr_search_latch is always released no matter whether the shortcut search is failed or success.

check this the code of 5.7.8

Besides, after this change ,I think the logic bellow in function row_search_mvcc may be  redundant

        if (trx->has_search_latch
            && rw_lock_get_writer(
                btr_get_search_latch(index)) != RW_LOCK_NOT_LOCKED
            ) {

                /* There is an x-latch request on the adaptive hash index:
                release the s-latch to reduce starvation and wait for
                BTR_SEA_TIMEOUT rounds before trying to keep it again over
                calls from MySQL */


                trx->search_latch_timeout = BTR_SEA_TIMEOUT;