[ZBX-21027] Zabbix 6.0 LTS slow mysql queries and 100% cpu Created: 2022 May 11  Updated: 2022 Nov 04

Status: Confirmed
Project: ZABBIX BUGS AND ISSUES
Component/s: Server (S)
Affects Version/s: 6.0.4
Fix Version/s: None

Type: Problem report Priority: Major
Reporter: Jesse Caudill Assignee: Zabbix Development Team
Resolution: Unresolved Votes: 2
Labels: MariaDB, graph, mysql, performance
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

Ubuntu 20.04.3 LTS
Mariadb 10.6.7

Attachments: File LNT.patch     Text File showfullprocesslist-slowqueries.txt    

 Description   

Steps to reproduce:

  1. This is a clean install starting at 6.0.3 LTS that has had the point upgrades done. It is a distributed VM deployment. 2 separate frontend VM's, 2 server VM's with the new built in HA feature and a single mariadb VM. All VM's are Ubuntu 20.04.3 LTS.
  2. I had seen periodic slow query messages in the server log since I started the setup and hadn't found it to be problem, but over the last day the system became unusable. We had 2 cores and the mariadb was maxing both. We bumped up to 4 cores and then it was maxing all 4 cores.
  3. Slow queries were 500+ seconds long. I will include some processlist outputs in attachments.
  4. My system is running nearly 80,000 items. new values per second 192.95
  5. I did some searching and found a thread referencing a database table that was missing a key, so we checked our create tables statements and they all had keys in them. (I created them using "/usr/share/doc/zabbix-sql-scripts/mysql/server.sql.gz" per the install instructions)
  6. The longest queries we found were on the triggers, functions, and graphs tables. While they all had keys they did not have a composite index. With no other changes besides creating the index on the table, query times on the tables went from 500+ seconds to 2-3 seconds.
  7. This query was running for 43 seconds at the time of checking:
    select distinct g.graphid,g.name,g.width,g.height,g.yaxismin,g.yaxismax,g.show_work_period,g.show_triggers,g.graphtype,g.show_legend,g.show_3d,g.percent_left,g.percent_right,g.ymin_type,g.ymin_itemid,g.ymax_type,g.ymax_itemid,g.discover from graphs g,graphs_items gi,items i,item_discovery id where g.graphid=gi.graphid and gi.itemid=i.itemid and i.itemid=id.itemid and id.parent_itemid=43199
  8. We added the command "create index graphs_items_i_2 on graphs_items(graphid,itemid);" and now that query takes 0.001 seconds with no other changes.
  9. We made similar changes on functions and triggers tables but I can't find in my logs the exact commands we ran to give for examples.

I would like to know if this was the proper fix for this? Should there be a different approach? While the massively slow queries are now resolved for the 3 worst offending tables, it appears there may be other tables that are facing simliar issues. trigger_depends and items are my persistent slow queries now in the 3-5 second range.

I'm not a database expert and so I'm not sure what relevant data I should include. Please let me know what other outputs are needed.

 Comments   
Comment by Edgar Akhmetshin [ 2022 May 12 ]

Hello,

Do you have ANALYZE output for the query before adding additional index?

Comment by Jesse Caudill [ 2022 May 12 ]

MariaDB [zabbix]> explain select distinct d.triggerid_down,d.triggerid_up from trigger_depends d,triggers t,hosts h,items i,functions f where t.triggerid=d.triggerid_down and t.flags<>2 and h.hostid=i.hostid and i.itemid=f.itemid and f.triggerid=d.triggerid_down and h.status in (0,1);
------------------------------------------------------------------------------------------------------------------------------------------------------------+

id select_type table type possible_keys key key_len ref rows Extra

------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 SIMPLE d index trigger_depends_1 trigger_depends_1 16 NULL 1 Using index; Using temporary
1 SIMPLE t eq_ref PRIMARY PRIMARY 8 zabbix.d.triggerid_down 1 Using where
1 SIMPLE i index PRIMARY,items_1 items_1 3066 NULL 1 Using index; Using join buffer (flat, BNL join)
1 SIMPLE f ALL functions_1,functions_2 NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join)
1 SIMPLE h eq_ref PRIMARY,hosts_2 PRIMARY 8 zabbix.i.hostid 1 Using where; Distinct

------------------------------------------------------------------------------------------------------------------------------------------------------------+

