1

I use Amazon RDS as mysql db backend for highload project and try to optimize insert performance for innodb tables.
According to information from different sources - good candidate to speed up inserts is innodb_flush_log_at_trx_commit parameter.
My local tests show significant boost when I set it to 2 (or 0), but changing this param on RDS has no effect.
What may be a problem?
I tried all available values and results are the same (30-40 inserts per second into empty table) while local tests show ~9000 inserts per second.
I also tried M3.large General Purpose SSD RDS test instance (0 load) with same result.

1

Complain to RDS.

Meanwhile...

Plan A: Design code to gather the rows up and do a multi-row INSERT or LOAD DATA.

Plan B: Put BEGIN and COMMIT around small batches of rows. (Suggest 1 second or 100 rows or 1MB, whichever comes first.)

| improve this answer | |
  • Only 100 rows? For a big import we did on a live server the 100k value seemed to give the most performance. Sure the table itself was otherwise inactive, the server quite idle and the rows short.. But 1000x less sounds very conservative. – jkavalik Jan 7 '16 at 6:17
  • Yeah, 100 may be too conservative. However, large numbers lead to slowdowns because of the undo log and/or the replication delays. – Rick James Jan 7 '16 at 6:49
  • Thanks. I usually suggest to test it with 1000 when asked but I would not want to give bad advices. – jkavalik Jan 7 '16 at 7:10
  • 100K rows is likely to blow out the undo log and cause replication stalls. 100KB for batch insert size is arguably unnecessarily small (think packet, buffer, etc sizes). – Rick James Jan 7 '16 at 7:15
  • My experiments with batched INSERT show that 1000 rows runs at about 99% of the theoretical max speed. Going beyond that is "diminishing returns" (and could lead to degradation, as I mentioned.) My conservative "100" is about 90% of max; I think of it as a "safe" tradeoff. – Rick James Jan 7 '16 at 7:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.