How to import bulk data to Amazon RDS for MySQL with best approaches
Tags: aws, mysql
Published: 2022-12-12 19:03:39

<h2><span data-preserver-spaces="true">Optimized loading or importing of Data</span></h2>
<p><span data-preserver-spaces="true">If your database does not have automated backups enabled, you can still take regular snapshots of your entire database. For this purpose, Set the backup retention period to a positive nonzero value. Once you do so, you can create an automatic backup by taking your database offline. A backup is immediately created whenever the backup retention period changes from zero to a nonzero value or vice versa. A manual backup is performed using the AWS CLI, or the backup is scheduled using a cron job. Manual and cron-scheduled backups are not subject to the backup retention period. If you want to see more details, check out our latest article on </span><strong><em><span data-preserver-spaces="true">backup restoration in Amazon RDS for MySQL.</span></em></strong></p>
<h2><span data-preserver-spaces="true">Storage Optimization </span></h2>
<p><span data-preserver-spaces="true">Use Amazon Cloudwatch for monitoring :</span></p>
<p><span data-preserver-spaces="true">You can use the FreeStorageSpace Aws CloudWatch metric to monitor the amount of space available to an RDS DB instance. Still, that metric doesn't describe how the DB instance consumes storage.</span></p>
<h2><span data-preserver-spaces="true">Reduce/disable general log and slow query log storage</span></h2>
<p><span data-preserver-spaces="true">Enabling the general log and slow query log parameters can cause your DB instance to start storing these logs and the backups of these logs. To control disk space usage, set rds.log_rotate_on_compress.To rotate these logs and manage disk space, visit MySQL database directories of </span><a class="editor-rtfLink" href="https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/mysql_rds_rotate_general_log.html" target="_blank" rel="noopener"><span data-preserver-spaces="true">general log rotation</span></a><span data-preserver-spaces="true"> and </span><a class="editor-rtfLink" href="https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/mysql_rds_rotate_slow_log.html" target="_blank" rel="noopener"><span data-preserver-spaces="true">slow log rotation</span></a><span data-preserver-spaces="true"> to see which log files can be rotated.</span></p>
<p><span data-preserver-spaces="true">For detailed storage optimization tips, you can visit </span><a class="editor-rtfLink" href="https://aws.amazon.com/premiumsupport/knowledge-center/rds-stuck-in-storage-optimization/" target="_blank" rel="noopener"><span data-preserver-spaces="true">here.</span></a></p>
<h2><span data-preserver-spaces="true">Modifications of Database Parameters</span></h2>
<p><span data-preserver-spaces="true">You can adjust the database parameters to improve performance while loading data into an RDS for MySQL instance. Several of these parameters can be changed via parameter groups, and several can be modified at the session level.</span></p>
<p><span data-preserver-spaces="true">After the data import activity is complete, undo the changes you made to the following parameters. The suggested values might not be appropriate when no ongoing data import activity exists. </span></p>
<p><span data-preserver-spaces="true">You can only modify RDS DB instance parameters if you have access to the parameter group in which they are stored. To change any parameter value, you must first look at the parameter group that stores those parameters and then change the parameter value in the appropriate field.</span></p>
<h3><span data-preserver-spaces="true">Step 1:</span></h3>
<p><span data-preserver-spaces="true">A DB parameter group is created by using the ParameterGroup DB object. You use this object to </span><strong><span data-preserver-spaces="true">create a parameter group in your database. </span></strong></p>
<h3><span data-preserver-spaces="true">Step 2 :</span></h3>
<p><span data-preserver-spaces="true">You then</span><strong><span data-preserver-spaces="true"> view the values of the parameters </span></strong><span data-preserver-spaces="true">in a parameter group to ensure that the Is Modifiable property is set to True. </span></p>
<h3><span data-preserver-spaces="true">Step 3 :</span></h3>
<p><span data-preserver-spaces="true">Finally, you </span><strong><span data-preserver-spaces="true">modify the parameter values</span></strong><span data-preserver-spaces="true"> in the parameter group.</span></p>
<p><span data-preserver-spaces="true">You have to keep in mind the following configurations as well :</span></p>
<h4><strong><span data-preserver-spaces="true">Batch Commits :</span></strong></h4>
<p><span data-preserver-spaces="true">All user activity in InnoDB takes place within a transaction. This means that each SQL statement executed is treated as a separate transaction. In MySQL, all transactions are automatically committed if they do not generate any errors, which may cause a delay during the import operation. To speed up the import operation, it is recommended to set autocommit=1 and use binary log switching. This will ensure that the database server does not wait for the end of the current transaction before processing the next one. You can study batch execute statements </span><a class="editor-rtfLink" href="https://docs.aws.amazon.com/rdsdataservice/latest/APIReference/API_BatchExecuteStatement.html" target="_blank" rel="noopener"><span data-preserver-spaces="true">here.</span></a></p>
<p><span data-preserver-spaces="true">For inserting more than one row in a table, use INSERT statements. This way, you can insert several rows of data at once. You need only supply the information to insert in the appropriate columns of the rows being inserted. Here is an example syntax :</span></p>

