GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Partitioning Design

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Partitioning Design

    I'm working on setting up a new schema which will store call detail records for a growing phone company. Right now the size is fairly small (10K inserts per day) but this is growing quickly.

    Data is really only looked at in monthly reports and so I figure partitioning the best way to handle the growth.

    My idea is to partition by Year and then sub-partition by Month. If I understand right I can't use month(datetime) to partition by and I also cannot have a primary key.

    My idea is to use the application to set a year and month. Is that the best way to go or am I missing something fundamental?

    Any help would be appreciated.


    CREATE TABLE `cdr` (
    `id` varchar(64) character set utf8 NOT NULL,
    `GATEWAY` varchar(27) character set utf8 NOT NULL,
    `calling` varchar(30) character set utf8 NOT NULL,
    `called` varchar(30) character set utf8 NOT NULL,
    `ingress_tg` varchar(23) character set utf8 NOT NULL,
    `egress_tg` varchar(23) character set utf8 NOT NULL,
    `duration` float NOT NULL,
    `calling_rc` varchar(45) character set utf8 NOT NULL,
    `calling_region` varchar(45) character set utf8 NOT NULL,
    `calling_lata` varchar(5) character set utf8 NOT NULL,
    `calling_ocn` varchar(45) character set utf8 NOT NULL,
    `called_rc` varchar(45) character set utf8 NOT NULL,
    `called_region` varchar(3) character set utf8 NOT NULL,
    `called_lata` varchar(5) character set utf8 NOT NULL,
    `called_ocn` varchar(45) character set utf8 NOT NULL,
    `btn` varchar(10) character set utf8 NOT NULL,
    `orig_datetime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
    `year` smallint(5) unsigned NOT NULL,
    `month` tinyint(3) unsigned NOT NULL,
    `rate` float NOT NULL default '0',
    `type` tinyint(3) unsigned default NULL,
    `cond` tinyint(3) unsigned default NULL,
    KEY `monthyear` (`year`,`month`),
    KEY `tg` USING BTREE (`ingress_tg`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8

  • #2
    I'd read up on scalability on http://highscalability.com.



    You may wish to use an archive type table for old data and partition that way.

    Comment

    Working...
    X