Mysql / swap space full

Question

I have MySQL Ver 14.12 Distrib 5.0.45 server running on Multiflex Intel Blade having 8G of RAM & 8 x Intel(R) Xeon(R) CPU E5410 @ 2.33GHz.

The problems I am facing are:

1. For the last 3-4 days my 2G swap space is getting full & the server goes into hangged state and I need to reboot the server each time.

   Output of free -m:

                total       used       free     shared    buffers     cached
   Mem:          7971        933       7037          0          0        800
   -/+ buffers/cache:        132       7838
   Swap:         1983        785       1198
   

2. I have added 2G more swap space but still problem exists.

   /usr/local/mysql/bin/mysql -uroot -p -e "show variables like '%cache%';":

   +------------------------------+----------------------+
   | Variable_name                | Value                |
   | binlog_cache_size            | 32768                | 
   | have_query_cache             | YES                  | 
   | key_cache_age_threshold      | 300                  | 
   | key_cache_block_size         | 1024                 | 
   | key_cache_division_limit     | 100                  | 
   | max_binlog_cache_size        | 18446744073709551615 | 
   | ndb_cache_check_time         | 0                    | 
   | query_cache_limit            | 1048576              | 
   | query_cache_min_res_unit     | 4096                 | 
   | query_cache_size             | 536870912            | 
   | query_cache_type             | ON                   | 
   | query_cache_wlock_invalidate | OFF                  | 
   | table_cache                  | 1024                 | 
   | thread_cache_size            | 16                   | 
   

Any suggestions?

Answer 1

This is a great article on how the Linux kernel handles swap space. "swappiness," or the likelihood that the kernel will swap out large programs into swap space can be decreased so that the swap space is not preferred over RAM.

Echoing a value from 0-100 to /proc/sys/vm/swappiness will cause the kernel to swap less or more depending on the value. The default is 60. I'd recommend something like 40-50.

You may want to actually reduce the amount of swap space, as that bottleneck may be the "hanged" state you describe.