Dashboard data missing - MySQL load very high, but no slow queries showing.

[slowjoe]

Hi.

I am running Zabbix 5.0.5 and MariaDB 10.3.25-MariaDB-0+deb10u1-log

Recently, problems do not show in the 'dashboard' page, nor do they show in the "problems by severity" element of the homepage.

Looking at the server, when these pages are requested, the CPU load used by the mysqld process is high - often 150-200% resource demand. This does not occur on any other zabbix frontend pages.
"Show processlist" does not reveal any queries taking time - in fact, usually no queries at all.

I have set mysql to log all queries, and I can see that ROLLBACK statements occur quite often. Thinking that this might be a temporary problem, I left this a few days, rebooted, but they still occur.

These are some of the queries that occur before the rollback statements:

update graph_discovery set lastcheck=1605312004 where graphid in (1814,1815,1816,1817)

65 Query insert into trends_uint (itemid,clock,num,value_min,value_avg,value_max) values (36198,1605308400,60,0,0,0),(32658,1605308400,60,1 811939328,1811939328,1811939328)
65 Query commit
65 Query begin
65 Query commit
60 Query begin
60 Query insert into history_uint (itemid,clock,ns,value) values (36018,1605312018,302064425,6)
60 Query commit
60 Query begin
60 Query insert into trends_uint (itemid,clock,num,value_min,value_avg,value_max) values (36018,1605308400,56,6,6,6)
60 Query commit
60 Query begin
60 Query commit
60 Query begin
60 Query insert into history_uint (itemid,clock,ns,value) values (32238,1605312018,311272907,0)
60 Query commit
60 Query begin
60 Query insert into trends_uint (itemid,clock,num,value_min,value_avg,value_max) values (32238,1605308400,60,0,0,0)
60 Query commit
60 Query begin
60 Query commit
9639 Query ROLLBACK

[slowjoe]

I suspect this is the 'history' table causing the problem:


MariaDB [(none)]> SELECT table_schema AS "Database",
-> ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)"
-> FROM information_schema.TABLES
-> GROUP BY table_schema;
+--------------------+-----------+
| Database | Size (MB) |
+--------------------+-----------+
| information_schema | 0.17 |
| mysql | 0.98 |
| performance_schema | 0.00 |
| zabbix | 7985.53 |
+--------------------+-----------+
4 rows in set (0.124 sec)

MariaDB [(none)]> SELECT table_name AS "Table",
-> ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)"
-> FROM information_schema.TABLES
-> WHERE table_schema = "zabbix"
-> ORDER BY (data_length + index_length) DESC;
+----------------------------+-----------+
| Table | Size (MB) |
+----------------------------+-----------+
| history | 3259.67 |
| history_uint | 2387.52 |
| alerts | 1321.22 |
| trends_uint | 750.00 |
| trends | 196.84 |
| events | 19.44 |
| history_text | 17.11 |
| items | 7.19 |
| event_recovery | 5.55 |

[slowjoe]

What I see now is that the 'history' table is very big, and there is an index on the 'itemid' column, AND mysql is using that in the query, but selecting from that index still takes a long time!

MariaDB [zabbix]> show indexes from history;
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| history | 1 | history_1 | 1 | itemid | A | 27622 | NULL | NULL | | BTREE | | |
| history | 1 | history_1 | 2 | clock | A | 29169287 | NULL | NULL | | BTREE | | |
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.001 sec)

MariaDB [zabbix]> select count(itemid) from history;

+---------------+
| count(itemid) |
+---------------+
| 30992996 |
+---------------+
1 row in set (4 min 31.945 sec)

MariaDB [zabbix]>
MariaDB [zabbix]> explain select count(itemid) from history;
+------+-------------+---------+-------+---------------+-----------+---------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+---------+-------+---------------+-----------+---------+------+----------+-------------+
| 1 | SIMPLE | history | index | NULL | history_1 | 12 | NULL | 29173414 | Using index |
+------+-------------+---------+-------+---------------+-----------+---------+------+----------+-------------+
1 row in set (0.000 sec)