Amazon RDS is a popular cloud-based relational database service that allows users to easily deploy, manage, and scale their databases on the cloud. RDS supports a variety of database engines, including MySQL, PostgreSQL, Oracle, and SQL Server. One common issue that users face when working with RDS is the “table full” error. This error occurs when a table in the RDS instance has reached its maximum storage capacity and can no longer accommodate new data. In this blog, we will discuss the causes of the table full error and how to resolve it by altering the big table in the RDS solution.
What causes the table full error?
The table full error occurs when a table in an RDS instance has reached its maximum storage capacity. There are several reasons why a table may reach its maximum storage capacity, including:
- Data growth: As data is added to the table, it occupies more storage space. If the rate of data growth exceeds the rate at which storage capacity is added to the RDS instance, the table may fill up.
- Poor database design: Poor database design can lead to inefficient use of storage space, resulting in tables reaching their maximum storage capacity faster than expected.
- Lack of maintenance: Failure to perform routine maintenance tasks, such as purging old data, can lead to tables filling up faster than expected.
- Inappropriate table schema: Inappropriate table schema can cause the table to become inefficient and consume more storage space than necessary.
Alter on Big Table in RDS Solution to table full Error. Let’s take an example, Tables that are very big in size (~> 100GB to 600GB). Doing alter on such big tables is HIGH memory and CPU-intensive task.
when you try to alter the query on one of the tables it gave an “ERROR 1114 (HY000): table full” error…
Why this issue occurred even though Amazon Aurora storage volume will automatically grow up to 64TB.?
First, we will understand the storage in RDS Aurora.
There are 2 types of storage in Aurora. Instance store is local storage where temporary objects are stored and the main storage for data. Therefore, when you run ALTER on a table, it will generate a temp table and RDS Aurora would use instance storage to store the temp table.
For your instance which is db.r3.large instance, it has 1×32 GB of local storage so if your temp objects on the instance get to be larger than this size, you get the “table full’ error. Also, the local storage limit is different from the total storage volume available for your Aurora instance, and based on your database usage, your Amazon Aurora storage volume will automatically grow, up to 64 TB, in 10GB increments.
Alter on big table in RDS solution to table full Error
- To overcome the issue, you can scale the instance up to get more local storage to run your ALTER, alter the table then scale down the instance type. This results in having some downtime while the upgrade/downgrade instance type.
- You can also use: “pt-online-schema-change” command, if you use this command it makes the original table still available to use with no downtime or no table lock.
If you couldn’t afford to have any downtime in the system, then use pt-online-schema-change command instead of scaling the instance.
However, the documentation of pt-online-schema-change says, we should take a backup before running this command. Hence to avoid any table locks and failures during production table change, you can test this command on an exact copy of the application database, with the same RDS instance type. Also, try to add a heavy write load on the table to mimic the traffic. To achieve this, create a bash script that continuously inserts a new row into the table.
To have a quick exact copy of your current DB take a snapshot of RDS DB and restore it from snapshot for testing.
Before running this command, we need to set log_bin_trust_function_creators to 1 in the RDS DB parameter group. After you’re done with the ALTER process, you can change the variable to “0” again.
Results:
If you are altering the table with pt-online-schema-change command on a table that has a size 35-40GB then it may take near about 30hrs.
Why to use pt-online-schema-change command and why to enable “log_bin_trust_function_creators “ in DB parameter group??
pt-online-schema-change does not lock the table. This command creates triggers on the original table. But for this, it needs superuser privileges.
when you are using the store procedure you will get the error:
#1419 – You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable
Reason: This error occurs on RDS instances when you try to use “Stored procedures”. You will soon find out that grant the super privilege to a user won’t work. So the only way to make things work is to set log_bin_trust_function_creators to 1.
As per Percona documents:
The bottom line is creating triggers on a server with binary logs enabled requires a user with SUPER privileges (which is impossible in Amazon RDS). The error message specifies the workaround. We need to enable the variable in DB parameter group “ log_bin_trust_function_creators”. Enabling it is like saying to the server:
“I trust regular users’ triggers and functions, and that they won’t cause problems, so allow my users to create them.”
Since the database functionality won’t change, it becomes a matter of trusting your users. log_bin_trust_function_creators is a global variable that can be changed dynamically.
Trying to find more details on “Super_priv”, When you check the permissions of the users in the user table of MySQL database. you could see that the Super_priv is not set for anyone except the rdsadmin user.
MySQL> select User,Super_priv from user;
+-----------+------------+
| User | Super_priv |
+-----------+------------+
| rdsadmin | Y |
| root | N |
| dbuser | N |
+-----------+------------+
3 rows in set (0.00 sec)
Here “root” is the Master user and “dbuser” is the DB user these users are created by us and “rdsadmin” user is automatically created by AWS which we don’t have access to this user. rdsadmin MySQL user is used by Amazon for maintenance and management work.
This is the end of the tutorial, on how to Alter on Big Table in RDS Solution to table full Error.
Check out other articles
How to Enable Slow Query Logs in AWS RDS MySQL
RDS Mysql ERROR max-prepared-stmt-count exceeded
