0

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?
  • 1
    what makes you think this will improve your performance? Do you have a performance problem? if so what is it? Best to attack the problem there rather than assuming partitioning is the answer. – danblack May 26 at 7:24
  • First, we don't have a "problem" but we have more and more queries against this DB and the performance is declining. Second, since each account is logically isolated and each query always contain "where account_id == ???" I thought that splitting it to partitions can dramatically reduce the size of information needs to be traversed each query. – Roee Gavirel May 26 at 7:27
  • btree transversal are quite quick to find so the partitioning really doesn't solve this. Ask/edit the question to be about the slow queries including the full query, EXPLAIN {query} and SHOW CREATE TABLE {tablename} for tables involved in the query. What ram/cpu resources are in use and MySQL configuration may be a consideration. – danblack May 26 at 7:32
  • True, but regardless of my reasons, the question is still valid. Is it possible to do it? to have 500,000 partitions per table? – Roee Gavirel May 26 at 7:39
  • 2
    500000 partitions per table is insane, and would probably not solve the performance problem(s). It's an often misunderstood database feature, and is most often used in the data lifecycle for archiving & in data warehousing. Spend some time analysing your current slow queries, this is really not the route you should go down TBH. Having that many partitions would probably cause performance problems within MySQL/InnoDB itself – Philᵀᴹ May 26 at 11:28
3

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.

  • And each partition has 4MB-7MB of free space. That adds up to about 50GB of "free" space due just to partitioning. – Rick James Jul 15 at 3:26
  • You have come very close to my exhaustive list of use cases for Partitioning: mysql.rjweb.org/doc.php/partitionmaint – Rick James Jul 15 at 3:28
0

each query always contain where account_id == ???

Then every index, including the PRIMARY KEY, should start with account_id and partitioning will not help performance.

Period. Full stop. End of discussion.

But, if you want to discuss further, please provide SHOW CREATE TABLE (with or without partitioning) and the important SELECTs.

How do you INSERT rows fast enough to go past a billion rows? I assume some sort of batch loading? If practical, sort the data by the PRIMARY KEY before feeding them to INSERT or LOAD DATA. Otherwise, you will have caching problems. It sounds like the data is about a terabyte?? How big is innodb_buffer_pool_size?

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.