I have a Drupal site, and I am suffering performance issues. I found How do I convert a database from MyISAM to InnoDB? indicating that performance may improve by switching.
How can I tell if my MySQL database is InnoDB, or MyISAM?
-
2Table type isn't tied to the catalog. You can have a catalog with a mix of MyISAM and InnoDB tables in it.– mpdonadio ♦Commented Jan 27, 2012 at 17:37
-
3While there are benefits to optimizations on that level, I highly doubt changing table types will fix your performance issues. The difference just isn't that big.– LetharionCommented Jan 27, 2012 at 17:50
-
2@Chris J. Lee, that is correct. Mixing table types can be used for performance reasons, or to exploit features only available to a particular table type (eg, FULLTEXT search w/ MyISAM tables). It can also happen by accident if you change mysqld defaults and create more tables in a catalog :)– mpdonadio ♦Commented Jan 27, 2012 at 17:52
-
2Letharion: that is just plain incorrect. Table types and the way mysql is configured for them have a huge impact on performance!– Walter HeckCommented Jan 31, 2012 at 22:02
-
2@Letharion: In fact, leaving aside architectural improvements, very large performance gains in the case of things like concurrency with mixed SELECTs and UPDATEs—an UPDATE will lock a table in MyISAM, but only a row in InnoDB—mean that huge performance increases can be seen with nothing more than changing a table's engine. Now, if you're doing a silly query, then you're doing a silly query, and changing table types won't help. But to dismiss changing engines out of hand is equally incorrect.– BMDanCommented May 17, 2013 at 14:02
|
Show 1 more comment
5 Answers
You can run a custom query:
SELECT TABLE_NAME, ENGINE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'database_name'
to list all the tables in your database and the engine that's in use for each.
Alternatively you could log into your database using phpMyAdmin and select your database...you'll see the engine in the Type column in the list of tables.
Personally I'd recommend Navicat for MySQL, it's a very nice MySQL GUI and makes finding out things like this very easy.
-
The old MySQL Administrator tool also works well for things like this. If you poke around on the MySQL website you can still find the installer.– mpdonadio ♦Commented Jan 27, 2012 at 17:39
-
@MPD Wow is that still going? Must download that even if just for a bit of nostalgia :)– Clive ♦Commented Jan 27, 2012 at 17:39
-
Administrator and Query Browser are officially deprecated and unsupported now, but you can still download them. I hate Workbench, and still use these.– mpdonadio ♦Commented Jan 27, 2012 at 17:53
-
To see output similar to what you saw in phpMyAdmin at the command line, use
SHOW TABLE STATUS. To get a simple count of how many tables are in each engine type, it'sSELECT ENGINE,COUNT(TABLE_NAME) FROM information_schema.TABLES WHERE TABLE_SCHEMA='database_name' GROUP BY ENGINE.– BMDanCommented May 17, 2013 at 14:04
My best experience was to decide on a per table basis. InnoDB is nice, because it can avoid table locking (no other process can read from a table while one process writes), but it performs terribly with COUNT() which is used often for pager queries.
(Edit: please see Clives comment below)
There are also implications on your MySQL setup, depending which DB you are using. If you have access to the server, mysqltuner should be your first step, to check the configuration:
-
2+1 I didn't know InnoDB was slow for
COUNTqueries, according to this article by the former High Performance Group manager for MySQL though it only really affects a query containingCOUNT(*)without aWHEREclause.– Clive ♦Commented Jan 27, 2012 at 18:41 -
3The reason is that MyISAM maintains a per-table value for the number of rows in the table, which means that a simple
SELECT COUNT(*) FROM tablecan return instantly with that value. InnoDB does not, so it must scan the entire primary key index. That said, while Drupal does a lot ofSELECT COUNT(*), I can only think of one place in the entire code that does it without aWHEREclause. Thus, do not use MyISAM out of concern for this particular edgecase. InnoDB with a decent size buffer pool will be faster for all real-world queries.– BMDanCommented May 17, 2013 at 14:08
Just trying things to get a web site faster is like blindely changing cars and hope you get a faster one next time.
First try the low hanging fruits, if it doesn't help, try to find the real bottleneck.
- Did you turn on the built in mechanisms: page caching (only effective for anonymous users), CSS and JS agregation?
- If your host offers an opcode cache like APC, turn it on.
- Set up your Drupal localy, including the full DB.
- Make sure you have XDebug running.
- Start doing real performance profiling. This way you can identify what part of your website consumes a lot of time. The slower your website the easier it is to find the guilty code/component.
Often it's just a slow DB query which needs an index, or a module doing things in a slow way.
I also discovered big differences between hosters. If you install a fresh drupal out of the box, is the performance ok? If not, it's time to look for an other hoster.
FWIW our almost 20K node Drupal 6.x site was experiencing some performance issues and I did elect to move all the tables to InnoDB. It was easy and the way I handled it was to use mysqldump to dump all content to a sql file, use an editor (sed) to replace all occurrences of MyISAM to InnoDB then reload the database from that file. One of the down sides is that you cannot recover space from an InnoDB database (IIRC) but as long as you keep your duplicate tables in a separate DB you should have no problems. Oh, and we did see a significant performance increase. And because we have four Drupal instances the sheer number of table files was eliminated from the file system (yes, they're contained inside the InnoDB file itself). This is my $.02 worth.
-
2Having a single InnoDB tablespace may save you inodes, but (as you mention yourself) at the expense of disk space. Even if you delete a lot of data, you'll not be able to reclaim that disk space. This also results in unnecessary big backup files and not being able to restore single tables. That's why I prefer using the
innodb_file_per_tableoption.– geewizCommented Feb 2, 2012 at 11:27 -
1Also, you can convert tables in-place:
for tbl in $(mysql -Ne 'show tables' databasename); do mysql -e "ALTER TABLE $tbl ENGINE=InnoDB"; done. Add appropriate options (-uroot -psomepass, for example) to the twomysqls if needed.– BMDanCommented May 17, 2013 at 14:11
Just a heads up. If your on Drupal 6, you can install the DBTuner module; it can easily move your tables from MyISAM to InnoDB. So yes, there's a module for that!