Comment by Jesse Caudill [ 2022 May 12 ]

MariaDB [zabbix]> analyze select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.snmp_oid,i.ipmi_sensor,i.delay,i.trapper_hosts,i.logtimefmt,i.params,ir.state,i.authtype,i.username,i.password,i.publickey,i.privatekey,i.flags,i.interfaceid,ir.lastlogsize,ir.mtime,i.history,i.trends,i.inventory_link,i.valuemapid,i.units,ir.error,i.jmx_endpoint,i.master_itemid,i.timeout,i.url,i.query_fields,i.posts,i.status_codes,i.follow_redirects,i.post_type,i.http_proxy,i.headers,i.retrieve_mode,i.request_method,i.output_format,i.ssl_cert_file,i.ssl_key_file,i.ssl_key_password,i.verify_peer,i.verify_host,i.allow_traps,i.templateid,null from items i inner join hosts h on i.hostid=h.hostid join item_rtdata ir on i.itemid=ir.itemid where h.status in (0,1) and i.flags<>2;
------------------------------------------------------------------------------------------------------------------

id   select_type table type   possible_keys   key     key_len ref               rows r_rows   filtered r_filtered Extra      

------------------------------------------------------------------------------------------------------------------

   1 SIMPLE       ir     ALL     PRIMARY         NULL     NULL     NULL             1     81367.00   100.00     100.00            
   1 SIMPLE       i     eq_ref PRIMARY,items_1 PRIMARY 8       zabbix.ir.itemid 1     1.00       100.00     100.00 Using where
   1 SIMPLE       h     eq_ref PRIMARY,hosts_2 PRIMARY 8       zabbix.i.hostid   1     1.00        90.82     100.00 Using where

------------------------------------------------------------------------------------------------------------------
3 rows in set (1.382 sec)

MariaDB [zabbix]> analyze select distinct d.triggerid_down,d.triggerid_up from trigger_depends d,triggers t,hosts h,items i,functions f where t.triggerid=d.triggerid_down and t.flags<>2 and h.hostid=i.hostid and i.itemid=f.itemid and f.triggerid=d.triggerid_down and h.status in (0,1)
    -> ;
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

id   select_type table type   possible_keys                         key               key_len ref                 rows r_rows filtered r_filtered Extra                                    

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

   1 SIMPLE       h     range   PRIMARY,hosts_2                       hosts_2           4       NULL               277   276.00   100.00     100.00 Using where; Using index; Using temporary
   1 SIMPLE       i     ref     PRIMARY,items_1                       items_1           8       zabbix.h.hostid     75   319.13   100.00     100.00 Using index                              
   1 SIMPLE       f     ref     functions_1,functions_2,functions_t_i functions_2       8       zabbix.i.itemid     1     1.47     100.00     100.00                                          
   1 SIMPLE       d     ref     trigger_depends_1                     trigger_depends_1 8       zabbix.f.triggerid 1     0.68     100.00     100.00 Using index                              
   1 SIMPLE       t     eq_ref PRIMARY                               PRIMARY           8       zabbix.f.triggerid 1     1.00     100.00      94.67 Using where; Distinct                    

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
5 rows in set (2.151 sec)

 

Comment by Jesse Caudill [ 2022 May 12 ]

I'm sorry that's all I could find from my session logs.

Comment by Michael Kuhn [ 2022 May 16 ]

Jesse

Do you remember the index you added for the point below:

  • The longest queries we found were on the triggers, functions, and graphs tables. While they all had keys they did not have a composite index. With no other changes besides creating the index on the table, query times on the tables went from 500+ seconds to 2-3 seconds.

I have the same issue with the below query running 5 minutes

select distinct d.triggerid_down,d.triggerid_up from trigger_depends d,triggers t,hosts h,items i,functions f where t.triggerid=d.triggerid_down and t.flags<>2 and h.hostid=i.hostid and i.itemid=f.itemid and f.triggerid=d.triggerid_down and h.status in (0,1);

Comment by Jesse Caudill [ 2022 May 16 ]

I'm sorry I don't! I had someone helping me and he did the changes via console in vsphere, there doesn't seem to be any history of what he put in that I can find. Below is the output of the 'show index' for the 3 tables if that's helpful. If there's a better command I'd be happy to run it and include the output.

 

