2

I am trying to setup an event, that automatically moves data older than a specified amount of time from a heavily used table to some "archive" table to improve performance.

So far I've only come across answers, that are based on a WHERE clause that is used for both INSERT INTO SELECT and the DELETE statement. Since time passes between both statements, the WHERE clause could theoretically have different results and thus lead to data loss.

This is my event:

DELIMITER $$
CREATE EVENT IF NOT EXISTS archive_6_month_old_entries
ON SCHEDULE EVERY 1 MINUTE
STARTS CURRENT_TIMESTAMP
DO
begin
START TRANSACTION;

CREATE TEMPORARY TABLE data_to_move 
SELECT *
FROM api_log
WHERE (datum - UNIX_TIMESTAMP(DATE_ADD(NOW(), INTERVAL -6 MONTH))) < 0 LIMIT 5000;

INSERT INTO api_log_archive
SELECT * FROM data_to_move;

DELETE
FROM api_log
WHERE id in (SELECT id FROM data_to_move);

COMMIT;
end $$
DELIMITER ;

Is there any way to do this without a temporary table?

2
  • Are you unable to simply INSERT INTO api_log_archive` from api_log?
    – matigo
    May 11, 2021 at 22:45
  • but then we have the issue that, if in theory, the insert query takes up to 5 minutes, there will be a difference in the result between the INSERT INTO and the DELETE statement, leading to 5 minutes of lost data.
    – Theo
    May 11, 2021 at 22:54

2 Answers 2

3

You say:

but we have the issue that, if in theory, the insert query takes up to 5 minutes, there will be a difference in the result between the INSERT INTO and the DELETE statement, leading to 5 minutes of lost data.

No, you don't have to risk losing data! That's what TRANSACTIONs are for!

InnoDB uses (Multi-Version Concurrency Control - MVCC). You should read up on this important topic in order to better exploit the capabilities of your server.

Something like this will work:

SET TRANSACTION LEVEL SERIALIZABLE; 
  BEGIN TRANSACTION; 

  INSERT INTO api_log_archive
    SELECT *
    FROM api_log
    WHERE (datum - UNIX_TIMESTAMP(DATE_ADD(NOW(), INTERVAL -6 MONTH))) < 0; -- removed LIMIT 5000, it's not necessary

  DELETE FROM api_log
    WHERE (datum - UNIX_TIMESTAMP(DATE_ADD(NOW(), INTERVAL -6 MONTH))) < 0; -- removed LIMIT
  
  COMMIT;

SET TRANSACTION LEVEL READ COMMITTED;  -- or whatever your default is

Put this into an EVENT and schedule it to run every day at (say) 03:30 every night.

Once you are happy that your event is working regularly at the time you require, then all you'll have to do is make monitoring it part of your daily monitoring routine.

Isolation levels

A brief explanation of the difference between the READ COMMITTED and the SERIALIZABLE transaction isolation levels follows:

The default TRANSACTION ISOLATION LEVEL on MariaDB is READ COMMITTED.

So, in terminal one, I do the following

Terminal_1_> SHOW TRANSACTION ISOLATION LEVEL;

Result:

 transaction_isolation
-----------------------
 read committed
(1 row)

That's what we want - so now we issue this command:

Terminal_1_> SELECT * FROM t;

Result:

 x |  y
---+-----
 1 | One
 2 | Two
(2 rows)

That's as we want it - so, now we delve into transactions as follows:

Terminal_1_> BEGIN TRANSACTION;
BEGIN
Terminal_1_> SELECT * FROM t;
 x |  y
---+-----
 1 | One
 2 | Two
(2 rows)

Here, we "suspend" the transaction by switching to another terminal 2...

We run this query:

SELECT 
       pid, 
       age(clock_timestamp(), query_start), 
       usename, query 
FROM pg_stat_activity 
WHERE datname = 'test' AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY query_start desc;

Result (active queries which are not the one above):

 pid  |       age       | usename  |      query
------+-----------------+----------+------------------
 3500 | 00:05:46.113281 | postgres | SELECT * FROM t;
(1 row)

So, we can see that my SELECT * FROM t; query is still active and is over 5 minutes old.

So, now, again in terminal 2, we insert a row into the table:

INSERT INTO t VALUES (3, 'Three');

And (still terminal 2), I check:

Terminal_2_> SELECT * FROM t;
 x |   y
---+-------
 1 | One
 2 | Two
 3 | Three
(3 rows)

And I go back to terminal 1 and issue a SELECT * FROM t; and the result is:

 x |   y
---+-------
 1 | One
 2 | Two
 3 | Three
(3 rows)

Now, to see the behaviour with TRANSACTION ISOLATION LEVEL set to SERIALIZABLE.

We issue the command:

SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE

I'm using a local PostgreSQL instance as that's the only thing I have running and running these sorts of commands isn't possible using a fiddle - the equivalents for MariaDB are here - I leave it up to you to construct the required tests with MariaDB.

So, from termina1, we check our settings:

SELECT name, setting, short_desc FROM pg_settings WHERE name ILIKE ('default%transaction%o%');

Result:

             name              |   setting    |                          short_desc
-------------------------------+--------------+---------------------------------------------------------------
 default_transaction_isolation | serializable | Sets the transaction isolation level of each new transaction.
 default_transaction_read_only | off          | Sets the default read-only status of new transactions.
(2 rows)

So, we're good to go. From terminal_1_> (SERIALIZABLE) we run;

BEGIN TRANSACTION;
SELECT * FROM t;

Result:

 x |  y
---+-----
 1 | One
 2 | Two
(2 rows)

Now, from terminal_2_> we run our check on active queries again:

SELECT 
       pid, 
       age(clock_timestamp(), query_start), 
       usename, query 
FROM pg_stat_activity 
WHERE datname = 'test' AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY query_start desc;

Result:

  pid  |       age       | usename  |      query
-------+-----------------+----------+------------------
 11064 | 00:03:37.694149 | postgres | SELECT * FROM t;
(1 row)

The query in terminal_1_> is sitting there and is now > 3mins old...

From terminal_2_> again, we update the table:

INSERT INTO t VALUES (3, 'Three');

and:

Terminal_2_> SELECT * FROM t;
 x |   y
---+-------
 1 | One
 2 | Two
 3 | Three
(3 rows)

Now we go back to terminal_1_> and run SELECT * FROM t; a second time within the same serializable transaction.

and we get:

Terminal_1_> SELECT * FROM t;
 x |  y
---+-----
 1 | One
 2 | Two
(2 rows)

The SERIALIZABLE transaction has the same view of table t that it had at the beginning of transaction, despite terminal_2_> having INSERTed a new record...

I would urge you to experiment with differing setups to get to know your server's capabilities and how you can best exploit them...

In terms of your problem, what this means is that:

Scenario 1 (you keep the defaults on your server - i.e. REPEATABLE READ):

At time t1, you run this:

CREATE TEMPORARY TABLE data_to_move 
SELECT *
FROM api_log
WHERE (datum - UNIX_TIMESTAMP(DATE_ADD(NOW(), INTERVAL -6 MONTH))) < 0 LIMIT 5000;

Say, for example, you SELECT 10 records. It's a log table, so it's constantly being updated...

At time t2, you INSERT into your archive table.

INSERT INTO api_log_archive
SELECT * FROM data_to_move;

At time t3, you DELETE from your api_log

DELETE
FROM api_log
WHERE id in (SELECT id FROM data_to_move);

Now, your process will miss any records that have aged to over 6 months between t1 and t3 - so, fine, you say, I'll pick them up the next time...

Scenario 2 (your run your archiving process with the TRANSACTION ISOLATION LEVEL set to SERIALIZABLE).

For starters, you can cut out the CREATE TEMPORARY TABLE phase as follows:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE        -- check out MariaDB syntax for this

BEGIN TRANSACTION;

INSERT INTO api_log_archive 
  SELECT id
  FROM api_log
    WHERE (datum - UNIX_TIMESTAMP(DATE_ADD(NOW(), INTERVAL -6 MONTH))) < 0;

DELETE FROM api_log
WHERE id IN 
  SELECT id
  FROM api_log
    WHERE (datum - UNIX_TIMESTAMP(DATE_ADD(NOW(), INTERVAL -6 MONTH))) < 0;

COMMIT;

-- clear up - set your transaction defaults back to your preferred default...

The advantage of this is you don't have the overhead of creating a potentially large TEMPORARY TABLE, you reduce the number of steps from 3 to 2 and your system will be more robust.

  • There is another possibility - you can lock your tables for the duration of your update - however, this means that no writes can occur to the api_log table during the time of your transaction - it's generally considered not to be good practice if it can be avoided. If you wish to explore this possibility, then you might like to ask another question...

This fiddle should help clarify things - PostgreSQL has the useful (and intelligently explicit) functions TRANSACTION_TIMESTAMP and CLOCK_TIMESTAMP - with READ COMMITTED any query sees the data as of CLOCK_TIMESTAMP and a SERIALIZABLE transaction sees the data as of TRANSACTION TIMESTAMP!

2
  • so the trick is to use both SET TRANSACTION LEVEL SERIALIZABLE; and BEGIN TRANSACTION, got it!
    – Theo
    May 12, 2021 at 14:26
  • 1
    That's what I would do - don't forget to set the TRANSACTION ISOLATION LEVEL READ COMMITTED back afterwards... But, if you're doing it as an EVENT, I don't think it should impact on other transactions - you might want to test? I'll put the text from some of the comments into the body of the answer!
    – Vérace
    May 12, 2021 at 14:58
1

This can be significantly sped up (if you have INDEX(datum)):

WHERE (datum - UNIX_TIMESTAMP(DATE_ADD(NOW(), INTERVAL -6 MONTH)))
      < 0 LIMIT

-->

WHERE datum > NOW() - INTERVAL 6 MONTH

Partitioning is the ultimate speedup: http://mysql.rjweb.org/doc.php/partitionmaint

Is your table have some id or timestamp that is guaranteed to be unique and monotonic? If so, first get the end value via LIMIT 1 OFFSET 5000, then use that value as the range for both the INSERT...SELECT and for the DELETE.

For that matter, can you perhaps fetch the day 6 months ago instead of fetching the second?

Fetch this once and use it for both queries:

SET @ago = NOW() - INTERVAL 6 MONTH;

then...

repeat
    INSERT INTO ...
        SELECT ... WHERE datum < @ago  LIMIT 1000;
until no more

repeat
    DELETE FROM ... WHERE datum < @ago  LIMIT 1000;
until no more

Better yet would be put that with my previous suggestion:

repeat
    SELECT @z := id FROM ..  WHERE datum < @ago
        ORDER BY id  LIMIT 1000, 1;

    INSERT INTO ...
        SELECT ... WHERE datum < @ago
                     AND id <= @id;

    DELETE FROM ... WHERE WHERE datum < @ago
                      AND id <= @id;
until no more

You do have INDEX(datum)?

2
  • The table has multiple million entries, I first need to clean it, then I can think about applying any kind of partitioning. thank you for your optimizations, I will add them to my query. and yes, I have INDEX(datum) but it is not part of the primary key (as of yet).
    – Theo
    May 14, 2021 at 7:58
  • 1
    @Theo - Or... Create a new table with partitioning, then INSERT into it the rows you want to keep. Then RENAME TABLE to move it into place. Probably the fastest and does it all at once. (Yes, it would be better to get a feel for each part first.)
    – Rick James
    May 14, 2021 at 16:47

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.