CaMM (Cacti Message Management) plugin v1.6.7 2010-08-26

[gthe]

Hi,
I think the "cacti-team" has written a beta of the old syslog-plugin - with partitioning in use.
Is this on your plan to use it in the next version?


Torsten

If this will improve performance - it certainly will change.

Hi gthe, any news about a new release?

Regards
Alessio

Possibly this week.

[torstentfk]

I tried to partition the DB into 365 parts - day by day but that is to much for the mysql server So I tried to summarize days and with 14 days it is handy.

Code: Select all

 CREATE TABLE `plugin_camm_syslog` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `host` varchar(128) DEFAULT NULL,
  `sourceip` varchar(45) NOT NULL,
  `facility` varchar(10) DEFAULT NULL,
  `priority` varchar(10) DEFAULT NULL,
  `sys_date` datetime DEFAULT NULL,
  `message` text,
  `status` tinyint(4) NOT NULL DEFAULT '0',
  `alert` smallint(3) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`,`sys_date` ),
  KEY `facility` (`facility`),
  KEY `priority` (`priority`),
  KEY `sourceip` (`sourceip`),
  KEY `status` (`status`),
  KEY `alert` (`alert`),
  KEY `status_date` (`status`,`sys_date`),
  KEY `sys_date` (`sys_date`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='camm plugin SYSLOG Data'
 PARTITION BY RANGE ( dayofyear(sys_date))
(PARTITION syslog_day0 VALUES LESS THAN (0) ENGINE = MyISAM,
 PARTITION syslog_day14 VALUES LESS THAN (14) ENGINE = MyISAM,
 PARTITION syslog_day28 VALUES LESS THAN (28) ENGINE = MyISAM,
 PARTITION syslog_day42 VALUES LESS THAN (42) ENGINE = MyISAM,
 PARTITION syslog_day56 VALUES LESS THAN (56) ENGINE = MyISAM,
 PARTITION syslog_day70 VALUES LESS THAN (70) ENGINE = MyISAM,
 PARTITION syslog_day84 VALUES LESS THAN (84) ENGINE = MyISAM,
 PARTITION syslog_day98 VALUES LESS THAN (98) ENGINE = MyISAM,
 PARTITION syslog_day112 VALUES LESS THAN (112) ENGINE = MyISAM,
 PARTITION syslog_day126 VALUES LESS THAN (126) ENGINE = MyISAM,
 PARTITION syslog_day140 VALUES LESS THAN (140) ENGINE = MyISAM,
 PARTITION syslog_day154 VALUES LESS THAN (154) ENGINE = MyISAM,
 PARTITION syslog_day168 VALUES LESS THAN (168) ENGINE = MyISAM,
 PARTITION syslog_day182 VALUES LESS THAN (182) ENGINE = MyISAM,
 PARTITION syslog_day196 VALUES LESS THAN (196) ENGINE = MyISAM,
 PARTITION syslog_day210 VALUES LESS THAN (210) ENGINE = MyISAM,
 PARTITION syslog_day224 VALUES LESS THAN (224) ENGINE = MyISAM,
 PARTITION syslog_day238 VALUES LESS THAN (238) ENGINE = MyISAM,
 PARTITION syslog_day252 VALUES LESS THAN (252) ENGINE = MyISAM,
 PARTITION syslog_day266 VALUES LESS THAN (266) ENGINE = MyISAM,
 PARTITION syslog_day280 VALUES LESS THAN (280) ENGINE = MyISAM,
 PARTITION syslog_day294 VALUES LESS THAN (294) ENGINE = MyISAM,
 PARTITION syslog_day308 VALUES LESS THAN (308) ENGINE = MyISAM,
 PARTITION syslog_day322 VALUES LESS THAN (322) ENGINE = MyISAM,
 PARTITION syslog_day336 VALUES LESS THAN (336) ENGINE = MyISAM,
 PARTITION syslog_day350 VALUES LESS THAN (350) ENGINE = MyISAM,
 PARTITION syslog_day364 VALUES LESS THAN (364) ENGINE = MyISAM,
 PARTITION syslog_day369 VALUES LESS THAN (369) ENGINE = MyISAM) ;
 ";

Perhaps this helps you for the code. Take care on the index and the time intervall.

Torsten

[TheWitness]

To get the partitioning to span that many partitions, there are file limits and max open tables that have to be adjusted. Also, dayofyear may not allow partition pruning.

TheWitness

[torstentfk]

as we talked yesterday - what would be in this case the best partitioning strategy?
And: which statement should I use to convert the existing one the the part.-one.?

Torsten

[TheWitness]

TO_DAYS(). Use the example in syslog_process.php and in setup.php.

For real large systems, you would use TO_SECONDS, but MySQL will not support till 5.5.

TheWitness

[torstentfk]

thanks - tha is a fast answer - do you sleep??
is there a possibility to convert the existingone with one mysql-command or should I create the new one and copy all lines to the new one?

[TheWitness]

I wolud rename the old table and then loop through the old table 10k rows at a time and insert into the new and then drop the old. Use the sequence number as you guide.

TheWitness

[torstentfk]

Hi,
I think the "cacti-team" has written a beta of the old syslog-plugin - with partitioning in use.
Is this on your plan to use it in the next version?
Torsten

If this will improve performance - it certainly will change.

Hi gthe, any news about a new release?
Regards
Alessio

Possibly this week.

I tested the partioned mysql-db with the new syslog-plugin and there was a great speed up. I tried to partition my DB but there was no use of this due to the old php-mysql-code. The source code has to be changed to use the new db structure (see hint on TheWitness-post).
So I will wait for the next version.......................

Torsten

[TheWitness]

Hes working on it.

TheWitness

[gthe]

I tested the partioned mysql-db with the new syslog-plugin and there was a great speed up. I tried to partition my DB but there was no use of this due to the old php-mysql-code. The source code has to be changed to use the new db structure (see hint on TheWitness-post).
So I will wait for the next version.......................

Torsten

I installed two test bases - one with and one without partitions. In each - the same set of data - 1.8 million rows. Used the latest public version camm - I did not notice any difference in speed.

From mysql - http://dev.mysql.com/doc/refman/5.1/en/ ... rview.html

Some queries can be greatly optimized in virtue of the fact that data satisfying a given WHERE clause can be stored only on one or more partitions, thereby excluding any remaining partitions from the search.

It is obvious that either the scheme of constructing queries in camm does not allow for additional optimization, or is it a maximum of possible speed.
I think it is necessary to compare the performance of both plug-ins. But they are (currently) has different data structure - so I need to create a different database with the same data and check.

[TheWitness]

I work with customers with very large databases and i can assure you that partitioning is essential.

TheWitness

[gthe]

I do not argue that partitioning gives a significant result in the plugin syslog. But in the plugin camm I unnoticeable difference.
Time to sample the same amount of data in camm and syslog v1.0 beta is now comparable

[smlick]

I have a db of over 6 million rows and I have some problem about speed and db management, so partitioning is the solution?
As I read seems that CAMM support it yet, so I can use it now or is better to use it (in a production environment) with the next release?

Regards
Alessio

[TheWitness]

Partitioning will fix this problem. Gthe just needs some help with the light bulb. It's still off.

TheWitness

[smlick]

Partitioning will fix this problem. Gthe just needs some help with the light bulb. It's still off.

TheWitness

Can you tell us some tips to succesfully partitioning the plugin_camm_syslog db?

Regards
Alessio