Why is my Amazon RDS for MySQL DB instance stuck in "Rebooting"?

6 minute read
0

I'm trying to restart my Amazon Relational Database Service (Amazon RDS) for MySQL DB instance. However, my DB instance is stuck in the "Rebooting" state, or the reboot is taking longer than expected. Why is this happening, and how do I resolve this?

Short description

Before performing a reboot, make sure to stop any incoming or ongoing transactions in your DB instance. Ongoing transactions will be stopped and any uncommitted transactions will be rolled back.

Note: The rollback of uncommitted transactions can be an expensive operation. Uncommitted transactions can also take a long time to complete before your Amazon RDS for MySQL instance becomes available again.

After a reboot has begun, the process can't be cancelled and the reboot will continue until it completes. If your reboot is taking longer than expected, investigate the root cause, and consider the following troubleshooting approaches:

  • Check for ongoing queries.
  • Check whether there are any unpurged transactions.
  • Review the MySQL error log file.

Resolution

Check for ongoing queries

Use the SHOW FULL PROCESSLIST command to check whether there are any active queries on your Amazon RDS for MySQL instance:

mysql> SHOW FULL PROCESSLIST;

Here's an example output that indicates that an UPDATE query is still in progress:

+-----+---------------+---------------------+------+---------+------+----------+-----------------------+
| Id  | User          | Host                | db   | Command | Time | State    | Info                  |
+-----+---------------+---------------------+------+---------+------+----------+-----------------------+
|   2 | rdsadmin      | localhost:30662     | NULL | Sleep   |    4 |          | NULL                  |
| 101 | admin         | 172.31.28.252:58288 | NULL | Query   |  111 | updating |UPDATE tutorials SET tu|
+-----+---------------+---------------------+------+---------+------+----------+-----------------------+

The output provides information about your MySQL threads running on your database. If there are queries that are still running, then allow the queries to complete before performing a reboot.

Note: Run the SHOW FULL PROCESSLIST query as the master user. If you're not the master user, then you must have MySQL server administration privileges to view all active threads on the MySQL instance. Otherwise, the output shows only active thread IDs in the user's MySQL account. For more information, see SHOW PROCESSLIST statement and MySQL server administration on the MySQL website.

Check whether there are any unpurged transactions

The MySQL InnoDB engine uses multiversion concurrency control (MVCC) that maintains a list of old versions of changed rows during a transaction. If a transaction must be rolled back, then InnoDB can perform any undo operations during this process. Old versions of rows are captured inside the undo space. These old versions are purged when they are no longer called during a transaction. If captured changes aren't purged because a transaction still references them, then the history list length can grow larger. For more information, see InnoDB multi-versioning on the MySQL website.

Unpurged transactions are represented as the history list length value. This value can be found under "TRANSACTIONS" in the SHOW ENGINE INNODB STATUS command output. Note that the history list length is typically a low value, although a write-heavy workload or long-running transactions can cause the value to increase. For more information about the history list length value, see Purge configuration on the MySQL website.

To check for any unpurged transactions inside the history list length, use the SHOW ENGINE INNODB STATUS command. The output from this command also allows you to view size of the history list length and for any ongoing transactions listed under the "TRANSACTIONS" section. For more information about reviewing the listed transactions, see InnoDB standard monitor and lock monitor output on the MySQL website.

For example:

------------
TRANSACTIONS
------------
Trx id counter 105746959
Purge done for trx's n:o < 105746958 undo n:o < 0 state: running but idle
History list length 32
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 328605240396520, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 328605240395600, not started
0 lock struct(s), heap size 1136, 0 row lock(s)

Note: If the history list length is a high value and there are active transactions, then it's not a best practice to reboot.

For more information, see SHOW ENGINE INNODB STATUS on the MySQL website.

Review the MySQL error log file

In Amazon RDS, the MySQL error log file is enabled by default. If you suspect that your RDS instance is taking too long to become available again, then review the content of your MySQL error log file. MySQL writes to the error log file when it starts up, shuts down, or when it encounters any errors.

For example, InnoDB might have to roll back any uncommitted transactions as part of the InnoDB crash recovery process. If any uncommitted transactions are rolled back, then the MySQL error log document this event. For more information, see InnoDB crash recovery on the MySQL website.

Additional troubleshooting

Note: If you choose to perform a point-in-time recovery (PITR) or restore from a snapshot, then your new Amazon RDS instance might not be immediately available. For more information, see the following articles:

If your Amazon RDS for MySQL DB instance is rebooting for a while, then try these additional troubleshooting tips:

  • If your DB instance has automated backups enabled, then perform a PITR to restore to a new Amazon RDS instance from a specified time. You can restore to any point in time within your backup retention period.
    Note: By default, restored DB instances are associated with the default DB parameter and option groups. However, you can use a custom parameter group and option group by specifying them during a restore.
  • Restore your DB instance from the most recent DB snapshot by creating a new DB instance. You can use the restored DB instance as soon as its status is available.
  • If your DB instance has a read replica, then promote the read replica to become a standalone DB instance. When you promote a read replica, the DB instance is automatically rebooted before it becomes available.

It's also a best practice to regularly monitor your database activity. You can monitor your Amazon RDS for MySQL DB instance using the following tools:

  • Amazon CloudWatch: With Amazon CloudWatch, you can monitor any ongoing database workload. If you're observing high values in DB connections, CPU utilization, or write/read IOPS, then there might be ongoing activity in your DB instance.
  • Enhanced Monitoring: Enhanced Monitoring requires permission to send OS metric information to CloudWatch Logs. You can grant Enhanced Monitoring permissions using an AWS Identity and Access Management (IAM) role. Before enabling Enhanced Monitoring, you must first create an IAM role.
  • Performance Insights: Performance Schema is an optional performance tool used by Amazon RDS for MySQL (or MariaDB). If you enable or disable Performance Insights, then you won't need to reboot your DB instance. You also won't experience any downtime or a failover.

AWS OFFICIAL
AWS OFFICIALUpdated 2 years ago