带有负载数据的InnoDB集群内存问题

Question

我需要创建一个具有8m行的新表，而不会对使用集群的其他服务造成太大的干扰。我将数据分割成100 k行的块，并使用LOAD DATA LOCAL FILE、睡眠5s和重复。Mysql开始消耗所有内存，直到OOM杀死集群节点上的mysqld为止。

我做错什么了？？

任何关于如何进步的帮助或想法都是受欢迎的，谢谢！

三个虚拟主机上的InnoDB集群8.0.24，Ubuntu20.04(8.0.23的行为相同)。6个CPU核心，每个12 CPU内存。默认情况下唯一的配置更改是local_infile = ON。

@Wilson (与pastebin的链接)所要求的其他信息：

1. 主，没有内存问题
2. 次要的，有内存问题

我要将数据加载到的表中：

CREATE TABLE IF NOT EXISTS `RefdataSnapshotM_20210421140303` (
  `SERVICE_INFO`             VARCHAR(64) NOT NULL,
  `PORTING_TIME`             TIMESTAMP   DEFAULT NULL,
  `IDN`                      VARCHAR(64) NOT NULL,
  `PORTING_ID`               VARCHAR(64) NOT NULL,
  `RECIPIENT_SP_ID`          CHAR(3)     NOT NULL,
  `DONOR_SP_ID`              CHAR(3)     NOT NULL,
  `RECIPIENT_ROUTING_NUMBER` CHAR(3)     NOT NULL,
  `DONOR_ROUTING_NUMBER`     CHAR(3)     NOT NULL,
  `PORTING_INDICATOR`        VARCHAR(64) NOT NULL,
  `CREATION_DATE`            TIMESTAMP   DEFAULT NULL,
  PRIMARY KEY  (`IDN`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

如何加载数据：

LOAD DATA
  LOCAL INFILE '/tmp/datasource.dat'
  INTO TABLE `RefdataSnapshotM_20210421140303`
  CHARACTER SET utf8mb4
  (
    `SERVICE_INFO`,
    @var_PORTING_TIME,
    @var_IDN,
    `PORTING_ID`,
    `RECIPIENT_SP_ID`,
    `DONOR_SP_ID`,
    `RECIPIENT_ROUTING_NUMBER`,
    `DONOR_ROUTING_NUMBER`,
    `PORTING_INDICATOR`,
    @var_CREATION_DATE
  )
  SET  `PORTING_TIME` = STR_TO_DATE(@var_PORTING_TIME, GET_FORMAT(DATETIME, 'INTERNAL')),
                `IDN` = REGEXP_REPLACE(@var_IDN, '^46', '', 1),
      `CREATION_DATE` = STR_TO_DATE(@var_CREATION_DATE, '%d-%m-%Y %H:%i:%s')

一些与内存相关的信息：

SELECT total_allocated DIV total_allocated 1024 AS FROM sys.x$memory_global_total
+-----------------+
| total_allocated |
+-----------------+
| 11366374 kB     |
+-----------------+
cat /proc/<pid_of_mysql_here>/smaps_rollup
Rss:            11784296 kB
Pss:            11783545 kB
Pss_Anon:       11767256 kB
Pss_File:          16289 kB
Pss_Shmem:             0 kB
Shared_Clean:        952 kB
Shared_Dirty:          0 kB
Private_Clean:     16196 kB
Private_Dirty:  11767148 kB
Referenced:     11123016 kB
Anonymous:      11767256 kB
LazyFree:              0 kB
AnonHugePages:         0 kB
ShmemPmdMapped:        0 kB
FilePmdMapped:         0 kB
Shared_Hugetlb:        0 kB
Private_Hugetlb:       0 kB
Swap:             371376 kB
SwapPss:          371376 kB
Locked:                0 kB

SELECT SUM(current_alloc) AS total_allocated
  FROM sys.x$memory_global_by_current_bytes
UNION
SELECT total_allocated
  FROM sys.x$memory_global_total;
+-----------------+
| total_allocated |
+-----------------+
|      1344314202 |
|      1344251938 |
+-----------------+

Answer 1

每秒速率= RPS

关于避免第二次执行OOM的建议，

HTOP实现睡眠任务减少的附加讨论

显示完整的进程列表指示event_scheduler是'ON‘，并且看起来总是空闲的。请发布显示全局状态的结果，如'com%event%'；以进行确认。

ulimit -a报告指出，开放文件限制在1024，但MySQL实例要求的open_files_limit为10,000。需要进一步讨论。

在第二个my.cnf mysqld部分中，考虑这些可能的值以提高性能，

read_rnd_buffer_size=128K  # from 256K to reduce handler_read_rnd_next RPS of 139
max_connections=64  # from default of 151 since max_used_connections was 19 in 2+ days
innodb_lru_scan_depth=100  # from 1024 to conserve 90% of cpu cycles used for function
innodb_log_buffer_size=96M  # from 16M for ~ 30 minutes bufffering before write to log
innodb_change_buffer_max_size=50  # from 25 (percent) to reduce time required to LOAD data to tables
innodb_concurrency_tickets=20000  # from 5000 to reduce reque by extending time allowed for inserts

这只是提高响应时间和数据吞吐量的开始。