MySQL Forums :: Partitioning :: Advice on partitioning


Advanced Search

Re: Advice on partitioning
Posted by: Mehdi Salarkia ()
Date: April 27, 2015 09:05AM

Hi Rick
These are the tables I created for my test case

---------------------------------------------------------------------------
CREATE TABLE mail_test_key (
id bigint AUTO_INCREMENT ,
ownerid int unsigned ,
creation_date datetime(6) default now(6),
subject varchar(500),
primary key (id,ownerid)
)
ENGINE=INNODB DEFAULT CHARSET=utf8
PARTITION BY KEY( ownerid )
PARTITIONS 100;




CREATE TABLE mail_test_linear_key (
id bigint AUTO_INCREMENT ,
ownerid int unsigned ,
creation_date datetime(6) default now(6),
subject varchar(500),
primary key (id,ownerid)
)
ENGINE=INNODB DEFAULT CHARSET=utf8
PARTITION BY LINEAR KEY( ownerid )
PARTITIONS 100;



CREATE TABLE mail_test_hash (
id bigint AUTO_INCREMENT ,
ownerid int unsigned ,
creation_date datetime(6) default now(6),
subject varchar(500),
primary key (id,ownerid)
)
ENGINE=INNODB DEFAULT CHARSET=utf8
PARTITION BY hash( ownerid )
PARTITIONS 100;




CREATE TABLE mail_test_linear_hash (
id bigint AUTO_INCREMENT ,
ownerid int unsigned ,
creation_date datetime(6) default now(6),
subject varchar(500),
primary key (id,ownerid)
)
ENGINE=INNODB DEFAULT CHARSET=utf8
PARTITION BY LINEAR hash( ownerid )
PARTITIONS 100;
---------------------------------------------------------------------------
Obviously this is not the ultimate table that we will have. Our table will most likely have some more columns as

`creationDate` datetime(6) NULL DEFAULT NULL default now(6),
`CreatedById` int unsigned NOT NULL,
`LastModifiedDate` datetime(6) NOT NULL default now(6),
`LastModifiedById` int unsigned DEFAULT NULL,
`Deleted` int unsigned NOT NULL DEFAULT '0',
`senddate` datetime(6) NULL,
`ParentID` int unsigned NULL ,
`Path` varchar(255) NOT NULL DEFAULT '/',
`from` varchar(255) DEFAULT NULL,
`replyto` varchar(255) DEFAULT NULL,
`sender` varchar(255) DEFAULT NULL,
`subject` varchar(255) DEFAULT NULL,
`cc` varchar(255) DEFAULT NULL,
`bcc` varchar(255) DEFAULT NULL,
`to` varchar(255) DEFAULT NULL,
`folder` varchar(75) DEFAULT '',
`size` int unsigned DEFAULT '0',
`Draft` tinyint(3) unsigned NOT NULL DEFAULT '0',
`SavedSend` tinyint(3) unsigned NOT NULL DEFAULT '0',
`ExpirationDate` datetime(6) NOT NULL ,
`flagged` tinyint(1) NOT NULL DEFAULT '0',
`repliedto` tinyint(1) NOT NULL DEFAULT '0',
`forwarded` tinyint(1) NOT NULL DEFAULT '0',
`isspam` int unsigned NOT NULL DEFAULT '0',
`HasAttachments` tinyint(1) DEFAULT NULL,

And some indexes on creationDate,LastModifiedDate like

KEY `modified_idx` (`ownerId`,`parentID`,`LastModifiedDate`)

to allow user to sort their e-mails based modification/arrival date. Most of our queries will have the "ownerId" filter to make sure user can't modify/view other user's emails. Each user will mainly receive a lot of emails and they will constantly mark them as read/unread or move them between folders (in this case just and update on "parentId" column) or deleting them (which is an update on `Deleted` column)

This table may have a lot of records (on average 200,000 to 300,000 records per user although we may have users with 5,000,000 emails). We should be able to support databases with millions of users and scale on user basis.

For now we chose the partition to be based on id,ownerId columns and we will also have shards based on user id which distribute users across databases.

If you need more information please let me know , I can also post my java test case here if you think it will help.

Thanks for your help

Options: ReplyQuote


Subject Views Written By Posted
Advice on partitioning 1600 me su 04/24/2015 01:40PM
Re: Advice on partitioning 912 Rick James 04/24/2015 05:34PM
Re: Advice on partitioning 849 Mehdi Salarkia 04/27/2015 09:05AM
Re: Advice on partitioning 896 Rick James 04/27/2015 02:22PM
Re: Advice on partitioning 749 Drew Morris 04/28/2015 11:02AM
Re: Advice on partitioning 783 Rick James 04/28/2015 12:32PM
Re: Advice on partitioning 810 Mehdi Salarkia 04/28/2015 04:07PM
Re: Advice on partitioning 659 Mehdi Salarkia 06/15/2015 06:39PM
Re: Advice on partitioning 803 Rick James 06/15/2015 07:05PM
Re: Advice on partitioning 751 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.