Wether is it possible to compress partitioned MyISAM tables?
I used MySQL to store CDRs for 3000TPS load, it's very huge data.
And my customer required me to store at least 3 months data, so they can do some on-demand query on these history data. A set of index was created on this table for query performance, so I choose MyISAM engine with partition support.
I stored each month's data in one partioned table, such as CDRYYYYMM, I want to use myisampack to compress last month's data.
Now the question, it's possible to compress partitioned MyISAM tables? I have do some test, but got error
show create table cdr_200905
CREATE TABLE `cdr` (
`id` bigint(18) NOT NULL AUTO_INCREMENT,
`msisdn` int(15) NOT NULL,
`ipaddress` varchar(15) DEFAULT NULL,
`fetchedurl` varchar(100) DEFAULT NULL,
`start_time` datetime NOT NULL,
PRIMARY KEY (`id`,`msisdn`,`start_time`)
) ENGINE=MyISAM AUTO_INCREMENT=990758 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (to_days(start_time))
SUBPARTITION BY HASH (msisdn)
SUBPARTITIONS 32
(PARTITION p20090501 VALUES LESS THAN (733925) ENGINE = MyISAM,
PARTITION p20090502 VALUES LESS THAN (733926) ENGINE = MyISAM,
PARTITION p20090503 VALUES LESS THAN (733927) ENGINE = MyISAM,
PARTITION p20090504 VALUES LESS THAN (733928) ENGINE = MyISAM,
PARTITION p20090505 VALUES LESS THAN (733929) ENGINE = MyISAM,
PARTITION p20090506 VALUES LESS THAN (733930) ENGINE = MyISAM,
PARTITION p20090507 VALUES LESS THAN (733931) ENGINE = MyISAM,
PARTITION p20090508 VALUES LESS THAN (733932) ENGINE = MyISAM,
PARTITION p20090509 VALUES LESS THAN (733933) ENGINE = MyISAM,
PARTITION p20090510 VALUES LESS THAN (733934) ENGINE = MyISAM,
PARTITION p20090511 VALUES LESS THAN (733935) ENGINE = MyISAM,
PARTITION p20090512 VALUES LESS THAN (733936) ENGINE = MyISAM,
PARTITION p20090513 VALUES LESS THAN (733937) ENGINE = MyISAM,
PARTITION p20090514 VALUES LESS THAN (733938) ENGINE = MyISAM,
PARTITION p20090515 VALUES LESS THAN (733939) ENGINE = MyISAM,
PARTITION p20090516 VALUES LESS THAN (733940) ENGINE = MyISAM,
PARTITION p20090517 VALUES LESS THAN (733941) ENGINE = MyISAM,
PARTITION p20090518 VALUES LESS THAN (733942) ENGINE = MyISAM,
PARTITION p20090519 VALUES LESS THAN (733943) ENGINE = MyISAM,
PARTITION p20090520 VALUES LESS THAN (733944) ENGINE = MyISAM,
PARTITION p20090521 VALUES LESS THAN (733945) ENGINE = MyISAM,
PARTITION p20090522 VALUES LESS THAN (733946) ENGINE = MyISAM,
PARTITION p20090523 VALUES LESS THAN (733947) ENGINE = MyISAM,
PARTITION p20090524 VALUES LESS THAN (733948) ENGINE = MyISAM,
PARTITION p20090525 VALUES LESS THAN (733949) ENGINE = MyISAM,
PARTITION p20090526 VALUES LESS THAN (733950) ENGINE = MyISAM,
PARTITION p20090527 VALUES LESS THAN (733951) ENGINE = MyISAM,
PARTITION p20090528 VALUES LESS THAN (733952) ENGINE = MyISAM,
PARTITION p20090529 VALUES LESS THAN (733953) ENGINE = MyISAM,
PARTITION p20090530 VALUES LESS THAN (733954) ENGINE = MyISAM,
PARTITION p20090531 VALUES LESS THAN (733955) ENGINE = MyISAM,
PARTITION pMax VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */
myisampack cdr_200905*.MYI
myisamchk -rq cdr_200905*.MYI
mysqladmin flush-tables
alter table cdr_200905 analyze partition all;
+--------------------+---------+----------+-----------------------------------+
| Table | Op | Msg_type | Msg_text |
+--------------------+---------+----------+-----------------------------------+
| nbgstat.cdr_200905 | analyze | Error | Got error 174 from storage engine |
| nbgstat.cdr_200905 | analyze | error | Corrupt |
+--------------------+---------+----------+-----------------------------------+
Edited 1 time(s). Last edit at 06/03/2009 07:38PM by Allen Wang.