How to make sure mysql do not start using SWAP?

It started to happen on my OpenVZ VPS that from time to time (like maybe 2 times monthly) im getting high swap usage warning (i set it) and see that mysql is using like 500Mb in the swap. I want to ask what steps to do to make sure mysql will not exhaust its memory and do not start swapping to the SWAP?

Another question is how to discover which process, which action forced VPS to start SWAPping, so i can optimize that thing, any monitoring script for this?

 

You could try installing iotop - that gives live info on swap usage, you coudl watch its output and determine what's going on.

 

@postcd you did not mentioned your actual memory of your VPS in question. Let me assume 1024MB of RAM in your VPS for this example.

You mentioned that mysql is using like 500Mb in the swap. Swapping will happen when your application (here your mysql server) wants more memory and your current sytem has no free memory.

You can check "free -m" command to check your free memory. Best practice is to upgrade your VPS with more memory. Or if your budget will not allow for an upgrade, you can avoid the common shared swap by enabling your own swap file.

1. Create an empty big file of 2GB Or 2 times of your actual memory (note the count = 2X1024X1024=2097152)

dd if=/dev/zero of=/some_location_where_you_have_2gb_disk_space/myownswap.swp  bs=1024 count=2097152

Code (markup):

2. make this newfile as aswap disk :

 
mkswap /some_location_where_you_have_2gb_disk_space/myownswap.swp 

Code (markup):

3. Activate this swap to your OS

swapon /some_location_where_you_have_2gb_disk_space/myownswap.swp 

Code (markup):

4. Make this change permanent :

echo 'swapon /some_location_where_you_have_2gb_disk_space/myownswap.swp' >> /etc/rc/local 

Code (markup):