Zabbix, monitoring last mile ports and optimizing work with the database using partitioning

Working with the largest provider of the Russian Federation faced with the situation that the subscriber is contacting problems in the recent past, i.e. Yesterday everything was bad, but now it worked. What to do in this case? There are options to use monitoring systems that will collect key parameters of all subscriber lines and store them for some time, and the TP operator will be able to easily access this data to solve such situations. Also, having these data, you can give an automatic expert assessment for each subscriber line, and if you wish, on xDSL, automatically select the most appropriate profile. Using the same data, you can identify defective lines or lines with unsatisfactory parameters and eliminate possible problems of subscribers even before they contact the TP.
At first glance, the task is not complicated, but when the amount of equipment easily oversteps thousands of access nodes, and the number of subscriber ports can amount to tens of thousands, some features of setting up and running such a system appear, with maximum automation.
If interested, welcome under the cat.


There was only one HP server with two processors and 4 cores on board, the memory was 10G, Raid5 132G 15000 SAS.
The main number of subscriber ports are ADSL ports on various DSLAMs, both high-capacity — up to 576 ports per “basket” and low-port ports — from 16 to 64 ports, depending on the configuration.

Determine how we will monitor and build triggers

What can I get with dslam'a? Almost everything, the only question is how to do it. For me, the surest way is a snmp survey. The trouble was that the MIB files for most of the glands are not accessible to mere mortals due to the manufacturer's tricks with entering into heaps of contracts and other obstacles that I don’t understand (to the honor of the huawei mibs, they still sent them without various contracts, but for another supplier I can not get). If your situation is similar to mine then we take snmpwalk and analyze everything that the piece of hardware produces, and it will give out a lot of text megabytes. The analysis of the received oid is a very laborious process. Well, if there are ready-made templates for zabbix or there is a mib (then templates of them can be made quickly and easily by reading this topic “connect any piece of hardware that has an MIB” habrahabr.ru/blogs/sysadm/85156 )
After we received the templates, we need to determine what we will collect, with what frequency and how long to store. All these parameters are eventually set in the working template which we will bind to the glands.
')
My set was not very large, for example, for a large capacity dslam, the parameters collected from the first port


1.01ifAtucFfrADSL 1.01ifAtucFrADSL
1.01ifAtucLossADSL
1.01ifDownSnrADSL 1.01ifUpSnrADSL
1.01ifDownSpeedADSL 1.01ifUpSpeedADSL
,
1.01ifOperPhysicalStatADSL
1.01ifOperStatADSL

And an additional template with elements that can be included.

1.01ifDownAttenuationADSL 1.01ifUpAttenuationADSL
1.01ifDownOutputPowerADSL 1.01ifUpOutputPowerADSL
1.01ifDownSpeedMaxADSL 1.01ifUpSpeedMaxADSL
1.01ifProfSpeedADSL

If you use the data described above, you can write a trigger that will take the current profile, the maximum speed and the current speed and based on this data, if the speed specified by the profile is lower than the real one and the maximum achievable, suggest or independently change the profile to another with a lower speed, and vice versa if the maximum speed is higher than the real one and higher than the one set in the profile, then suggest changing or changing the profile to another one with a higher speed.

I note that these triggers did not take root because of the banal clogging of all the recommendations for changing profiles at ports, but I was afraid to give it to the automation.

There is one more variant of very useful triggers, these are triggers on the magnitude of noise, on attenuation, on the change in time (delta function) of the number of errors and reconnects and other joys that can very much deliver to subscribers to get subscribers. Again, these triggers did not catch on for the same reason. It is heavy on your soul when you have a couple of hundred ports considered unsatisfactory. ;)

For nodes using the FTTx technology, the collected parameters are somewhat different, now only the delta function of the number of errors on the subscriber port was collected, if they are more than a certain value over a period of time, we deduce the port as a list of those requiring attention.
Here is an example of data collected from a single port.

Data can be represented as a graph over time.


I also had some interesting Chinese Edge-core switches from which I got rid of in favor of Huawei, so with them I made such a template that interrogated the measurement results of subscriber cables with a reflectometer and analyzing the results produced a list of ports with faulty UTPs. These measurements could be carried out by sending a command to the snmp glands either using a zabix script or with a crown. I did not finish this template due to the replacement of all the hedgehogs on the huyavay.

The overall picture and the approach to monitoring I think are clear, if there are more specific questions, for some not very lighted moments, then ask, I will try to answer.

Let's proceed to setting up the database under Zabbix

