Uploaded image for project: 'ZABBIX FEATURE REQUESTS'
  1. ZABBIX FEATURE REQUESTS
  2. ZBXNEXT-874

cache item values that are needed in trigger expressions

XMLWordPrintable

    • Icon: New Feature Request New Feature Request
    • Resolution: Duplicate
    • Icon: Major Major
    • None
    • None
    • Server (S)
    • 3.0.0-gentoo x64, 24cores and 20GB memory. Separate database and zabbix_server. PostgreSQL 9.0.4.
      Zabbix server only icmp and snmp queries.

      We have:
      zabbix=# select count(*)from items;
      652688
      zabbix=# select count(*)from hosts;
      10249
      zabbix=# select count(*)from triggers;
      40436
      zabbix=# select count(*)from functions;
      60380

      In history sync executing function DCmass_update_triggers(history, history_num) where for every function executing query:
      select distinct i.itemid,i.key_,h.host,i.type,i.history,i.lastvalue,i.prevvalue,i.hostid,i.value_type,i.delta,i.prevorgvalue,i.lastclock,i.units,i.multiplier,i.formula,i.status,i.valuemapid,i.trends,i.data_type,f.function,f.parameter,h.status from hosts h,items i,functions f where i.hostid=h.hostid and i.itemid=f.itemid and f.functionid=<functionid>

      zabbix=# explain select distinct i.itemid,i.key_,h.host,i.type,i.history,i.lastvalue,i.prevvalue,i.hostid,i.value_type,i.delta,i.prevorgvalue,i.lastclock,i.units,i.multiplier,i.formula,i.status,i.valuemapid,i.trends,i.data_type,f.function,f.parameter,h.status from hosts h,items i,functions f where i.hostid=h.hostid and i.itemid=f.itemid and f.functionid=36251;
      QUERY PLAN
      -----------------------------------------------------------------------------------------------------
      HashAggregate (cost=9.13..9.13 rows=1 width=122)
      -> Nested Loop (cost=0.00..9.08 rows=1 width=122)
      -> Nested Loop (cost=0.00..8.82 rows=1 width=103)
      -> Index Scan using functions_pkey on functions f (cost=0.00..4.26 rows=1 width=14)
      Index Cond: (functionid = 36251)
      -> Index Scan using items_pkey on items i (cost=0.00..4.55 rows=1 width=97)
      Index Cond: (i.itemid = f.itemid)
      -> Index Scan using hosts_pkey on hosts h (cost=0.00..0.26 rows=1 width=27)
      Index Cond: (h.hostid = i.hostid)

      Cost for thise query very small, but query executing for every! functionid and blocking rows from items,triggers,hosts tables!

      bigest triggers use 'last' function, why need executing thise query? item last value exists in zabbix cache (also trigger expression, items properties).

      i think evaluate 'last' function can doing without any queries and maybe some other trigger functions

        1. busy_poolers.png
          59 kB
          Alexey Pustovalov
        2. cpu_avg.png
          49 kB
          Alexey Pustovalov
        3. zbx_wr_history.png
          43 kB
          Alexey Pustovalov

            Unassigned Unassigned
            dotneft Alexey Pustovalov
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Created:
              Updated:
              Resolved: