Thesql 8.0 in-memory table slow insert

MySQLmysql-8.0

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;
  1. I created the procedure to insert data.
DELIMITER $$
CREATE PROCEDURE generate_data()
BEGIN
  DECLARE i INT DEFAULT 0;
  WHILE i < 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$$

Best Answer

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 <= 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.
Related Question