Hi, We'd like to keep nagios_servicecheks for 14 months of current time but, of course, it can get quite big (37GB just for the table with about 6 months of data).
So I tried partitioning it: ALTER TABLE nagios_servicechecks PARTITION BY RANGE(TO_DAYS(end_time)) ( partition p201301 VALUES LESS THAN (TO_DAYS('2013-02-01 00:00:00')), partition p201302 VALUES LESS THAN (TO_DAYS('2013-03-01 00:00:00')), partition p201303 VALUES LESS THAN (TO_DAYS('2013-04-01 00:00:00')), partition p201304 VALUES LESS THAN (TO_DAYS('2013-05-01 00:00:00')), partition p201305 VALUES LESS THAN (TO_DAYS('2013-06-01 00:00:00')), partition p201306 VALUES LESS THAN (TO_DAYS('2013-07-01 00:00:00')), partition p201307 VALUES LESS THAN (TO_DAYS('2013-08-01 00:00:00')), partition p201308 VALUES LESS THAN (TO_DAYS('2013-09-01 00:00:00')), partition p201309 VALUES LESS THAN (TO_DAYS('2013-10-01 00:00:00')), partition p201310 VALUES LESS THAN (TO_DAYS('2013-11-01 00:00:00')), partition p201311 VALUES LESS THAN (TO_DAYS('2013-12-01 00:00:00')), partition p201312 VALUES LESS THAN (TO_DAYS('2014-01-01 00:00:00')), partition p201xxx VALUES LESS THAN maxvalue); However, MySQL complains: ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function As a non expert in MySQL, this appears to me as making little sense since end_time is not a key! CREATE TABLE `nagios_servicechecks` ( `servicecheck_id` int(11) NOT NULL AUTO_INCREMENT, `instance_id` smallint(6) NOT NULL DEFAULT '0', `service_object_id` int(11) NOT NULL DEFAULT '0', `check_type` smallint(6) NOT NULL DEFAULT '0', `current_check_attempt` smallint(6) NOT NULL DEFAULT '0', `max_check_attempts` smallint(6) NOT NULL DEFAULT '0', `state` smallint(6) NOT NULL DEFAULT '0', `state_type` smallint(6) NOT NULL DEFAULT '0', `start_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `start_time_usec` int(11) NOT NULL DEFAULT '0', `end_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `end_time_usec` int(11) NOT NULL DEFAULT '0', `command_object_id` int(11) NOT NULL DEFAULT '0', `command_args` varchar(255) NOT NULL DEFAULT '', `command_line` varchar(255) NOT NULL DEFAULT '', `timeout` smallint(6) NOT NULL DEFAULT '0', `early_timeout` smallint(6) NOT NULL DEFAULT '0', `execution_time` double NOT NULL DEFAULT '0', `latency` double NOT NULL DEFAULT '0', `return_code` smallint(6) NOT NULL DEFAULT '0', `output` varchar(255) NOT NULL DEFAULT '', `long_output` text NOT NULL, `perfdata` text NOT NULL, PRIMARY KEY (`servicecheck_id`), KEY `instance_id` (`instance_id`), KEY `service_object_id` (`service_object_id`), KEY `start_time` (`start_time`) ) ENGINE=MyISAM AUTO_INCREMENT=245571748 DEFAULT CHARSET=latin1 COMMENT='Historical service checks' And there is no added index: mysql> SHOW INDEX FROM nagios_servicechecks; +----------------------+------------+-------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +----------------------+------------+-------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+ | nagios_servicechecks | 0 | PRIMARY | 1 | servicecheck_id | A | 245571747 | NULL | NULL | | BTREE | | | nagios_servicechecks | 1 | instance_id | 1 | instance_id | A | NULL | NULL | NULL | | BTREE | | | nagios_servicechecks | 1 | service_object_id | 1 | service_object_id | A | NULL | NULL | NULL | | BTREE | | | nagios_servicechecks | 1 | start_time | 1 | start_time | A | NULL | NULL | NULL | | BTREE | | +----------------------+------------+-------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+ 4 rows in set (0.01 sec) Has anyone tried this successfully and would like to share some hints? Best regards, Rui ------------------------------------------------------------------------------ This SF.net email is sponsored by Windows: Build for Windows Store. http://p.sf.net/sfu/windows-dev2dev _______________________________________________ Nagios-users mailing list Nagios-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/nagios-users ::: Please include Nagios version, plugin version (-v) and OS when reporting any issue. ::: Messages without supporting info will risk being sent to /dev/null