This article will provide an insight into the local storage of each instance of the Aurora cluster.
Aurora instances have two types of storage:
1. Storage for persistent data, it is a cluster level and it increases automatically when more space is required.
2. Local storage based on the instance class. This storage can be changed only by moving to a larger DB instance class. Aurora uses local storage for storing error logs, non-innoDB temporary files, general logs, and slow logs.
Temporary Storage limits for Aurora MySQL
These values represent the theoretical maximum amount of free storage on each DB instance. The actual local storage available to you might be lower. Aurora uses some local storage for its management processes
For what is it used?
Aurora for MySQL uses local storage for storing
- error logs,
- general logs,
- slow query logs,
- audit logs, and
- non-InnoDB temporary tables.
Error and slow logs are listed in the instance log section, are they supposed to be in CW logs only?
AWS provides RDS as a service besides it is not possible to access to the files that reside on the local storage.
MySQL Extent size
* InnoDB page size (default 16KB). Many values are counted in pages; the page size enables them to be easily converted to bytes.
* EXTENT_SIZE
For InnoDB: Extent size is 1048576 (1MB) for files with a 4KB, 8KB, or 16KB page size.
FILES does not report InnoDB page size. Page size is defined by the innodb_page_size system variable. Extent size information can also be retrieved from the INNODB_SYS_TABLESPACES table where FILES.FILE_ID = INNODB_SYS_TABLESPACES.SPACE.
basket-2-prod>select * from information_schema.INNODB_SYS_TABLESPACES where SPACE=7;
+-------+------------------------+------+-------------+----------------------+-----------
| SPACE | NAME | FLAG | FILE_FORMAT | ROW_FORMAT | PAGE_SIZE
+-------+------------------------+------+-------------+----------------------+-----------
| 7 | basket_service/baskets | 0 | Antelope | Compact or Redundant | 16384
+-------+------------------------+------+-------------+----------------------+-----------
Monitoring & Tuning the instance
You can determinate how much free memory is available by monitoring the FreeableMemory metric
The parameters max_heap_table_size and tmp_table_size can be increased. 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.
Note: If you increase the values for max_heap_table_size and tmp_table_size, then the tables consume more memory on the instance.
Workarounds
Low free storage capacity error
Local storage can reach capacity when you store no-persistent data, such as temporary tables and log files.
MySQL commands that creates temporary files can be the cause of this error
Login on your MySQL server and check if there are ALTER TABLE commands running or if long queries are writing temporary tables.
Run multiple times this command to find how many created tmp tables are created.
SHOW GLOBAL STATUS LIKE 'created_tmp%tables';
MySQL commands that creates temporary files can be the cause of this error
Login on your MySQL server and check if there are ALTER TABLE commands running or if long queries are writing temporary tables.
Run multiple times this command to find how many created tmp tables are created.
SHOW GLOBAL STATUS LIKE 'created_tmp%tables';
Error 1114 table full
When MySQL performs ALTER TABLE, it creates an immediate temporary table with the new schema. It loads data into the intermediate table before deleting the old table. If the intermediate table requires more storage than is available, MySQL raises the following error:
ERROR 1114 (HY000): the table 'table-name' is full;
To resolve this error, the only solution is to increase local storage by scaling up the instance