MySQL 8.0 – Maximum Number of Partitions per Table

mysql-8.0partitioning

I have around 1T (1,000,000,000) records of products belong to 500,000 accounts. Each product record have the account_id and since I always query within a single account context (there are no cross-account queries) I thought of creating a partition per account to improve performance.

  1. Is it even possible to create 500,000 list partitions in mysql8?
  2. Will it required changing in the number of open files the process required?
  3. If it is possible, is there a reason for me not to go this path?

Best Answer

The maximum number of partitions allowed in MySQL 8 for InnoDB tables is 8192 (from https://dev.mysql.com/doc/refman/8.0/en/partitioning-limitations.html). This includes subpartitions.

In general, partitions do not on their own improve performance, but there are a some scenarios where they can help:

  • Secondary indexes are local to the partition, so if you for example bulk insert data one partition at a time (or in order), it makes it more likely the secondary indexes fit into the buffer pool which can improve insert performance.
  • For partition pruning. This can for example mean that MySQL effectively uses two indexes for the same table without doing an index merge.
  • Managing logical grouped data such as moving all data that belongs to one partition to another table (for example to move it to another MySQL instance) or to delete all data in a partition.

One thing to be aware of if you have many partitions for a table is that by default each partition gets each own tablespace file (.ibd file). So if you have 8192 partitions, the table will end up using 8192 files.

Related Question