Partitioning Events or dealing with its size

[rankinc]

Hey,

How is everyone dealing with the size of the events and event_recovery tables? I'm trying to figure out how to partition it but the foreign keys are preventing. I had disabled housekeeping because of large environment but of course now I'm having issues with the size of events, event_recovery, and problem tables.

Zabbix 4.0
Mysql 8.0
innodb

 zabbix             | trends                                               |     142.86
 zabbix             | history                                              |     406.95
 zabbix             | items                                                |     506.20
 zabbix             | problem                                              |    4205.55
 zabbix             | history_uint                                         |    9688.02
 zabbix             | event_recovery                                       |   12682.95
 zabbix             | trends_uint                                          |   12703.05
 zabbix             | events                                               |   55880.97

[ingus.vilnis]

Hello and welcome to Zabbix forums!

Even for large environments the cleanup of events and problems should be handled by housekeeper. Only the five history and two trends tables are considered safe to partition because as you said the foreign keys are preventing you from partitioning. These tables are tightly linked together and breaking this linkage will lead to bad consequences like orphaned events, never closed problems etc.

Now the very best thing you can do is to enable the housekeeper for these internal tables. If you don't have the partitioning for history and trends then go for it.

And if you still have issues with housekeeper deleting the events then you either have not optimally tuned DB engine or weak server and slow disks where the DB is running.

[kloczek]

First of all. Instead thinking what to do with relatively big events table size you should focus on why you have so many evens.
Many depends on triggers definitions. You should check most frequently occurring events and check do you have:
- enabled "multiple" on "PROBLEM event generation mode" (probably should be single)
- maybe you need to apply hysteresis to definition of some triggers (?)

[rankinc]

Thank you both for your replies. I re-enabled housekeeper and I believe it is finally catching up after giving it some time. I read some advice somewhere to disable housekeeper entirely but nowhere could I find how to deal with the non-partitioned tables. I need to do an thorough audit of my items and triggers because I started with templates to more rapidly get it going.

I am no DBA so I do believe the database isn't great. I have been doing a lot of reading on tuning and such but the forum post I added about tuning was removed.

It is performing a lot better than it was but I have it on a 3par san right now. May move to an ESX ssd.

[rankinc]

Turns out this was just performing better for a week or so I guess. I've been looking around and just don't see the answer anywhere other than "tweak your intervals". This is not a solution to the problem but only pushes the problem out to occurring later down the road. Everybody says housekeeper isn't good enough for large organizations and should be disabled but the partitioning docs only detail history and trends. I'm missing what ideas there are for the other tables.

I know I am missing some configuration parameters to boost performance of housekeeper somewhere or if I were a DBA I'm sure I would have some better ideas but I haven't been able to find anything online that resolves this.


| zabbix | housekeeper | 110.64 |
| zabbix | history_text | 142.50 |
| zabbix | triggers | 144.13 |
| zabbix | items | 544.80 |
| zabbix | history | 674.27 |
| zabbix | trends | 765.06 |
| zabbix | problem | 4637.66 |
| zabbix | history_uint | 17429.00 |
| zabbix | trends_uint | 44306.64 |
| zabbix | event_recovery | 51290.98 |
| zabbix | events | 72857.17 |
+--------------------+------------------------------------------------------+------------+



https://i.gyazo.com/14365a394453998a...8bb0d6cfd4.png