<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 TRANSITIONAL//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=UTF-8">
<META NAME="GENERATOR" CONTENT="GtkHTML/4.6.5">
</HEAD>
<BODY>
On Qui, 2013-06-27 at 09:23 +0100, Rui Miguel Silva Seabra wrote:
<BLOCKQUOTE TYPE=CITE>
<PRE>
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
</PRE>
</BLOCKQUOTE>
<BR>
For the record, the only way I found out how to make this work, was to make servicecheck_id not unique, but still a key.<BR>
<BR>
Due to the number of servicechecks one might have, I have my doubts whether that could be a problem, can anyone tell me he knows for sure it is a problem?<BR>
<BR>
Rui
</BODY>
</HTML>