# Letting MySQL/MariaDB handle the history partitions? **URL:** \ **Category:** Ignition\ **Created:** [May 22, 2023, 6:02pm UTC](https://forum.inductiveautomation.com/t/letting-mysql-mariadb-handle-the-history-partitions/74037 "2023-05-22T18:02:27Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![justin.brzozoski](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/justin.brzozoski/32/13452_2.png) [@justin.brzozoski](https://forum.inductiveautomation.com/u/justin.brzozoski)\ **Post date:** [May 22, 2023, 6:02pm UTC](https://forum.inductiveautomation.com/t/letting-mysql-mariadb-handle-the-history-partitions/74037/1 "2023-05-22T18:02:27Z")
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: ```auto 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. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [May 22, 2023, 6:09pm UTC](https://forum.inductiveautomation.com/t/letting-mysql-mariadb-handle-the-history-partitions/74037/2 "2023-05-22T18:09:39Z")
You probably don't want to use a hash, but I would expect this to work. ---
**Author:** ![justin.brzozoski](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/justin.brzozoski/32/13452_2.png) [@justin.brzozoski](https://forum.inductiveautomation.com/u/justin.brzozoski)\ **Post date:** [May 22, 2023, 6:15pm UTC](https://forum.inductiveautomation.com/t/letting-mysql-mariadb-handle-the-history-partitions/74037/3 "2023-05-22T18:15:54Z")
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`? ---
**Author:** ![WillMT10](https://avatars.discourse-cdn.com/v4/letter/w/7feea3/32.png) [@WillMT10](https://forum.inductiveautomation.com/u/WillMT10)\ **Post date:** [May 22, 2023, 6:30pm UTC](https://forum.inductiveautomation.com/t/letting-mysql-mariadb-handle-the-history-partitions/74037/4 "2023-05-22T18:30:24Z")
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. ```auto 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) ``` ---
**Author:** ![justin.brzozoski](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/justin.brzozoski/32/13452_2.png) [@justin.brzozoski](https://forum.inductiveautomation.com/u/justin.brzozoski)\ **Post date:** [May 22, 2023, 6:34pm UTC](https://forum.inductiveautomation.com/t/letting-mysql-mariadb-handle-the-history-partitions/74037/5 "2023-05-22T18:34:19Z")
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](https://mariadb.com/kb/en/partition-maintenance/), 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. ---
**Author:** ![WillMT10](https://avatars.discourse-cdn.com/v4/letter/w/7feea3/32.png) [@WillMT10](https://forum.inductiveautomation.com/u/WillMT10)\ **Post date:** [May 22, 2023, 6:52pm UTC](https://forum.inductiveautomation.com/t/letting-mysql-mariadb-handle-the-history-partitions/74037/6 "2023-05-22T18:52:17Z")
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. ---
**Author:** ![justin.brzozoski](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/justin.brzozoski/32/13452_2.png) [@justin.brzozoski](https://forum.inductiveautomation.com/u/justin.brzozoski)\ **Post date:** [May 22, 2023, 7:29pm UTC](https://forum.inductiveautomation.com/t/letting-mysql-mariadb-handle-the-history-partitions/74037/7 "2023-05-22T19:29:06Z")
Or I can [pre-create partitions](https://forum.inductiveautomation.com/t/pre-creating-historical-partitions/3675) before the prior one ends...