MariaDB [zabbix]> show index in functions;
------------------------------------------------------------------------------------------------------------------------------------+

Table     Non_unique Key_name       Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored

------------------------------------------------------------------------------------------------------------------------------------+

functions          0 PRIMARY                  1 functionid   A              130980     NULL NULL         BTREE                             NO      
functions          1 functions_1              1 triggerid   A              130980     NULL NULL         BTREE                             NO      
functions          1 functions_2              1 itemid       A              130980     NULL NULL         BTREE                             NO      
functions          1 functions_2              2 name         A              130980     NULL NULL         BTREE                             NO      
functions          1 functions_2              3 parameter   A              130980     NULL NULL         BTREE                             NO      
functions          1 functions_t_i            1 triggerid   A              130980     NULL NULL         BTREE                             NO      
functions          1 functions_t_i            2 itemid       A              130980     NULL NULL         BTREE                             NO      

------------------------------------------------------------------------------------------------------------------------------------+
7 rows in set (0.001 sec)

MariaDB [zabbix]> show index in graphs;
----------------------------------------------------------------------------------------------------------------------------+

Table   Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored

----------------------------------------------------------------------------------------------------------------------------+

graphs          0 PRIMARY              1 graphid     A                   0     NULL NULL         BTREE                             NO      
graphs          1 graphs_1            1 name         A                   0     NULL NULL         BTREE                             NO      
graphs          1 graphs_2            1 templateid   A                   0     NULL NULL   YES   BTREE                             NO      
graphs          1 graphs_3            1 ymin_itemid A                   0     NULL NULL   YES   BTREE                             NO      
graphs          1 graphs_4            1 ymax_itemid A                   0     NULL NULL   YES   BTREE                             NO      

----------------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.000 sec)

MariaDB [zabbix]> show index in triggers;
--------------------------------------------------------------------------------------------------------------------------------+

Table     Non_unique Key_name   Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored

--------------------------------------------------------------------------------------------------------------------------------+

triggers          0 PRIMARY                1 triggerid   A               48361     NULL NULL         BTREE                             NO      
triggers          1 triggers_1            1 status       A                   2     NULL NULL         BTREE                             NO      
triggers          1 triggers_2            1 value       A                   2     NULL NULL         BTREE                             NO      
triggers          1 triggers_2            2 lastchange   A                 435     NULL NULL         BTREE                             NO      
triggers          1 triggers_3            1 templateid   A                 469     NULL NULL   YES   BTREE                             NO      

--------------------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.001 sec)

Comment by Michael Kuhn [ 2022 May 16 ]

Thanks Jesse

This helped a lot.  I added the below and my issue went away immediately.

create index function_t_i on functions(triggerid, itemid);

Thanks again

Michael

Comment by Jesse Caudill [ 2022 May 16 ]

Great! I was thinking  function_t_i was the index added to functions. The triggers one isn't as clear to me from the output. Right now mine is giving me fits on the item_discovery table but they're in the 5-15 second range rather than 500+, so I'm hoping I can wait for the official correction on these.

Comment by Anton Baranov [ 2022 Jun 13 ]

I'm not sure about the correctness of the request, but in my case the problem was solved

LNT.patch

Comment by Vladislavs Sokurenko [ 2022 Jul 13 ]

Please check if it's same issue as in ZBX-20936

Comment by Jesse Caudill [ 2022 Jul 13 ]

yes looks similar

Comment by Nathan [ 2022 Aug 03 ]

Hello,
I have the same issue on item_discovery. The query takes 14 000 secs.

Did you find something on this query to improve it?

Comment by Jesse Caudill [ 2022 Aug 03 ]

My issue and an associated ticket are apparently fixed in pre-6.0.7rc1. I believe its fixed by changing the parameters of the query rather than modifying the database.

 

If you are having issues I would recommend these steps:
1) Make sure your slow query log is turned on

2) Get an "analyze" output of your slow query. Sometimes the "describe" and "explain" outputs are useful as well.

3) Start a post or ticket asking for help - with that info we can see what's going on.

Comment by Nathan [ 2022 Aug 03 ]

Thank you for your feedback. I'll start a new post with all the info.

Comment by Péter Láng [ 2022 Nov 04 ]

I think this issue is still very much present in version 6.0.9.

