How to speed up INNODB queries comparable to MYISAM metrics?

Question

I recently switched my database tables from MYISAM to INNODB and experienced unsuccessful timeouts with queries, mostly inserts. One of the functions that I used earlier takes less than 2 seconds to insert, delete and update a large collection of records in ~ 30 MYISAM tables, but now that they are INNODB, this function calls the PHP timeout.

The timeout was set to 60 seconds. I optimized my script enough that now, although there are still a lot of requests, they are combined together (several inserts, several deletions, etc.), and the script now takes ~ 25 seconds, which is a significant increase from the fact that at least 60 seconds.

This duration is still over 10 times faster than before using MYISAM, are there any errors that I could make to the way these requests are handled? Or are there any tweaks that could help in performance? MySQL currently uses the default settings for installation.

Queries are nothing special, DELETE ... WHERE ...simple logic is the same with queries INSERTand UPDATE.

Answer 1

It's hard to say without knowing too much about your environment, but it could be a problem with setting up your database. InnoDB can be VERY slow on budget equipment, where each entry creates a real flash. (This affects the write, not the read.)

For example, you can familiarize yourself with such parameters as:

innodb_flush_log_at_trx_commit=2
sync_binlog=0

, , .

- , , - .

Answer 2

explain . , select foo from bar;, explain select foo from bar;.

. , .

Answer 3

Innodb builds hash indexes that help speed up index searches by passing the BTREE index and using a hash that is faster