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

proxy_get_history_data query using temporary and filesort in mysql

    XMLWordPrintable

    Details

    • Type: Incident report
    • Status: Closed
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 1.8.5
    • Fix Version/s: 2.1.0
    • Component/s: Proxy (P)
    • Environment:
      Ubuntu 10.10, 32 bits, mysql 5.1.49-1ubuntu8

      Description

      I'm using zabbix proxy in a small virtual machine with mysql in the same virtual machine. Mysql crawls when querying a big proxy_history table (3.5 million rows).

      The original query needs filesorting and takes a lot of time. I've modified the default 1000 items to 10

      mysql> select p.id,h.host,i.key_,p.clock,p.timestamp,p.source,p.severity,p.value,p.logeventid from hosts h inner join items i on h.hostid=i.hostid inner join proxy_history p on i.itemid=p.itemid where p.id>21824754 order by p.id limit 10;
      .... (Removed)....
      10 rows in set (1 min 50.70 sec)

      Testing another way to get the data I got the following results:
      mysql> select p.id,h.host,i.key_,p.clock,p.timestamp,p.source,p.severity,p.value,p.logeventid from hosts h inner join items i on h.hostid=i.hostid right outer join proxy_history p on i.itemid=p.itemid where p.id>21824754 order by p.id limit 10
      right outer join proxy_history p

      ...(Removed)....
      10 rows in set (0.07 sec)

      The difference is in the right outer join for the proxy_history table.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned
              Reporter:
              graffic Javier Gonel
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: