What is stored in the local storage of Amazon Aurora for MySQL, and how can I troubleshoot storage capacity issues?
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.
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 non-persistent data, such as temporary table and log files, are stored 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 similar to the following multiple times to see the number of temporary tables on your instance:
SHOW GLOBAL STATUS LIKE 'created_tmp%tables';
To monitor the number of temp tables created during specific time range, you can enable GoSH. This allows you to store the server status variables in tables that you can review later for future analysis.
Review the Amazon CloudWatch metric for FreeableMemory to determine how much free memory is available. Then, check to see if your space is consumed by audit logs, general logs, or slow query logs. You can monitor the local storage space associated with the Aurora instance by using the CloudWatch metric for FreeLocalStorage. For more information, see Monitoring Amazon Aurora DB Cluster Metrics.
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 Changing Your DB Instance Class.
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's used. You can modify these parameters in the parameter group that's attached to your instance. For more information, see Modifying Parameters in a DB Parameter Group.
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. For more information, see Using Metrics to Identify Performance Issues.
ERROR 1114 table full
When MySQL performs operations, such as ALTER TABLE, an intermediate temporary table is created 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, you can 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 Changing Your DB Instance Class.