MyISAM vs InnoDB

When is it appropriate to use InnoDB table types? What about for a forum such as this one with huge post and thread and memberlist tables? I was reading on dev.mysql.com that it is more efficient than MyISAM for large table types? Is this always the case? Would it make sense to mix and match MyISAM and InnoDB table types in my database based on the number of rows in the table?

I did some research after I posted this thread. Apparently MyISAM is faster than InnoDB. The only advantage InnoDB has over MyISAM is that it supports row locking, while MyISAM only supports table locking. Therefore, if lots of reads and writes are constantly being done to a very large table, it eliminates the constant database errors that using a MyISAM table would cause from the overload. InnoDB would therefore be a tad more reliable when you don't mind taking a small performance hit in exchange for not suffering from table locking issues.

And if what you describe is true, depending on the write frequency of a table, the row-level locking may be faster because additional updates are not waiting for the entire table to unlock. Sounds like reads would always be a tad slower? I don't know of course.

You should probably weigh in the database backup/copy advantages of InnoDB we talked about previously. Those could mean a lot to you.

Isn't the table type something you set per table as you create them? Does the installation process for vBulletin allow you to select your table type? I know apps such as phpBB and Wordpress do not allow you to choose that--so you are going to get MyISAM tables. I suppose their is a process to convert a table's type? I wonder what other gotchas there may be.

vBulletin creates MyISAM tables by default. You can convert a table to InnoDB either via phpMyAdmin or via the mysql> prompt via ssh. However, converting a large table is very resource intensive, so it's best to plan out which table type you want from the start.

DaniWeb is far away from needing InnoDB tables right now. The reason being that while we receive lots of pageviews - and lots of database reads - user activity and posts (aka database writes) aren't up to par with 65,000 members, unfortunately. Therefore, it wouldn't make sense, in my case.

No problem. :) I, too, am sticking with MyISAM for now. However, after reading a whole bunch of posts on the issue on vBulletin.com, it looks as if InnoDB is in my future for the thread and post tables to prevent locking issues that can take down the database. Before I get to that point, I'm going to get myself a dedicated db server. :)

Very not long after my last post, DaniWeb moved to a dual server setup. And now, roughly one year later, it looks like we've been forced into going the InnoDB route. Surfing DaniWeb is speedy but posting has been hit or miss lately. Not sure if this is the reason but it's worth a shot I guess.

Very not long after my last post, DaniWeb moved to a dual server setup. And now, roughly one year later, it looks like we've been forced into going the InnoDB route. Surfing DaniWeb is speedy but posting has been hit or miss lately. Not sure if this is the reason but it's worth a shot I guess.

Hello

I am fighting a Mysql server since weeks to make it run ok, after a few days it gets stuck no matter what I do.

I am using some large configuration in cnf as the site suppose to handle some load.

Anyone can help understnd the issue there?
Do you have a goof cnf to test out?

I optimized my queries to the limits, at least as much as I could imagine, so I really have no ideea what to do next.

Very not long after my last post, DaniWeb moved to a dual server setup. And now, roughly one year later, it looks like we've been forced into going the InnoDB route. Surfing DaniWeb is speedy but posting has been hit or miss lately. Not sure if this is the reason but it's worth a shot I guess.

What caused the force?

From what I have been able to gather over the last few days, InnoDB is more robust, yet more resource intensive.

Hey, I just thought I'd chime in with my 25 cents... (hey, that's inflation for ya)

I recently had an InnoDB database crash. I was using a web application that allowed me to store notes, and I had input a lot of notes into this app which was self hosted. Out of the blue, I could no longer login because the application was not able to locate the database. Upon contacting the host (TextDrive Sucks....) they said it was because the database was of InnoDB format. That also meant that they could not restore the DB because InnoDB tables only backed up structure not data.

It's really wack. But I highly advise you to stick with MyISAM, I've never had a problem with it. Also, backup your data often, I can't stress that enough! I'll live, obviously, without my notes. But who knows? I may have had the next trillion-dollar idea stored up, and now it's for the birds! (I'm highly exaggerating now.)

Good to know the differences though, I suppose. This forum seems speedy enough to me.

Hello, all. I just thought I would chime in a few things about this that I have realized the hard way about this question, and see if anyone can help me with my own problem.

MyIsam
First, if you want to search through large samples of text efficiently, you want a FULLTEXT index - this allows people to search things like webpages in a page-ranked "google-style", and it's been really helpful. Only MyISAM can do this.

The fulltext index creates a numeric index of all the words, ranks them by frequency of the word within each document, the length of the word, and the length of the document. The engine uses a formula that combines all of these factors into one value that ranks the results when you search for the terms.

I'm sure plenty of folks in here are familiar with trying to search through a bulletin board or a website for something specific and seeing results that never seem to make any sense - these results are simply counting each specific instance of the words in a document and spitting out the results. It makes the process of finding anything in a large collection extremely annoying.

Innodb
Second, if you want to enforce referential integrity between tables, you must use Innodb. I just found this out today, and I am pretty frustrated, because I need this feature for a system I have already created and populated. If you aren't familiar with referential integrity, (apologies if this is over-simplified) it's really important to enforcing business rules when you are creating a db. If I want to link the primary key from one table to a foreign key in another table and not allow values in the child table that don't exist in the parent already, I have to use Innodb.

