Mysql Insert/Update is so Slow

Question

I have a laravel application which must insert/update thousands of records per second in a for loop. my problem is that my Database insert/update rate is 100-150 writes per second . I have increased the amount of RAM dedicated to my database but got no luck.

is there any way to increase the write rate for mysql to thousands of records per second ? please provide me configurations.

My storage engine is InnoDB

Answer 1

Innodb storage is care about ACID transaction it will make sure your data(insert/update) commit to disk that why take the time:

1. Considering about the columns which frequently change(insert/update), you should not indexing on that column.
2. if you always need big data of DML operation try to avoid auto-commit=0.but it not good if server crush or some fallen.
3. turning your query if your DML operation bases on subquery:

Example:

UPDATE TEST SET NAME='XXXX' WHERE (subquery).

Answer 2

• Batch INSERTs -- That is, do one INSERT with up to 100 rows.
• Group into transactions -- BEGIN; do several things; COMMIT;
• Change innodb_flush_log_at_trx_commit=2 (see documentation for discussion of speed versus security tradeoff.)

If you need further discussion, show us SHOW CREATE TABLE and some of the queries involved.