Look at partitioned table definitions, stored procedure definitions with creating new partitions, and stored procedure definitions with dropping old partitions.
A MariaDB Support customer recently asked how they could automatically drop old partitions after six months. MariaDB does not have a mechanism to do this automatically out-of-the-box, but it is not too difficult to create a custom stored procedure and an event to call the procedure on the desired schedule. In fact, it is also possible to go even further and create a stored procedure that can also automatically add new partitions.
In this blog post, I will show you how to write stored procedures that perform these tasks.
For this demonstration, I’ll use a table definition based on one from MySQL’s documentation on range partitioning, with some minor changes:
The most significant change is that the partition naming scheme is based on the date. This will allow us to more easily determine which partitions to remove.
The stored procedure itself contains some comments that explain what it does, so I will let the code speak for itself, for the most part. One noteworthy item to point out is that we are not doing ALTER TABLE… ADD PARTITION. This is because the partition p_future already covers the end range up to MAXVALUE, so we actually need to do ALTER TABLE… REORGANIZE PARTITION instead.
Let’s try running the new procedure:
We can see that it appears to be working as expected.
This additional stored procedure also contains some comments that explain what it does, so I will let the code speak for itself, for the most part. One noteworthy item to point out is that the stored procedure drops all old partitions individually with ALTER TABLE… DROP PARTITION, and then it increases the range of the p_first partition with ALTER TABLE… REORGANIZE PARTITION, so that it fills in the gap left behind.
Let’s try running the new procedure:
We can see that our changes seem to be working as expected. In addition to old partitions being dropped, we can also see that p_first ‘s date range was updated.
It is probably going to be preferable in most cases to perform all partition maintenance at the same time. Therefore, we can create another stored procedure that calls our other two stored procedures. This is fairly straight forward.
Let’s reset our partitioned table to its original state, and then let’s try running our new stored procedure.
This stored procedure also seems to be working as expected.
It should be noted that these stored procedures can be run more often than is necessary. If the procedures are run when no partitions need to be added or deleted, then the procedure will not perform any work. Let’s reset our table definition and try it out.
As we can see from the above output, the procedure did not perform any work the second time.
We want our stored procedure to run automatically every month, so we can use an event to do that. Before testing the event, we need to do two things:
And then we can run the following:
However, there’s another great change that we can make here. It might not be ideal to only run the procedure once per month, because if the procedure fails for whatever reason, then it might not get another chance to run again until the next month. For that reason, it might be better to run the procedure more often, such as once per day. As mentioned above, the procedure will only do work when partition maintenance is actually necessary, so it should not cause any issues to execute the procedure more often.
If we wanted to run the procedure once per day, then the event definition would become:
Thanks to the flexibility of stored procedures and events, it is relatively easy to automatically perform partition maintenance in MariaDB. Has anyone else implemented something like this?
