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

    • Incident report
    • Resolution: Fixed
    • Minor
    • 2.1.0
    • 1.8.5
    • Proxy (P)
    • 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

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

              Dates

                Created:
                Updated:
                Resolved: