Slow queries after upgrade to 3.4

[bundaburg]

After performing an in place upgrade to 3.4, the mysql log is showing lots of these slow queries every minute -

4742:20180404:154012.051 slow query: 8.377756 sec, "select i.itemid,f.functionid,f.function,f.parameter,t.tri ggerid 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"
4742:20180404:154029.983 slow query: 16.702824 sec, "select distinct t.triggerid,t.description,t.expression,t.error,t.p riority,t.type,t.value,t.state,t.lastchange,t.stat us,t.recovery_mode,t.recovery_expression,t.correla tion_mode,t.correlation_tag 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"

[aigars.kadikis]

Hi, bundaburg!

What is the size of Zabbix database?
SELECT table_schema "zabbix", sum(data_length + index_length)/1024/1024 "DВ size in MB" FROM information_schema.TABLES GROUP BY table_schema;

[bundaburg]

Hi, bundaburg!

What is the size of Zabbix database?
SELECT table_schema "zabbix", sum(data_length + index_length)/1024/1024 "DВ size in MB" FROM information_schema.TABLES GROUP BY table_schema;

Hi there,
The DB is 75gb

[saskuu]

select i.itemid,i.hostid,i.status,i.type,i.value_type,i.k ey_,i.snmp_community,i.snmp_oid,i.port,i.snmpv3_se curityname,i.snmpv3_securitylevel,i.snmpv3_authpas sphrase,i.snmpv3_privpassphrase,i.ipmi_sensor,i.de lay,i.trapper_hosts,i.logtimefmt,i.params,i.state, i.authtype,i.username,i.password,i.publickey,i.pri vatekey,i.flags,i.interfaceid,i.snmpv3_authprotoco l,i.snmpv3_privprotocol,i.snmpv3_contextname,i.las tlogsize,i.mtime,i.history,i.trends,i.inventory_li nk,i.valuemapid,i.units,i.error,i.jmx_endpoint,i.m aster_itemid from items i,hosts h where i.hostid=h.hostid and h.status in (0,1) and i.flags<>2;

this query returned 344868 records !
it is not surprising that we constantly get "slow query" !
zabbix 3.4.13
base mariadb 2 Gb

[kloczek]

Yep .. if you don't have enough memory used y innodb pool your engine will be not able to cache data and to often your engine will be forced to read data for selects from storage .. which increases avg selects latency.

[saskuu]

this ?
innodb_buffer_pool_size = 4G
or what should i set up?

It's unclear, next to zabbix 2.4 (12GB RAM, 50GB base, 104 hosts, 86 new values per seconds) and no problems. but on the new one (zabbix 3.4.13 48 gigabytes of RAM, 2 gigabytes, 12 hosts, 8 new values per seconds), where I just started monitoring, get "slow queries"

[kloczek]

this ?
innodb_buffer_pool_size = 4G

Yes.
If you would have ZFS as file system this tuning should be done a bit different.
innodb_buffer_pool_size size should be size of the all indexes [1] then ell other necessary memory should be available to ZFS ARC.

It's unclear, next to zabbix 2.4 (12GB RAM, 50GB base, 104 hosts, 86 new values per seconds) and no problems. but on the new one (zabbix 3.4.13 48 gigabytes of RAM, 2 gigabytes, 12 hosts, 8 new values per seconds), where I just started monitoring, get "slow queries"

One of the GoodEnough(tm) estimations about how much memory should be using SQL engine used by zabbix server is size of the last 24h data added to the database.
Why?
Because most of the select queries are operating within data added in last of the 24h.
This assumption is valid if history* tables are partitioned (for example on daily base). If those tables are not partitioned exact size of the memory is way greater and depends on internal database fragmentation.
With partitioned history* tables is easy to do such estimation by simple looking on the size of the prev day history* tables partitions.

[1] In my MySQL template on https://github.com/kloczek/zabbix-te...ervice%20MySQL is possible find LLD which samples size of the data and indexes per database.