MySQL Forums
Forum List  »  Partitioning

Wether is it possible to compress partitioned MyISAM tables?
Posted by: Allen Wang
Date: June 02, 2009 09:30PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Wether is it possible to compress partitioned MyISAM tables?
6306
June 02, 2009 09:30PM


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.