At the moment, the last stable 1.8.5 branch can be downloaded from the official site www.zabbix.com/download.php
I will not give instructions on how to install it and do the initial setup, as it is on the website www.zabbix.com/documentation/start

I have everything on Ubuntu server 10.04 64 bit. Postgresql 8.4 was originally used as a database. Further 9.0 has now been moved to MySQL 5.5, installed on the server according to the official manual and a small doping file. Reason for moving support partitioning table dev.mysql.com/doc/refman/5.5/en/partitioning.html "(well, I couldn’t get postgres to work as fast as the muscle didn’t fight, I don’t know how to prepare it, Shl partition table is in the post-regression, but it is implemented somewhat differently than in the muscle).

MySQL configuration is not complicated and is well described on the forum. Here is an example of the topic on which I set up MySQL www.zabbix.com/forum/showthread.php?t=12407

I will note the key mandatory option
innodb_file_per_table

The default engine must be set innodb, utf-8 encoding.
If you are very curious lay out config.

Zabbix stores in the database a set of values; the main large tables are hystory * and trend *
Initially, in the history tables I had up to 500 million records and this table weighed about 40 gigs with an index file, which caused some performance problems.
Data collected from subscriber ports are stored on average for about 3 days. Old data is removed by the housekeeper, leaving fragmented tables after that. I do not need to explain how bad it is.
What to do in this case? The answer is - use partition table with time division. For this, I used the experience from the blog of the Brazilian comrade zabbixzone.com/zabbix/partitioning-tables
He proposes to divide the table into 2 types: large - with division by days, and not very large - with division by months.
Here are the tables by day:

• history
• history_log
• history_str
• history_text
• history_uint

and divided by month:

• acknowledges
• alerts
• auditlog
• events
• service_alarms
• trends
• trends_unit

I already had a set up monitoring system and in order to add what my friend suggested, the following actions had to be done (they are almost the same as in the above mentioned blog).

First we make a dump of each table that we are going to change.
mysqldump --no-create-info --lock-tables zabbix history_str > 09.06.2011_history_str.sql

Clear the above tables from the data.
Slightly changing table structures

ALTER TABLE `acknowledges` DROP PRIMARY KEY , ADD KEY `acknowledgedid` (`acknowledgeid`);
ALTER TABLE `alerts` DROP PRIMARY KEY , ADD KEY `alertid` (`alertid`);
ALTER TABLE `auditlog` DROP PRIMARY KEY , ADD KEY `auditid` (`auditid`);
ALTER TABLE `events` DROP PRIMARY KEY , ADD KEY `eventid` (`eventid`);
ALTER TABLE `service_alarms` DROP PRIMARY KEY , ADD KEY `servicealarmid` (`servicealarmid`);
ALTER TABLE `history_log` DROP PRIMARY KEY , ADD PRIMARY KEY (`itemid`,`id`,`clock`);
ALTER TABLE `history_log` DROP KEY `history_log_2`;
ALTER TABLE `history_text` DROP PRIMARY KEY , ADD PRIMARY KEY (`itemid`,`id`,`clock`);
ALTER TABLE `history_text` DROP KEY `history_text_2`;

* This source code was highlighted with Source Code Highlighter .

We create partition table with division by months till 2013

ALTER TABLE `acknowledges` PARTITION BY RANGE( clock ) (
PARTITION p201105 VALUES LESS THAN (UNIX_TIMESTAMP("2011-06-01 00:00:00")),
PARTITION p201106 VALUES LESS THAN (UNIX_TIMESTAMP("2011-07-01 00:00:00")),
PARTITION p201107 VALUES LESS THAN (UNIX_TIMESTAMP("2011-08-01 00:00:00")),
PARTITION p201108 VALUES LESS THAN (UNIX_TIMESTAMP("2011-09-01 00:00:00")),
PARTITION p201109 VALUES LESS THAN (UNIX_TIMESTAMP("2011-10-01 00:00:00")),
PARTITION p201110 VALUES LESS THAN (UNIX_TIMESTAMP("2011-11-01 00:00:00")),
PARTITION p201111 VALUES LESS THAN (UNIX_TIMESTAMP("2011-12-01 00:00:00")),
PARTITION p201112 VALUES LESS THAN (UNIX_TIMESTAMP("2012-01-01 00:00:00")),
PARTITION p201201 VALUES LESS THAN (UNIX_TIMESTAMP("2012-02-01 00:00:00")),
PARTITION p201202 VALUES LESS THAN (UNIX_TIMESTAMP("2012-03-01 00:00:00")),
PARTITION p201203 VALUES LESS THAN (UNIX_TIMESTAMP("2012-04-01 00:00:00")),
PARTITION p201204 VALUES LESS THAN (UNIX_TIMESTAMP("2012-05-01 00:00:00")),
PARTITION p201205 VALUES LESS THAN (UNIX_TIMESTAMP("2012-06-01 00:00:00")),
PARTITION p201206 VALUES LESS THAN (UNIX_TIMESTAMP("2012-07-01 00:00:00")),
PARTITION p201207 VALUES LESS THAN (UNIX_TIMESTAMP("2012-08-01 00:00:00")),
PARTITION p201208 VALUES LESS THAN (UNIX_TIMESTAMP("2012-09-01 00:00:00")),
PARTITION p201209 VALUES LESS THAN (UNIX_TIMESTAMP("2012-10-01 00:00:00")),
PARTITION p201210 VALUES LESS THAN (UNIX_TIMESTAMP("2012-11-01 00:00:00")),
PARTITION p201211 VALUES LESS THAN (UNIX_TIMESTAMP("2012-12-01 00:00:00")),
PARTITION p201212 VALUES LESS THAN (UNIX_TIMESTAMP("2013-01-01 00:00:00"))
);

