Why is MyISAM still the default for all tables?

solstice

1 posts

Hi All!

I’m an experienced UNIX & Linux sysadmin in the central IT department at a university. I’m planning a hardware/OS/MySQL upgrade on a database server that we manage centrally. One of the campus departments that’s using the database server is using ExpressionEngine, apparently version 2.6.1.

Poking around in the expression engine code and the database, I also see the following info for modules:

mysql> select module_name,module_version,has_cp_backend,has_publish_fields from exp_modules;
+----------------+----------------+----------------+--------------------+
| module_name    | module_version | has_cp_backend | has_publish_fields |
+----------------+----------------+----------------+--------------------+
| Comment        | 2.3.1          | y              | n                  | 
| Email          | 2.0            | n              | n                  | 
| Emoticon       | 2.0            | n              | n                  | 
| Jquery         | 1.0            | n              | n                  | 
| Structure      | 3.3.10         | y              | y                  | 
| Rss            | 2.0            | n              | n                  | 
| Search         | 2.2.1          | n              | n                  | 
| Channel        | 2.0.1          | n              | n                  | 
| Member         | 2.1            | n              | n                  | 
| Stats          | 2.0            | n              | n                  | 
| Query          | 2.0            | n              | n                  | 
| Reelocate      | 1.1            | y              | n                  | 
| Updater        | 3.2.4          | y              | n                  | 
| Channel_images | 5.4.6          | y              | n                  | 
| Channel_videos | 3.1.3          | y              | n                  | 
| Editor         | 3.1.4          | y              | n                  | 
| Freeform       | 4.0.12         | y              | n                  | 
| Metaweblog_api | 2.1            | y              | n                  | 
| Zenbu          | 1.8.3          | y              | n                  | 
| Twitter        | 1.4.1          | y              | n                  | 
| Ce_cache       | 1.9.5          | y              | n                  | 
+----------------+----------------+----------------+--------------------+
21 rows in set (0.00 sec)

What I don’t understand is why CodeIgniter is forcing every one of the 110+ tables to be in MyISAM format, overriding the system default. I completely understand that a few tables may require MyISAM, for example for FULLTEXT indexing. That doesn’t explain why the remaining tables would override the system default, though.

InnoDB has many advantages (and a few drawbacks) over MyISAM, but for most uses it’s clearly superior. It also uses mostly different tuning settings, so most sites prefer to use one format whenever possible, rather than an even mix of both. I know that the mysql.* tables are still MyISAM format, but that’s a drop in the bucket compared to the exp_* tables.

So can anyone explain what the rationale is with overriding the system default for ENGINE type? Does Ellis Lab support sites that choose to convert the tables that can be converted from MyISAM to InnoDB? Does anyone have a list of which tables can be converted and which ones must not be?

Thanks,

Tim

mysql> desc exp_sessions; +---------------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+------------------+------+-----+---------+-------+ | session_id | varchar(40) | NO | PRI | 0 | | | member_id | int(10) | NO | MUL | 0 | | | admin_sess | tinyint(1) | NO | | 0 | | | ip_address | varchar(45) | NO | | 0 | | | user_agent | varchar(120) | NO | | NULL | | | fingerprint | varchar(40) | NO | | NULL | | | sess_start | int(10) unsigned | NO | | 0 | | | last_activity | int(10) unsigned | NO | MUL | 0 | | +---------------+------------------+------+-----+---------+-------+ 8 rows in set (0.00 sec)

Thread

solstice

biblio

Username

Password

Thread