Mysql Database performance issue

Much longer insertion time in MyISAM tables

Switch all MyISAM tables to InnoDB. MyISAM uses FULL TABLE LOCKING for all inserts/updates/deletes. There is no concurrency. Switch to InnoDB for row-level locking and better concurrency/parallelism.

What settings would allow us to reduce the number of deadlocks?

Change your transaction isolation level to READ-COMMITTED. The default, REPEATABLE READ, requires extra locks to be ACID compliant. Also, use of FOREIGN KEYS will drastically increase deadlocks. Remove FKs as much as possible.

Schema performance detailed stats

I highly recommend you spin up a PMM instance in AWS. We have an official AMI for this. You will get all of these statistics and 100s more by using PMM vs what AWS provides.

1 Like