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

cache item values that are needed in trigger expressions

    XMLWordPrintable

Details

    • New Feature Request
    • Status: Closed
    • Major
    • Resolution: Duplicate
    • 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.

    Description

      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

      Attachments

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

        Issue Links

          Activity

            People

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

              Dates

                Created:
                Updated:
                Resolved: