MyISAM vs InnoDB

I'm very interested in this answer, too. Hopefully some light will be shed on this thread.

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.

Here is some information about this: http://dev.mysql.com/doc/refman/5.0/en/table-locking.html

Here is some information about this: http://dev.mysql.com/doc/refman/5.0/en/table-locking.html

Thanks for the info and the link. The page at that link sells me on sticking with MyISAM I think.

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. :)

*bump*

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.

*bump*

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.

Thanks
Cris

*bump*

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.

This link is a bit dated, but shows what each [MYSQL storage engine] does or does not have: http://dev.mysql.com/tech-resources/articles/storage-engine/part_3.html

And, finally, 5.0 storage engine reading: http://dev.mysql.com/doc/refman/5.0/en/storage-engines.html

For small projects that do not have a need for transactions, I am hard pressed to see a reason to use InnoDB. For large-scale or ACID compliance, InnoDB seems the proper choice.

There is also how each stores their database, max table sizes, and so on.

For my projects, I will be using InnoDB. Mainly for the foreign key constraints. I am not overly concerned with resource usage.

We ended up switching back because InnoDB was just way too slow.

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:

ALTER TABLE votes ADD CONSTRAINT FK_votes_REFS_citation 
    FOREIGN KEY (master_id) REFERENCES citation (master_id);

... with keys? Also, I only need to have 3 out of about 20 tables to be fulltext-searchable... Should I just convert the other 17 to Innodb? Will tables using different engines link up efficiently?

Again, I apologize if this is over-simplified or has been answered already. Thanks in advance!

Sadly, no. You cannot link Inno and MyISAM and maintain foreign key constraints. I found this one out the hard way as well and is causing me a slight headache.

I did convert the MyISAM to InnoDB without issue, but I am unsure if other functionality was broken in the process (constructing a wrapper app for an already existing app).

I do not really need big time constraints. But, I enjoy my database handling as much logic as it can (unique, foreign keys, etc) and letting the app play dumb.

Sadly, no. You cannot link Inno and MyISAM and maintain foreign key constraints. I found this one out the hard way as well and is causing me a slight headache.

Can I maintain the constraints between the innodb tables only, and just hope for the best with the three tables requiring fulltext?

I did convert the MyISAM to InnoDB without issue, but I am unsure if other functionality was broken in the process (constructing a wrapper app for an already existing app).

So, no lost data or anything like that? Is there a way to create indexes that do the same thing in MyISAM?

I do not really need big time constraints. But, I enjoy my database handling as much logic as it can (unique, foreign keys, etc) and letting the app play dumb.

I agree. It keeps everyone honest. Thanks.

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.

Hope that helps :-/

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?

Thanks,

Chris Fry
Canberra, Australia

Hi

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.

Hope this helps!!

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 hope they are not going to do so. Read the Oracle statement.

BTW, SUN has acquired MySQL...

this post is very helpful.thanks

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)

this thread was very helpful thanks a lot dani

Here is some information about this: http://dev.mysql.com/doc/refman/5.0/en/table-locking.html

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.

Here is some information about this: http://dev.mysql.com/doc/refman/5.0/en/table-locking.html

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.

Yep, the conclusion is:
if your table is mainly Read: MyISAM better because faster
if your table is mainly Write: InnoDB better because faster, especially for concurrency:
MyISAM or InnoDB?

You can't do full text search with InnoDB, but you can search using more resourse heavy methods such as LIKE '% %'

This is really nice read, but it has included only few points.