MyISAM Tables keep Crashing

Hi everyone!

Hoping somebody here could help.

We have a VPS that’s using MyISAM as the default storage engine.
Most of the sites that are on there are Wordpress sites.

The Mysql component keeps restarting because tables are crashing.

It appears when they crash, they stay crashed, unless somebody manually repairs them
or the MySQL server restarts.

Is this something everybody experiences on hosts?

How do we get reports when our tables have crashed?

How do we manage them?

How do we get them automatically get repaired?

Why are they crashing?

Would you advise to switch to InnoDB instead?

What are the Pros?

What are the Cons?

Thanks!

First consideration is your hosting.

Whether you’re using MyISAM or InnoDB, repetitive crashes relate to unstable hosting.

Your first question then becomes, “Is this a hobby project or a money project?”

For Hobby sites, ignore the problem. For Money sites, move to fast stable hosting.

What I do for my clients, to maintain stable database system is…

  1. Use MariaDB rather than MySQL. It’s faster + more stable + recovers faster when problems occur.

  2. Run mysqltuner + mysql-tuning-primer, retuning database every day for first week of hosting, then once a week thereafter, till no tuning suggestions are presented.

  3. Track database error log + slow query log + /tmp usage (used when SELECTs exceed memory buffers) + resolve all problems as they arise.

Reasons for repeat table crashes include…

  1. Interrupted long queries, or updates which will show up in one of the database logs, if correct logging is enabled.

  2. Incorrect configuration/use of BINARY LOGs.

  3. /tmp filing up

  4. Database disk filing up

  5. Connections broken during database transaction

And the list goes on…

Only way to know for sure is have access to all system logs + database logs + have correct database logging enabled.

If this is a Money site, likely best you hire an expert to fix your site.

Also likely you’ll require changing hosting companies.