PostgreSQL 14.5
TimescaleDB 2.7.2

Hello Yurii,

How often do you do maintenance update?
Does that slowness related to some slow queries in the log?
how to reproduce it accurately? How many hosts/items you have and which goes to maintenance and which conditions with or without data collection?

Best regards,
Andrei

Hello,

Such maintenances occur almost every weekday. But apply to a different number of hosts.
Each maintenance is created for one or more VMs, the product is deployed to the VM usually in 2-10 minutes, then this maintenance is updated with the new end time because already everything should be up and running and we should monitor the services as usual. End time is now+5 minutes to be sure all Zabbix events are resolved.

Only slow queries in Zabbix server logs:
2771378:20231109:063027.178 slow query: 3.015999 sec, "lock table maintenances in share mode"
2771381:20231109:063709.894 slow query: 32.688270 sec, "lock table maintenances in share mode"

Example of maintenance you may find on screenshoot in this Issue.
Maintenance type "With data collection". "No data collection" doesn't allow tags and doesn't work for us because we create maintenance based on a one parent host group.
You can try to reproduce this by creating and then updating 350+ maintenances within 30 minutes for different hosts. Use one large parent host group that contains all hosts and one or two tags associated with a unique Zabbix host. For example you have hosts test1.com and test2.com then tags will be:
tag name "hostname", tag value "test1.com" and
tag name "hostname", tag value "test2.com"

Could you please be so kind to provide prof_enable output with ZBX-23690-6.4-1 1.diff

Are there many events with tags ?

yuriip ?

Most likely problem with matching each event to many maintenances using tags for each host. It should be checked if can be optimised.
For example:
If we have simple maintenance equal tag as described in issue then put those maintenances in hashset do hashset search for each event to maintenances instead of iterating through all hosts for each event.

Test package installed in dev env. If it works without problems it will be installed in prod on Monday.

Each event has tags.
Unfortunately, we now have a lot of test/technical Not classified events (about 700) and about 700 real events.
Most events are correlated, but as I understand for maintenance all events, not only cause, matter.

Could you please check attached patch replace_shared_table_lock_with_row_lock_for_update.diff - it should help in scenario with many host specific maintenances.

The optimize_host_maintenance_index.diff reduces configuration cache locking time when processing maintenances (need to have 100+ maintenances over thousands of hosts matched by tags to have any real effect).

Could you please be so kind and provide profiling information (prof_enable) for history syncers with optimize_host_maintenance_index_prof-2.diff it contains more profiling that is needed to identify bottleneck.

Hello,
We can state that after installing the last provided package, the situation with the load improved, there was no threshold exceeded. The other day there was quite a large amount of maintenance, the load was much higher than usual, but still did not reach the threshold value. I hope it was not just a coincidence and the problem is really solved.
We hope to see this fix in version 6.4.13.
Thank you!

Released ZBX-23690 in:

• pre-6.4.16rc1 be05dc3d77e
• pre-7.0.1rc1 320bb5bee26
• pre-7.2.0alpha1 38ed95090b3