MySQL Memory Allocation

[Chris]

What is the right way to allocate memory on a MySQL server?

[Chris]

A simple, uneventful answer is: http://dev.mysql.com/doc/refman/5.0/en/memory-use.html

[Chris]

Here is a formula that I always use:
innodb_buffer_pool_size
+ key_buffer
+ max_connections * (join_buffer + sort_buffer + read_buffer + binlog_cache_size)
+ max_connections * 2
---------------------
= TOTAL RAM MYSQL CAN USE

Note: this is a general formula so make sure you do you do-diligence for your system before you plug all the numbers into production.

[Sarah Sproehnle]

Chris,

Good formula! I use a similar one, generally this:
innodb_buffer_pool_size +
key_buffer_size +
query_cache_size +
(max_connections * (192k + sort_buffer_size + read_buffer_size + tmp_table_size)) +
32M

Note: 192k is the approximate thread size. The 32M accounts for the binaries and some other settings that I didn't add into the formula. Of course there are plenty of things that aren't being accounted for that *could* get used, but this formula gives a pretty good estimate of worse case ram usage.

[Chris]

I like it better than mine given that yours explains a bit more and that max_connections * 2 is not the multiplier for the whole equation.