MySQL Bulk Insert taking long time

Vishal Anand — Tue, 09 Jun 2020 13:51:39 +0000

I'm using python, using MySQL connector. I'm having nearly 67 Million (14GB) entries in a table. When I do a bulk insert of 2K data each time, it is taking very long to insert.

Inserted 2000 rows in 23 Seconds
Inserted 2000 rows in 25 Seconds
Inserted 2000 rows in 29 Seconds
Inserted 2000 rows in 28 Seconds
For another table (having less data), insertion speed is fine(2-4 seconds).

After using the transaction:

Inserted 2000 rows in 21 Seconds
Inserted 2000 rows in 20 Seconds
Inserted 2000 rows in 20 Seconds
Inserted 2000 rows in 18 Seconds
How can I improve the speed?

I'm using AWS RDS, Aurora MySQL version 5.7.12 (db.t3.medium) having CPU usage 4% to 8%. My objective is to insert around 50K data into a table. This table is currently having nearly 67 Million (14GB) data already. Data must need to be inserted ASAP. This almost real-time data is very important for the client. The table is having 18 columns: id(PK auto-increment), customer, serial_number, batch, data, and some others. Indexes are on (customer,serial_number) - To make the combination unique, batch - For searching, data(unique). All are by default BTREE indexed. This insertion should need to take less than 1 minute for 50K. But currently taking around 15 minutes. I've tried inserting on an empty table. It is inserting 50K data just in 5-7 seconds. As you increase the number of entries in the table, the insertion process time is increasing.

Is upgrading MySQL version is going to speed-up the insertion process anyhow? Is it the last option to split or partitioning the table? I cannot consolidate the data because each data is important, specially the last 2 years of data. Please help.

My table schema is already having some default values in 8 columns and these data are never going to update later. There are not many Read/Write operations are going on. Almost 2 or in some cases 3 selects per second as per RDS monitor shows.

How to write cron jobs on AWS EC2 instance

Vishal Anand — Thu, 16 Apr 2020 07:22:30 +0000

Cron is a time-based job scheduler run on the server in the Unix-like operating system.
To perform such action we write cron expression made of five fields, followed by a shell command to execute.

First, we have to log in to AWS EC2 instance using CLI (Command Line Interface).
For log in, type:
ssh -i <.pem file> username@ip_address
After successfully log in, type:
crontab -e
then select any editor from the list (preferred nano).
Type cron command. For example:

* * * * * php /var/www/html/file/name.php Now save the changes. If you are using nano editor type Ctrl + x and Select Y and then press Enter. To list out all the running cron type: crontab -l
is used for every minute, every hour, etc. Here ”php” is shell command followed by file location of php script, separated by white space.

If you want to run nodejs script then type:

* * * * * node /var/www/html/nodejs/file/name.js Each from five stars represents a job:

How to write CRON jobs on AWS EC2 instance

Allowed special characters are:

Comma (,) – Commas are used to separate the item list.

For example:

Cron to run at minute 1 and 10 every hour
1,10 * * * * php /var/www/html/file/name.php
Cron to run at mid-night and mid-day
0 0,12 * * * php /var/www/html/file/name.php
Hyphen (-) – Hyphen defines range.

For example:

Cron to run at every minute from 1 minute through 10 minute, every hour
0-10 * * * * php /var/www/html/file/name.php
Cron to run at minute 0 past every hour from 6 AM through 6 PM
0 6-18 * * * php /var/www/html/file/name.php
Slash (/) – Slash defines step values.

For example:

Cron to run every 5th minute
*/5 * * * * php /var/www/html/file/name.php
Cron to run at minute 0 past every 6th hour
0 */6 * * * php /var/www/html/file/name.php
Sleep cron:

For example:

Cron to run every minute after sleep of 30 seconds

* * * * * sleep 30; php /var/www/html/file/name.php

For more examples visit: https://coderssecret.com/how-to-write-cron-jobs-on-aws-ec2-instance/

DEV Community: Vishal Anand

MySQL Bulk Insert taking long time

How to write cron jobs on AWS EC2 instance