ALTER TABLE `alerts` PARTITION BY RANGE( clock ) (
PARTITION p201105 VALUES LESS THAN (UNIX_TIMESTAMP("2011-06-01 00:00:00")),
PARTITION p201106 VALUES LESS THAN (UNIX_TIMESTAMP("2011-07-01 00:00:00")),
PARTITION p201107 VALUES LESS THAN (UNIX_TIMESTAMP("2011-08-01 00:00:00")),
PARTITION p201108 VALUES LESS THAN (UNIX_TIMESTAMP("2011-09-01 00:00:00")),
PARTITION p201109 VALUES LESS THAN (UNIX_TIMESTAMP("2011-10-01 00:00:00")),
PARTITION p201110 VALUES LESS THAN (UNIX_TIMESTAMP("2011-11-01 00:00:00")),
PARTITION p201111 VALUES LESS THAN (UNIX_TIMESTAMP("2011-12-01 00:00:00")),
PARTITION p201112 VALUES LESS THAN (UNIX_TIMESTAMP("2012-01-01 00:00:00")),
PARTITION p201201 VALUES LESS THAN (UNIX_TIMESTAMP("2012-02-01 00:00:00")),
PARTITION p201202 VALUES LESS THAN (UNIX_TIMESTAMP("2012-03-01 00:00:00")),
PARTITION p201203 VALUES LESS THAN (UNIX_TIMESTAMP("2012-04-01 00:00:00")),
PARTITION p201204 VALUES LESS THAN (UNIX_TIMESTAMP("2012-05-01 00:00:00")),
PARTITION p201205 VALUES LESS THAN (UNIX_TIMESTAMP("2012-06-01 00:00:00")),
PARTITION p201206 VALUES LESS THAN (UNIX_TIMESTAMP("2012-07-01 00:00:00")),
PARTITION p201207 VALUES LESS THAN (UNIX_TIMESTAMP("2012-08-01 00:00:00")),
PARTITION p201208 VALUES LESS THAN (UNIX_TIMESTAMP("2012-09-01 00:00:00")),
PARTITION p201209 VALUES LESS THAN (UNIX_TIMESTAMP("2012-10-01 00:00:00")),
PARTITION p201210 VALUES LESS THAN (UNIX_TIMESTAMP("2012-11-01 00:00:00")),
PARTITION p201211 VALUES LESS THAN (UNIX_TIMESTAMP("2012-12-01 00:00:00")),
PARTITION p201212 VALUES LESS THAN (UNIX_TIMESTAMP("2013-01-01 00:00:00"))
);

