MyISAM in a MySQL 5.6 InnoDB Only instance

With MySQL 5.5 the default storage engine was changed to InnoDB. This was a good step as most users expected MySQL to support transactions, row level locking and all the other InnoDB features, but with 5.1 they sometimes forgot to choose the right storage engine. As most databases don't have multiple storage engines and many MySQL users changed the default storage engine to InnoDB this made the switch to MySQL easier and the default behaviour more in line with what people expect from a relational database.

Changing the storage engine can of course be done on a per table or per instance basis (default-storage-engine in my.cnf). The temporary tables created with 'CREATE TEMPORARY TABLE ...' should not be forgotten. The performance of InnoDB or MyISAM for temporary tables can have quite some impact, especially with slow storage, a buffer pool which is too small to hold the complete dataset or very small temporary tables. In MySQL 5.6 there is a new variable introduced to set the default storage engine for temporary tables: default_tmp_storage_engine. This makes it possible to use MyISAM or MEMORY as default temporary storage engine, which could benefit performance in some cases.

A great new feature of MySQL 5.6 is the full text indexing support for InnoDB. Now you can switch your fulltext tables to InnoDB! (after careful testing of course as results may differ)
So do we still need MyISAM? The answer is Yes.

The system tables (mysql.*) are mostly MyISAM. The general log and slow query log tables are in CSV format. In 5.6 there are also some InnoDB tables: innodb_index_stats, innodb_table_stats and optionally slave_master_info and slave_relay_log_info. It's not supported to change these MyISAM tables to InnoDB.

mysql> SELECT engine, COUNT(*) FROM information_schema.tables
    -> WHERE table_schema='mysql' GROUP BY engine;
+--------+----------+
| engine | COUNT(*) |
+--------+----------+
| CSV    |        2 |
| InnoDB |        5 |
| MyISAM |       21 |
+--------+----------+
3 rows in set (0.00 sec)

The implicit temporary tables as created by MySQL (e.g. not with CREATE TABLE) are still in MyISAM format. This can be shown by running some query which uses 'Using temporary; Using filesort' and while the query runs looking in the tmpdir location:

Session1:

select a.user,a.host,b.user from mysql.user a, mysql.user b, mysql.user c,
mysql.user d ,mysql.user e,mysql.user f, mysql.user g, mysql.user h order by a.user,b.user

Session2:

mysql> select @@tmpdir;
+----------------------------------------+
| @@tmpdir                               |
+----------------------------------------+
| /home/dveeden/sandboxes/msb_5_6_10/tmp |
+----------------------------------------+
1 row in set (0.00 sec)

mysql> \! ls -l /home/dveeden/sandboxes/msb_5_6_10/tmp
total 4044
-rw-rw---- 1 dveeden dveeden 4120272 Mar 18 13:30 #sql_69cd_0.MYD
-rw-rw---- 1 dveeden dveeden    1024 Mar 18 13:30 #sql_69cd_0.MYI

And the third one: mysqldump uses MyISAM.
A view is first restored as a MyISAM table and after all views are restored then they are converted to 'real' views. This is needed as views might be dependent on each other.
With performance_schema it's possible to 'see' the usage of MyISAM. With 5.6 not everything is enabled by default, so first check the setup_* tables to see what's enabled and what's not. In setup_objects the system tables are disabled, which can easily give wrong results if you're interested in MyISAM usage.

mysql> SELECT * FROM performance_schema.events_waits_summary_global_by_event_name 
    -> WHERE event_name LIKE 'wait/io/file/myisam/%'\G
*************************** 1. row ***************************
    EVENT_NAME: wait/io/file/myisam/data_tmp
    COUNT_STAR: 0
SUM_TIMER_WAIT: 0
MIN_TIMER_WAIT: 0
AVG_TIMER_WAIT: 0
MAX_TIMER_WAIT: 0
*************************** 2. row ***************************
    EVENT_NAME: wait/io/file/myisam/dfile
    COUNT_STAR: 145525
SUM_TIMER_WAIT: 4793712275400
MIN_TIMER_WAIT: 0
AVG_TIMER_WAIT: 32940608
MAX_TIMER_WAIT: 2244115016
*************************** 3. row ***************************
    EVENT_NAME: wait/io/file/myisam/kfile
    COUNT_STAR: 329
SUM_TIMER_WAIT: 17011553368
MIN_TIMER_WAIT: 0
AVG_TIMER_WAIT: 51706768
MAX_TIMER_WAIT: 236143040
*************************** 4. row ***************************
    EVENT_NAME: wait/io/file/myisam/log
    COUNT_STAR: 0
SUM_TIMER_WAIT: 0
MIN_TIMER_WAIT: 0
AVG_TIMER_WAIT: 0
MAX_TIMER_WAIT: 0
4 rows in set (0.00 sec)

mysql> SELECT event_name,object_name,COUNT(*),SUM(TIMER_WAIT) FROM performance_schema.events_waits_history_long 
    -> WHERE event_name LIKE 'wait/io/file/myisam/%' GROUP BY event_name,object_name\G
*************************** 1. row ***************************
     event_name: wait/io/file/myisam/dfile
    object_name: /home/dveeden/sandboxes/msb_5_6_10/tmp/#sql_69cd_0.MYD
       COUNT(*): 84
SUM(TIMER_WAIT): 1120085576
*************************** 2. row ***************************
     event_name: wait/io/file/myisam/kfile
    object_name: /home/dveeden/sandboxes/msb_5_6_10/tmp/#sql_69cd_0.MYI
       COUNT(*): 6
SUM(TIMER_WAIT): 501999104
2 rows in set (0.02 sec)

Some possible sources of high(er) MyISAM usage:

• The mysql.proc table might be read often if you're often calling stored procedures. (seen with 5.5). This might also be true for events.
• Monitoring which checks things (too) often.
• Usage of server side help

And InnoDB doesn't support spatial indexes, so if you need them you still need MyISAM. Storing spatial information can be done with InnoDB, so if you can do without index you don't need MyISAM:

mysql> CREATE TABLE geo_m (i INT NOT NULL,
    -> g GEOMETRY NOT NULL, PRIMARY KEY (i),
    -> SPATIAL KEY (g)) ENGINE=MyISAM;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE geo_i (i INT NOT NULL,
    -> g GEOMETRY NOT NULL, PRIMARY KEY (i),
    -> SPATIAL KEY (g)) ENGINE=InnoDB;
ERROR 1464 (HY000): The used table type doesn't support SPATIAL indexes
mysql> CREATE TABLE geo_i (i INT NOT NULL,
    -> g GEOMETRY NOT NULL, PRIMARY KEY (i)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

If you're interested in performance_schema, make sure to attend the MySQL Virtual Developer Day.

So MyISAM is still a critical part of MySQL and you should not just forget about it, even if you're only using InnoDB.