Unnecessary overhead from persistent adaptive hash index latches

[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 any and proceeds to #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:

/**
  @details
  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.

Also with AHI partitioning we have many latches to take care of, so the above logic becomes quite complicated, fragile, and is likely the reason for bugs like bug #1100760.

This report is to disable that persistent latches functionality.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-715