Re: How to enable index fast in MyISAM table
Posted by:
Gejun Shen
Date: March 13, 2014 10:49PM
Thanks for reply. And Nice article on MyISAM to Innodb.
Given 2&3 times bigger than MyISAM. I guess the conversion will not happen in near future. I still have no idea on how the performance will be when I have 5T data stored in Innodb running on a 48G memory machines.
As for the partitions, it looks like:
CREATE TABLE `servicetable` (
`date` date DEFAULT NULL,
`timestamp` timestamp NULL DEFAULT NULL,
`realts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`level` varchar(10) NOT NULL DEFAULT '',
`host` varchar(32) NOT NULL DEFAULT '',
`timezone` varchar(32) NOT NULL DEFAULT '',
`servicetype` int(4) unsigned DEFAULT '9',
`service` varchar(32) NOT NULL DEFAULT '',
`domain` varchar(32) NOT NULL DEFAULT '',
`userID` varchar(32) NOT NULL DEFAULT '',
`role` varchar(32) NOT NULL DEFAULT '',
`action` varchar(32) NOT NULL DEFAULT '',
`version` varchar(16) NOT NULL DEFAULT '',
`result` varchar(32) NOT NULL DEFAULT '',
`process_sid` varchar(32) DEFAULT '0',
`parent_sid` varchar(32) DEFAULT '',
`correlationID` varchar(32) NOT NULL DEFAULT '',
`encoding` varchar(64) NOT NULL DEFAULT '',
`iptype` varchar(64) NOT NULL DEFAULT '',
`isarchive` int(4) unsigned DEFAULT '0',
`original_files` mediumtext,
`file` varchar(256) NOT NULL DEFAULT '',
`filesize` int(10) DEFAULT '0',
`ipnum` int(10) unsigned DEFAULT '0',
`filenum` int(10) unsigned DEFAULT '0',
`filelist` mediumtext,
`memo` mediumtext,
`session_uid` varchar(32) NOT NULL DEFAULT '',
`session_seq` bigint(20) unsigned DEFAULT '0',
`uid` bigint(20) unsigned DEFAULT NULL,
`seq` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
KEY `seq` (`seq`),
KEY `userID` (`userID`),
KEY `timestamp` (`timestamp`),
KEY `process_sid` (`process_sid`,`action`),
KEY `host` (`host`)
) ENGINE=MyISAM AUTO_INCREMENT=3809189089 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (to_days(date))
(PARTITION p20130930 VALUES LESS THAN (735507) ENGINE = MyISAM,
PARTITION p20131031 VALUES LESS THAN (735538) ENGINE = MyISAM,
PARTITION p20131130 VALUES LESS THAN (735568) ENGINE = MyISAM,
PARTITION p20131231 VALUES LESS THAN (735599) ENGINE = MyISAM,
PARTITION p20140131 VALUES LESS THAN (735630) ENGINE = MyISAM,
PARTITION p20140228 VALUES LESS THAN (735658) ENGINE = MyISAM,
PARTITION p20140331 VALUES LESS THAN (735689) ENGINE = MyISAM,
PARTITION p20140430 VALUES LESS THAN (735719) ENGINE = MyISAM,
PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = MyISAM)
The data are stored in month partitions, I have a script to maintain the partitions. Every beginning of a month, I create a new partition and drop the partition six months ago to reclaim disk. Originally I have daily partition that means I will have hundreds of partitions. However, in case I have tens of partitions the query performance is bad. Hence I switch to month partitions.
Most of the select queries would be:
select xxxx from servicetable where timestamp>="xxx" and timestamp<"xxx" ...
select xxx from servicetable where process_id="xxx" ...
How many connections can mysqld handle without obvious performance downgrade?
PS: enable keys finally finished in 35 hours.
Edited 2 time(s). Last edit at 03/13/2014 11:18PM by Gejun Shen.