MySQL Forums
Forum List  »  MyISAM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: How to enable index fast in MyISAM table
2225
March 13, 2014 10:49PM


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.