Converting MyISAM tables to InnoDB can significantly enhance database performance and reliability. Although MyISAM has its unique advantages, InnoDB offers incredible benefits that often make it the preferred choice for production environments.
Why is it Required?
MyISAM had been the default storage engine in MySQL prior to version 5.5. However, with the release of MySQL 5.5, InnoDB replaced MyISAM as the default storage engine. This shift occurred because InnoDB offers a host of features that make it indispensable for modern database applications.
| Features | MyISAM | InnoDB |
|---|---|---|
| Transaction Support | Not Support | InnoDB supports ACID-compliant transactions, ensuring data integrity |
| Foreign Key Constraints | Not Support | InnoDB supports foreign key constraints, ensuring that relationships between tables |
| Locking Mechanism | Table-level Locking: MyISAM locks entire tables during read/write operations, which can cause contention | Row-level Locking: InnoDB locks rows during transactions, allowing for higher concurrency |
| MVCC (Multi-Version Concurrency Control) | Not Support | Support:Provides consistent reads without locking |
| Clustered Indexes | Not Support | Support |
| Compressed data | Support :Compressed MyISAM tables are supported only when using the compressed row format. Tables using the compressed row format with MyISAM are read only. | Support |
| Caches | Index caching only | Data and index caching |
| Crash Recovery | Limited: MyISAM has basic crash recovery capabilities, mainly through repair operations that may result in data loss. | Robust: InnoDB uses transactional logs and automatic crash recovery to maintain data integrity and minimize data loss. |
So It is time to switch to InnoDB. We recently undertook this conversion process and encountered a few challenges. We want to share our experience and provide a detailed, step-by-step guide to help you navigate this task effectively.
Summary of Steps
- Backup Your Databases
- Identify MyISAM Tables
- Generate ALTER TABLE Statements
- Execute ALTER TABLE Statements
- Verify the Conversion
Step 1: Backup Your Databases
Before starting any major changes, it is crucial to back up your databases to ensure data safety. This step protects your data in case anything goes wrong during the conversion process.
Step 2: Identify MyISAM Tables
To identify the tables using the MyISAM engine, use the following query:
mysql> SELECT table_schema,table_name,ROUND((data_length + index_length) / 1024 / 1024, 2) AS table_size_mb FROM information_schema.tables WHERE engine = 'MyISAM'
AND table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys');This query provides the schema name, table name, and table size. Checking the table size is crucial as it impacts storage and the time taken to execute the ALTER statement. Ensure sufficient storage space is available and consider the impact on production before proceeding.
Step 3: Generate ALTER TABLE Statements
To convert the engine to InnoDB, you can use the following query:
mysql> ALTER TABLE your_schema.your_table ENGINE=InnoDB;Writing this query manually is manageable for a few tables, but if you need to convert hundreds of tables, it can be time-consuming. Use this query to generate the ALTER statements for all MyISAM tables:
mysql> SELECT CONCAT('ALTER TABLE ', table_schema, '.', table_name, ' ENGINE=InnoDB;') AS
alter_statement FROM information_schema.tables WHERE engine = 'MyISAM';Step 4: Execute the Statements
You can execute the statements one by one or use scripting, depending on what is more convenient for you.During our execution, everything was going smoothly until we encountered two types of errors:
mysql> ALTER TABLE your_schema.your_table ENGINE=InnoDB;
ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes
mysql> ALTER TABLE your_schema.your_table ENGINE=InnoDB;
ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In the current row format, BLOB prefix of 768 bytes is stored inline.Let’s see how we fixed these errors. First, we need to understand the errors and their causes.
Error 1: Index Column Size Too Large
The error ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes occurs because InnoDB has a stricter limit on the maximum index key length compared to MyISAM. Specifically, InnoDB limits the length of index keys to 767 bytes for the utf8mb3 character set, which uses up to 3 bytes per character. For utf8mb4, which uses up to 4 bytes per character, the limit is 767 bytes.
First, identify which indexes are causing the issue. Likely problematic indexes are those on large VARCHAR columns. To resolve this, you can use prefix indexes:
Drop Problematic Indexes:
mysql> ALTER TABLE your_table DROP INDEX index_name1, DROP INDEX index_name2;Add New Indexes with Prefix Lengths:
mysql> ALTER TABLE your_table
ADD INDEX index_name1 (column_name1(255)),
ADD INDEX index_name2 (column_name2(255));By specifying a prefix length (e.g., 255), you index only the first 255 characters of the column, reducing the total index size. This allows you to convert the table to InnoDB successfully:
mysql> ALTER TABLE your_table ENGINE=InnoDB;Error 2: Row Size Too Large
The error ERROR 1118 (42000): Row size too large (> 8126) indicates that the total row size exceeds the maximum row size allowed by the InnoDB storage engine. This is common with multiple large VARCHAR columns, as the storage engine attempts to keep most data inline with the row.
To resolve this, you can convert large VARCHAR columns to TEXT and use ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED.
Why the Error Occurs
MySQL stores table data in pages, typically 16KB in size. The maximum row size is often around 8KB for the InnoDB engine, including all column data stored inline with the row. When the combined size of the row exceeds this limit, you encounter the error.
How ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED Helps
MySQL’s InnoDB storage engine supports different row formats, which determine how data is stored internally. The key formats are COMPACT, REDUNDANT, DYNAMIC, and COMPRESSED. The COMPACT and REDUNDANT formats store data differently compared to DYNAMIC and COMPRESSED.
These DYNAMIC and COMPRESSED formats were introduced to handle large columns more efficiently. In these formats, large VARCHAR, TEXT, and BLOB columns primarily store their data off-page, with only a small pointer (typically 20 bytes) stored inline within the row. This helps in reducing the row size significantly
How Converting to TEXT Helps
VARCHAR columns store data inline up to their maximum length. When you have multiple large VARCHAR columns, the row size can easily exceed the limit. TEXT columns inherently store data off-page, which aligns well with the storage mechanisms of DYNAMIC and COMPRESSED row formats.
Converting large VARCHAR columns to TEXT reduces the inline row size significantly because only a small pointer is stored inline, pointing to the actual data stored off-page.
Change Row Format to DYNAMIC:
mysql> ALTER TABLE your_table ROW_FORMAT=DYNAMIC;Convert Large VARCHAR Columns to TEXT:
Identify and convert large VARCHAR columns to TEXT:
mysql> ALTER TABLE your_table MODIFY column_name1 TEXT,MODIFY column_name2 TEXT;Step 5: Verify the Conversion
After executing the ALTER statements, it is essential to verify that all tables have been successfully converted to InnoDB. Use the following query to check the table engine:
mysql> SHOW TABLE STATUS LIKE 'your_table'\G
Sample Output:
*************************** 1. row ***************************
Name: employees
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 1000
Avg_row_length: 100
Data_length: 102400
Max_data_length: 0
Index_length: 20480
Data_free: 4096
Auto_increment: 1001
Create_time: 2023-06-27 12:34:56
Update_time: 2024-06-27 12:34:56
Check_time: NULL
Collation: utf8mb4_general_ci
Checksum: NULL
Create_options:
Comment:This query will provide the engine type for the specified table. Ensure that the Engine column shows InnoDB for all converted tables.
Conclusion
Converting MyISAM tables to InnoDB can significantly enhance database performance and reliability. By carefully following the steps outlined above and addressing common errors, you can ensure a smooth and efficient conversion process. This not only optimizes storage but also ensures your database can handle large text data without exceeding row size limits. Embracing InnoDB will bring your database to a new level of performance and stability, making it well-suited for modern production environments.