MySQL Forums :: Partitioning :: Advice on partitioning

Advanced Search

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

Subject Views Written By Posted
Advice on partitioning 1622 me su 04/24/2015 01:40PM
Re: Advice on partitioning 925 Rick James 04/24/2015 05:34PM
Re: Advice on partitioning 870 Mehdi Salarkia 04/27/2015 09:05AM
Re: Advice on partitioning 904 Rick James 04/27/2015 02:22PM
Re: Advice on partitioning 761 Drew Morris 04/28/2015 11:02AM
Re: Advice on partitioning 794 Rick James 04/28/2015 12:32PM
Re: Advice on partitioning 821 Mehdi Salarkia 04/28/2015 04:07PM
Re: Advice on partitioning 668 Mehdi Salarkia 06/15/2015 06:39PM
Re: Advice on partitioning 816 Rick James 06/15/2015 07:05PM
Re: Advice on partitioning 760 Mehdi Salarkia 06/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.