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

    Working with the largest provider of the Russian Federation, I came across a situation that a subscriber is contacting about 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 the key parameters of all subscriber lines and store them for a while, and the TP operator can easily access this data to solve such situations. Also, having these data, it is possible to give an automatic expert assessment on each subscriber line, and if desired, on xDSL, automatically select the most suitable profile. Using the same data, it is possible to 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 difficult, but when the number of equipment easily steps over thousands of access nodes, and the number of subscriber ports can reach tens of thousands, some features of setting up and running such a system appear, with maximum automation of everything.
    If interested, welcome to kat.


    In the presence of 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 - from 16 to 64 ports, depending on configuration.

    Define 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 polling by snmp. The whole trouble was that MIB files for most of the hardware are not accessible to mere mortals because of the tricks of the manufacturers with the conclusion of a bunch of contracts and other obstacles that are not clear to me (to the credit of huawei mibs, they nevertheless sent them without different contracts, but for another supplier and can not get). If your situation is similar to mine, then we take snmpwalk into our hands and analyze everything that the piece of hardware gives out, and it will give out a lot, megabytes of text. Analysis of the obtained oid is a very time-consuming process. It’s good if there are ready-made templates for zabbix or there is mib (then templates from them can be made quickly and easily by reading this topic “connect any piece of hardware with a MIB” habrahabr.
    After you receive the templates, you need to determine what we will collect, with what frequency and how much time to store. All these parameters are ultimately set in the working template, which we will bind to the glands.

    My set was not very large, for example, for dslam'a high-capacity parameters collected from the first port AND an additional template with elements that can be turned on.


    1.01ifAtucFfrADSL 1.01ifAtucFrADSL количество ретрайнов
    1.01ifAtucLossADSL количество потерь сигнала
    1.01ifDownSnrADSL 1.01ifUpSnrADSL шумы
    1.01ifDownSpeedADSL 1.01ifUpSpeedADSL реальная скорость на абонентской линии
    и не активные, но включаемые при необходимости расширенного анализа состояния порта
    1.01ifOperPhysicalStatADSL физическое состояние порта
    1.01ifOperStatADSL состояние порта




    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, maximum speed and current speed and based on these data, if the speed specified by the profile is lower than the real and maximum achievable, offer or independently change the profile to another with a lower speed, and vice versa if the maximum speed is higher than real and higher than that set in the profile, then offer to change or change the profile to another with a higher speed.

    I note that such triggers did not take hold of me because of the banal clogging of all recommendations for changing profiles on ports, and I was afraid to give it to automation.

    There is another option of very useful triggers, these are triggers for the amount of noise, for attenuation, for a change in time (delta function) of the number of errors and reconnects and other joys that can very much deliver subscribers to get subscribers. And again, these triggers did not take root for the same reason. It's hard on your soul when you have a couple of hundred ports are considered unsatisfactory. ;)

    For nodes using 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 for a period of time, we list the port in the list of attention.
    Here is an example of data collected from one port. Data can be presented as a graph of time.




    I also had interesting Chinese Edge-core switches from which I got rid of in favor of Huawei, so I made a template with them that polled the results of measuring subscriber cables with an OTDR and analyzing the results I got a list of ports with faulty UTP. These measurements could be made by sending a snmp command to the glands using either a script from the zabix or a crown. I did not finish this template because of the replacement of all hedgehogs with huyavei.

    I think the overall picture and approach to monitoring are clear, if there are more specific questions, on some not very well-illuminated points, then ask, I will try to answer.

    Let's set up the database for Zabbix


    At the moment, the last stable branch 1.8.5 can be downloaded from the official website www.zabbix.com/download.php
    I won’t 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 the database, then 9.0 now moved to MySQL 5.5, which was installed on the server according to the official manual and a little file dipping. Reason for moving partitioning table support 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 beat, it seems I can’t cook it, Threat partition table is in the congress, but it is implemented somewhat differently than in the muscle).

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

    I ’ll note the key required option The default engine should be set to innodb, encoding utf-8. If you are very curious, I’ll post the config. Zabbix saves there are many values ​​in the database; the main large tables are hystory * and trend *
    innodb_file_per_table





    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 is stored on average for about 3 days. The old data is then deleted by the housekeeper leaving fragmented tables after that. How bad this is, I think, no need to explain.
    What to do in this case? The answer is to use a partition table with time sharing. To do this, I took advantage of the experience from the blog of the Brazilian comrade zabbixzone.com/zabbix/partitioning-tables.
    He suggests dividing the tables into 2 types: large - with division by day, and not very large - with division by month.
    Here are the dividing tables by day:
    • history
    • history_log
    • history_str
    • history_text
    • history_uint

    and divided by months:
    • acknowledges
    • alerts
    • auditlog
    • events
    • service_alarms
    • trends
    • trends_unit


    I already had a configured monitoring system and in order to add what the friend was offering, the following actions had to be done (they are almost the same as in the above blog).

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

    clear the above tables from the data.
    Change table structures slightly

    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.


    Create a partition table with monthly division until 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.


    Create a daily partition table

    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.


    Восстанавливаем данные каждой таблицы так (подставив для каждой таблицы свое имя файла дампа)
    mysql zabbix < 09.06.2011_history_str.sql

    В конечном результате данные будут храниться очень удобно — каждый день/месяц в отдельном файле, отдельной части таблицы

    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


    Очистка старых данных производится простой командой (вместо `history_uint` подставьте нужную вам таблицу)
    ALTER TABLE `history_uint` DROP PARTITION p20110604;
    Добавление новой части таблицы
    ALTER TABLE `history_uint` ADD PARTITION (PARTITION p20110628 VALUES LESS THAN (UNIX_TIMESTAMP("2011-06-29 00:00:00")));

    Руками чистить таблицы и добавлять новые как то не интересно, опять же в блоге предлагают процедуру для таблиц разбитых по дням код взят тут pastebin.com/ijyKkxLh дабы сохранить его для потомков выложу его здесь без изменений.

    /**************************************************************
     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.


    Теперь остается добавить выполение этого кода по крону раз в 3 дня
    строчка в конфиге крона будет выглядить приблизительно так
    0 6 */3 * * Логинка_от_кого_выполняем mysql -B -h localhost -u zabbix -pПАРОЛЬКА zabbix -e "CALL create_zabbix_partitions();"

    С настройкой БД закончили.

    Let's configure auto-binding of templates on the number of boards on the equipment


    My pieces of iron are quite large and scale by adding boards with subscriber ports. In order not to interrogate non-existing ports in a piece of iron once again, I broke all the templates into boards. Added a detection rule that once a day is knocked on a specific oid. The answer from this oid allows you to find out whether the board is installed in the slot or not. Scanning goes over the entire range of types of glands. Added two actions that either bind the template of this card to the node, or untie the template from it, performing this depending on the value obtained upon detection. With such simple actions, we get rid of the tedious task of controlling the number of installed cards in access nodes.







    What did we get?


    Download process and other different graphics below.
    On the graph of the database size you can see two drops, the first drop - the first start of the procedure, the second drop in a day - manual verification of the procedure. Brief quality characteristics As you can see, resources allow you to lift the load without much damage to performance. I used to dabble and bring the number of items to 300,000 and the number of received values ​​per second to 200. Now everything that is superfluous and not needed from monitoring has been removed.








    Minor bonus


    An example of a part of the FTTB network map. Monitoring the links of one corporate client. Continuation will be if the topic is interesting)) PS I apologize for the somewhat messy presentation of the material. UPD: Fixed some spelling and grammar errors. UPD2: Corrected many more spelling and grammar errors, corrected the lost links.










    Also popular now: