MySQL often hangs, suspect memory is not enough, plus swap optimization

Title, recently http://www.12reads.cn/MySQL often hangs, recently hangs the log:

At time:wed Dec 7 09:15:01 CST 2016:mysql error STOP. At time:wed Dec 7 09:30:01 CST 2016:mysql error STOP. At time:wed Dec 7 10:00:02 CST 2016:mysql error STOP. At Time:mon Dec 04:30:01 CST 2016:mysql error STOP. At Time:mon Dec 04:45:01 CST 2016:mysql error STOP. At Time:mon Dec 05:00:01 CST 2016:mysql error STOP. At Time:mon Dec 05:15:01 CST 2016:mysql error STOP. At Time:mon Dec 05:30:01 CST 2016:mysql error STOP. At Time:mon Dec 05:45:01 CST 2016:mysql error STOP. At Time:mon Dec 06:00:01 CST 2016:mysql error STOP. At Time:mon Dec 06:15:01 CST 2016:mysql error STOP. At Time:mon Dec 06:30:01 CST 2016:mysql error STOP. At Time:mon Dec 06:45:01 CST 2016:mysql error STOP. At Time:mon Dec 07:00:01 CST 2016:mysql error STOP. At Time:mon Dec 07:15:01 CST 2016:mysql error STOP. At Time:mon Dec 07:30:02 CST 2016:mysql error STOP. At Time:mon Dec 07:45:01 CST 2016:mysql error STOP. At Time:mon Dec 08:00:02 CST 2016:mysql error STOP. At Time:mon Dec 08:15:01 CST 2016:mysql error STOP. At Time:mon Dec 08:30:01 CST 2016:mysql error STOP. At Time:mon Dec 08:45:01 CST 2016:mysql error STOP. At Time:mon Dec 09:00:01 CST 2016:mysql error STOP. At Time:mon Dec 09:15:02 CST 2016:mysql error STOP. At Time:mon Dec 09:30:01 CST 2016:mysql error STOP. At Time:mon Dec 09:45:01 CST 2016:mysql error STOP. At Time:mon Dec 10:00:01 CST 2016:mysql error STOP. At Time:tue Dec 23:15:02 CST 2016:mysql error STOP. At Time:tue Dec 23:30:01 CST 2016:mysql error STOP. At Time:tue Dec 23:45:01 CST 2016:mysql error STOP. At Time:thu Dec 11:56:04 CST 2016:mysql error STOP. At Time:thu Dec 21:26:14 CST 2016:mysql error STOP. At Time:thu Dec 21:56:12 CST 2016:mysql error STOP. At Time:sat Dec 12:02:17 CST 2016:mysql error STOP. At Time:sat Dec 12:08:01 CST 2016:mysql error STOP. At Time:sat Dec 12:10:01 CST 2016:mysql error STOP.

While viewing the MySQL error log, you are prompted:

[ERROR] Innodb:unable to Lock/usr/local/mysql/var/ibdata1, error:11

Error 11 is a resource that is temporarily unavailable in MySQL.

Find a bunch of reasons did not, best think is not a memory bottleneck?

See the next server has not opened the swap, so opened the next, observe to see if the situation has changed.

Here are the steps to open swap: (http://www.12reads.cn/is currently 2G memory, so I chose bs=1m,count=2048, which is to allocate 2G to swap.) )

1. Create a file for the swap partition:

 
   
  

  
    
   
dd if=/dev/zero of=/mnt/swap bs=block_size count=number_of_block 
 
   
  

Note : block_size, Number_of_block size can be customized, such as bs=1m count=1024 representative set 1G size SWAP partition.

2. Set the swap partition file:

 
   
  

  
    
   
mkswap /mnt/swap
 
   
  

3. Enable swap partition files now

 
   
  

  
    
   
swapon /mnt/swap
 
   
  

Note : If you have swapoff-a in/etc/rc.local, you need to modify it to swapon-a

4. Set the SWAP partition from the start-up:

You need to modify the SWAP lines in the file/etc/fstab to add

 
   
  

  
    
   
/mnt/swap swap swap defaults 0 0
 
   
  

Note : The/mnt/swap path can be modified to be configured according to the specific path of the swap file created.

5. Modify Swpapiness Parameters

In a Linux system, you can determine how the system uses the SWAP partition by looking at the value of the/proc/sys/vm/swappiness content. When the value of the swappiness content is 0 o'clock, the SWAP partition is used when the physical memory is used to maximize physical memory. When the value of the swappiness content is 100, the swap partition is actively used, and the in-memory data is replaced in a timely manner to the swap partition.

To see 0 before the modification, the SWAP partition will be used only after the physical memory is used:

You can temporarily modify this parameter by using the following method, assuming that we are configured to use a SWAP partition if we have less than 10% free memory:

 
   
  

  
    
   
echo 10 >/proc/sys/vm/swappiness
 
   
  

If you need to permanently modify this configuration, after the system restarts, you can modify the/etc/sysctl.conf file and add the following:

 
   
  

  
    
   
# vim /etc/sysctl.conf
  
    
   
vm.swappiness=10
  
    
   
# sysctl -p
 
   
  

Close SWAP

When the system is out of memory, switching on swap may cause the IO performance to degrade due to frequent paging operations. If you want to close SWAP, you can use the following method.

1,free-m query swap partition settings:

2. Use command Swapoff to close SWAP, for example:

 
   
  

  
    
   
swapoff /mnt/swap 
 
   
  

3. Modify the/etc/fstab file, delete or comment the relevant configuration, cancel the SWAP auto-mount:

4. Confirm that SWAP is closed by free-m .

5, swappiness parameter adjustment:

You can temporarily modify this parameter by using the following method, which is configured as 0%:

 
   
  

  
    
   
echo 0 >/proc/sys/vm/swappiness 
 
   
  

If you need to permanently modify this configuration, after the system restarts, you can modify the/etc/sysctl.conf file and add the following:

 
   
  

  
    
   
# vim /etc/sysctl.conf
  
    
   
vm.swappiness=0
  
    
   
# sysctl -p
 
   
  

MySQL often hangs, suspect memory is not enough, plus swap optimization