1

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$$
  • Is there no PRIMARY KEY? – Rick James Jan 3 at 18:54
  • Please provide SHOW CREATE TABLE data -- I wonder if the table was forced to be InnoDB. – Rick James Jan 3 at 22:30
  • The table DEFINITION is stored in the Data Dictionary and this could be the reason you observe IO. – Wilson Hauck Jan 6 at 19:34
2

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.
  • Perfect answer, anyway the question still open: why I see the I/O? it should be "In memory".. When I restart the server the data is gone, so it's truly in memory. – Yuri Lansberg Dec 30 '19 at 12:52
  • @YuriLansberg why I see the I/O? it should be "In memory" Table locks, which occures each separate INSERT... transactions (each separate INSERT is a transaction) with their undo buffer... and everything must be written into system tablespace... – Akina Dec 30 '19 at 16:32
  • @Akina - I don't think "undo" is the answer --- ENGINE=MEMORY is non-transactional. – Rick James Jan 3 at 18:50
  • @RickJames 'waiting for handler commit' state means that there are transactions during the process. I don't know where the term "commit" can be applied without reference to transactions. – Akina Jan 3 at 19:15
  • @Akina - Perhaps the "handler" tells the Engine that it is time for a "commit". Then the Engine has the option to do something with it (as in InnoDB), or simply ignore it (MyISAM or MEMORY). That is, the "handler", which is engine-agnostic, does not care. – Rick James Jan 3 at 21:54
0

If, for unknown reasons, the table is really ENGINE=InnoDB, then you can speed it up this way:

BEGIN;
CALL generate_data()
COMMIT.

If you have overcommitted RAM, then the I/O is caused by swapping. To fix this, please provide: RAM size, other apps running, innodb_buffer_pool_size, and any other settings you increased. We can advise then.

  • No swapping for sure, I have gigs of free ram and no swap defined. – Yuri Lansberg Jan 14 at 17:51
  • Some benchmarks have shown InnoDB to be faster than MEMORY. I don't have details, nor do I remember if they were doing the same thing you are doing. – Rick James Jan 14 at 19:02
  • I disagree, those benchmark are based on parallel in-memory table updates. I always update my in-memory table in 1 thread. The most important for me is select, that runs very fast as it's no chance to read from the disk. – Yuri Lansberg Jan 19 at 11:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.