Zabbix Database Partitioning

[@zabbixtraining]

► Grab your Monitoring Love Merchandise - teespring.com/stores/monitoring-it

[@dimirx]

Small correction about the perl script 14:38. If you're on MySQL 8 you need to remove the code that calls check_have_partition(). Removal of the function itself is optional.

[@rafael10086]

Exact

[@eduardoaran2457]

Ty very much for this video, it was a life saver... But i just have 1 question, When you execute the script, do I have to keep executing it manually or is there a way to make it a loop?

[@zabbixtraining]

Add it to Cron

[@eduardoaran2457]

@@zabbixtraining Ty very much. Sry for asking you something else. If my zabbix is slowly loading my Monitoring>Host page... Is this the solution to make it faster? (I have 150+ host)

[@nahueld9326]

Is this guide useful to perform partitions on zabbix 5.0 or 6.0? and is it better to upgrade from 5.0 to 6.0 before or after patitioning the database?

[@nahueld9326]

And is it posible to partition the history tables monthly instead of daily? Are there any disadvantages?

[@ChrisHale0629]

Thanks for this video! I started using Zabbix a few months ago and this is by far the best resource for the software.

[@zabbixtraining]

Thank you :) If you need some minor tips on any topics, I really suggest to check out official documentation. That will help further

[@ChrisHale0629]

@@zabbixtraining I have a few items that need to be adjusted on the script if you're using MySQL 8.0. The sections of script that start with 1. unless ( check_have_partition() ) { 2. unless (defined($part_tables->{$key})) { and the previously mentioned perl sections that check for partitioning in your how-to all need to be commented out. If you don't do that MySQL 8.0 will return no partitions to the perl script. Thanks again for the how to. Used it today on my development box that's running on older hardware. It was tanking for 3 hours doing housekeeping but now it's smooth sailing. Cheers!

[@rafael10086]

@@ChrisHale0629 thank you, you save my life! :)

[@manupmukherjee]

Does doing zabbix partitioning, remove history and trends completely? So much so that graph and data from hosts is not visible on front end? the requirement is for audit related stuff, need to show data of a complete year starting from June to May.

[@zabbixtraining]

Books About Zabbix Amazon COM ► Zabbix 4 Network Monitoring 3rd Edition amzn.to/2Ra7XDE ► Zabbix Network Monitoring 2nd Edition amzn.to/2DQLWqI Amazon UK ► Zabbix 4 Network Monitoring amzn.to/2G7BCey ► Mastering Zabbix - Second Edition amzn.to/306Ru86 Amazon DE ► Zabbix 4 Network Monitoring amzn.to/2FYg3NU ► Zabbix Cookbook amzn.to/3iXuqQK

[@eduardoaran2457]

Sorry for the inconvenience, my database does not have the name zabbix (its name is test), what should I change in the perl script so that it reads my database?

[@llesomora]

dmitry hello what happens if i partitioned tables history, trends but the problems/alerts table has increase and we have some issues with performance on that tables. i can turn on housekeeper to this tables and dont have any problem ? my tables problems/alerts has 8 bilion registers and we face some slow queries

[@bruhattamishra6924]

Hi @Dmitry Lambert thanks for this informative session. I have an use cases issue. I have many files getting updated during a particular time of day where cpu gets used much. all those files are history*.frm and history*.ibd. are these due to house keeping jobs? If yes, I wish to stop this because these use much cpu. In config file housekeeping parameter is set as 0 but still the jobs are running and takes 7 hours. and bunch of these history files gets generated and appended any clue how to fix, (zabbix version 4.0.5)

[@yogeshpatel701]

Thanks a lot for sharing your knowledge , Could you please create similar video for postgresdb on how to perform zabbix db tunning and how to cleanup old history and trends table

[@waelthebigboss]

If i found out after 1 month that the script stoped working and the data are stored again without partionning, so if i run the script again will that damage the database ???

[@dfisicaro]

What happens if one of the tables returns a NULL value? Of the 7 tables I need to run the ALTER commands on. You just run the ALTER command for the last day?

[@Olson83CR]

Nice post, really appreciate the effort. I have been postponing this for months since I am not a DB person but have some general knowledge on DBs. My db does not use a unix socket, uses a tcp connection. Do you think the stored procedures described in the wiki will help me to archive the same result?

