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

Redundant SQLs to history/trends tables when building graphs

    Details

    • Type: Incident report
    • Status: Closed
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 1.8.22, 2.0.15, 2.2.10, 2.4.6, 3.0.0alpha3
    • Fix Version/s: 3.2.0alpha1
    • Component/s: Frontend (F)

      Description

      When getting values from history|trends, zabbix frontend does absolutely unneeded extra query to one of history|trends table for every item on a graph.
      Take a look to /frontends/php/include/classes/graphdraw/CLineGraphDraw.php:235-281, here it is:

      			if (!$trendsEnabled
      					|| (($item['history'] * SEC_PER_DAY) > (time() - ($this->from_time + $this->period / 2))
      						&& ($this->period / $this->sizeX) <= (ZBX_MAX_TREND_DIFF / ZBX_GRAPH_MAX_SKIP_CELL))) {
      				$this->dataFrom = 'history';
      
      				array_push($sql_arr,
      					'SELECT itemid,'.$calc_field.' AS i,'.
      						'COUNT(*) AS count,AVG(value) AS avg,MIN(value) as min,'.
      						'MAX(value) AS max,MAX(clock) AS clock'.
      					' FROM history '.
      					' WHERE itemid='.zbx_dbstr($this->items[$i]['itemid']).
      						' AND clock>='.zbx_dbstr($from_time).
      						' AND clock<='.zbx_dbstr($to_time).
      					' GROUP BY itemid,'.$calc_field
      					,
      					'SELECT itemid,'.$calc_field.' AS i,'.
      						'COUNT(*) AS count,AVG(value) AS avg,MIN(value) AS min,'.
      						'MAX(value) AS max,MAX(clock) AS clock'.
      					' FROM history_uint '.
      					' WHERE itemid='.zbx_dbstr($this->items[$i]['itemid']).
      						' AND clock>='.zbx_dbstr($from_time).
      						' AND clock<='.zbx_dbstr($to_time).
      					' GROUP BY itemid,'.$calc_field
      				);
      			}
      			else {
      				$this->dataFrom = 'trends';
      
      				array_push($sql_arr,
      					'SELECT itemid,'.$calc_field.' AS i,'.
      						'SUM(num) AS count,AVG(value_avg) AS avg,MIN(value_min) AS min,'.
      						'MAX(value_max) AS max,MAX(clock) AS clock'.
      					' FROM trends'.
      					' WHERE itemid='.zbx_dbstr($this->items[$i]['itemid']).
      						' AND clock>='.zbx_dbstr($from_time).
      						' AND clock<='.zbx_dbstr($to_time).
      					' GROUP BY itemid,'.$calc_field
      					,
      					'SELECT itemid,'.$calc_field.' AS i,'.
      						'SUM(num) AS count,AVG(value_avg) AS avg,MIN(value_min) AS min,'.
      						'MAX(value_max) AS max,MAX(clock) AS clock'.
      					' FROM trends_uint '.
      					' WHERE itemid='.zbx_dbstr($this->items[$i]['itemid']).
      						' AND clock>='.zbx_dbstr($from_time).
      						' AND clock<='.zbx_dbstr($to_time).
      					' GROUP BY itemid,'.$calc_field
      				);
      
      				$this->items[$i]['delay'] = max($this->items[$i]['delay'], SEC_PER_HOUR);
      			}
      

      I tried to investigate when this logic has been added in the past to try to understand why. Results:

      Additional unconditional select from "history_uint" table added 2006-03-27 in r2711 in /trunk/frontends/php/include/classes/graph.inc.php
      svn log - replaced "union" command by sql arrays, for compatability of old MySQL (Eugene)
      Worth to mention that "history_uint" table support has been added ~5 months earlier in r2313. svn log - added table history_uint (Alexei)

      Additional unconditional select from "trends_uint" table has been added 2008-07-29 in r5845 /trunk/frontends/php/include/classes/chart.inc.php
      svn log - [DEV-197] added support of trends_uint

      As for me, it looks like it was introduced NOT to show values on graphs even when someone has changed item information type after some history has been already collected. I think it was just a simplest implementation.
      In any case, currently those 2 SQLs per single item looks very suspicious when you investigate SQLs performed by zabbix frontend.

      The additional SQL produces extra and unneeded load on database and this is the most critical point.
      Removing those unneeded SQLs can really speed up graphs building.

      As for current zabbix versions, the possibly positive point (to show values on graph event after changing information type) does NOT work!
      Well, it DOES work, but only for very short period (up to 1 hour) after changing information type - until the item collected history and inserted single trends value.
      (why insertion to trends is a key point - because most likely all numeric items have trends configured longer than history and frontend checks for min(clock) of longest period)
      After single value is inserted to a trends* table - frontend will limit possible time periods (slider, pre-selected periods) accordingly to to "min(clock) from current-table-type".
      So frontend users will not see the old collected data at all and not will be able to select old period to display.

      Housekeeping collected data for items after changing information type discussed in ZBX-9278

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                zalex_ua Oleksiy Zagorskyi
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: