Uploaded image for project: 'ZABBIX BUGS AND ISSUES'
  1. ZABBIX BUGS AND ISSUES
  2. ZBX-12066

Timeline slider initialization slow SQL queries.

XMLWordPrintable

    • Sprint 5, Sprint 6

      Today we identified a serious performance issue caused by the timeline slider resp. its need to find out the lowest time stamp:

      ******************** Script profiler ********************
Total time: 93.772462
Total SQL time: 93.650582
SQL count: 41 (selects: 27 | executes: 14)
Peak memory usage: 6.75M
Memory limit: 1G

--- SNIP --- SNAP --- SNIP --- SNAP ---

SQL (92.13814): SELECT MIN(ht.c) AS min_clock FROM (SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='839162' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='839198' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='839121' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='839103' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='839073' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='839132' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='882105' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='839144' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='839109' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='839096' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='839043' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='882098' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='839174' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='839186' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='836308' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='839193' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='839078' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='839169' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='839049' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='882093' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='836307' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='839055' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='839127' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='839145' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='839211' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='839072' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='839163' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='882104' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='839175' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='882131' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='839054' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='882113' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='839066' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='839115' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='882119' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='839157' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='839060' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='839042' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='839204' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='839151' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='839199' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='839126' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='839180' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='882118' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='839156' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='839067' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='882137' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='839192' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='882130' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='839133' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='882112' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='882099' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='839187' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='882124' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='839181' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='882092' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='839079' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='839210' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='839120' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='839138' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='839097' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='839139' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='839061' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='882136' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='839085' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='882125' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='839205' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='839048' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='839108' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='839150' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='839114' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='839102' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='839168' UNION ALL SELECT MIN(ht.clock) AS c FROM trends_uint ht WHERE ht.itemid='839084') ht

--- SNIP --- SNAP --- SNIP --- SNAP ---

      Admittedly this is an extreme example of a Custom graph with more then 70 Items and a total size of over 316 GB for trends tables.
      However spending one and a half minute on preparing the time slider and just the fraction of a second on querying/processing the actual graph data seems a bit disproportionate to me

      We briefly discussed this in the office and didn't came up with a satisfying alternative method of initializing the timeline slider nor the zoom links.
      Then our Zabbix admin asked: Does anybody of you actually make use of that thing?
      The answer was surprisingly simply: No!

      Nobody on-site seems to make, or ever had made, use of it. The Zooming and shifting, as well as the date picker is used frequently - but never the slider itself.

      So, how about getting rid of it and using the gained space for visually more appealing resp. user-friendly zoom and shift buttons?

      PS: An even more rigorous proposal was to get even rid of the zoom pre-sets and replacing them by a simple zoom-out function. But the guy who throws that in is a bit "Grafana affected"

        1. MaxBackPeriod.png
          10 kB
          Marc
        2. Zbx12066.patch
          0.6 kB
          Marc
        3. zbx-12066.sql
          8 kB
          Gregory Chalenko
        4. Zbx12066-zbx_max_back.patch
          2 kB
          Marc

            Unassigned Unassigned
            okkuv9xh Marc
            Votes:
            2 Vote for this issue
            Watchers:
            4 Start watching this issue

              Created:
              Updated:
              Resolved: