Uploaded image for project: 'Percona Server for MySQL'
  1. Percona Server for MySQL
  2. PS-715

LP #1218347: Unnecessary overhead from persistent adaptive hash index latches

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Done
    • Priority: High
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None

      Description

      **Reported in Launchpad by Alexey Kopytov last update 06-09-2013 13:43:52

      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.

        Attachments

          Activity

            People

            Assignee:
            Unassigned
            Reporter:
            lpjirasync lpjirasync (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Smart Checklist