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.