Mysql
MySQL 在具有足夠 RAM 的系統上交換
我的新雙 AMD EPYC 7401 24 核 (128G RAM) MySQL 即使在記憶體中沒有數據的情況下重新啟動後也開始交換。
在
# numactl --hardware available: 8 nodes (0-7) node 0 cpus: 0 1 2 3 4 5 48 49 50 51 52 53 node 0 size: 32114 MB node 0 free: 198 MB node 1 cpus: 6 7 8 9 10 11 54 55 56 57 58 59 node 1 size: 32232 MB node 1 free: 124 MB node 2 cpus: 12 13 14 15 16 17 60 61 62 63 64 65 node 2 size: 0 MB node 2 free: 0 MB node 3 cpus: 18 19 20 21 22 23 66 67 68 69 70 71 node 3 size: 0 MB node 3 free: 0 MB node 4 cpus: 24 25 26 27 28 29 72 73 74 75 76 77 node 4 size: 32253 MB node 4 free: 136 MB node 5 cpus: 30 31 32 33 34 35 78 79 80 81 82 83 node 5 size: 32227 MB node 5 free: 212 MB node 6 cpus: 36 37 38 39 40 41 84 85 86 87 88 89 node 6 size: 0 MB node 6 free: 0 MB node 7 cpus: 42 43 44 45 46 47 90 91 92 93 94 95 node 7 size: 0 MB node 7 free: 0 MB node distances: node 0 1 2 3 4 5 6 7 0: 10 16 16 16 32 32 32 32 1: 16 10 16 16 32 32 32 32 2: 16 16 10 16 32 32 32 32 3: 16 16 16 10 32 32 32 32 4: 32 32 32 32 10 16 16 16 5: 32 32 32 32 16 10 16 16 6: 32 32 32 32 16 16 10 16 7: 32 32 32 32 16 16 16 10MySQL 配置
# cat /etc/mysql/my.cnf # Ansible managed [client] port = 3306 socket = /run/mysqld/mysqld.sock ssl_cert = /etc/mysql/client-cert.pem ssl_key = /etc/mysql/client-key.pem [mysqld_safe] socket = /run/mysqld/mysqld.sock nice = 0 flush_caches = 1 [mysqld] user = mysql socket = /run/mysqld/mysqld.sock pid_file = /run/mysqld/mysqld.pid port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp lc_messages_dir = /usr/share/mysql general_log = 0 general_log_file = /var/log/mysql/mysql.log log_error = /var/log/mysql/error.log log_queries_not_using_indexes = 0 slow_query_log = 0 slow_query_log_file = /var/log/mysql/mysql-slow.log skip_external_locking = 1 skip_name_resolve = 1 max_connections = 1500 back_log = 1024 wait_timeout = 28800 interactive_timeout = 28800 sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION character_set_server = utf8 collation_server = utf8_general_ci skip-character-set-client-handshake init_connect = 'SET collation_connection = utf8_general_ci; SET NAMES utf8;' default_storage_engine = InnoDB key_buffer_size = 32M myisam_recover_options = FORCE,BACKUP thread_stack = 256K thread_cache_size = 750 query_cache_type = 0 query_cache_limit = 2M query_cache_size = 64M max_allowed_packet = 256M group_concat_max_len = 256M tmp_table_size = 256M max_heap_table_size = 64M open_files_limit = 65535 innodb_open_files = 8192 table_definition_cache = 8192 table_open_cache = 8192 innodb_buffer_pool_size = 64G innodb_log_file_size = 4G innodb_log_buffer_size = 128M innodb_flush_log_at_trx_commit = 2 innodb_flush_method = O_DIRECT innodb_thread_concurrency = 0 innodb_write_io_threads = 12 innodb_read_io_threads = 12 innodb_io_capacity = 7500 innodb_numa_interleave = 1 innodb_file_per_table innodb_stats_on_metadata = 0 innodb_flush_neighbors = 0 performance_schema = 0 ssl_ca = /etc/mysql/ca-cert.pem ssl_cert = /etc/mysql/server-cert.pem ssl_key = /etc/mysql/server-key.pem [mysqldump] quick quote-names max_allowed_packet = 256M [isamchk] key_buffer_size = 32M據我所知，有一些 NUMA 節點沒有剩餘 RAM，這就是交換的原因嗎？我怎樣才能防止這種情況（例如 NUMA 似乎在 MySQL 中配置）？
雖然機器正在交換，但性能似乎相當不錯。我需要擔心 MySQL 會被殺死嗎？
使用記憶體的程序
# for i in $(ls -d /proc/[0-9]*) > do > out=$(grep Swap $i/status 2>/dev/null) > if [ "x$(echo $out | awk '{print $2}')" != "x0" ] && [ "x$(echo $out | awk '{print $2}')" != "x" ] > then > echo "$(ps -p $(echo $i | cut -d'/' -f3) \ > | tail -n 1 | awk '{print $4'}): $(echo $out | awk '{print $2 $3}')" > fi > done systemd-journal: 92kB perl: 1364kB /usr/bin/pt-dis: 1452kB sshd: 8kB bash: 76kB mysqld: 204436kB sshd: 4kB bash: 24kB
交換設置
vm.swappiness=1（已填充）記憶體插槽數
# dmidecode -t memory | grep -i size Size: No Module Installed Size: No Module Installed Size: No Module Installed Size: No Module Installed Size: 8192 MB Size: 8192 MB Size: 8192 MB Size: 8192 MB Size: No Module Installed Size: No Module Installed Size: 8192 MB Size: 8192 MB Size: 8192 MB Size: 8192 MB Size: No Module Installed Size: No Module Installed Size: No Module Installed Size: No Module Installed Size: No Module Installed Size: No Module Installed Size: 8192 MB Size: 8192 MB Size: 8192 MB Size: 8192 MB Size: No Module Installed Size: No Module Installed Size: 8192 MB Size: 8192 MB Size: 8192 MB Size: 8192 MB Size: No Module Installed Size: No Module Installedlshw（記憶體）輸出
# lshw -class memory *-firmware description: BIOS vendor: HPE physical id: 5 version: A40 date: 01/25/2019 size: 64KiB capacity: 15MiB capabilities: pci pnp upgrade shadowing escd cdboot bootselect edd int13floppy360 int13floppy1200 int13floppy720 int5printscreen int9keyboard int14serial int17printer int10video acpi usb biosbootspecification netboot uefi *-memory:0 description: System Memory physical id: 15 slot: System board or motherboard *-bank:0 description: DIMM Synchronous [empty] product: NOT AVAILABLE vendor: UNKNOWN physical id: 0 slot: PROC 1 DIMM 1 width: 64 bits *-bank:1 description: DIMM Synchronous [empty] product: NOT AVAILABLE vendor: UNKNOWN physical id: 1 slot: PROC 1 DIMM 2 width: 64 bits *-bank:2 description: DIMM Synchronous [empty] product: NOT AVAILABLE vendor: UNKNOWN physical id: 2 slot: PROC 1 DIMM 3 width: 64 bits *-bank:3 description: DIMM Synchronous [empty] product: NOT AVAILABLE vendor: UNKNOWN physical id: 3 slot: PROC 1 DIMM 4 width: 64 bits *-bank:4 description: DIMM Synchronous 2400 MHz (0.4 ns) product: NOT AVAILABLE vendor: UNKNOWN physical id: 4 slot: PROC 1 DIMM 5 size: 8GiB width: 64 bits clock: 2400MHz (0.4ns) *-bank:5 description: DIMM Synchronous 2400 MHz (0.4 ns) product: NOT AVAILABLE vendor: UNKNOWN physical id: 5 slot: PROC 1 DIMM 6 size: 8GiB width: 64 bits clock: 2400MHz (0.4ns) *-bank:6 description: DIMM Synchronous 2400 MHz (0.4 ns) product: NOT AVAILABLE vendor: UNKNOWN physical id: 6 slot: PROC 1 DIMM 7 size: 8GiB width: 64 bits clock: 2400MHz (0.4ns) *-bank:7 description: DIMM Synchronous 2400 MHz (0.4 ns) product: NOT AVAILABLE vendor: UNKNOWN physical id: 7 slot: PROC 1 DIMM 8 size: 8GiB width: 64 bits clock: 2400MHz (0.4ns) *-bank:8 description: DIMM Synchronous [empty] product: NOT AVAILABLE vendor: UNKNOWN physical id: 8 slot: PROC 1 DIMM 9 width: 64 bits *-bank:9 description: DIMM Synchronous [empty] product: NOT AVAILABLE vendor: UNKNOWN physical id: 9 slot: PROC 1 DIMM 10 width: 64 bits *-bank:10 description: DIMM Synchronous 2400 MHz (0.4 ns) product: NOT AVAILABLE vendor: UNKNOWN physical id: a slot: PROC 1 DIMM 11 size: 8GiB width: 64 bits clock: 2400MHz (0.4ns) *-bank:11 description: DIMM Synchronous 2400 MHz (0.4 ns) product: NOT AVAILABLE vendor: UNKNOWN physical id: b slot: PROC 1 DIMM 12 size: 8GiB width: 64 bits clock: 2400MHz (0.4ns) *-bank:12 description: DIMM Synchronous 2400 MHz (0.4 ns) product: NOT AVAILABLE vendor: UNKNOWN physical id: c slot: PROC 1 DIMM 13 size: 8GiB width: 64 bits clock: 2400MHz (0.4ns) *-bank:13 description: DIMM Synchronous 2400 MHz (0.4 ns) product: NOT AVAILABLE vendor: UNKNOWN physical id: d slot: PROC 1 DIMM 14 size: 8GiB width: 64 bits clock: 2400MHz (0.4ns) *-bank:14 description: DIMM Synchronous [empty] product: NOT AVAILABLE vendor: UNKNOWN physical id: e slot: PROC 1 DIMM 15 width: 64 bits *-bank:15 description: DIMM Synchronous [empty] product: NOT AVAILABLE vendor: UNKNOWN physical id: f slot: PROC 1 DIMM 16 width: 64 bits *-memory:1 description: System Memory physical id: 16 slot: System board or motherboard *-bank:0 description: DIMM Synchronous [empty] product: NOT AVAILABLE vendor: UNKNOWN physical id: 0 slot: PROC 2 DIMM 1 width: 64 bits *-bank:1 description: DIMM Synchronous [empty] product: NOT AVAILABLE vendor: UNKNOWN physical id: 1 slot: PROC 2 DIMM 2 width: 64 bits *-bank:2 description: DIMM Synchronous [empty] product: NOT AVAILABLE vendor: UNKNOWN physical id: 2 slot: PROC 2 DIMM 3 width: 64 bits *-bank:3 description: DIMM Synchronous [empty] product: NOT AVAILABLE vendor: UNKNOWN physical id: 3 slot: PROC 2 DIMM 4 width: 64 bits *-bank:4 description: DIMM Synchronous 2400 MHz (0.4 ns) product: NOT AVAILABLE vendor: UNKNOWN physical id: 4 slot: PROC 2 DIMM 5 size: 8GiB width: 64 bits clock: 2400MHz (0.4ns) *-bank:5 description: DIMM Synchronous 2400 MHz (0.4 ns) product: NOT AVAILABLE vendor: UNKNOWN physical id: 5 slot: PROC 2 DIMM 6 size: 8GiB width: 64 bits clock: 2400MHz (0.4ns) *-bank:6 description: DIMM Synchronous 2400 MHz (0.4 ns) product: NOT AVAILABLE vendor: UNKNOWN physical id: 6 slot: PROC 2 DIMM 7 size: 8GiB width: 64 bits clock: 2400MHz (0.4ns) *-bank:7 description: DIMM Synchronous 2400 MHz (0.4 ns) product: NOT AVAILABLE vendor: UNKNOWN physical id: 7 slot: PROC 2 DIMM 8 size: 8GiB width: 64 bits clock: 2400MHz (0.4ns) *-bank:8 description: DIMM Synchronous [empty] product: NOT AVAILABLE vendor: UNKNOWN physical id: 8 slot: PROC 2 DIMM 9 width: 64 bits *-bank:9 description: DIMM Synchronous [empty] product: NOT AVAILABLE vendor: UNKNOWN physical id: 9 slot: PROC 2 DIMM 10 width: 64 bits *-bank:10 description: DIMM Synchronous 2400 MHz (0.4 ns) product: NOT AVAILABLE vendor: UNKNOWN physical id: a slot: PROC 2 DIMM 11 size: 8GiB width: 64 bits clock: 2400MHz (0.4ns) *-bank:11 description: DIMM Synchronous 2400 MHz (0.4 ns) product: NOT AVAILABLE vendor: UNKNOWN physical id: b slot: PROC 2 DIMM 12 size: 8GiB width: 64 bits clock: 2400MHz (0.4ns) *-bank:12 description: DIMM Synchronous 2400 MHz (0.4 ns) product: NOT AVAILABLE vendor: UNKNOWN physical id: c slot: PROC 2 DIMM 13 size: 8GiB width: 64 bits clock: 2400MHz (0.4ns) *-bank:13 description: DIMM Synchronous 2400 MHz (0.4 ns) product: NOT AVAILABLE vendor: UNKNOWN physical id: d slot: PROC 2 DIMM 14 size: 8GiB width: 64 bits clock: 2400MHz (0.4ns) *-bank:14 description: DIMM Synchronous [empty] product: NOT AVAILABLE vendor: UNKNOWN physical id: e slot: PROC 2 DIMM 15 width: 64 bits *-bank:15 description: DIMM Synchronous [empty] product: NOT AVAILABLE vendor: UNKNOWN physical id: f slot: PROC 2 DIMM 16 width: 64 bits *-cache:0 description: L1 cache physical id: 81 slot: L1-Cache size: 2304KiB capacity: 2304KiB clock: 1GHz (1.0ns) capabilities: pipeline-burst internal write-back unified configuration: level=1 *-cache:1 description: L2 cache physical id: 82 slot: L2-Cache size: 12MiB capacity: 12MiB clock: 1GHz (1.0ns) capabilities: pipeline-burst internal write-back unified configuration: level=2 *-cache:2 description: L3 cache physical id: 83 slot: L3-Cache size: 64MiB capacity: 64MiB clock: 1GHz (1.0ns) capabilities: pipeline-burst internal write-back unified configuration: level=3 *-cache:3 description: L1 cache physical id: 85 slot: L1-Cache size: 2304KiB capacity: 2304KiB clock: 1GHz (1.0ns) capabilities: pipeline-burst internal write-back unified configuration: level=1 *-cache:4 description: L2 cache physical id: 86 slot: L2-Cache size: 12MiB capacity: 12MiB clock: 1GHz (1.0ns) capabilities: pipeline-burst internal write-back unified configuration: level=2 *-cache:5 description: L3 cache physical id: 87 slot: L3-Cache size: 64MiB capacity: 64MiB clock: 1GHz (1.0ns) capabilities: pipeline-burst internal write-back unified configuration: level=3 *-memory:2 UNCLAIMED physical id: 0 *-memory:3 UNCLAIMED physical id: 1
很高興認識你，米莎。我們親自討論過這個問題，但我想我會在這裡提出一些想法供其他人參考，我們可以在這裡繼續討論。;)
查看上面提供的資訊，對我來說最突出的是您總共有 8 個 NUMA 節點，但其中只有 4 個附加了記憶體。我對 AMD EPYC 架構了解不多，所以我正在實時學習，但這通常是一個糟糕的配置，並且可能會導致問題。
特別是我不確定 Linux 的 NUMA 交錯節點在跨所有節點交錯時的行為如何，但並非所有節點實際上都有記憶體——我認為你可以很容易地進入有趣的邊緣情況，無論是性能還是交換。
嘗試一些事情並看看性能和行為有何不同會很有趣：
- 用於
numactl限制mysqld僅在實際具有記憶體的節點上交錯，這應該是例如numactl --interleave=0,1,4,5- 調整您的物理記憶體配置以使用兩倍大小的 DIMM。我猜您現在有 8 x 16 GiB DIMM，因此您可能會嘗試使用 16 x 8 GiB DIMM 的配置，填充所有DIMM 插槽。
- 調整物理記憶體配置以使用兩倍的 RAM 也可能很有趣（至少作為測試），只需使用目前大小的 DIMM 填充所有插槽。
- 再次假設您目前使用的是 8 x 16 GiB DIMM，調整您的物理記憶體配置以填充每個通道（也是每個 NUMA 節點）一個 DIMM 插槽，而不是單個通道中的兩個 DIMM，並且某些通道未填充。看起來 AMD 在其針對 AMD EPYC 處理器的記憶體填充指南中也推薦了這一點。
我很想知道這最終的結果是什麼。
numactl --interleave=all. 或者：innodb_numa_interleave=1，從 5.6.27 開始作為 MySQL VARIABLE 可用。更多討論：http: //mysql.rjweb.org/doc.php/memory#numa我可能是作業系統決定駐留在“第一個”核心中，並且在該核心上掛起的記憶體空間不足。通過使用“交錯”，您可以聲明 RAM 確實分佈在所有核心中。這對於單一用途的機器是合理的，例如專用的數據庫伺服器。如果您嘗試將某些程序專用於某些核心並為它們提供相鄰的記憶體（以獲得輕微的性能提升），則非交錯可能是有意義的。