INSERT is too slow on RDS
Closed this issue · 1 comments
On xxxx-rds-test-1, which is a db.t3.medium, INSERT 12,000 rows (amounting to 2 MB of data) to the xxx table, takes 12 minutes. I believe this is because of the indexes.
Also, in general we want to improve the reading speeds as well, especially of key tables.
At the very least it will need to be as fast as on the legacy mariadb server.
It turns out RDS already sets the innodb_buffer_pool_size to 70% of the available instance RAM, so it's not obvious this that's the problem.
However: it's almost hard to believe, but by using LOAD DATA LOCAL INFILE instead of INSERT, inserting the data now takes less than one second. This might be my best optimization yet. (720 s -> 1 s).
I'm not sure if it's recalculating the indexes properly, though. I tried SHOW INDEX FROM xxx and ANALYZE TABLE xxx and a few queries and everything seems normal, though.