MySQL high load errors

Question

The server runs php 5.5.38 with apache2.4 and MySQL 5.5.62 there are about 5k-10k read write per minute.

And the table of main use was MyISAM and started crashing, saying "in use" instead of stats. The attempts of repair failed. And finally changed it to InnoDB and stopped but what can I do to prevent the errors?

Answer 1

MyISAM is obsolete technology. InnoDB is ACID compliant and designed for crash recovery. This is the solution.

For example, the default engine in MySQL 8.0 is InnoDB: https://dev.mysql.com/doc/refman/8.0/en/storage-engine-setting.html

When you omit the ENGINE option, the default storage engine is used. The default engine is InnoDB in MySQL 8.0.

You should also implement some caching at the PHP level with Redis/Memcached/Files to prevent unnecessary reads.

Make sure that you have indexes on the columns used in join/where clauses.

Indexes are used to find rows with specific column values quickly. Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows. The larger the table, the more this costs

https://dev.mysql.com/doc/refman/8.0/en/mysql-indexes.html