Tag Archives: myisam

MySQL Engine – InnoDB vs MyISAM

InnoDB does locking on the row level and runs queries as nonlocking consistent reads by default

How to check the engine of tables for a database

mysql> select engine from tables.information_schema where table_schema='<DB_NAME>';

 

Summary of Engines’ space utilised

select engine,sum(index_length+data_length)/1024/1024,count(engine) from information_schema.tables group by engine;

 

Check MyISAM tables

NOTE:

When it converts from MyISAM to InnoDB, you will see in process list ‘copy to tmp table’. This has nothing to do with tmp_table_size or tmpdir. What it does, it goes inside your /var/lib/mysql/<dbname>/ and creates temporary files called like #sqlXXXX.frm and #sqlXXXX.ibd. Keep an eye on the size of the .ibd file to monitoring the progression. It should be roughly big like the sum of <table>.MYI and <table>.MYD. +~20%

 

SELECT table_schema,table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA NOT IN ("information_schema", "performance_schema", "mysql") and engine = 'MyISAM';

Change all to InnoDB
https://support.rackspace.com/how-to/mysql-engines-myisam-vs-innodb/
When?
https://docs.dev4press.com/tutorial/wordpress/choosing-mysql-database-engine-myisam-or-innodb/
Quick answer: Always EXCEPT If you want to use FULLTEXT or SPATIAL indexes for some tables: in this case there is no choice and you must use MyISAM.

NOTE: FULLTEXT index support for InnoDB tables requires MySQL 5.6.4 or higherMariaDB 10.0.5 or higher
Highly recommend upgrading to MariaDB 10.0 as this is the 5.6 MySQL equivalent. It does not alter the data structures as 5.6 does and can be downgraded much more easily. It contains all the features of 5.6 plus more as well as being a drop-in replacement for MySQL in RHEL7.
I would point you to these KB articles:
https://mariadb.com/kb/en/mariadb-vs-mysql-features/https://mariadb.com/kb/en/mariadb/mariadb-vs-mysql-compatibility/Repositories: https://downloads.mariadb.org/mariadb/repositories/#mirror=somerset

Check if there are FULLTEXT or SPATIAL indexes:

mysql> use information_schema;
mysql> SELECT DISTINCT CONCAT(STATISTICS.TABLE_SCHEMA, '.', STATISTICS.TABLE_NAME) AS fulltext_table, (TABLES.DATA_LENGTH + TABLES.INDEX_LENGTH)/1024/1024 AS size_M FROM STATISTICS INNER JOIN TABLES ON TABLES.TABLE_SCHEMA = STATISTICS.TABLE_SCHEMA AND TABLES.TABLE_NAME = STATISTICS.TABLE_NAME WHERE STATISTICS.INDEX_TYPE IN ("SPATIAL", "FULLTEXT") GROUP BY STATISTICS.TABLE_SCHEMA, STATISTICS.TABLE_NAME;

Convert ALL MyISAM tables to InnoDB (all databases):

2 steps + backup

# mysql --batch --skip-column-names --execute 'select concat("alter table ",TABLE_SCHEMA,".",TABLE_NAME," ENGINE='MyISAM';") from information_schema.TABLES where ENGINE = "MyISAM" AND TABLE_SCHEMA NOT IN ("information_schema", "performance_schema", "mysql");' > rollback_all2Innodb.sql
# mysql --batch --skip-column-names --execute 'select concat("alter table ",TABLE_SCHEMA,".",TABLE_NAME," ENGINE='InnoDB';") from information_schema.TABLES where ENGINE = "MyISAM" AND TABLE_SCHEMA NOT IN ("information_schema", "performance_schema", "mysql");' > all2Innodb.sql
# mysql < all2Innodb.sql

(one liner – CAREFUL!)

# mysql --batch --skip-column-names --execute 'select concat("alter table ",TABLE_SCHEMA,".",TABLE_NAME," ENGINE='InnoDB';") from information_schema.TABLES where ENGINE = "MyISAM" AND TABLE_SCHEMA NOT IN ("information_schema", "performance_schema", "mysql");' | mysql

 

Convert MyISAM tables of a SPECIFIC database to InnoDB:

Backup and convert-file

# DB="<dbname>"
# mysql --batch --skip-column-names --execute "select concat('alter table ',TABLE_SCHEMA,'.',TABLE_NAME,' ENGINE = \'MyISAM\';') from information_schema.TABLES where TABLE_SCHEMA = '"$DB"' and ENGINE = 'MyISAM';" > rollback_$DB_all2Innodb.sql

# mysql --batch --skip-column-names --execute "select concat('alter table ',TABLE_SCHEMA,'.',TABLE_NAME,' ENGINE = \'InnoDB\';') from information_schema.TABLES where TABLE_SCHEMA = '"$DB"' and ENGINE = 'MyISAM';" > $DB_all2Innodb.sql

NOTE: When it converts from MyISAM to InnoDB, you will see in process list ‘copy to tmp table’. This has nothing to do with tmp_table_size or tmpdir. What it does, it goes inside your /var/lib/mysql/<dbname>/ and creates temporary files called like #sqlXXXX.frm and #sqlXXXX.ibd. Keep an eye on the size of the .ibd file to monitoring the progression. It should be roughly big like the sum of <table>.MYI and <table>.MYD. +~20%