Just fyi ZBX-4357
Some statistics from a 50 seconds system call trace of one history syncer being affected by this issue:
- 16,808 database queries in total.
- 2.1229ms query time in average.
- 10.974ms maximum query time.
- 0.185ms minimum query time.
- 35.6813 seconds total query time.
Query count has been derived from count of sendto(select ...) system calls.
Query time has been derived from poll() system call time appearing after sendto(select ...) system calls.
Edit:
For the sake of information. Summary report of count time, calls, and errors for each system call of the very same trace.
% time seconds usecs/call calls errors syscall ------ ----------- ----------- --------- --------- ---------------- 91.48 0.132029 8 17085 poll 4.65 0.006706 0 17122 sendto 2.19 0.003155 0 17086 recvfrom 1.68 0.002430 0 91964 semop 0.00 0.000000 0 38 write 0.00 0.000000 0 38 open 0.00 0.000000 0 38 close 0.00 0.000000 0 42 stat 0.00 0.000000 0 38 fstat 0.00 0.000000 0 38 mmap 0.00 0.000000 0 38 munmap 0.00 0.000000 0 6 rt_sigaction 0.00 0.000000 0 12 rt_sigprocmask 0.00 0.000000 0 5 nanosleep 0.00 0.000000 0 11 times 0.00 0.000000 0 1 restart_syscall ------ ----------- ----------- --------- --------- ---------------- 100.00 0.144320 143562 total
Perhaps an 'actions cache' might be a solution?
To me this must not necessarily be a dedicated cache. From my point of view this is configuration data and the existing configuration cache appears to me to be an appropriate place.
I don't suspect the count of actions to be the major/only issue but the count of conditions and the matter of fact that most of condition checks result in SQL queries.
E.g. see mentioned select_trigger_condition() function.
Finally I think all related information (action + conditions) which currently lead to SQL queries in the act of history synchronization should be cached.
I'm personally not very sure that internal cache for actions should help here a lot.
Current config cache is useful because it contains data of many gigabytes (as for db tables) in big installations.
I don't think that tables related to actions are so huge, even with ~100 actions etc.
So, IMO, most optimization can go to bulk processing of actions and code optimization.
zalex_ua, the issue is not related to the payload resp. size of configuration data which indeed is very low.
I see the problem rather in doing repetitive SQL queries to that extend.
I mean just do the math:
Here a single SQL query takes 2ms what I'd consider as quite fast for:
- sending the query over the network to the database
- letting the query optimizer analyze the query
- evaluating the result set
- receiving the result set over the network
Now this query has been done ~16,000 times in 50 seconds what sums up to ~32 seconds just for doing SQL queries.
Querying such kind of information from memory I'd expect to be a magnitude faster than 2ms each.
Btw, when proposing of "[...] caching most relevant information needed for action processing in memory [...]",
then I had also information necessary to do select_trigger_condition() in mind. This is probably not that obvious from the issue description.
In fact the database queries made there are the most time consuming part in the whole chain during history synchronization respectively action processing.
Considering a scenario of having only 100 actions with 1000 conditions in total respectively 10 conditions per action in average for simplicity reasons, then the count of SQL queries to issue may be distributed like this over the event process chain:
- 1 SQL query to get enabled actions on an event
- 100 SQL queries get conditions of the actions
- 700 SQL queries to evaluate action conditions
(assuming 3 non-SQL related conditions per action)
It's definitely not my intention to say that caching actions and conditions only is not worth to!
But if there is a chance to also cache information to avoid at least some more database queries in condition evaluations, then this may further improve performance significantly.
Personally I'd order the condition types from most worthy to consider for caching to least worthy as follows:
- CONDITION_TYPE_MAINTENANCE
expected to be part of almost each action - CONDITION_TYPE_HOST_GROUP
likely the major criteria to differentiate between teams and responsibilities - CONDITION_TYPE_TRIGGER
possibly major criteria to pin dedicated triggers (most likely on template level) - CONDITION_TYPE_HOST_TEMPLATE
- CONDITION_TYPE_APPLICATION
- CONDITION_TYPE_HOST
- CONDITION_TYPE_EVENT_ACKNOWLEDGED
actually very worthy to consider but expected to be rather difficult to implement
I created ZBXNEXT-3086 regarding action condition evaluation. This issue will deal only with action and action condition caching in configuration cache for action processing.
Specifications at https://www.zabbix.org/wiki/Docs/specs/ZBXNEXT-3051
Fixed in development branch svn://svn.zabbix.com/branches/dev/ZBXNEXT-3051
(1) In configuration cache for actions we save only actionid, eventsource, evaltype, formula rows from the actions table. Fetching the rest of the rows is not necessary.
sandis.neilands RESOLVED in r57682.
wiper CLOSED
(2) When documenting don't forget to mention the effect of CacheUpdateFrequency configuration parameter.
wiper CLOSED.
Successfully tested.
- 350 hosts each with 3 items.
- Add trigger to the template.
- 3 actions match all events generated from the trigger (e.g. 350 * events when the trigger fires).
The performance is still limited by DB access elsewhere (see ZBXNEXT-3086).
Released in:
- pre-3.0.0beta1 r57792
(3) Documentation:
- https://www.zabbix.com/documentation/3.0/manual/installation/upgrade_notes_300#action_and_action_condition_caching
- https://www.zabbix.com/documentation/3.0/manual/introduction/whatsnew300#action_and_action_condition_caching
sasha CLOSED
should the 'alpha' above be changed to 'beta' now ? 
It caused a regression, see ZBX-10608.