ALTER TABLE `auditlog` PARTITION BY RANGE( clock ) (
PARTITION p201105 VALUES LESS THAN (UNIX_TIMESTAMP("2011-06-01 00:00:00")),
PARTITION p201106 VALUES LESS THAN (UNIX_TIMESTAMP("2011-07-01 00:00:00")),
PARTITION p201107 VALUES LESS THAN (UNIX_TIMESTAMP("2011-08-01 00:00:00")),
PARTITION p201108 VALUES LESS THAN (UNIX_TIMESTAMP("2011-09-01 00:00:00")),
PARTITION p201109 VALUES LESS THAN (UNIX_TIMESTAMP("2011-10-01 00:00:00")),
PARTITION p201110 VALUES LESS THAN (UNIX_TIMESTAMP("2011-11-01 00:00:00")),
PARTITION p201111 VALUES LESS THAN (UNIX_TIMESTAMP("2011-12-01 00:00:00")),
PARTITION p201112 VALUES LESS THAN (UNIX_TIMESTAMP("2012-01-01 00:00:00")),
PARTITION p201201 VALUES LESS THAN (UNIX_TIMESTAMP("2012-02-01 00:00:00")),
PARTITION p201202 VALUES LESS THAN (UNIX_TIMESTAMP("2012-03-01 00:00:00")),
PARTITION p201203 VALUES LESS THAN (UNIX_TIMESTAMP("2012-04-01 00:00:00")),
PARTITION p201204 VALUES LESS THAN (UNIX_TIMESTAMP("2012-05-01 00:00:00")),
PARTITION p201205 VALUES LESS THAN (UNIX_TIMESTAMP("2012-06-01 00:00:00")),
PARTITION p201206 VALUES LESS THAN (UNIX_TIMESTAMP("2012-07-01 00:00:00")),
PARTITION p201207 VALUES LESS THAN (UNIX_TIMESTAMP("2012-08-01 00:00:00")),
PARTITION p201208 VALUES LESS THAN (UNIX_TIMESTAMP("2012-09-01 00:00:00")),
PARTITION p201209 VALUES LESS THAN (UNIX_TIMESTAMP("2012-10-01 00:00:00")),
PARTITION p201210 VALUES LESS THAN (UNIX_TIMESTAMP("2012-11-01 00:00:00")),
PARTITION p201211 VALUES LESS THAN (UNIX_TIMESTAMP("2012-12-01 00:00:00")),
PARTITION p201212 VALUES LESS THAN (UNIX_TIMESTAMP("2013-01-01 00:00:00"))
);

ALTER TABLE `events` PARTITION BY RANGE( clock ) (
PARTITION p201105 VALUES LESS THAN (UNIX_TIMESTAMP("2011-06-01 00:00:00")),
PARTITION p201106 VALUES LESS THAN (UNIX_TIMESTAMP("2011-07-01 00:00:00")),
PARTITION p201107 VALUES LESS THAN (UNIX_TIMESTAMP("2011-08-01 00:00:00")),
PARTITION p201108 VALUES LESS THAN (UNIX_TIMESTAMP("2011-09-01 00:00:00")),
PARTITION p201109 VALUES LESS THAN (UNIX_TIMESTAMP("2011-10-01 00:00:00")),
PARTITION p201110 VALUES LESS THAN (UNIX_TIMESTAMP("2011-11-01 00:00:00")),
PARTITION p201111 VALUES LESS THAN (UNIX_TIMESTAMP("2011-12-01 00:00:00")),
PARTITION p201112 VALUES LESS THAN (UNIX_TIMESTAMP("2012-01-01 00:00:00")),
PARTITION p201201 VALUES LESS THAN (UNIX_TIMESTAMP("2012-02-01 00:00:00")),
PARTITION p201202 VALUES LESS THAN (UNIX_TIMESTAMP("2012-03-01 00:00:00")),
PARTITION p201203 VALUES LESS THAN (UNIX_TIMESTAMP("2012-04-01 00:00:00")),
PARTITION p201204 VALUES LESS THAN (UNIX_TIMESTAMP("2012-05-01 00:00:00")),
PARTITION p201205 VALUES LESS THAN (UNIX_TIMESTAMP("2012-06-01 00:00:00")),
PARTITION p201206 VALUES LESS THAN (UNIX_TIMESTAMP("2012-07-01 00:00:00")),
PARTITION p201207 VALUES LESS THAN (UNIX_TIMESTAMP("2012-08-01 00:00:00")),
PARTITION p201208 VALUES LESS THAN (UNIX_TIMESTAMP("2012-09-01 00:00:00")),
PARTITION p201209 VALUES LESS THAN (UNIX_TIMESTAMP("2012-10-01 00:00:00")),
PARTITION p201210 VALUES LESS THAN (UNIX_TIMESTAMP("2012-11-01 00:00:00")),
PARTITION p201211 VALUES LESS THAN (UNIX_TIMESTAMP("2012-12-01 00:00:00")),
PARTITION p201212 VALUES LESS THAN (UNIX_TIMESTAMP("2013-01-01 00:00:00"))
);

