InnoDB cluster memory issue with LOAD DATA

Question

I need to create a new table with ~8M rows without causing too much noise for other services using the cluster. I split the data into chunks of 100k rows and use LOAD DATA LOCAL FILE, sleep 5s and repeat. Mysql starts to consume all memory until OOM kills mysqld on the cluster nodes.

What am I doing wrong??

Any help or ideas on how to progress are welcome, thanks!

InnoDB Cluster 8.0.24, Ubuntu 20.04 on three virtual hosts (same behavior for 8.0.23). 6 CPU cores, 12GB memory each. The only configuration change from default is local_infile = ON.

Additional info as kindly requested by @Wilson (links to pastebin):

1. Primary, with no memory issue
2. Secondary, with memory issue

My table I'm loading data into:

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

How I load data:

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')

Some memory related info:

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

Rate Per Second = RPS

Suggestions to consider for avoiding OOM on your Secondary instance,

HTOP additional discussion to achieve Sleeping tasks reduction

Show Full Process list indicates event_scheduler is 'ON' and appears to always be idle. Please post results of SHOW GLOBAL STATUS LIKE 'com%event%'; for confirmation.

ulimit -a report indicates Open Files limited to 1024 and yet MySQL instance is asking for open_files_limit of 10,000. Additional discussion needed.

In your SECONDARY my.cnf [mysqld] section, consider these possible values to improve performance,

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

This is just the beginning of improving your response time and throughput of data.