<!-- wp:code -->
<pre class="wp-block-code"><code>INSERT INTO &lt;tablename&gt; VALUES (value1, value2), (value3, value4),…..;</code></pre>
<!-- /wp:code -->

<h4><strong><span data-preserver-spaces="true">Batching flat files with LOAD DATA</span></strong></h4>
<p><span data-preserver-spaces="true">LOAD DATA allows you to load flat files into the database. Batch loading of data from files is possible. Local files are helpful when working on a particular table in the database. These flat files are created per table, which directly helps in parallel data loading to the database. For further details on load data files, you can visit </span><a class="editor-rtfLink" href="https://dev.mysql.com/doc/refman/8.0/en/load-data.html" target="_blank" rel="noopener"><span data-preserver-spaces="true">here.</span></a></p>
<h4><strong><span data-preserver-spaces="true">Disable autocommit</span></strong><span data-preserver-spaces="true"> </span></h4>
<p><span data-preserver-spaces="true">Whenever you change the value for auto-commit by making changes in the custom parameter group that associates it with the RDS instance, these changes occur at a global level. You can instead change this value at the session level. You can surround the SQL import statements with the following code:</span></p>
<p><strong>SET autocommit=0; </strong></p>
<p><strong>... SQL import statements ... </strong></p>
<p><strong>SET autocommit=1;</strong></p>
<p><span data-preserver-spaces="true">In all these scenarios, the number of commits remains unaffected. Even though this can be beneficial, avoiding making transactions too big is essential. If the transaction size exceeds 100 MB, you must split it into 250 MB chunks and load them sequentially. This will allow you to minimize the rollback time if there is any issue in any chunks.</span></p>
<h2><span data-preserver-spaces="true">Increase the size of innodb_log_file_size</span></h2>
<p><a class="editor-rtfLink" href="https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_log_file_size" target="_blank" rel="noopener"><span data-preserver-spaces="true">InnoDB log_file_size </span></a><span data-preserver-spaces="true">defines the checkpointing age. Large redo logs cause a lot of disk writes. Therefore, increasing the size of the log file leads to less disk I/O.</span></p>
<p><span data-preserver-spaces="true">The default value of innodb_log_file_size for Amazon RDS for MySQL is 128 MiB.</span></p>
<p><span data-preserver-spaces="true">The maximum log file size should be equal to about 524,288 bytes for your server. This value is computed from a constant value expressed in GB, multiplied by the product of innodb_log_file_size and innodb_log_files_in_group. More than that, the combined size of log files can't exceed 512 GB. If it reaches that value, you have run out of space on the redo log. Consequently, it can't smooth out peaks and troughs in workload activity, which often means there is not enough redo log space to handle more than an hour of write activity. Therefore, starting with a relatively large log file is advisable to ensure a smooth and successful server operation. </span></p>
<p><span data-preserver-spaces="true">For parameter modification, you can visit the</span><a class="editor-rtfLink" href="https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithParamGroups.html#USER_WorkingWithParamGroups.Creating" target="_blank" rel="noopener"><span data-preserver-spaces="true"> detailed guide on Parameter configuration</span></a><span data-preserver-spaces="true">.</span></p>
<h2><span data-preserver-spaces="true">Increase the size of innodb_log_buffer_size</span></h2>
<p><span data-preserver-spaces="true">While importing data to the database, the transaction inserts many rows. If you set the log buffer size to a higher value, then it decreases the number of writes to the logs. This, in turn, decreases the log I/O and helps improve the data import performance.</span></p>
<p><span data-preserver-spaces="true">Innodb_log_buffer_size is a static parameter, so a reboot is required to modify the value. You can configure the value of this parameter using the custom parameter group associated with the RDS instance. Doing this allows you to change the parameter's value without requiring a reboot. Doing this helps keep the write IOPS from becoming a bottleneck and improves the data import performance.</span></p>
<p><span data-preserver-spaces="true">You can find the elaborated article on this site for </span><a class="editor-rtfLink" href="https://aws.amazon.com/blogs/database/improve-performance-of-your-bulk-data-import-to-amazon-rds-for-mysql/" target="_blank" rel="noopener"><span data-preserver-spaces="true">best practices to import data on amazon RDS for MySQL.</span></a></p>