ALTER TABLE `service_alarms` PARTITION BY RANGE( clock ) (
PARTITION p201105 VALUES LESS THAN (UNIX_TIMESTAMP("2011-06-01 00:00:00")),
PARTITION p201106 VALUES LESS THAN (UNIX_TIMESTAMP("2011-07-01 00:00:00")),
PARTITION p201107 VALUES LESS THAN (UNIX_TIMESTAMP("2011-08-01 00:00:00")),
PARTITION p201108 VALUES LESS THAN (UNIX_TIMESTAMP("2011-09-01 00:00:00")),
PARTITION p201109 VALUES LESS THAN (UNIX_TIMESTAMP("2011-10-01 00:00:00")),
PARTITION p201110 VALUES LESS THAN (UNIX_TIMESTAMP("2011-11-01 00:00:00")),
PARTITION p201111 VALUES LESS THAN (UNIX_TIMESTAMP("2011-12-01 00:00:00")),
PARTITION p201112 VALUES LESS THAN (UNIX_TIMESTAMP("2012-01-01 00:00:00")),
PARTITION p201201 VALUES LESS THAN (UNIX_TIMESTAMP("2012-02-01 00:00:00")),
PARTITION p201202 VALUES LESS THAN (UNIX_TIMESTAMP("2012-03-01 00:00:00")),
PARTITION p201203 VALUES LESS THAN (UNIX_TIMESTAMP("2012-04-01 00:00:00")),
PARTITION p201204 VALUES LESS THAN (UNIX_TIMESTAMP("2012-05-01 00:00:00")),
PARTITION p201205 VALUES LESS THAN (UNIX_TIMESTAMP("2012-06-01 00:00:00")),
PARTITION p201206 VALUES LESS THAN (UNIX_TIMESTAMP("2012-07-01 00:00:00")),
PARTITION p201207 VALUES LESS THAN (UNIX_TIMESTAMP("2012-08-01 00:00:00")),
PARTITION p201208 VALUES LESS THAN (UNIX_TIMESTAMP("2012-09-01 00:00:00")),
PARTITION p201209 VALUES LESS THAN (UNIX_TIMESTAMP("2012-10-01 00:00:00")),
PARTITION p201210 VALUES LESS THAN (UNIX_TIMESTAMP("2012-11-01 00:00:00")),
PARTITION p201211 VALUES LESS THAN (UNIX_TIMESTAMP("2012-12-01 00:00:00")),
PARTITION p201212 VALUES LESS THAN (UNIX_TIMESTAMP("2013-01-01 00:00:00"))
);

* This source code was highlighted with Source Code Highlighter .

We create partition table with division by days

ALTER TABLE `history_uint` PARTITION BY RANGE( clock ) (
PARTITION p20110603 VALUES LESS THAN (UNIX_TIMESTAMP("2011-06-03 00:00:00")),
PARTITION p20110604 VALUES LESS THAN (UNIX_TIMESTAMP("2011-06-04 00:00:00")),
PARTITION p20110605 VALUES LESS THAN (UNIX_TIMESTAMP("2011-06-05 00:00:00")),
PARTITION p20110606 VALUES LESS THAN (UNIX_TIMESTAMP("2011-06-06 00:00:00")),
PARTITION p20110607 VALUES LESS THAN (UNIX_TIMESTAMP("2011-06-07 00:00:00")),
PARTITION p20110608 VALUES LESS THAN (UNIX_TIMESTAMP("2011-06-08 00:00:00")),
PARTITION p20110609 VALUES LESS THAN (UNIX_TIMESTAMP("2011-06-09 00:00:00")),
PARTITION p20110610 VALUES LESS THAN (UNIX_TIMESTAMP("2011-06-10 00:00:00")),
PARTITION p20110611 VALUES LESS THAN (UNIX_TIMESTAMP("2011-06-11 00:00:00"))
);

ALTER TABLE `history_log` PARTITION BY RANGE( clock ) (
PARTITION p20110603 VALUES LESS THAN (UNIX_TIMESTAMP("2011-06-03 00:00:00")),
PARTITION p20110604 VALUES LESS THAN (UNIX_TIMESTAMP("2011-06-04 00:00:00")),
PARTITION p20110605 VALUES LESS THAN (UNIX_TIMESTAMP("2011-06-05 00:00:00")),
PARTITION p20110606 VALUES LESS THAN (UNIX_TIMESTAMP("2011-06-06 00:00:00")),
PARTITION p20110607 VALUES LESS THAN (UNIX_TIMESTAMP("2011-06-07 00:00:00")),
PARTITION p20110608 VALUES LESS THAN (UNIX_TIMESTAMP("2011-06-08 00:00:00")),
PARTITION p20110609 VALUES LESS THAN (UNIX_TIMESTAMP("2011-06-09 00:00:00")),
PARTITION p20110610 VALUES LESS THAN (UNIX_TIMESTAMP("2011-06-10 00:00:00")),
PARTITION p20110611 VALUES LESS THAN (UNIX_TIMESTAMP("2011-06-11 00:00:00"))
);