[@zabbixtraining]

Internal procedures are very hard to maintain and troubleshoot.

[@Olson83CR]

@@zabbixtraining any advise on Hoy to implement it when using tcp connections to the DB?

[@zabbixtraining]

There is no difference. Just make script to connect over simple tcp

[@subkhanave]

after implementing partitioning, should housekeeper be disabled or leave it as it is? I read about that in someone’s blog thanks for the knowledge

[@zabbixtraining]

Exactly, if you are using partitioning, there is no need to use housekeeper, so you need to disable it for partitioned tables only in the frontend Administration -> General -> Housekeeping

[@subkhanave]

Zabbix CookBook thanks for clarifying. Video request, maybe you can cover about reporting in zabbix like weekly or monthly since there’s no builtin feature. any 3rd party software is ok , paid or free

[@zabbixtraining]

Zabbix DB replication to other node which is used for reporting only. PowerBI pointed to that node. Thats the thing i was doing myself a while ago

[@subkhanave]

any free alternative? powerbi seems too pricey

[@maxtissera]

New subscriber here. Amazing video, will configure in my zabbix server this week. Thanks.

[@zabbixtraining]

Thank you for your feedback.

[@gatto_cu]

not work in zabbix 5.2 and the website zabbix.org/wiki/Main_Page don't exist, redirect to www.zabbix.com/community

[@SantosBR_Oficial]

Hi Dimitri, Would you help me ? When I run the "ALTER TABLE" command, I get the error below: MariaDB [zabbix]> ALTER TABLE `history_uint` PARTITION BY RANGE (clock) (PARTITION p2019_12_04 VALUES LESS THAN (UNIX_TIMESTAMP("2019-12-05 00:00:00")) ENGINE = InnoDB; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1 I am using Centos 7 with zabbix 4.2 and Mariadb in version (mysql Ver 15.1 Distrib 5.5.60-MariaDB, for Linux (x86_64) using readline 5.1) Could you tell me where the error is?

[@praveenkumarrajasekaran7911]

you missed a bracket after InnoDB);

[@TheMaxnux]

there is this script for postgres?

[@maxtissera]

I would love a video about templates and how you deploy zabbix agent on multiple servers, lets say windows 2k12/2k16 or Linux servers. I have 200 VMs to monitor, and it will be impossible doing it 1 by 1

[@zabbixtraining]

Templates sounds fine, but there not much to say about mass deployment. There is no difference in OS versioning. And deployment can be done with any configuration management tools like AD, Bigfix, Chef etc It really depends on what you use in your environment and there is nothing Zabbix specific.

[@lanceeilers5061]

Cool thanks so much - Great tutorial , So one could use ansible to install the agents , and possibly do the configurations on the VM's , but what about on the zabbix server it's self , apologies I am a bit stupid when it comes to putting it all together , perhaps you could expand on that it maybe be quit helpful , thanks again in advance , regards Lance :-)

[@zabbixtraining]

That's the task for Auto registration or Network discovery. Well ok, I guess it will be easier to create a video about that :)

[@lanceeilers5061]

Cool thanks so so much , I wish I knew all these functionalities of the zabbix server , look forward to seeing how you can implement that , have a great day further - you really teach me so much :-)

[@maxtissera]

Wow thanks for the new video!

[@ygtntxrf]

Thank you for great channel '-) Please can you make tutorial about smart HDD monitoring with notifications?

[@zabbixtraining]

On Linux or Windows?

[@ygtntxrf]

@@zabbixtraining I would appreciate both options (please)

[@zabbixtraining]

Will try to help with SMART later this week :) Be careful with load on DB from grafana ;)

[@ygtntxrf]

I try to be careful with my new small VM instance. Two cores, 1Gb RAM, over 70 devices in plans. Zabbix 3 without Grafana with same qty hosts have load average 0.29, 0.58, 0.44

[@zabbixtraining]

Check this one : github.com/stas630/smartctl-zabbix Unfortunately I don't have any machine with SMART support by hand. You can also try to use smartctl -a and extract required data with preprocessing ( it's available starting from Zabbix 3.4 )