MySQL Forums
Forum List  »  Partitioning

Big Table and Partitioning
Posted by: Adrian Stanila
Date: September 20, 2006 02:37AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Big Table and Partitioning
5065
September 20, 2006 02:37AM


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.