I am trying to insert huge (around 4m) records in aurora-mysql thru a Java program. While running the program on a test env with RDS-Mysql it takes around 15sec (at most), however while running it against aurora-mysql it take 15minutes (on average), which is sucking the daylight out of me.
this is what I have tried
- set
autocommit offwhile creating a connection. I commit every 100K records, I have tried changing the count to 500K but not much change in performance. useServerPrepStmts=false&rewriteBatchedStatements=true, this helped a lot for sure, the 15m average time is after making this change, before this I had to wait for eternity :(-XX:+UseConcMarkSweepGC- I just wanted to make sure the GC is not having any impact on the perforamance and to avoid any heap error- changed the JVM to JDK11 (earlier it was Java8)
BTW,
autocommit is true on aurora-mysql by default.
the code is running on M5-2xLarge ec2 instance
I am totally at my wits end as to why its taking 15m with aurora-mysql and only 15sec with rds-mysql and if anything else I can try to make it better.
any help / pointer will be helpful