2

I want to cleanup one of my tables which has 4 million. So I prepare this query to clean it. But it's throwing an error, can anyone please help me to fix this?

DELIMITER $$
DROP PROCEDURE IF EXISTS archive_table $$
create procedure archive_table () 
set @min=(select min(dob) from test
              where dob < DATE_SUB(CURDATE(), INTERVAL 30 day));
while @min is not null
begin   
    delete from test where dob = @min;
    commit ;
    select min(dob) from test
            where dob > @min
              and dob < DATE_SUB(CURDATE(), INTERVAL 30 day) );
END WHILE;
END$$

ERROR:

    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use
 near 'while @min is not null
begin
    delete from test where dob = @min;
    commi' at line 1
0

5 Answers 5

2

The procedure is missing its BEGIN.

For deleting in chunks, see http://mysql.rjweb.org/doc.php/deletebig#deleting_in_chunks

With 4 million rows, you have perhaps 133K rows per delete. This will choke InnoDB, even if you have an index starting with dob. See the link, above, for how to trim the deletes back to no more than 1000 at a time. (1K is a more civilized chunk.)

2
  • Hey Rick, I just tested the command which is in your blog in 2 cases. The total number of rows in the table are 520000. No replication. chunk size 10000. Case 1: without select sleep(1); It took 46 seconds. Total loop 52. Case2: added sleep(1) after the delete command, this time it took 1min 57sec. (if add 1 sec per loop + without sleep results = 1min 30sec) can you please help me to understand why we are adding Sleep command, how this will hep in a replication scanrio?
    – TheDataGuy
    Commented Sep 19, 2018 at 18:58
  • Hmmm... I can't explain why adding the sleeps added more than 52 seconds. Replication needs the pauses to give it time to get other things done. Whether 1 second per 10K rows is too much or too little depends on many things. Benchmarking is probably the only way to decide.
    – Rick James
    Commented Sep 20, 2018 at 1:02
5

This might do what you want. It's a way to delete old rows from a very large table, without placing as much load on the database as much as a regular DELETE

DROP PROCEDURE IF EXISTS archive_table;

DELIMITER $$

CREATE PROCEDURE archive_table()

BEGIN   
    REPEAT
        DO SLEEP(1); ## Optional, to minimise contention
        DELETE FROM test 
        WHERE dob < NOW() - INTERVAL 1 MONTH 
        ORDER BY primary_key 
        LIMIT 1000; ## 10000 also works, this is more conservative      
    UNTIL ROW_COUNT() = 0 END REPEAT;
END$$

DELIMITER ;

CALL archive_table();
2

The openark kit by Shlomi Noach could be right up your alley!

From the introduction here:

  • oak-chunk-update: perform long, non-blocking UPDATE/DELETE operation in auto managed small chunks.

From the documentation page here:

This utility allows for splitting long running or non-indexed UPDATE/DELETE oprations, optionally multi-table ones. Long running updating queries are often used. Some examples:

Purging old table records (e.g. purging old logs).
Updating a column on a table scale.
Deleting or updating a small number of rows, but with a non-indexed search condition.
Inserting into one table aggregated values from other tables.

The tool has a load of options with examples - I was very impressed when I used a couple of his tools for various bits and pieces.

So, kick it off with cron at 03:00 in the morning and relax!

5
  • Unfortunately the openark-kit is not maintained but you could use pt-archiver.
    – ColinM
    Commented Oct 14, 2022 at 22:56
  • @ColinM - you might want to put that in as an answer? No longer have anything to do with MySQL - have found the one true database - am Saved!
    – Vérace
    Commented Oct 23, 2022 at 21:09
  • What database is that?
    – ColinM
    Commented Oct 27, 2022 at 15:27
  • PostgreSQL in the OTDB! :-) Seriously though, if you want to write up that pt-archiver is now the way to go - I'll upvote! I've decided (for a while now) to go with PostgreSQL - here's the view of a former programmer - and that's not all of it. Postgres is just vastly superior in so many ways - and with the work being done on table access methods, it'll soon have alternative engines! Hard to beat! All of this is just IMHO though - you/anyone ard/is free to hold different views, but AFAIC, the evidence speaks for itself!
    – Vérace
    Commented Oct 27, 2022 at 15:40
  • I suspected that was it, I definitely agree Postgres is ahead of MySQL. MySQL has stagnated and I even get segfaults in new releases. MariaDb would probably be a better candidate for comparison, but unfortunately switching is not really a piece of cake..
    – ColinM
    Commented Oct 30, 2022 at 20:29
1

The command line tool pt-archiver found in the Percona Toolkit can move or just delete rows in automatically managed chunks given a where clause.

0

I'm confused at why you're looping. It looks like you want to delete a batch and return the results. What you want is something like this.

CREATE TEMPORARY TABLE foo
AS
  SELECT *
  FROM test
  WHERE dob < DATE_SUB(CURDATE(), INTERVAL 30 day);

DELETE
FROM test
INNER JOIN foo
  ON (foo.dob = test.dob);

SELECT *
FROM foo;

Or use a real database that supports DELETE .. RETURNING

If you don't care about the results just do

DELETE
FROM test
WHERE dob < DATE_SUB(CURDATE(), INTERVAL 30 day);
2
  • I have few more very huge tables to clean, So I want to do it via batches like what percona's pt-archive is doing.
    – TheDataGuy
    Commented Oct 27, 2017 at 17:19
  • a) I don't think it's going to be faster to break it up. b) just add a limit statement in the select statement above. LIMIT 1000 or whatever. Commented Oct 27, 2017 at 17:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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