How to avoid using swap in MySQL

When the amount of physical memory does not fit the data, the OS writes the data in virtual memory to disk, where the exchange is transparent to the process running in the operating system.

The exchange pair has a great effect on MySQL performance, and for the InnoDB storage engine It locks the global mutex on the data in memory, and if this results in the IO of the disk, then all the actions will wait until IO is complete.

We can pass the Vmstat command. Check the IO change status of the S1,s0.

If file exchange is completely banned, in extreme cases, the system core forces an exchange, which can degrade OS performance. Copying a large file, using mysqldump to export a large database, the file system will often request a large amount of memory for Linux as cache, when MySQL may run out of memory, crash, or be forced to kill by the operating system.

Adjustments can be made in the following ways:

1,/proc/sys/vm/swappiness content to be changed to 0 (temporary),/etc/sysctl.conf add vm.swappiness=0 (permanent). So when the memory is tight, the lower the data value Linux tends to use physical memory.

2, modify the MySQL refresh Disk method:

For the InnoDB storage engine, its own caching for data and indexing cache management we can consider that MySQL's own process to manage IO buffer cache caching mechanism and write latency, which can also reduce the overhead of the system. You can use raw partitions to implement (this is a good example) or Innodb_flush_method, open o_direct mode. In this case, the INNODB buffer pool will use direct IO to bypass the swap to access the disk, redo log will still use swap, because redo log is overwrite mode, so need less space. An option similar to O_direct is O_sync, which is only valid for write data.

3, add MySQL configuration parameters Memlock

Individuals do not recommend this approach, this will lock MySQL into memory, so that although the exchange is avoided, if there is not enough memory to lock, then the MySQL in the attempt to allocate more memory will crash, if locked too much memory, left to the operating system is not enough memory, the same problem.

This column more highlights: http://www.bianceng.cn/database/MySQL/