Big Table and Partitioning
Hi,
I just want your opinion about my table.
Right now We have a mysql 4.1 with more than 270 000 000 records (40 G of data) and we want to switch on mysql 5.1.11 beta with partitions. What do you recommend us ?
My tables look like:
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`parentId` bigint(20) DEFAULT '0',
`address` varchar(255) DEFAULT NULL,
`Text` varchar(200) DEFAULT NULL,
`level` mediumint(9) DEFAULT NULL,
`Date` date DEFAULT NULL,
`pending` tinyint(3) DEFAULT '0',
`status` smallint(6) DEFAULT NULL,
`scr` int(11) NOT NULL DEFAULT '0',
`hId` int(11) DEFAULT '-1',
`sId` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `Address Index` (`address`),
KEY `IndexDate Index` (`Date`),
KEY `Scr Index` (`scr`),
KEY `Pending Index` (`pending`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 MAX_ROWS=500000000
First of all we are thinking to do a cluster later so I'm limited on KEY partitioning (I think).
What we tested:
We alter our table in this way to be supported by key partitioning :
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`parentId` bigint(20) DEFAULT '0',
`address` varchar(255) DEFAULT NULL,
`Text` varchar(200) DEFAULT NULL,
`level` mediumint(9) DEFAULT NULL,
`Date` date DEFAULT NULL,
`pending` tinyint(3) DEFAULT '0',
`status` smallint(6) DEFAULT NULL,
`scr` int(11) NOT NULL DEFAULT '0',
`hId` int(11) DEFAULT '-1',
`sId` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `Address Index` (`address`, `id`),
KEY `IndexDate Index` (`Date`),
KEY `Scr Index` (`scr`),
KEY `Pending Index` (`pending`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 MAX_ROWS=500000000
PARTION BY KEY (id) PARTITIONS 10000
but when we try to upload our database it get 24 hours for 4G of data (we have more than 40 G right now)
... Any suggestions ?
Thank you
Best Regards
Adrian Stanila
P.S. My hardware is a EMT64 with 2 G of ram sata 2 disk OS Debian Linux 3.1
Edited 2 time(s). Last edit at 09/20/2006 02:59AM by Adrian Stanila.