NDO: Partitioning nagios_servicechecks

[Rui Miguel Silva Seabra]

On Qui, 2013-06-27 at 09:23 +0100, Rui Miguel Silva Seabra wrote: 

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


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.

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?

Rui
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://www.monitoring-lists.org/archive/users/attachments/20130715/74268b44/attachment.html>
-------------- next part --------------
------------------------------------------------------------------------------
See everything from the browser to the database with AppDynamics
Get end-to-end visibility with application monitoring from AppDynamics
Isolate bottlenecks and diagnose root cause in seconds.
Start your free trial of AppDynamics Pro today!
http://pubads.g.doubleclick.net/gampad/clk?id=48808831&iu=/4140/ostg.clktrk
-------------- next part --------------
_______________________________________________
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