0

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?

ja0
  • 37
  • 4

1 Answers1

0

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

Valeriu Ciuca
  • 2,084
  • 11
  • 14
  • My php has a diskcache system saving in *.cache files. There is nothing to cache from this table. – ja0 Nov 09 '22 at 16:58
  • It's ok if your server is using SSDs. But I recommend switching to a memory caching system like Redis if you can, to minimize I/O bottlenecks. – Valeriu Ciuca Nov 09 '22 at 16:59
  • The disk is HDD. The server is quite old. – ja0 Nov 09 '22 at 17:02
  • You should upgrade/use a memory caching system. I updated the answer about indexes on columns. – Valeriu Ciuca Nov 09 '22 at 17:08
  • Additional DB information request, please. RAM size, # cores? Post TEXT data on justpaste.it and share the links. From your SSH login root, Text results of: A) SELECT COUNT(*), sum(data_length), sum(index_length), sum(data_free) FROM information_schema.tables; B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) STATUS; not SHOW STATUS, just STATUS; G) SHOW ENGINE INNODB STATUS; for server workload tuning analysis to provide suggestions. – Wilson Hauck Nov 09 '22 at 19:40
  • 1vCORE, 2GB ram. – ja0 Nov 09 '22 at 19:59
  • @JimAntonopoulos Please consider posting the additional information requested on 2022-11-09 about 19:40. Your system needs to be tuned for MINIMAL available ram and core's. Will provide suggestions after analysis of your data. – Wilson Hauck Nov 12 '22 at 16:25