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
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.
Topics
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 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
Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/
. -
In the navigation pane, choose Performance Insights.
-
Choose a DB instance. The Performance Insights dashboard is shown for that DB instance.
-
In the Database load chart, choose Slice by wait.
-
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
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.
