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

slow query "select distinct t.triggerid..."

    XMLWordPrintable

Details

    • Problem report
    • Status: Closed
    • Critical
    • Resolution: Won't fix
    • 3.0.9
    • None
    • Server (S)
    • zabbix ver 3.0.9:
      cpu : 4 core 4GHz
      RAM : 4GB
      OS : opensuse 42.2

      mysql 5.6:
      cpu : 4 core 4GHz
      RAM : 16GB
      OS : opensuse 42.2

      both machin are virtual on ESXi 6
      H.D.D 15K raid 5-0

    Description

      hi,
      i have setuped zabbix with mysql separately on two machines

      number of item : 120K
      Number of hosts : 500
      Number of triggers : 40K
      -----------------------------------------------
      my.cnf innodb config

      1. network
        connect_timeout = 60
        wait_timeout = 28800
        max_connections = 350
        max_allowed_packet = 64M
        max_connect_errors = 1000
      1. limits
        tmp_table_size = 512M
        max_heap_table_size = 256M
        table_open_cache = 512
      2. innodb
        innodb_file_per_table = 1
        innodb_status_file = 1
        innodb_additional_mem_pool_size = 128M
        innodb_buffer_pool_size = 12G
        innodb_buffer_pool_instances = 12
        innodb_flush_method = O_DIRECT
        innodb_io_capacity = 2000
        innodb_flush_log_at_trx_commit = 0
        innodb_support_xa = 0
        innodb_log_file_size = 6G
        innodb_log_buffer_size = 6G
        innodb_read_io_threads = 8
        innodb_write_io_threads = 8
        innodb_old_blocks_time = 1000
        sync_binlog = 0
        query_cache_size = 0
        innodb_doublewrite = 0

      ----------------------------------------------------------------------------
      problem is
      after start zabbix-server even before start [main process] and [poller] and ...
      in log file have

      3665:20170704:235729.617 slow query: 3.108452 sec, "select distinct t.triggerid,t.description,t.expression,t.error,t.priority,t.type,t.value,t.state,t.lastchange,t.status from hosts h,items i,functions f,triggers t where h.hostid=i.hostid and i.itemid=f.itemid and f.triggerid=t.triggerid and h.status in (0,1) and t.flags<>2"

      3665:20170704:235731.383 server #0 started [main process]
      .
      .
      .

      all zabbix function is work correctly for a few minutes... then have a several slow query and you dont have any garphs but i see zabbix do all function correctly

      Attachments

        1. cache.JPG
          cache.JPG
          77 kB
        2. gathering.JPG
          gathering.JPG
          81 kB
        3. hole graphics.JPG
          hole graphics.JPG
          55 kB
        4. internal.JPG
          internal.JPG
          68 kB
        5. performance.JPG
          performance.JPG
          68 kB

        Activity

          People

            Unassigned Unassigned
            fatehchehr Fatehchehr
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: