Letting MySQL/MariaDB handle the history partitions?

So, I've been pondering increasing the stringvalue column size on our historian since we have some strings bigger than 256 to be saved. But I also don't want to have to re-create the tweak every new partition. But I also know that MySQL and MariaDB can handle partitions on their side...

Has anyone ever tried disabling partitions altogether on Ignition, then heading over to SQL and just altering the one eternal Ignition-generated table with a command like this:

ALTER TABLE sqlth_3_data PARTITION BY HASH(t_stamp DIV 604800000) PARTITIONS 64;

I'm making a big assumption that the Ignition-controlled partitioning and SQL-controlled partitioning would give roughly similar performance benefits. I know the SQL-controlled one would just keep re-using the same partitions, doesn't guarantee truly even distribution, and would lose benefit as time goes on if we're not pruning old data...

But, ignoring those issues, what other unforeseen pain am I opening myself up to?

Are there any MySQL, MariaDB or historian gurus that have thoughts on this?

Just FYI, we're currently storing on the order of 30 million rows/week in the history DB, and that rate is trending upwards quickly.

You probably don't want to use a hash, but I would expect this to work.

I struggled with which type of partition rule to use.

LIST and KEY are unusable and bad if I'm trying to make sure entries from the same proximate time end up in the same partition. RANGE looks like it would be the obvious choice, but there is no way to add an eternally repeating RANGE rule that would make a new partition every hour/day/week/month/whatever. The RANGE type requires you explicitly specify all the ranges and which partition each one goes into.

The HASH is the only one left that can go forever without having to keep adding new partition rules. I did the DIV to force all records within a range of t_stamp values would end up in the same partition, so I get some time-proximity benefits.

What would you suggest instead of HASH?

I've done this with RANGE before, not specifically for Ignition history tables, but other tables that would be considered analogous. Use the MAXVALUE keyword as a catch all for any data that it is newer than the max partition range. I also used a Scheduled Event in MySQL to REORGANIZE the "catchall" partition (p_new in the example below) as necessary.

PARTITION BY RANGE COLUMNS (time) (
        PARTITION p_old VALUES LESS THAN ('2023-04-01')
        , PARTITION p_2023_04 VALUES LESS THAN ('2023-05-01')
        , PARTITION p_2023_05 VALUES LESS THAN ('2023-06-01')
        , PARTITION p_new VALUES LESS THAN (MAXVALUE)
1 Like

If I'm using scheduled events to REORGANIZE partitions, I suspect I'd be better off using scheduled events to just ALTER the Ignition-created partitions, so I'm less far off the beaten path.

Also, was reading some wisdom in the MariaDB manual on maintaining partitions, and it's shifting my whole belief on what the benefit of partitions is. I'm thinking it probably isn't worth it in my case.

Should work fine with the ALTER, just as long as you run it before any significant amount of data is added to it, otherwise it could lock up for quite some time trying to complete the operation.

Yeah, I've only used DB partitioning for some really extreme projects (one was projected at a billion rows per week...we have yet to get anywhere close to that). Otherwise, I tend to avoid DB partitioning.

Or I can pre-create partitions before the prior one ends...

1 Like