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):
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 |
+-----------------+
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,
This is just the beginning of improving your response time and throughput of data.