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'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/...e-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.

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