MySQL partitioning by hash: year and quarter

user_11378048 0
Viewed 134

I guess I don't understand how does the subject work.

I have a MySQL table with such columns:

some_data1, some_data2, date

I expect large volumes, so I want this table to be partitioned based on year and quarter, so I want the data to be inserted in a new partition each quarter.

I have such criteria for partitioning:

 HASH(YEAR(date) * 100 + QUARTER(date))

Which to my understanding is supposed to be calculated like 2021001 to first quarter of 2021, 2021002 to second quarter of 2021, and so on. And of course I expect it to do what I want it to do: to insert data into partitions based on this criteria: year and quarter.

But what it actually does - it ignores year. So data with dates

2021-01-01, 2010-02-03, 3000-03-02, etc.

goes exactly to one and only partition.

Another part of this question is - I guess MySQL doesn't automatically create new partition for each new calculated hash criteria, right? What if I clear some old data from such table, leaving some particular partition empty. Will new data with new calculated hash go to this empty partition or should I - like usual - do dirty work and create partition manually again? Which will leave a whole bunch of empty partitions...

(I am using PhpMyadmin, just in case)

So...Which part of my head matches potato most? :)

database-partitioning mysql
1 Answers

Well I guess I have figured out that weird logic.

The result of HASH function should be the id (or #) of a partition - to be 100% sure and see where the data will go.

Weird things start to happen when you have lets say 10 partitions, but HASH returns something greater than 10: it will write data, I would say, into somewhere.

When you simplify - it becomes clearer.

In my example above I had 5 partitions. My HASH results with test data for years 2021 and 3000 were something like 202102, 202104, 300001, 30002, etc. As a smart guy I expected MySQL to auto create new partition for each unique HASH result and fill it. Aha. Keep expecting.

I realized that for a simplicity (or to test it for good) HASH function should just return quarter number starting from this year. So 2021 would have 1 to 4 quarter numbers, for next year quarters would be 5 to 8, for year after next - 9 to 12, and so on.

And - of course - I must have all partitions created in advance! Or MySQL will start overfilling existing partitions. I expect a partition to be under 1m records, but if I have created partitions for just 3 years (in my case), in a 4th year my partitions will go over 1m! Pointless partitioning.

My new HASH criteria:

 HASH((YEAR(date) - 2021) * 4 + QUARTER(date))

And all partitions created in advance for defined amount of years to keep. Cleanup function implemented too - but that already was by my app design.

PS: whole day screwed, just because some - lets be polite and say: "guy" - didn't bother to document or explain such behavior. At least google and MySQL site did not help me.

user_113780480
Related