views:

1296

answers:

3

Hi all,

Does anyone know how much memory MyISAM and innoDB use? How does their memory usages compare when dealing with small tables vs. when dealing with bigger tables (up to 32 GB)?

I know innoDB is heavier than MyISAM, but just how much more?

Any help would be appreciated.

Thanks, jb

+3  A: 

You can't compare them like that. Or at least, you shouldn't. Each one uses the memory in a different way. This is especially true if you're tunning your DB's for performance.

MyISAM has specific buffers for indexes and it uses the OS disk buffer for caching other data. It doesn't make sense to have your buffers larger than the sum of your indexes, but the more memory you give it, the faster it will be.

InnoDB has a buffer pool for all data. You configure this based on your available memory and how much you want to give it. InnoDB buffers as much of your data in memory as possible. If you can fit the entire DB in memory, InnoDB will never read from disk. A lot of InnoDB databases see huge performance hits when the data size becomes larger than the buffer pool.

MySQL is very configurable. It's tunable to meet your needs. Typically, databases should be given as much memory as possible since they are almost always disk bound. More memory means more can be buffered.

Gary Richardson
This is interesting. I was reading about people setting up a dedicated InnoDB box and allocating 80% of the system memory to the buffer to try and keep the entire database in memory. It sounds like this is not possible with MyISAM. Is that correct?
Brian Armstrong
A: 

Thanks for your answer, gary. So then what do people mean when they say the database is lightweight?

-jb

I would ask this in a different question
epochwolf
A: 

Also db file size on disk its almost 3X then MyISAM tables.

http://kedar.nitty-witty.com/blog/mysql-memory-usage-limits-on-32-bit-linu-os/

kedar