Mysql Insert/Update is so Slow

MySQLperformanceperformance-tuningquery-performance

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.
enter image description here

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

Best Answer

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).

Related Question