Jump to content

MySQL: MyISAM vs InnoDB


Recommended Posts

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:



post-26432-126694388896_thumb.png

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?
Link to comment
Share on other sites

  • 2 months later...

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...