MYSQL SWAP 内存 vm.swappiness
oracle database 没有这个说法
对于 vm.swappiness 内核参数使用 0 或 1 的值存在一些混淆。
关于 vm.swappiness 的困惑来自于这样一个事实：在较旧的 Red Hat 内核中，vm.swappiness 的值为 0 会导致最少量的交换以避免内存不足的情况。在较新的内核中（从 RHEL 内核 2.6.32-303 开始），值为 0 将完全禁用交换，但值为 1 将提供最少量的交换以避免内存不足的情况。
对于专用的 MySQL 服务器，建议将 vm.swappiness 的值设置为 1。在较旧的内核中，0 和 1 之间的实际差异并不大，但 1 是避免较新内核中出现 OOM 情况的安全设置（当然，假设有一些交换空间可用）。
要将 vm.swappiness 设置为 1 并使其在重启后保持不变：
---MySQL 服务器使用的内存被内核交换出去并不罕见，这并不一定意味着存在内存泄漏或 MySQL 服务器使用了过多的内存。内核有一个名为 vm.swappiness 的设置，它控制将主内存中的页面交换到磁盘而不是从页面缓存中移出页面的积极程度。默认设置为 60，在其他正常环境中可能会导致此行为。如果您担心并且不希望系统在正常运行期间使用交换，请将 vm.swappiness 值设置为 1。使用以下命令完成此操作：
Can swap be disabled on a MySQL server?
Solution
You can disable swap on most Linux systems, however it isn't recommended unless you have an abundance of free memory.
The normal tuning guidelines given about the InnoDB buffer pool and memory usage, take into account the system automatically swapping unused pages to disk. If you disable swap, the system will not page out unused pages and therefore you may require more RAM to cover the needs of the system. You can also run afoul of the OOM-Killer job in the kernel, more easily crashing your MySQL server when any memory pressure builds up (like during backups).
考虑到系统自动将未使用的页面交换到磁盘。如果禁用交换，系统将不会将未使用的页面换出，因此您可能需要更多 RAM 来满足系统的需求。
既然您知道了风险，我建议您不要禁用交换，而是调整交换系数。swapiness
You could very well end up killing MySQL nightly when system jobs run and backups run. Cannot give you any exact numbers of how much memory you will need, you need to observe each system independently and decide if they can run safely without swap indefinitely. Disabling swap can also impact your database performance because the Kernel must reduce the disk cache size to accommodate the needs of applications. If you have any MyISAM tables, these are directly affected by page cache. Binary logging is also affected by page cache, because the kernel caches binlog writes unless you have sync_binlog enabled.
Now that you know the risks, I recommend that you don't disable swap, but adjust the swappiness factor instead.
Here is an article discussing this parameter.
Applies to:
MySQL Server - Version 5.6 and later
Information in this document applies to any platform.
Goal
How can I determine if MySQL is growing due to a memory leak?
Solution
MySQL maintains many internal buffers that grow until they reach the configured maximum size. The largest of these buffers is typically the Innodb buffer pool. On a busy server with many tables this buffer can grow to the configured size quite quickly, however on servers where the dataset is small it is possible that the buffer pool never fully initializes. In this case it can appear that MySQL has a memory leak, but in fact the the buffer pool is growing continuously over a longer period of time, to meet the configured size. Only once all buffers have been fully allocated can you evaluate whether there is a memory leak. A memory leak will look like a non-cyclic pattern of memory usage over a longer period of time; cycles typically repeat from week to week as daily query loads form a pattern. If the memory usage only grows over a multi-week period, *and* your buffers are all fully allocated, then you may have a memory leak.
To monitor the actual usage of the mysqld process, you can use a command like this:
shell> ps -ovsz,rss -p $(pidof mysqld)
(This assumes there is only one mysqld process on the host.)
Another way to monitor for memory leaks in MySQL 5.7 is to have the Performance Schema memory instruments enabled (preferably in my.cnf), then monitor the total usage and individual usages like:
To get total current memory usage:
mysql> SELECT * FROM sys.memory_global_total;
To get individual contributions:
mysql> SELECT EVENT_NAME, COUNT_ALLOC, COUNT_FREE, sys.format_bytes(SUM_NUMBER_OF_BYTES_ALLOC) AS TotalAlloc,
sys.format_bytes(SUM_NUMBER_OF_BYTES_FREE) AS TotalFree, sys.format_bytes(CURRENT_NUMBER_OF_BYTES_USED) AS CurrentUsage,
sys.format_bytes(HIGH_NUMBER_OF_BYTES_USED) AS MaxUsed
FROM performance_schema.memory_summary_global_by_event_name
WHERE CURRENT_NUMBER_OF_BYTES_USED > 0
ORDER BY CURRENT_NUMBER_OF_BYTES_USED DESC;
MySQL Server - Version 5.6 and later
Linux x86
Linux x86-64
Goal
Should I use 0 or 1 for vm.swappiness?
Solution
These instructions are valid for Red Hat Enterprise Linux and Oracle Linux systems, but should be similar for other Linux distros.
There is some confusion about using a value of 0 or 1 for the vm.swappiness kernel parameter.
The confusion about vm.swappiness comes from the fact that in older Red Hat kernels, a value of 0 for vm.swappiness resulted in the minimal amount of swapping to avoid an out of memory condition. In newer kernels (as of RHEL kernel 2.6.32-303), a value of 0 will completely disable swap, but a value of 1 will provide the minimal amount of swapping to avoid an out of memory condition.
The recommendation is to set vm.swappiness to a value of 1 for a dedicated MySQL server. There is not much practical difference between a value of 0 and a value of 1 on older kernels, but 1 is the safe setting to avoid an OOM condition on newer kernels (assuming that some swap space is available, of course).
To set vm.swappiness to 1 and make it persistent across reboots:
echo "vm.swappiness=1" >> /etc/sysctl.conf
sysctl -p
Note that for RHEL 8/OEL 8, this does not apply. Refer instead to: Doc ID 2992034.1
RHEL 9 returns to the behavior documented here.
Purpose
This document talks about Linux swapping and it's nature briefly with references to database workloads.
Scope
This document is useful for Linux and database administrators for configuring, evaluating and monitoring systems.
Details
Linux OS is a virtual memory system like any other modern operating system. The Virtual Memory Management system of Linux includes:
- Paging
- Swapping
- HugePages
- Slab allocator
- Shared memory
When almost all of the available physical memory (RAM) is started to be used in Linux, the kernel will start to swap out pages to the swap (disk space), or worse it may start swapping out entire processes. One another scenario is that it starts killing processes using the Out-of-Memory (OOM) Killer (See Document 452000.1)
Swap Usage on Linux
To check swap usage on Linux use one of below:
- free: Seek for low (or zero) values for Swap / used:
# free -m
total used free shared buffers cached
Mem: 4018 3144 873 0 66 2335
-/+ buffers/cache: 742 3276
Swap: 4690 0 4690
- meminfo: Seek for SwapTotal = SwapFree
# grep Swap /proc/meminfo
SwapCached: 0 kB
SwapTotal: 4803392 kB
SwapFree: 4803380 kB
- top: Look for low (preferably zero) values of Swap / used:
# top
...
Mem: 4115320k total, 3219408k used, 895912k free, 68260k buffers
Swap: 4803392k total, 12k used, 4803380k free, 2390804k cached
...
- vmstat: Look for si / so values to be zero:
# vmstat
procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
0 0 12 871592 69308 2405188 0 0 103 36 275 500 14 13 71 1
Why is Swapping Bad
Especially on Linux try to avoid swapping because:
- The swapping potentially makes every memory page access a thousand (or more) times slower (and Linux swapping mechanism is not specifically fast).
- As more memory swapped, more operations take longer time
- As operations take longer time, more requests come in to be served
- The demand for resources exponentially increase
Due to scenario above, if any memory bound application is running (like a database), if swapping is started, most of the time there is no recovering back.
The Oracle Database SGA pages are pageable on Linux by default, and potentially those pages can be swapped out if system runs out of memory. Using HugePages is one of the methods to make the Oracle SGA not to be swapped out at all, still one needs to be careful about the configuration. To learn all about HugePages please read Document 361323.1 and references.
Conclusions
- Make sure total SGA, PGA fit in the RAM also leaving some decent memory for process spaces and system services. See the database installation guides for more information
- Consider using HugePages on Linux
- Be very careful with memory configuration (HugePages, Automatic Memory Management, Swap, VLM)
- Monitor OS continuously for memory usage and swapping