What is stored in Aurora MySQL-Compatible local storage, and how can I troubleshoot local storage issues?

Short description

Instances in Aurora clusters have two types of storage:

• Storage for persistent data (called the cluster volume). This storage type increases automatically when more space is required. For more information, see What the cluster volume contains.
• Local storage for each Aurora instance in the cluster, based on the instance class. This storage type and size is bound to the instance class, and can be changed only by moving to a larger DB instance class. Aurora for MySQL uses local storage for storing error logs, general logs, slow query logs, audit logs, and non-InnoDB temporary tables.

For more information on local storage size, and to see the maximum amount of storage available for each instance size, see Temporary storage limits for Aurora MySQL.

Resolution

Low free storage capacity error

When you're approaching the limit for free storage capacity, you might receive the following error:

"The free storage capacity for DB Instance: instance-name is low at x% of the provisioned storage [Provisioned Storage: xx GB, Free Storage: xx GB]. You may want to increase the provisioned storage to address this issue."

Local storage can reach capacity when you store non-persistent data, like temporary table and log files, in the local storage. Check if you have ALTER TABLE commands running or if you are storing excessive temporary tables. By optimizing ALTER commands and temporary tables, you can lower local storage usage. Run a command like this multiple times to see the number of temporary tables on your instance:

SHOW GLOBAL STATUS LIKE 'created_tmp%tables';

To monitor the number of temporary tables created during a specific time range, you can enable GoSH. This allows you to store the server status variables in tables that you can review later for further analysis.

Review the Amazon CloudWatch metric for FreeableMemory to determine how much free memory is available. Then, check to see if audit logs, general logs, or slow query logs are consuming your space. You can monitor the local storage space associated with the Aurora instance by using the CloudWatch metric for FreeLocalStorage. For more information, see Monitoring metrics in an Amazon Aurora cluster.

If you need more storage space, you can increase the amount of your local storage. To increase your local storage, you must increase your DB instance class. For more information, see Aurora DB instance classes.

You can also increase the values for the parameters max_heap_table_size and tmp_table_size. Modifying these parameters can decrease the amount of data that spills to the disk, which can lower the amount of local storage that is used. You can modify these parameters in the parameter group that's attached to your instance. For more information, see Working with parameter groups.

Note: If you increase the values for max_heap_table_size and tmp_table_size, then the tables consume more memory on the instance. If you increase these values, be sure that there is enough available memory on the instance. You can monitor available memory by using the CloudWatch metric for FreeableMemory.

ERROR 1114 table full

When MySQL performs operations, such as ALTER TABLE, it creates an intermediate temporary table with the new schema. MySQL then loads data into the intermediate table before deleting the old table. The intermediate table might use local storage. If the intermediate table requires more storage than is available, you might receive the following error:

"ERROR 1114 (HY000): The table 'table-name' is full"

To resolve this error, increase local storage by scaling up the instance, temporarily. After the ALTER TABLE operation is complete, you can scale down the instance class. To increase your local storage, you must increase your Aurora instance class. For more information, see Aurora DB instance classes.

---

Related information

How do I view storage used for my Amazon Aurora MySQL DB cluster?

Temporary storage limits for Aurora MySQL