A good example of this would be to say "I only want to allow sales offers to be linked to pre-approved customers" - and you have a table of pre-approved customers and a separate table of offers - you would have to create a bunch of application code to enforce this, instead of doing it in the db (both can be done, of course... but that's another discussion). If it was done in the db, it would never allow the offer to be created for a customer who didn't exist in the customer table already.

I found this out the hard way with a system I created from scratch using MyISAM tables (because I needed FULLTEXT). The thing that bugs me the most about this mess is that MySQL created my foreign key constraints and told me everything was cool.
I assumed everything would work if it did that.

I'm sure many of the folks in here have worked with keys instead of foreign key constraints to link tables (I am used to dealing with Oracle products). Can anyone show me an example that would be equivalent? So, what to do now? Create indexes? What's the best way to do something like this:

After doing some more research, I want to point out some differences with how MyISAM and InnoDB tables are backed up.

If you use MyISAM tables exclusively, you can back up with mysqlhotcopy. It creates files that can easily be dumped back into the database directory to restore the db. However, mysqlhotcopy only stores structure, and not data, of InnoDB tables. Purefusion, I believe this is what happened with you?

mysqldump, however, works for both InnoDB as well as MyISAM tables. It creates an .sql dump that can then be reimported by using the source command.

With both mysqlhotcopy and mysqldump, tables are locked from updates during the actual backup process, so as to ensure that the entire backup is in sync. They can still be read from though. However, this can cause problems with dynamic sites (such as forums) where nearly every page load requires a table of some sort to be updated, even if it's just to update a views counter. Effectively the database is unreachable during the entire backup process. It's therefore recommended, if feasable, to have a second database server on which to backup from.

If you use InnoDB tables exclusively, you can use mysqldump with the --single-transaction flag. Because InnoDB tables support transactions, you can achieve a flawlessly in sync backup of a very large database without having to close the database off to new writes during the upgrade process. Everything is simply in sync to the moment the backup first started despite how long it takes and how much the database is updated during the backup process.

The --single-transaction flag will work with MyISAM tables but because they're not transactional, and the tables aren't being locked off to writes, MyISAM tables won't be in sync. Depending upon how long the backup takes, every individual table would effectively be a snapshot of a different moment in time, effectively corrupting the backup.

I used InnoDB for a while and just recently finished switching everything back to MyISAM. It was really hard to reliably back up and if you ever decide to set up replication forget about it.
Weighing all the pros and cons though I would say stick with MyISAM unless you absolutely need the features that InnoDB provides and you can't get away with just building them into your application.

The way we typically back up MySQL at work is to run MySQL with replication between two systems, and once a day we bring down the mysql instance on the passive box and perform a file-level backup.

It gets really touchy if the two somehow get out of sync (as often did happen with MySQL's various untraceable bugs).
In that situation, Linux LVM snapshots became a godsend--it was very little trouble to bring down the primary database for 5 minutes, create a snapshot volume of the database's filesystem, get the primary back up and do a concurrent file copy to the passive system.

...if you want to enforce referential integrity between tables, you must use Innodb.

Hi Anjama and others,

I'm in the initial stages of building a MySQL database for my employer using the MyISAM engine. I intend to limit database access to stored procedures and triggers which will enforce referential integrity. That seems to be a fairly obvious, but time-consuming and complicated way to implement it to me. It seems like I'll be reinventing the wheel a bit and no doubt it would be a nightmare to implement for anyone trying to convert a large, populated DB from InnoDB. Does anyone know if there is there a better way to do this?

I have been researching the differences (implementation as well as performance) between MyISAM and InnoDB for sometime. It looks like we need to hit a subtle balance between both of them,

In particular, it was very disappointing for me to read that Dani had to revert back from InnoDB.

The right way to use the mix, IMO, is to use a master-slave configuration for database replication. One should use InnoDB on the master to store tables for fast concurrent inserts and updates. The same table should be replicated on the slave using MyISAM for fast reads.

Also, when using InnoDB, don't forget to play around with the innodb_buffer_pool and innodb_thread_concurrency parameters. They seriously impact the performance you get out of InnoDB setup.

One last point to note here is that InnoBase has been acquired by Oracle already. So, future bug fixes and improvements may not be free.

I've been asking myself this same question but aren't you forgetting some important differences between Inno and MyIsam here?

As far as I know MyIsam does NOT support Foreign keys and InnoDB does. Which makes a huge difference in your coding / db design.

That is the main reason I'm struggling with the question, to switch or not to switch, as when I'm going to InnoDB I should redesign my database (just partially but still some extra commands are necessary to define the FK's) and more work is changing the (php / sql) code (where you don't have to do FK relations manually anymore)

There is another very good reason to use INNODB: concurrency. If you are going o have multiple users updating (different rows of) the same table concurrently, you really need the row locking functionality of INNODB.

I had really only ever seen the MyISAM engine in use but when I installed MySQL Workbench it defaults to the innodb engine and that led me to do the same research you did and I concluded exactly what you did:

MyISAM is faster but innodb is better for databases who have lots and lots of writes and I think the vast majority are as you describe daniweb to be... mostly reads.