NDO: Partitioning nagios_servicechecks

[Rui Miguel Silva Seabra]

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 at 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