ALTER TABLE `history_text` PARTITION BY RANGE( clock ) (
PARTITION p20110603 VALUES LESS THAN (UNIX_TIMESTAMP("2011-06-03 00:00:00")),
PARTITION p20110604 VALUES LESS THAN (UNIX_TIMESTAMP("2011-06-04 00:00:00")),
PARTITION p20110605 VALUES LESS THAN (UNIX_TIMESTAMP("2011-06-05 00:00:00")),
PARTITION p20110606 VALUES LESS THAN (UNIX_TIMESTAMP("2011-06-06 00:00:00")),
PARTITION p20110607 VALUES LESS THAN (UNIX_TIMESTAMP("2011-06-07 00:00:00")),
PARTITION p20110608 VALUES LESS THAN (UNIX_TIMESTAMP("2011-06-08 00:00:00")),
PARTITION p20110609 VALUES LESS THAN (UNIX_TIMESTAMP("2011-06-09 00:00:00")),
PARTITION p20110610 VALUES LESS THAN (UNIX_TIMESTAMP("2011-06-10 00:00:00")),
PARTITION p20110611 VALUES LESS THAN (UNIX_TIMESTAMP("2011-06-11 00:00:00"))
);

ALTER TABLE `history` PARTITION BY RANGE( clock ) (
PARTITION p20110603 VALUES LESS THAN (UNIX_TIMESTAMP("2011-06-03 00:00:00")),
PARTITION p20110604 VALUES LESS THAN (UNIX_TIMESTAMP("2011-06-04 00:00:00")),
PARTITION p20110605 VALUES LESS THAN (UNIX_TIMESTAMP("2011-06-05 00:00:00")),
PARTITION p20110606 VALUES LESS THAN (UNIX_TIMESTAMP("2011-06-06 00:00:00")),
PARTITION p20110607 VALUES LESS THAN (UNIX_TIMESTAMP("2011-06-07 00:00:00")),
PARTITION p20110608 VALUES LESS THAN (UNIX_TIMESTAMP("2011-06-08 00:00:00")),
PARTITION p20110609 VALUES LESS THAN (UNIX_TIMESTAMP("2011-06-09 00:00:00")),
PARTITION p20110610 VALUES LESS THAN (UNIX_TIMESTAMP("2011-06-10 00:00:00")),
PARTITION p20110611 VALUES LESS THAN (UNIX_TIMESTAMP("2011-06-11 00:00:00"))
);

ALTER TABLE `history_str` PARTITION BY RANGE( clock ) (
PARTITION p20110603 VALUES LESS THAN (UNIX_TIMESTAMP("2011-06-03 00:00:00")),
PARTITION p20110604 VALUES LESS THAN (UNIX_TIMESTAMP("2011-06-04 00:00:00")),
PARTITION p20110605 VALUES LESS THAN (UNIX_TIMESTAMP("2011-06-05 00:00:00")),
PARTITION p20110606 VALUES LESS THAN (UNIX_TIMESTAMP("2011-06-06 00:00:00")),
PARTITION p20110607 VALUES LESS THAN (UNIX_TIMESTAMP("2011-06-07 00:00:00")),
PARTITION p20110608 VALUES LESS THAN (UNIX_TIMESTAMP("2011-06-08 00:00:00")),
PARTITION p20110609 VALUES LESS THAN (UNIX_TIMESTAMP("2011-06-09 00:00:00")),
PARTITION p20110610 VALUES LESS THAN (UNIX_TIMESTAMP("2011-06-10 00:00:00")),
PARTITION p20110611 VALUES LESS THAN (UNIX_TIMESTAMP("2011-06-11 00:00:00"))
);

* This source code was highlighted with Source Code Highlighter .

Restore the data of each table as follows (substituting for each table your own dump file name)
mysql zabbix < 09.06.2011_history_str.sql

In the end result, the data will be stored very conveniently - every day / month in a separate file, a separate part of the table.

