mysql 8.0 in-memory table slow insert Please help with following strange issue.I defined in memory table but insert of 600.000 rows takes 13 minutes and causes heavy write to disk. The CPU is underutilized and I have enough free memory. What's wrong and why I see the I/O?
1. I defined max_heap_table_size as 1GB and restarted the DB.
2. I created the table as:

CREATE TABLE `data`
     (
       `id`         bigint(20)       DEFAULT NULL,
       `datetime`   timestamp        DEFAULT CURRENT_TIMESTAMP,
       `channel`    int(11)          DEFAULT NULL,
       `value`      bigint(20)       DEFAULT NULL
     ) ENGINE = MEMORY;



I created the procedure to insert data.



DELIMITER $$
CREATE PROCEDURE generate_data()
BEGIN
  DECLARE i INT DEFAULT 0;
  WHILE i &lt; 600001 DO
    INSERT INTO `data` (`id`, `datetime`,`value`,`channel`) VALUES (
      i,
      FROM_UNIXTIME(UNIX_TIMESTAMP('2014-01-01 01:00:00')+FLOOR(RAND()*31536000)),
      ROUND(RAND()*100,2),
      i
    );
    SET i = i + 1;
  END WHILE;
END$$
Look SHOW PROCESSLIST - the most time the process state is 'waiting for handler commit'.

If your server version is 8 use recursive CTE instead:

INSERT INTO `data` (`id`, `datetime`,`value`,`channel`)
WITH recursive 
cte AS (select 1 i
        union all
        select i+1 from cte where i &lt;= 600000)
select i, 
       FROM_UNIXTIME(UNIX_TIMESTAMP('2014-01-01 01:00:00')+FLOOR(RAND()*31536000)),
       ROUND(RAND()*100,2),
       i
FROM cte;


If you have 5+ server, use synthetic number tables:

INSERT INTO `data` (`id`, `datetime`,`value`,`channel`)
select i, 
       FROM_UNIXTIME(UNIX_TIMESTAMP('2014-01-01 01:00:00')+FLOOR(RAND()*31536000)),
       ROUND(RAND()*100,2),
       i
FROM (  SELECT 1+d1.num+d2.num*10+d3.num*100+d4.num*1000+d5.num*10000+d6.num*100000 i
        FROM (SELECT 0 num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) d1,
             (SELECT 0 num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) d2,
             (SELECT 0 num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) d3,
             (SELECT 0 num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) d4,
             (SELECT 0 num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) d5,
             (SELECT 0 num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) d6
      ) d0;


At the same system (server version 8.0.18):


your procedure - over 20 min. (killed after 5 min, 122k records were inserted)
insert with CTE - 3.10 sec.
insert with synth. tables - 4.08 sec.