0

My events are stored in MySQL and I'm reaching a point where I have tens of millions of rows and things are starting to get sluggish when pulling an aggregate

I have read about "table partitioning" and was wondering if it was a good option for storing my aggregate events, since an aggregate is just a stream of events, each aggregate would have it's own partition.

My doubt is, do I still need a index on the aggregate ID if I'm going to partition also by the aggregate ID? Have you guys ever used "table partitioning" to solve this issue?

  • 2
    Each aggregate having its own partition might be impractical. Maybe use a hash of the aggregate ID so you can have a reasonable, finite number of partitions. – Aaron M. Eshbach Jul 5 '18 at 15:34
  • What do you mean "use a hash of the aggregate ID"? The aggregate ID is a uuid. Also, why would it be impractical? – lucaswxp Jul 5 '18 at 16:42
  • 1
    I believe MySql has a limit of 8192 partitions for a table. When you define a partitioning function, you will be telling the DB engine how to divide the data into partitions. For situations like yours, a hash function is typically used to divide the data into a finite number of partitions, in order to optimize the distribution and stay below the 8192 limit. If you have one partition for each aggregate ID, you will quickly run out of partitions. – Aaron M. Eshbach Jul 5 '18 at 17:14
  • Thanks @AaronM.Eshbach, yes, now I understand. Thank you! Do you know if it's possible using the last char of the aggregate ID as partition? In a 30M table, this gives me exactly 16 partitions almost evenly distributed. – lucaswxp Jul 5 '18 at 17:18
  • Ok, I looked it up and SUBSTRING cant be used. I think I'll have to put this into a new column. Do you mind transforming this into an answer? – lucaswxp Jul 5 '18 at 17:39
1

MySql has a limit to the number of partitions you can have on a table, which I believe is currently 8192. Therefore, it may be impractical to have a separate partition for each Aggregate ID, as you would likely run out of partitions very quickly.

When altering the table, you'll need to provide a partitioning function that tells the database how to distribute the data into partitions. If you want to use a substring of the Aggregate ID to determine the partition, you can use a PARTITION BY RANGE function where you define fixed set of partitions based on the values, as described in this answer. Personally, I would probably use either the PARTITION BY HASH or PARTITION BY KEY so that you'll get an even distribution of the data into partitions, and you can define an arbitrary number of partitions. This will also allow you to easily add more partitions later. You can see an example of this technique described here.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.