synch/sxlock/innodb/hash_table_locks - Amazon Aurora
Supported engine versionsContextLikely causes of increased waitsActions
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

synch/sxlock/innodb/hash_table_locks

The synch/sxlock/innodb/hash_table_locks event occurs when pages not found in the buffer pool must be read from storage.

Supported engine versions

This wait event information is supported for the following versions:

  • Aurora MySQL versions 2 and 3

Context

The event synch/sxlock/innodb/hash_table_locks indicates that a workload is frequently accessing data that isn't stored in the buffer pool. This wait event is associated with new page additions and old data evictions from the buffer pool. The data stored in the buffer pool aged and new data must be cached, so the aged pages are evicted to allow caching of the new pages. MySQL uses a least recently used (LRU) algorithm to evict pages from the buffer pool. The workload is trying to access data that hasn't been loaded into the buffer pool or data that has been evicted from the buffer pool.

This wait event occurs when the workload must access the data in files on disk or when blocks are freed from or added to the buffer pool's LRU list. These operations wait to obtain a shared excluded lock (SX-lock). This SX-lock is used for the synchronization over the hash table, which is a table in memory designed to improve buffer pool access performance.

For more information, see Buffer Pool in the MySQL documentation.

Likely causes of increased waits

When the synch/sxlock/innodb/hash_table_locks wait event appears more than normal, possibly indicating a performance problem, typical causes include the following:

An undersized buffer pool

The size of the buffer pool is too small to keep all of the frequently accessed pages in memory.

Heavy workload

The workload is causing frequent evictions and data pages reloads in the buffer cache.

Errors reading the pages

There are errors reading pages in the buffer pool, which might indicate data corruption.

Actions

We recommend different actions depending on the causes of your wait event.

Increase the size of the buffer pool

Make sure that the buffer pool is appropriately sized for the workload. To do so, you can check the buffer pool cache hit rate. Typically, if the value drops below 95 percent, consider increasing the buffer pool size. A larger buffer pool can keep frequently accessed pages in memory longer. To increase the size of the buffer pool, modify the value of the innodb_buffer_pool_size parameter. The default value of this parameter is based on the DB instance class size. For more information, see Best practices for Amazon Aurora MySQL database configuration.

Improve data access patterns

Check the queries affected by this wait and their execution plans. Consider improving data access patterns. For example, if you are using mysqli_result::fetch_array, you can try increasing the array fetch size.

You can use Performance Insights to show queries and sessions that might be causing the synch/sxlock/innodb/hash_table_locks wait event.

To find SQL queries that are responsible for high load

  1. Sign in to the Amazon Web Services Management Console and open the Amazon RDS console at https://console.amazonaws.cn/rds/.

  2. In the navigation pane, choose Performance Insights.

  3. Choose a DB instance. The Performance Insights dashboard is shown for that DB instance.

  4. In the Database load chart, choose Slice by wait.

  5. At the bottom of the page, choose Top SQL.

    The chart lists the SQL queries that are responsible for the load. Those at the top of the list are most responsible. To resolve a bottleneck, focus on these statements.

For a useful overview of troubleshooting using Performance Insights, see the Amazon Database Blog post Analyze Amazon Aurora MySQL Workloads with Performance Insights.

Reduce or avoid full-table scans

Monitor your workload to see if it's running full-table scans, and, if it is, reduce or avoid them. For example, you can monitor status variables such as Handler_read_rnd_next. For more information, see Server Status Variables in the MySQL documentation.

Check the error logs for page corruption

You can check the mysql-error.log for corruption-related messages that were detected near the time of the issue. Messages that you can work with to resolve the issue are in the error log. You might need to recreate objects that were reported as corrupted.