Zabbix mySQL Linked Server with SQL

[wpagane]

Hello Team,

I have the following environment.

• Zabbix 5.2 + mySQL
• 8 CPU COREs
• 16 GB RAM
• CentOS 8

I was able to created a Linked Server for DB zabbix (mySQL) with a SQL Server. Its OK, I'm able to access DB as well and run a simple SELECT.


PROBLEM:

• When I try to run a more complex SELECT query through SQL Server Management Studio, it takes too long to run and/or it gets a failure, for exemple:

SELECT T2.*
,(SELECT TOP 1 value FROM OPENQUERY (ZABBIX, 'SELECT * FROM history' ) WHERE itemid = t2.itemid ORDER BY clock DESC) [Values1]
FROM
(SELECT t1.hostid, itemid

,(SELECT TOP 1 NAME FROM OPENQUERY (ZABBIX, 'SELECT * FROM hosts') where t1.hostid = hostid) HOSTNAME
,t1.key_ Item
,(SELECT TOP 10000 value FROM OPENQUERY (ZABBIX, 'SELECT * FROM history_text' ) WHERE itemid = t1.itemid ORDER BY clock DESC) [Values]


FROM
(SELECT *
FROM OPENQUERY
(
ZABBIX,
'SELECT * FROM items'
)
WHERE key_ IN ('system.sw.packages', 'system.cpu.util[,idle]') and hostid in ('10084'))T1 ) T2

My currently /etc/my.cnf configuration has the following information. Should it be changed to something better? Any hints? I have around 80 hosts. Thank you very much!!!