My setup is much smaller (single server node, 20 hosts, 2500 items, 1100 triggers, 20 values/sec), but otherwise quite similar: I'm using Ubuntu 22.04 LTS and MariaDB 10.6.7-2ubuntu1.1.

Previously I had more items (around 10K I think) and started to get slow query logs in the server:

MariaDB [zabbix]> analyze select distinct g.graphid,g.name,g.width,g.height,g.yaxismin,g.yaxismax,g.show_work_period,g.show_triggers,g.graphtype,g.show_legend,g.show_3d,g.percent_left,g.percent_right,g.ymin_type,g.ymin_itemid,g.ymax_type,g.ymax_itemid,g.discover from graphs g,graphs_items gi,items i,item_discovery id where g.graphid=gi.graphid and gi.itemid=i.itemid and i.itemid=id.itemid and id.parent_itemid=75725;
+------+-------------+-------+--------+-----------------------------------+------------------+---------+------------------------+------+----------+----------+------------+-------------------------------------------------+
| id   | select_type | table | type   | possible_keys                     | key              | key_len | ref                    | rows | r_rows   | filtered | r_filtered | Extra                                           |
+------+-------------+-------+--------+-----------------------------------+------------------+---------+------------------------+------+----------+----------+------------+-------------------------------------------------+
|    1 | SIMPLE      | g     | ALL    | PRIMARY                           | NULL             | NULL    | NULL                   | 1    | 4127.00  |   100.00 |     100.00 | Using temporary                                 |
|    1 | SIMPLE      | gi    | ALL    | graphs_items_1,graphs_items_2     | NULL             | NULL    | NULL                   | 1    | 15117.00 |   100.00 |       0.02 | Using where; Using join buffer (flat, BNL join) |
|    1 | SIMPLE      | id    | eq_ref | item_discovery_1,item_discovery_2 | item_discovery_1 | 16      | zabbix.gi.itemid,const | 1    | 0.00     |   100.00 |     100.00 | Using index; Distinct                           |
|    1 | SIMPLE      | i     | eq_ref | PRIMARY                           | PRIMARY          | 8       | zabbix.gi.itemid       | 1    | 1.00     |   100.00 |     100.00 | Using index; Distinct                           |
+------+-------------+-------+--------+-----------------------------------+------------------+---------+------------------------+------+----------+----------+------------+-------------------------------------------------+
4 rows in set (12.754 sec)

 

I initially fixed this by allocating more resources the the DB and dropping a ton of items, triggers and graphs created by some overly-eager templates (Docker). Analyze with the smaller dataset:

MariaDB [zabbix]> analyze select distinct g.graphid,g.name,g.width,g.height,g.yaxismin,g.yaxismax,g.show_work_period,g.show_triggers,g.graphtype,g.show_legend,g.show_3d,g.percent_left,g.percent_right,g.ymin_type,g.ymin_itemid,g.ymax_type,g.ymax_itemid,g.discover from graphs g,graphs_items gi,items i,item_discovery id where g.graphid=gi.graphid and gi.itemid=i.itemid and i.itemid=id.itemid and id.parent_itemid=75725;
+------+-------------+-------+--------+-----------------------------------+------------------+---------+-------------------+------+--------+----------+------------+-----------------------+
| id   | select_type | table | type   | possible_keys                     | key              | key_len | ref               | rows | r_rows | filtered | r_filtered | Extra                 |
+------+-------------+-------+--------+-----------------------------------+------------------+---------+-------------------+------+--------+----------+------------+-----------------------+
|    1 | SIMPLE      | id    | ref    | item_discovery_1,item_discovery_2 | item_discovery_2 | 8       | const             | 37   | 37.00  |   100.00 |     100.00 | Using temporary       |
|    1 | SIMPLE      | gi    | ref    | graphs_items_1,graphs_items_2     | graphs_items_1   | 8       | zabbix.id.itemid  | 1    | 0.35   |   100.00 |     100.00 |                       |
|    1 | SIMPLE      | g     | eq_ref | PRIMARY                           | PRIMARY          | 8       | zabbix.gi.graphid | 1    | 1.00   |   100.00 |     100.00 |                       |
|    1 | SIMPLE      | i     | eq_ref | PRIMARY                           | PRIMARY          | 8       | zabbix.id.itemid  | 1    | 1.00   |   100.00 |     100.00 | Using index; Distinct |
+------+-------------+-------+--------+-----------------------------------+------------------+---------+-------------------+------+--------+----------+------------+-----------------------+
4 rows in set (0.001 sec)

 