ls -l /var/lib/mysql/zabbix/ |grep history_u
-rw-rw---- 1 mysql mysql 8628 2011-06-08 14:00 history_uint.frm
-rw-rw---- 1 mysql mysql 140 2011-06-08 14:00 history_uint.par
-rw-rw---- 1 mysql mysql 457179136 2011-06-07 11:34 history_uint#P#p20110605.ibd
-rw-rw---- 1 mysql mysql 557842432 2011-06-07 11:34 history_uint#P#p20110606.ibd
-rw-rw---- 1 mysql mysql 620756992 2011-06-07 11:34 history_uint#P#p20110607.ibd
-rw-rw---- 1 mysql mysql 629145600 2011-06-08 00:03 history_uint#P#p20110608.ibd
-rw-rw---- 1 mysql mysql 666894336 2011-06-09 00:08 history_uint#P#p20110609.ibd
-rw-rw---- 1 mysql mysql 423624704 2011-06-09 14:55 history_uint#P#p20110610.ibd
-rw-rw---- 1 mysql mysql 114688 2011-06-07 11:09 history_uint#P#p20110611.ibd
-rw-rw---- 1 mysql mysql 114688 2011-06-07 11:44 history_uint#P#p20110612.ibd
-rw-rw---- 1 mysql mysql 114688 2011-06-07 11:44 history_uint#P#p20110613.ibd
-rw-rw---- 1 mysql mysql 114688 2011-06-07 11:44 history_uint#P#p20110614.ibd
-rw-rw---- 1 mysql mysql 114688 2011-06-08 14:00 history_uint#P#p20110615.ibd
-rw-rw---- 1 mysql mysql 8688 2011-05-27 21:30 history_uint_sync.frm
-rw-rw---- 1 mysql mysql 131072 2011-05-27 21:30 history_uint_sync.ibd


Clearing old data is done with a simple command (substitute the table you need instead of `history_uint`)
ALTER TABLE `history_uint` DROP PARTITION p20110604;
Adding a new part of the table
ALTER TABLE `history_uint` ADD PARTITION (PARTITION p20110628 VALUES LESS THAN (UNIX_TIMESTAMP("2011-06-29 00:00:00")));

Hands to clean the tables and add new ones as it is not interesting, again in the blog they offer a procedure for the tables divided by days, the code is taken here pastebin.com/ijyKkxLh in order to save it for posterity, I will post it here without changes.

/**************************************************************
MySQL Auto Partitioning Procedure for Zabbix 1.8
zabbixzone.com/zabbix/partitioning-tables

Author: Ricardo Santos (rsantos at gmail.com)
Version: 20110518
**************************************************************/
DELIMITER //
DROP PROCEDURE IF EXISTS `zabbix`.`create_zabbix_partitions` //
CREATE PROCEDURE `zabbix`.`create_zabbix_partitions` ()
BEGIN
CALL zabbix.create_next_partitions("zabbix","history");
CALL zabbix.create_next_partitions("zabbix","history_log");
CALL zabbix.create_next_partitions("zabbix","history_str");
CALL zabbix.create_next_partitions("zabbix","history_text");
CALL zabbix.create_next_partitions("zabbix","history_uint");
CALL zabbix.drop_old_partitions("zabbix","history");
CALL zabbix.drop_old_partitions("zabbix","history_log");
CALL zabbix.drop_old_partitions("zabbix","history_str");
CALL zabbix.drop_old_partitions("zabbix","history_text");
CALL zabbix.drop_old_partitions("zabbix","history_uint");
END //
DROP PROCEDURE IF EXISTS `zabbix`.`create_next_partitions` //
CREATE PROCEDURE `zabbix`.`create_next_partitions` (SCHEMANAME varchar (64), TABLENAME varchar (64))
BEGIN
DECLARE NEXTCLOCK timestamp ;
DECLARE PARTITIONNAME varchar (16);
DECLARE CLOCK int ;
SET @totaldays = 7;
SET @i = 1;
createloop: LOOP
SET NEXTCLOCK = DATE_ADD(NOW(), INTERVAL @i DAY );
SET PARTITIONNAME = DATE_FORMAT( NEXTCLOCK, 'p%Y%m%d' );
SET CLOCK = UNIX_TIMESTAMP(DATE_FORMAT(DATE_ADD( NEXTCLOCK , INTERVAL 1 DAY ), '%Y-%m-%d 00:00:00' ));
CALL zabbix.create_partition( SCHEMANAME, TABLENAME, PARTITIONNAME, CLOCK );
SET @i=@i+1;
IF @i > @totaldays THEN
LEAVE createloop;
END IF ;
END LOOP;
END //
DROP PROCEDURE IF EXISTS `zabbix`.`drop_old_partitions` //
CREATE PROCEDURE `zabbix`.`drop_old_partitions` (SCHEMANAME varchar (64), TABLENAME varchar (64))
BEGIN
DECLARE OLDCLOCK timestamp ;
DECLARE PARTITIONNAME varchar (16);
DECLARE CLOCK int ;
SET @mindays = 3;
SET @maxdays = @mindays+4;
SET @i = @maxdays;
droploop: LOOP
SET OLDCLOCK = DATE_SUB(NOW(), INTERVAL @i DAY );
SET PARTITIONNAME = DATE_FORMAT( OLDCLOCK, 'p%Y%m%d' );
CALL zabbix.drop_partition( SCHEMANAME, TABLENAME, PARTITIONNAME );
SET @i=@i-1;
IF @i <= @mindays THEN
LEAVE droploop;
END IF ;
END LOOP;
END //
DROP PROCEDURE IF EXISTS `zabbix`.`create_partition` //
CREATE PROCEDURE `zabbix`.`create_partition` (SCHEMANAME varchar (64), TABLENAME varchar (64), PARTITIONNAME varchar (64), CLOCK int )
BEGIN
DECLARE RETROWS int ;
SELECT COUNT (1) INTO RETROWS
FROM `information_schema`.`partitions`
WHERE `table_schema` = SCHEMANAME AND `table_name` = TABLENAME AND `partition_name` = PARTITIONNAME;

