synch/rwlock/innodb/hash_table_locks - Amazon Aurora

synch/rwlock/innodb/hash_table_locks

The synch/rwlock/innodb/hash_table_locks event occurs when there is contention on modifying the hash table that maps the buffer cache.

Supported engine versions

This wait event information is supported for Aurora MySQL version 1, up to 1.23.1.

Context

The event synch/rwlock/innodb/hash_table_locks indicates that there is contention on modifying the hash table that maps the buffer cache. A hash table is a table in memory designed to improve buffer pool access performance. This wait event is invoked when the hash table needs to be modified.

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

Likely causes of increased waits

When the synch/rwlock/innodb/hash_table_locks 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.

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 the AWS Database Blog post Best practices for configuring parameters for Amazon RDS for MySQL, part 1: Parameters related to performance.

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/rwlock/innodb/hash_table_locks wait event.

Find SQL queries responsible for high load

Typically, databases with moderate to significant load have wait events. The wait events might be acceptable if performance is optimal. If performance isn't optimal, then examine where the database is spending the most time. Look at the wait events that contribute to the highest load, and find out whether you can optimize the database and application to reduce those events.

To find SQL queries that are responsible for high load

  1. Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/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 AWS 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.