MySQL: Innodb Memory Usage Formula
December 23, 2008 – 3:43 pmI hate looking for this….
This will give you a rough idea of your innodb memory usage. I know it’s in a hundred spots, but i hate looking for it when i double check things.
innodb_buffer_pool_size
+ key_buffer_size
+ max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size +2MB)
5 Responses to “MySQL: Innodb Memory Usage Formula”
Hi,
Just a few remarks:
* The formula you’ve presented does not relate directly to InnoDB. The innodb_buffer_pool_size is in fact the only innodb related parameter there.
* It also formulates a worst case scenario where all possible connections are open, all querying, all using the sort buffer (you may as well have thrown in the join_buffer_size).
* The formula also measures key_buffer_size. If you’re using MyISAM tables, than make sure the memory sum of the formula is well below system memory, since MyISAM really needs OS page cache.
Regards
By Shlomi Noach on Dec 23, 2008
Hi Shlomi,
Yes, it’s the worst case scenario. However, I’ve seen it happen often enough to know that a system won’t recover once it starts paging to disk.
What else would you factor in, assuming only innodb is in use?
By jon on Dec 24, 2008
Hi,
Missing from the equation is the thread_stack_size (per connection), query_cache_size (global).
There isn’t anything I would add to the equation which relates to InnoDB. buffer_pool_size is the only thing I consider (to be honest, I don’t even count query_cache_size as this usually measures in a few MB)
Regards
By Shlomi Noach on Dec 24, 2008
Hi,
You could also try or which both will assist you in figuring out MySQL memory usage.
By Ole Morten Halvorsen on Dec 26, 2008
(Seems like the links was stripped from my previous comment)
You could also try http://www.omh.cc/mycnf/ or http://wiki.mysqltuner.com/MySQLTuner which both will assist you in figuring out MySQL memory usage.
By Ole Morten Halvorsen on Dec 26, 2008