3

We are trying to move data from one table that currently has over 1B rows to a partitioned table. But as we started to do so we notice that half of the partitions are empty, why is that? Table is partitioned by the column that has UUIDv4 values.

Our table definition:

CREATE TABLE table1 (
  `col1` varchar(36) NOT NULL DEFAULT '',
  `col2` varchar(256) NOT NULL DEFAULT '',
  `col3` datetime NOT NULL DEFAULT NOW(),
  UNIQUE KEY `unq_col1_col2 (`col1`,`col2`),
  KEY `idx_col3` (`col3`)
)
ENGINE=InnoDB
PARTITION BY KEY(col1)
PARTITIONS 100;

INFORMATION_SCHEMA.PARTITIONS output:

PARTITION_NAME  TABLE_ROWS
p0  136532
p1  0
p2  339523
p3  0
p4  156955
p5  0
p6  305700
p7  0
p8  209548
p9  0
p10 329291
p11 0
p12 152995
p13 0
p14 388600
p15 0
p16 251903
p17 0
p18 364532
p19 0
p20 200799
p21 0
......
p89 0
p90 303628
p91 0
p92 215546
p93 0
p94 399165
p95 0
p96 210364
p97 0
p98 318675
p99 0

We are using Google Cloud SQL (MySQL) which is currently on version 5.7.32-google-log.

Improve this question
7
  • Do you get any performance over the non-partitioned equivalent? If so, let's see a query that runs faster.
    – Rick James
    Mar 1, 2021 at 21:53
  • The main issue is that we were getting a bunch of deadlocks during the peak hours. After moving to the partitions this is no longer the case. We are upserting(INSERT ... ON DUPLICATE KEY UPDATE ...) records and deleting records by the col1+col2. This table currently has 1B of records and they are overwritten every 7 days, but by the end of the year with current growth, we will be doing this with over 20B of records every 7 days. Is there a better way to deal with this?
    – ffox003
    Mar 2, 2021 at 7:50
  • Those numbers look like 5 million rows, nowhere near 1 billion. Am I missing something?
    – Rick James
    Mar 3, 2021 at 4:17
  • Please show us the entire upsert that is having deadlocks. Also SHOW ENGINE=InnoDB STATUS (at least the deadlock part), so we can see more details.
    – Rick James
    Mar 3, 2021 at 4:19
  • @RickJames when I wrote the post we just started using the partitioned table and as we saw empty partitions we removed that table from the process. But we had almost all unique col1 values in it. Here is the example of how we do it currently: dbfiddle.uk/… I can't send you the output of the SHOW ENGINE=InnoDB STATUS as we are no longer using the old table and do not have the deadlocks. I'll try to see if I can replicate the same behavior on our staging environment to send you that.
    – ffox003
    Mar 3, 2021 at 13:08

1 Answer 1

Reset to default
2

You have 100 partitions. So it's likely that your hash values modulus 100 are all even numbers.

This is a general risk of hashing. If the hashing function produces only values with a pattern like this, you can get imbalanced partitions.

One way to fix this is to use a prime number for the number of partitions. For example 101 is a prime number close to your desired number of partitions.

This helps because if the hash values modulus 100 produced a pattern, that pattern will be offset by 1 per window of 100 values. Thus it will be spread more evenly over the partitions.

Improve this answer
1
  • 1
    Thanks, this did the trick. By using 97 or 101 partitions, values are spread out more evenly.
    – ffox003
    Mar 1, 2021 at 21:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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