So it seems the strategy is different for smaller dataset, but as new data was added the strategy went back to the one above, and the execution time started to increase once again. So I added the composite index recommended by OP (create index gp_graphs_items_i_2 on graphs_items(graphid,itemid);), results:

MariaDB [zabbix]> analyze select distinct g.graphid,g.name,g.width,g.height,g.yaxismin,g.yaxismax,g.show_work_period,g.show_triggers,g.graphtype,g.show_legend,g.show_3d,g.percent_left,g.percent_right,g.ymin_type,g.ymin_itemid,g.ymax_type,g.ymax_itemid,g.discover from graphs g,graphs_items gi,items i,item_discovery id where g.graphid=gi.graphid and gi.itemid=i.itemid and i.itemid=id.itemid and id.parent_itemid=75725;
+------+-------------+-------+--------+---------------------------------------------------+---------------------+---------+------------------------+------+---------+----------+------------+-----------------------+
| id   | select_type | table | type   | possible_keys                                     | key                 | key_len | ref                    | rows | r_rows  | filtered | r_filtered | Extra                 |
+------+-------------+-------+--------+---------------------------------------------------+---------------------+---------+------------------------+------+---------+----------+------------+-----------------------+
|    1 | SIMPLE      | g     | ALL    | PRIMARY                                           | NULL                | NULL    | NULL                   | 1    | 1736.00 |   100.00 |     100.00 | Using temporary       |
|    1 | SIMPLE      | gi    | ref    | graphs_items_1,graphs_items_2,gp_graphs_items_i_2 | gp_graphs_items_i_2 | 8       | zabbix.g.graphid       | 1    | 2.97    |   100.00 |     100.00 | Using index; Distinct |
|    1 | SIMPLE      | i     | eq_ref | PRIMARY                                           | PRIMARY             | 8       | zabbix.gi.itemid       | 1    | 1.00    |   100.00 |     100.00 | Using index; Distinct |
|    1 | SIMPLE      | id    | eq_ref | item_discovery_1,item_discovery_2                 | item_discovery_1    | 16      | zabbix.gi.itemid,const | 1    | 0.00    |   100.00 |     100.00 | Using index; Distinct |
+------+-------------+-------+--------+---------------------------------------------------+---------------------+---------+------------------------+------+---------+----------+------------+-----------------------+
4 rows in set (0.028 sec)

 

With this, all was fine, until the frontend started to be extremely slow when loading certain data (for example: Trigger overview widgets on the dashboard or the Monitoring/Hosts page):

MariaDB [zabbix]> analyze SELECT DISTINCT t.triggerid,t.expression,t.description,t.value,t.priority,t.lastchange,t.flags,t.comments,t.manual_close FROM triggers t,functions f,items i WHERE i.hostid IN (10532,10533,10534,10535,10536,10537,10538,10539,10540) AND f.triggerid=t.triggerid AND f.itemid=i.itemid AND NOT EXISTS (SELECT NULL FROM functions f,items i,hosts h WHERE t.triggerid=f.triggerid AND f.itemid=i.itemid AND i.hostid=h.hostid AND (i.status<>0 OR h.status<>0)) AND t.status=0 AND t.flags IN (0,4);
+------+--------------------+-------+--------+-----------------------------------------------------------+---------+---------+--------------------+------+----------+----------+------------+------------------------------+
| id   | select_type        | table | type   | possible_keys                                             | key     | key_len | ref                | rows | r_rows   | filtered | r_filtered | Extra                        |
+------+--------------------+-------+--------+-----------------------------------------------------------+---------+---------+--------------------+------+----------+----------+------------+------------------------------+
|    1 | PRIMARY            | f     | ALL    | functions_1,functions_2                                   | NULL    | NULL    | NULL               | 1    | 12252.00 |   100.00 |      22.22 | Using where; Using temporary |
|    1 | PRIMARY            | i     | eq_ref | PRIMARY,items_1                                           | PRIMARY | 8       | zabbix.f.itemid    | 1    | 1.00     |   100.00 |      58.54 | Using where                  |
|    1 | PRIMARY            | t     | eq_ref | PRIMARY,triggers_1                                        | PRIMARY | 8       | zabbix.f.triggerid | 1    | 1.00     |    49.99 |      83.50 | Using where                  |
|    2 | DEPENDENT SUBQUERY | f     | ALL    | functions_1,functions_2                                   | NULL    | NULL    | NULL               | 1    | 6507.38  |   100.00 |       0.02 | Using where                  |
|    2 | DEPENDENT SUBQUERY | i     | eq_ref | PRIMARY,items_1,items_3,items_4,items_5,items_6,items_7   | PRIMARY | 8       | zabbix.f.itemid    | 1    | 1.00     |   100.00 |     100.00 |                              |
|    2 | DEPENDENT SUBQUERY | h     | eq_ref | PRIMARY,hosts_1,hosts_2,hosts_3,hosts_4,hosts_5,c_hosts_3 | PRIMARY | 8       | zabbix.i.hostid    | 1    | 1.00     |   100.00 |      52.06 | Using where                  |
+------+--------------------+-------+--------+-----------------------------------------------------------+---------+---------+--------------------+------+----------+----------+------------+------------------------------+
6 rows in set (24.510 sec)

 

