MySQL Forums
Forum List  »  Partitioning

Advice on partitioning
Posted by: me su
Date: April 24, 2015 01:40PM

We are currently deciding on a partitioning scheme for a table in our MySQL database. We have multiple shards and we route all of a single users records to one shard. We also want to partition the table itself by userid. We are somewhat new to partitioning and would like some feedback as to which type of partition to use and how often we will likely need to maintain the partition.

This is mainly a mail meta data table which is used to store user's mail information (not the body). The table is pretty equally read/write heavy since new mail is constantly coming in, mail is being deleted, and mail meta data is being updated (read/unread/flagged/etc.) while users are listing their mailboxes or using the metadata to seed IMAP responses prior to downloading the physical messages. The system will support millions of users so we are employing both a sharding and a partitioning strategy.

We did some simple tests using partitioning by key, linear key, hash and linear hash. In our tests it seems like hash is the fastest option for insertion and it also seems to give us the best distribution using randomly generated userids. While reading the documentation, however, we read that linear hash is better if you want to merge or optimize partitions but we noticed it is a lot slower on insertion. We don’t really understand why we would ever need to merge or optimize partitions so we aren’t sure how much of a consideration this should be.

Also… we are planning to use the maximum number of partitions (1000 I think) because we don’t see any negative to this approach and it should give us the best performance by limiting the number of records per partition to the maximum degree. Is there anything we should be considering when deciding on the number of partitions or is ok to simply use 1000 partitions? Does anybody have any advice on this?

Options: ReplyQuote

Written By
Advice on partitioning
April 24, 2015 01:40PM
April 24, 2015 05:34PM
April 27, 2015 09:05AM
April 27, 2015 02:22PM
April 28, 2015 11:02AM
April 28, 2015 12:32PM
April 28, 2015 04:07PM
June 15, 2015 06:39PM
June 15, 2015 07:05PM
June 16, 2015 06:40PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.