Advice on partitioning

[me su]

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?