MySQL (Innodb vs MyISAM)

[Omniscient]

Recently I was having severe mysql problems with my biggest forum. I would have constant crashes and I tried just about everything.

Eventually I tried to alter tables from myisam to innodb and with a few minor adjustments my 1.8gb database was actually consuming 1/10th of it's previous resources. Where I was nearly about to get a new server now I have room to double or even triple in database size without a problem.

The secret why innodb was so much more efficient was how it handles table locks over mysiam. The method used by myisam to lock tables is to lock the entire table on every read/write. Innodb does row-level locking which means only the effected row is locked.

For tables that are constantly accessed that get locked very often Innodb is incredibly efficient. One important note is that innodb does take more overheard and ram but for large databases the offset could be a huge difference to your server resources.

Here is a blog post I made about my adjustments: Innodb Vs MyISAM

[Gaijin]

I was always creating MyISAM, because I never took some time to read how InnoDB works...
You could say I was afraid of errors I might get into and wouldn't be able to resolve it...!

But if I understand you right, I'm good with using MyISAM on small sites with not that much content saved in the database.
I'll work with InnoDB to see how it works, thanks for sharing!

[Omniscient]

You'll most likely benefit from the stability of MYISAM in most situations. Innodb is going to be slower on small sites that aren't as active but as activity grows and tables are waiting in locked states innodb would do better.

I was doing 250 queries per second with 1 out 35 queries being locked. So that's 6-8 query per seconds would lock. You take into account busy periods and long queries then you can see how that's a problem. With innodb I have 1:250 lock. I rarely see my tables with lock problems now. Innodb has been fantastic at solving my problems.

[Gaijin]

I understand, I will write a script that will overload my database with content to see how it affects the site...
Then switch to InnoDB, so I can see what you're talking about in action...

But MySQL Commands are still the same, I just need to convert few table types?

[Cyandelta]

InnoDB is better, IMHO.

I use InnoDB for my personal website, i converted from MyISAM like you and I noticed a huge change.

[υℓqυισяяα]

You are precisely correct. Innodb has more fixed resource usage, but less additional usage as the db gets bigger. MyISAM has less fixed load but causes more resource usage, as the DB gets bigger. ;) [I read it somewhere]
Innodb = good for big sites
myisam = good for small sites

[alfonzo1955]

I have never used InnoDB because my database is not large enough to bother switching.

[Camgaertner]

Thats very good. It seems to be much more efficient.

On an un-related note, I'm curious about backups for your large sites?

[Omniscient]

Curious about what in particular? I do regular backups.

[Camgaertner]

Curious about what in particular? I do regular backups.

What do you use to restore them?