Archiving metering (events) data

With time, the metering table where the events logged by the system can grow very big. This makes some queries to take a lot of time to complete making some processes in the UI very slow.

With the aim of keeping the metering table as light as possible, the Abiquo database schema includes a stored procedure that will archive old metering data (ie. old events) to a secondary archive table. The events in this table are not queried at all by any port of Abiquo, so you will need to decide how often to run the archive process and which period of data you want to retain available in the main events table. Of course, as a platform administrator you will always be able to manually run queries in the metering archive table, so data is not lost.

Archiving data

A table named metering_archive exists in the Abiquo database schema, which is an actual copy of the metering table designed to contain older events data. Data can be migrated from the metering table to the metering_archive table by calling the following procedure:

CALL kinton.ArchiveMeteringData(90);

The number supplied to the stored procedure is the number of days old that the data must be to migrate into the archive table.

Schedule data archiving

Additionally, it is possible to automate the archiving process by creating a a database event as shown below:

USE kinton;
CREATE EVENT IF NOT EXISTS metering_archive_job
ON SCHEDULE EVERY 1 DAY STARTS CURRENT_DATE + INTERVAL 1 DAY
COMMENT 'Migrates metering data into the archive table'
DO
    CALL kinton.ArchiveMeteringData(90);

You will need to make sure the event scheduler in MariaDB is turned on:

MariaDB [kinton]> select @@event_scheduler;
+-------------------+
| @@event_scheduler |
+-------------------+
| OFF               |
+-------------------+
1 row in set (0.00 sec)

To turn it on you can just change the value of the global variable:

MariaDB [kinton]> SET GLOBAL event_scheduler = ON;
Query OK, 0 rows affected (0.00 sec)

MariaDB [kinton]> select @@event_scheduler;
+-------------------+
| @@event_scheduler |
+-------------------+
| ON                |
+-------------------+
1 row in set (0.00 sec)

Now you will be able to see the event scheduler in the process list:

MariaDB [kinton]> SHOW PROCESSLIST;
+--------+-----------------+-------------------+------------+---------+------+-----------------------------+------------------+----------+
| Id     | User            | Host              | db         | Command | Time | State                       | Info             | Progress |
+--------+-----------------+-------------------+------------+---------+------+-----------------------------+------------------+----------+
...
| 594892 | event_scheduler | localhost         | NULL       | Daemon  |    4 | Waiting for next activation | NULL             |    0.000 |
...
+--------+-----------------+-------------------+------------+---------+------+-----------------------------+------------------+----------+
38 rows in set (0.00 sec)

More info on MariaDB events can be found at MariaDB documentation.