IF RETROWS = 0 THEN
SELECT CONCAT( "create_partition(", SCHEMANAME, ",", TABLENAME, ",", PARTITIONNAME, ",", CLOCK, ")" ) AS msg;
SET @ sql = CONCAT( 'ALTER TABLE `' , SCHEMANAME, '`.`' , TABLENAME, '`' ,
' ADD PARTITION (PARTITION ' , PARTITIONNAME, ' VALUES LESS THAN (' , CLOCK, '));' );
PREPARE STMT FROM @ sql ;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;
END IF ;
END //
DROP PROCEDURE IF EXISTS `zabbix`.`drop_partition` //
CREATE PROCEDURE `zabbix`.`drop_partition` (SCHEMANAME varchar (64), TABLENAME varchar (64), PARTITIONNAME varchar (64))
BEGIN
DECLARE RETROWS int ;
SELECT COUNT (1) INTO RETROWS
FROM `information_schema`.`partitions`
WHERE `table_schema` = SCHEMANAME AND `table_name` = TABLENAME AND `partition_name` = PARTITIONNAME;

IF RETROWS = 1 THEN
SELECT CONCAT( "drop_partition(", SCHEMANAME, ",", TABLENAME, ",", PARTITIONNAME, ")" ) AS msg;
SET @ sql = CONCAT( 'ALTER TABLE `' , SCHEMANAME, '`.`' , TABLENAME, '`' ,
' DROP PARTITION ' , PARTITIONNAME, ';' );
PREPARE STMT FROM @ sql ;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;
END IF ;
END //
DELIMITER ;

* This source code was highlighted with Source Code Highlighter .

It now remains to add this cron code every 3 days.
the line in the crown config will look something like this
0 6 */3 * * ___ mysql -B -h localhost -u zabbix -p zabbix -e "CALL create_zabbix_partitions();"

With the configuration of the database is finished.

Set up the automatic binding of templates on the number of boards on the equipment

My glands are quite large and are scaled by adding cards with subscriber ports. In order not to interrogate non-existing ports in the piece of iron, I broke all the templates into boards. Added a detection rule that once a day knocks on a certain oid. The answer from this question allows you to know whether the board is installed in the slot or not. Scanning goes over the entire range of ip glands.

Added two actions that either bind the template of this card to the node, or untie the template from it, performing it depending on the value received during detection.



By such simple actions we get rid of the tedious exercise of monitoring the number of installed cards in the access nodes.

What did we get?

Download proca and other different graphics below.
On the database size chart, you can see two drops, the first drop is the first run of the procedure, the second drop in a day is a manual check of the procedure.



Brief quality characteristics


As can be seen, resources allow you to lift the load without much damage to performance. Previously, I dabbled in and brought the number of items to 300,000 and the number of received values ​​per second to 200. Now, everything superfluous and unnecessary from monitoring has been removed.

Minor bonus

Example of part of the FTTB network map


Link control for a single corporate client


Continuation will be if the topic is interesting))

PS I apologize for a somewhat confused presentation of the material.
UPD: Corrected some spelling and grammatical errors.
UPD2: Corrected many more spelling and grammatical errors, corrected lost links.