Ad Widget

Collapse

Slow queries on Zabbix 1.8.10 when viewing screens

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • danims
    Junior Member
    • Feb 2012
    • 8

    #1

    Slow queries on Zabbix 1.8.10 when viewing screens

    Since I use Zabbix 1.8.10, I can't see screens anymore. I have about 10 graphs in one screen and it takes longer to show the graphs than the page reloads.

    I figured out that I have slow queries in MySQL. I've set up the "slow queries" log and when I access the screens page, I get:

    Code:
    SELECT itemid,round(450*(mod( CAST(clock AS UNSIGNED) +53582,86400))/(86400),0) 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=22373 AND clock>=1332061618 AND clock<=1332148018 GROUP BY itemid,round(450*(mod( CAST(clock AS UNSIGNED) +53582,86400))/(86400),0);
    # User@Host: zabbix[zabbix] @ localhost []
    # Query_time: 13.789228  Lock_time: 0.000589 Rows_sent: 450  Rows_examined: 18143
    SET timestamp=1332148051;
    SELECT itemid,round(450*(mod( CAST(clock AS UNSIGNED) +53615,86400))/(86400),0) 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=22934 AND clock>=1332061585 AND clock<=1332147985 GROUP BY itemid,round(450*(mod( CAST(clock AS UNSIGNED) +53615,86400))/(86400),0);
    # User@Host: zabbix[zabbix] @ localhost []
    # Query_time: 13.877881  Lock_time: 0.000321 Rows_sent: 450  Rows_examined: 18140
    SET timestamp=1332148051;
    SELECT itemid,round(450*(mod( CAST(clock AS UNSIGNED) +53615,86400))/(86400),0) 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=23993 AND clock>=1332061585 AND clock<=1332147985 GROUP BY itemid,round(450*(mod( CAST(clock AS UNSIGNED) +53615,86400))/(86400),0);
    # User@Host: zabbix[zabbix] @ localhost []
    # Query_time: 13.838075  Lock_time: 0.000269 Rows_sent: 450  Rows_examined: 18074
    SET timestamp=1332148051;
    Is there anything I can do about this? Maybe adding indexes? I've already spent some effort in optimizing MySQL, without any luck. Thanks for help!
Working...