Select on a big mySQL table provokes all connections to switch to status waiting for handler commit

I have an InnoDB table of about 90Gb. I've taken it out of production. Therefore only me is doing operations on it and I don't risk locking issues.

I am looking for a strategy to Select and group by to keep a summary of the data in another table and then delete all the 89Gb.

Everything I have tried seems to take too many resources: After a few minutes, all the connections get stuck on status waiting for handler commit, which is very bad. (even though no any other user queries this table) I suspect a problem in memory

So far the best I came up with is this

CREATE PROCEDURE insert_into_backup_summary()
BEGIN
    DECLARE d DATE DEFAULT DATE("2020-07-07");
    WHILE d <= "2020-10-16" DO
        INSERT INTO backup_summary
        SELECT NOW() backupDate, userId,
               DATE(createdDate) `date`, COUNT(id) count 
          FROM backup
          WHERE DATE(createdDate) >= d
            AND DATE(createdDate) < DATE_ADD(d, INTERVAL 10 DAY)
          GROUP BY userId, `date`;
        SET d = DATE_ADD(d, INTERVAL 10 DAY);
    END WHILE;
END;

But it still blocks all connections after 10-15 minutes.

I tried just a simple SELECT * FROM backup and it blocks after a few seconds, which makes me assume the issue is on the SELECT query

Any idea to keep memory fine during the process?

Solution

It sounds like your loop is causing a lot of contention on system resources. When I see threads blocked on COMMIT it's usually because the I/O is saturated. In other words, you are forcing so much data to be read from disk that nothing else can get a chance to use the disk.

I notice your conditions are going to cause table-scans for every iteration of your loop. When you use a condition like DATE(createdDate) >= d it cannot use an index on createdDate even if you have an index on that column. This will happens anytime you use a function on the indexed column. So it has to examine every row in the table instead of just rows between your date range.

To fix this, reference the functions without putting them inside functions:

WHERE createdDate >= d AND createdDate < DATE_ADD(d, INTERVAL 10 DAY)

That should work equally well for selecting the date range, and it will allow an index to be used. That should reduce the demand on the disk.

You might also want to make the loop rest a little bit in between iterations. You can do this:

    SET d = DATE_ADD(d, INTERVAL 10 DAY);
    DO SLEEP(10);
END WHILE;

This should give a chance for other threads to use the disk storage if they were blocked.

Aside from that, I could ask what type of storage you are using? Because if you have outdated or slow storage, you should take that into account when you do work on 90GB tables.

Re your comments:

@@innodb_log_file_size: 50331648 @@innodb_buffer_pool_size: 4294967296

Your log file size is 50MB which is the default. I recommend you increase this to at least 1GB. At my company, we set it to 1GB as a starting point, and that's enough for most production apps.

I'm now thinking that as you write so many records so rapidly, you're filling up your innodb log file. When the log file is full, innodb must block commits until some part of the buffer pool is flushed to the tablespace and thus frees up part of the log file to be overwritten. Increasing the size of the log file should allow more writes before you get to this state.

Your buffer pool size is 4GB, which seems small considering you have 128GB of RAM on your server. You should probably increase it by a lot, but you have to keep in mind other processes on your server that may need RAM.

I have a presentation about tuning options. I wrote the presentation when MySQL 5.5 was the current version, but it's still pretty accurate advice. https://www.slideshare.net/billkarwin/mysql-55-guide-to-innodb-status

This blog is also very good information: https://www.percona.com/blog/2016/10/12/mysql-5-7-performance-tuning-immediately-after-installation/