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.
Mysql – RDS innodb_flush_log_at_trx_commit problem
amazon-rdsawsinnodbMySQL
Related Question
- Mysql RDS migration problem
- Mysql – innodb_thread_concurrency seems evil as it enlarge table size by big factor
- Mysql – What can cause a rapid drop in RDS MySQL Database free storage space
- Mysql – Google Cloud SQL Replication Delay
- Mysql – Innodb: after 48 hours of optimizing 10mb/sec write speed
- Mysql – limiting MySQL to write max. 10 Inserts per second
- Mysql – Estimate query speed for +1.5 billion rows thesql table
Best Answer
Complain to RDS.
Meanwhile...
Plan A: Design code to gather the rows up and do a multi-row
INSERTorLOAD DATA.Plan B: Put
BEGINandCOMMITaround small batches of rows. (Suggest 1 second or 100 rows or 1MB, whichever comes first.)