MyISAM and innodb engine - David Yin's Blog

I have a VM that hosts a few websites there. Today, I ran the mysqltuner.pl script to check the performance and see if there is any recommondation.

-------- Recommendations -------------------------------------------------------                                                                                                                                                             --------------------
General recommendations:
    Configure your accounts with ip or subnets only, then update your configurat                                                                                                                                                             ion with skip-name-resolve=ON
    Performance schema should be activated for better diagnostics
    MyISAM engine is deprecated, consider migrating to InnoDB
    Be careful, increasing innodb_log_file_size / innodb_log_files_in_group mean                                                                                                                                                             s higher crash recovery mean time
Variables to adjust:
    skip-name-resolve=ON
    table_definition_cache (400) > 1126 or -1 (autosizing if supported)
    performance_schema=ON
    key_buffer_size (~ 25M)
    innodb_buffer_pool_size (>= 770.7M) if possible.
    innodb_log_file_size should be (=32M) if possible, so InnoDB total log file                                                                                                                                                              size equals 25% of buffer pool size.
    innodb_log_buffer_size (> 16M)

I know I have a database with over 900M data. There are 67 tables in the MyISAM engine. It was created about 10 years ago. Still use MyISAM. All other databases are powered by the innodb database engine.

I want to know the main difference between MyISAM and innodb.

The two most commonly used MySQL storage engines are MyISAM and InnoDB. The key differences, especially in terms of performance, are:

1. Transaction Support

MyISAM: Does not support transactions.
InnoDB: Supports ACID-compliant transactions, ensuring data integrity with rollback and commit features.

2. Concurrency & Locking

MyISAM: Uses table-level locking, meaning if one query modifies a row, the entire table is locked, reducing concurrency.
InnoDB: Uses row-level locking, allowing multiple queries to modify different rows simultaneously, which significantly improves concurrency.

3. Performance

Read-Heavy Workloads: MyISAM is generally faster than InnoDB for simple SELECT queries because of its lightweight design and table-level caching.
Write-Heavy Workloads: InnoDB performs better for INSERT, UPDATE, DELETE operations because of row-level locking and support for transactions.
Indexes: MyISAM has full-text search indexing (before MySQL 5.6), making it faster for text-heavy searches, while InnoDB requires additional indexing work.
Caching: InnoDB uses the InnoDB Buffer Pool for caching both indexes and data, while MyISAM only caches indexes.

4. Data Integrity & Crash Recovery

MyISAM: Lacks crash recovery and can easily get corrupted after a failure.
InnoDB: Uses Redo logs, Undo logs, and WAL (Write-Ahead Logging) for crash recovery, making it more reliable.

5. Foreign Keys & Data Integrity

MyISAM: Does not support foreign keys or referential integrity.
InnoDB: Supports foreign keys with cascading ON DELETE/UPDATE actions.

The mysqltuner.pl report said that MyISAM engine is deprecated.

MyISAM Deprecation Status:

Starting from MySQL 5.5 (2010) → InnoDB became the default storage engine.
MySQL 8.0 (2018) → MyISAM is deprecated, meaning it’s still available but not actively developed, and some features may be removed in future versions.

Why is MyISAM Deprecated?

Lack of Transactions – Modern databases need ACID compliance, which MyISAM lacks.
Poor Crash Recovery – MyISAM tables can easily get corrupted after a crash, requiring manual repair.
Table-Level Locking – This severely limits performance in high-concurrency environments.
No Foreign Keys – InnoDB supports referential integrity, which is essential for relational databases.
MySQL Development Focus – MySQL has shifted entirely toward InnoDB, with performance optimizations and new features being developed only for it.

So the question is how to migrate the MyISAM tables to the InnoDB tables.

ALTER TABLE your_table ENGINE=InnoDB;

I will convert all 67 tables to innodb engine. And wait a few days to run mysqltuner.pl again.

It took me about two hours to convert the MyISAM tables to InnoDB. There are two big tables. One is over 500MB. I did three steps: Alter, Optimization, Analyze. To make sure they are good to go.