Alter on Big Table in RDS Solution to table full Error. Let’s take an example, Tables which 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 query on one of the tables it gave “ERROR 1114 (HY000): table full” error…
Why this issue occurred even though Amazon Aurora storage volume will automatically grow up to 64TB.?
There are 2 types of storage in Aurora. Instance store which 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 instance type. This result to have some downtime while 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.
Note: 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, 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 same RDS instance type. Also, try to add heavy write load on the table to mimic the traffic. To achieve this, create a bash script which 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.
NOTE: Before running this command, we need to set log_bin_trust_function_creators to 1 in RDS DB parameter group. After you’re done with the ALTER process, you can change the variable to “0” again.
If you are altering the table with pt-online-schema-change command on a table which has the 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 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 for 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 of this user. rdsadmin MySQL user is used by Amazon for maintenance and management work.