Slow Query Trigger Tag and Value

[Richi]

Hi,

i have one slow Query which I dont get fixed. I hope someone can help me to find the cause.

The Query itslef is done by the Zabbix Server:
zabbix_server.log:

29675:20190711:130650.872 slow query: 38.739595 sec, "select distinct tt.triggertagid,tt.triggerid,tt.tag,tt.value from trigger_tag tt,triggers t,hosts h,items i,functions f where t.triggerid=tt.triggerid and t.flags<>2 and h.hostid=i.hostid and i.itemid=f.itemid and f.triggerid=tt.triggerid and h.status in (0,1)"

In the Slow Query Log of our Database I also see the same every 3minutes:

# Time: 190711 13:03:50
# User@Host: zabbix[zabbix] @ XXXX [XX.XX.XX.XX]
# Thread_id: 29571539  Schema: zabbix  QC_hit: No
# Query_time: 38.640142  Lock_time: 0.000048  Rows_sent: 1098189  Rows_examined: 3342564
SET timestamp=1562843030;
select distinct tt.triggertagid,tt.triggerid,tt.tag,tt.value from trigger_tag tt,triggers t,hosts h,items i,functions f where t.triggerid=tt.triggerid and t.flags<>2 and h.hostid=i.hostid and i.itemid=f.itemid and f.triggerid=tt.triggerid and h.status in (0,1);

# Time: 190711 13:06:50
# User@Host: zabbix[zabbix] @ XXXX [XX.XX.XX.XX]
# Thread_id: 29571539  Schema: zabbix  QC_hit: No
# Query_time: 38.739229  Lock_time: 0.000051  Rows_sent: 1098189  Rows_examined: 3342564
SET timestamp=1562843210;
select distinct tt.triggertagid,tt.triggerid,tt.tag,tt.value from trigger_tag tt,triggers t,hosts h,items i,functions f where t.triggerid=tt.triggerid and t.flags<>2 and h.hostid=i.hostid and i.itemid=f.itemid and f.triggerid=tt.triggerid and h.status in (0,1);

Version: Zabbix 4.0.0
Database: MariaDB 5.5
System Informations: Hosts 3899, Items 366161, Triggers 277904, Value per Second 2726.98

Database Master cnf:

[mysqld]
max_allowed_packet=500M
innodb_flush_log_at_trx_commit = 0
innodb_doublewrite = 0
innodb_support_xa = 0
innodb_file_per_table = 1
innodb_buffer_pool_size = 50G
innodb_log_file_size = 6400M
innodb_buffer_pool_instances = 50
log-bin = mysql-bin
server_id = 1
expire_logs_days = 6
sync_binlog = 1
max_connections = 250

Zabbix Server Config (non defaults):

StartPollers=100
StartPollersUnreachable=30
StartTrappers=15
StartPingers=35
MaxHousekeeperDelete=15000

CacheSize=4G
CacheUpdateFrequency=120
StartDBSyncers=8
HistoryCacheSize=128M
HistoryIndexCacheSize=64M
TrendCacheSize=384M
ValueCacheSize=384M
Timeout=30
LogSlowQueries=3000
ExportDir=/var/export/zabbix/
ExportFileSize=1000M

Best Regards
Richi