MySQL: MyISAM vs InnoDB

[Wolfie]

Based on this topic: http://community.invisionpower.com/topic/304612-large-forum-db-question/

And this post:

I've posted this a few times over the last year, but it fits here too. We've got one database server with three huge databases on it, sizes are as follows:

Database 1: 1,481,163,341 Rows, 690GB Data, 300GB Indexes

Database 2: 702,376,617 Rows, 91GB Data, 52GB Indexes

Database 3: 2,784,634,883 Rows, 170GB Data, 104GB Indexes

Total: 4,968,174,841 Rows, 951GB Data, 456GB Indexes

As a bit of image evidence:

Now, this isn't running a forum, but it is running on one machine using MySQL. You'll likely never find that MySQL becomes the bottleneck, unless it's incredibly badly configured or doesn't have enough resources available to it.

I'm wondering, what are the pros/cons of MyISAM and InnoDB? Which is recommended overall and which tables are recommended to be one vs the other?

[Clickfinity]

I'd like to know the difference too, and will changing some tables over improve the speed of the forum?

Cheers,
Shaun :D

[agent462]

Anybody that has really used MySQL at scale will not use MyISAM. There is a lot of fluff put into it because it was originally developed for MySQL. There has been a lot of work put into Innodb to make it much faster than it was. Large companies like google, facebook, percona, etc.. have put their own work and patches into innodb to make it better.

In most cases the only tables you should ever have that are myisam are the required default mysql ones (information_schema, etc). The marketing fluff behind myisam being much better for reads is just about that, marketing. Today innodb performs nearly the same or better and also gives you better writes with row level locking instead of locking the entire table like myisam does. I would recommend a few sources www.mysqlperformanceblog.com, Percona or buying High Performance MySQL. Anybody using MySQL will learn quite a bit from it. Myisam tables are also prone to corrupting.

You will lose "full text search" from Myisam but if you really read into it full text has quite a bit of its own limitations. In any small to medium forum you shouldnt really see any impact from not having it. In a large one you might see a little. Any large forum should be using some nice engine like Sphinx, Solr or other lucene based engine anyway.

just my .02


edit:
I'd also recommend checking out Xtradb http://www.percona.com/docs/wiki/percona-xtradb:start

[Clickfinity]

So on my board with 1.3+ million posts (using Sphinx for search) - would it be advisable to change the posts table to InnoDB, and would I see a difference in performance?

If so, what is the process for doing this?

Would I be right in assuming that after converting I would lose the board-based search functionality if Sphinx was off-line for any reason?

Cheers,
Shaun :D

[agent462]

You shouldn't see any negative impact in forum performance. IMO, you might see better. The first thing I would do is change the db default to innodb. If something creates a new table and it doesn't specify the engine it will use myisam. Then you should either find a script online to run that will traverse through all the tables and convert them or you can go through manually one by one with something like phpmyadmin.

You won't lose board-based search but there is one setting you will need to change otherwise you'll get an error. Under System > Tools & Settings > System Settings > System > Search Set-Up you would turn "Use fulltext searching?" to No.

This is a great book and I recommend everyone fiddling with MySQL to read it, http://www.amazon.com/High-Performance-MySQL-Optimization-Replication/dp/0596101716/ref=sr_1_1?s=books&ie=UTF8&qid=1283788383&sr=1-1