Adding a composite index on functions seems to fix this: create index gp_functions_t_i on functions(triggerid, itemid);

MariaDB [zabbix]> analyze SELECT DISTINCT t.triggerid,t.expression,t.description,t.value,t.priority,t.lastchange,t.flags,t.comments,t.manual_close FROM triggers t,functions f,items i WHERE i.hostid IN (10532,10533,10534,10535,10536,10537,10538,10539,10540) AND f.triggerid=t.triggerid AND f.itemid=i.itemid AND NOT EXISTS (SELECT NULL FROM functions f,items i,hosts h WHERE t.triggerid=f.triggerid AND f.itemid=i.itemid
AND i.hostid=h.hostid AND (i.status<>0 OR h.status<>0)) AND t.status=0 AND t.flags IN (0,4);
+------+--------------------+-------+--------+-----------------------------------------------------------+------------------+---------+--------------------+------+----------+----------+------------+-------------------------------------------+
| id   | select_type        | table | type   | possible_keys                                             | key              | key_len | ref                | rows | r_rows   | filtered | r_filtered | Extra                                     |
+------+--------------------+-------+--------+-----------------------------------------------------------+------------------+---------+--------------------+------+----------+----------+------------+-------------------------------------------+
|    1 | PRIMARY            | f     | index  | functions_1,functions_2,gp_functions_t_i                  | gp_functions_t_i | 16      | NULL               | 1    | 12252.00 |   100.00 |      22.22 | Using where; Using index; Using temporary |
|    1 | PRIMARY            | i     | eq_ref | PRIMARY,items_1                                           | PRIMARY          | 8       | zabbix.f.itemid    | 1    | 1.00     |   100.00 |      58.54 | Using where                               |
|    1 | PRIMARY            | t     | eq_ref | PRIMARY,triggers_1                                        | PRIMARY          | 8       | zabbix.f.triggerid | 1    | 1.00     |    49.99 |      83.50 | Using where                               |
|    2 | DEPENDENT SUBQUERY | f     | ref    | functions_1,functions_2,gp_functions_t_i                  | gp_functions_t_i | 8       | func               | 1    | 1.24     |   100.00 |     100.00 | Using index                               |
|    2 | DEPENDENT SUBQUERY | i     | eq_ref | PRIMARY,items_1,items_3,items_4,items_5,items_6,items_7   | PRIMARY          | 8       | zabbix.f.itemid    | 1    | 1.00     |   100.00 |     100.00 |                                           |
|    2 | DEPENDENT SUBQUERY | h     | eq_ref | PRIMARY,hosts_1,hosts_2,hosts_3,hosts_4,hosts_5,c_hosts_3 | PRIMARY          | 8       | zabbix.i.hostid    | 1    | 1.00     |   100.00 |      67.47 | Using where                               |
+------+--------------------+-------+--------+-----------------------------------------------------------+------------------+---------+--------------------+------+----------+----------+------------+-------------------------------------------+
6 rows in set (0.100 sec)

 

So far everything seems to be running fine, however MysqlTuner still reports that there are "Joins performed without indexes", so I also think this is a broader issue, as mentioned by OP.

Generated at Mon Jun 16 09:17:06 EEST 2025 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.