Linux – troubleshooting out of memory error messages from thesql

(1) I see that each of the running processes occupies a very small percentage of memory (%MEM no more than 0.2%, and most just 0.0%), but how the total memory is almost used as in the fourth line of output ("Mem: 130766620k total, 130161072k used, 605548k free, 919300k buffers")? The sum of used percentage of memory over all processes seems unlikely to achieve almost 100%, doesn't it?

To see how much memory you are currently using, run free -m. It will provide output like:

             total       used       free     shared    buffers     cached
Mem:          2012       1923         88          0         91        515
-/+ buffers/cache:       1316        695
Swap:         3153        256       2896

The top row 'used' (1923) value will almost always nearly match the top row mem value (2012). Since Linux likes to use any spare memory to cache disk blocks (515).

The key used figure to look at is the buffers/cache row used value (1316). This is how much space your applications are currently using. For best performance, this number should be less than your total (2012) memory. To prevent out of memory errors, it needs to be less than the total memory (2012) and swap space (3153).

If you wish to quickly see how much memory is free look at the buffers/cache row free value (695). This is the total memory (2012)- the actual used (1316). (2012 - 1316 = 696, not 695, this will just be a rounding issue)

(2) how to understand the load average on the first line ("load average: 14.04, 14.02, 14.00")?

This article on load average uses a nice traffic analogy and is the best one I've found so far: Understanding Linux CPU Load - when should you be worried?. In your case, as people pointed out:

On multi-processor system, the load is relative to the number of processor cores available. The "100% utilization" mark is 1.00 on a single-core system, 2.00, on a dual-core, 4.00 on a quad-core, etc.

So, with a load average of 14.00 and 24 cores, your server is far from being overloaded.

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;