10

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?

  • 1
    Table type isn't tied to the catalog. You can have a catalog with a mix of MyISAM and InnoDB tables in it. – mpdonadio Jan 27 '12 at 17:37
  • 2
    While 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. – Letharion Jan 27 '12 at 17:50
  • 1
    @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 Jan 27 '12 at 17:52
  • 2
    Letharion: that is just plain incorrect. Table types and the way mysql is configured for them have a huge impact on performance! – Walter Heck Jan 31 '12 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. – BMDan May 17 '13 at 14:02
8

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.

Source: http://www.electrictoolbox.com/mysql-table-storage-engine/

  • 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 Jan 27 '12 at 17:39
  • @MPD Wow is that still going? Must download that even if just for a bit of nostalgia :) – Clive Jan 27 '12 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 Jan 27 '12 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's SELECT ENGINE,COUNT(TABLE_NAME) FROM information_schema.TABLES WHERE TABLE_SCHEMA='database_name' GROUP BY ENGINE . – BMDan May 17 '13 at 14:04
7

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.

  1. Did you turn on the built in mechanisms: page caching (only effective for anonymous users), CSS and JS agregation?
  2. If your host offers an opcode cache like APC, turn it on.
  3. Set up your Drupal localy, including the full DB.
  4. Make sure you have XDebug running.
  5. 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.

6

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:

https://github.com/rackerhacker/MySQLTuner-perl#readme

  • 2
    +1 I didn't know InnoDB was slow for COUNT queries, according to this article by the former High Performance Group manager for MySQL though it only really affects a query containing COUNT(*) without a WHERE clause. – Clive Jan 27 '12 at 18:41
  • 3
    The 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 table can return instantly with that value. InnoDB does not, so it must scan the entire primary key index. That said, while Drupal does a lot of SELECT COUNT(*), I can only think of one place in the entire code that does it without a WHERE clause. 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. – BMDan May 17 '13 at 14:08
5

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.

  • 2
    Having 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_table option. – geewiz Feb 2 '12 at 11:27
  • 1
    Also, 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 two mysqls if needed. – BMDan May 17 '13 at 14:11
5

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!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.