Question:
I have a question regarding the insert of rows in RDS.
I am inserting in that example 301119 records in 1 table.
Here my log on those inserts batch :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
Amazon : 2014-09-05 12:12:47,245 - Processing 30119 users 2014-09-05 12:15:01,508 - 5000 users updated in transaction 2014-09-05 12:17:29,672 - 10000 users updated in transaction 2014-09-05 12:19:30,499 - 15000 users updated in transaction 2014-09-05 12:21:38,866 - 20000 users updated in transaction 2014-09-05 12:23:26,659 - 25000 users updated in transaction 2014-09-05 12:25:16,727 - 30000 users updated in transaction 2014-09-05 12:25:19,376 - 30119 users updated in transaction - DONE 2014-09-05 12:25:19,379 - Process completed in **752134** ms Local : 2014-09-05 12:33:20,767 - Processing 30119 users 2014-09-05 12:33:39,692 - 5000 users updated in transaction 2014-09-05 12:33:50,949 - 10000 users updated in transaction 2014-09-05 12:33:59,476 - 15000 users updated in transaction 2014-09-05 12:34:06,962 - 20000 users updated in transaction 2014-09-05 12:34:14,874 - 25000 users updated in transaction 2014-09-05 12:34:22,945 - 30000 users updated in transaction 2014-09-05 12:34:23,111 - 30119 users updated in transaction - DONE 2014-09-05 12:34:23,112 - Process completed in **62345** ms |
The query is a straightforward batch insert in 1 table.
My local is not very fast and those results are quite weird … it takes 12 times more to run the same inserts in AWS … So, there is something wrong … It that a configuration problem or other ?
Some pointers would be very welcome 🙂 🙂
Thank you !
Answer:
So, I figured it out ! After searching and tweaking the configuration, we were around 300 Inserts per Seconds, we disabled the innodb_flush_log_at_trx_commit (1 -> 0) and the sync_binlog (1 -> 0), and we went up to 1500-2000 Inserts per Second !!
Since we are ok losing the last transaction / commit if the db crash so we went with and it is now blazing fast !!
Hopes it helps some of other people that are having the same issue !