Ubuntu – MySQL: How to get the “Maximum possible memory usage” down

The memory usage of MySQL is quite hard to predict. I would recommend having a look at http://www.mysqlperformanceblog.com/2006/05/17/mysql-server-memory-usage/ which gives an introduction to what is using memory in your installation. But to summarize:

"In fact typical server with 8GB of memory will often run with maximum theoretical memory usage of 100GB or more."

Or in other words, don't worry too much about the maximum possible memory usage unless you start seeing your server swap.

I can easily believe a 95% key cache hit rate. Listen to your statistics:

You stated you have 50GB of Indexes for MyISAM tables

The key cache is designed to cache index pages for MyISAM tables. This option is set by the key_buffer_size option

You said it was set at 2G. No wonder the hit rate is 95%. Index blocks are being paged in and out of the key cache constantly. Dozens of queries needing a specific row gets loaded into the keycache after MySQL finds out the index (.MYI) info by the first query needed was not initially there. All subsequent queries requiring the same .MYI info to lookup data (.MYD) will already be cached after the first query loaded it. MySQL DOES NOT CACHE MyISAM Data in its own caches.

You should be able to set key_buffer_size to 8G.

Here is an excerpt from http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_key_buffer_size:

You can increase the value to get better index handling for all reads and multiple writes; on a system whose primary function is to run MySQL using the MyISAM storage engine, 25% of the machine's total memory is an acceptable value for this variable. However, you should be aware that, if you make the value too large (for example, more than 50% of the machine's total memory), your system might start to page and become extremely slow. This is because MySQL relies on the operating system to perform file system caching for data reads, so you must leave some room for the file system cache. You should also consider the memory requirements of any other storage engines that you may be using in addition to MyISAM.

This same URL explains that the max key_buffer_size for 32-bit OS is 4G. You should not go past 8G for key_buffer_size with your given server config.

As for your data reads, the only status variable you need is Key_reads. This gives an indication how often an index page has to be fetched from a .MYI file.

What you need to monitor for you key cache hit ratio is the following

KRR_NOW = Key_read_requests Now
KRR_SEC = Key_read_requests one ago
KRD_NOW = Key_reads Now
KRD_SEC = Key_reads one second ago
KRR_DELTA = KRR_NOW - KRR_SEC
KRD_DELTA = KRD_NOW - KRD_SEC

KeyCache Hit Ratio (KHR) is therefore this formula

KHR = 100 * (KRR_DELTA - KRD_DELTA) / KRR_DELTA

You want a KeyCache Hit Ratio 99+%

Now on the flipsize of things, let discuss InnoDB.

Your innodb_buffer_pool_size should be set to 4G. Thi is because the InnoDB buffer pool caches both data and index pages. You can give innodb_buffer_pool a more specific figure rounded up using the following:

SELECT CONCAT(CEILING(ibbytes / POWER(1024,3)),'G') FROM (SELECT SUM(data_length+index_length) ibbytes FROM information_schema.tables where engine='InnoDB') A;