Question about mysql8 performance

[pvdzwan]

Hi,
We are using mysql8 as backend for Zabbix 6.0 and I have some doubts about mysql config/tuning.
When I run select min(clock) from history_text it takes more than 7 minutes and select count(*) from history_text it takes more than 5 minutes.
To me that feels like something is very wrong.

mysql> select min(clock) from history_text ;
+------------+
| min(clock) |
+------------+
| 1693384510 |
+------------+
1 row in set (7 min 9.18 sec)
mysql> select count(*) from history_text ;
+-----------+
| count(*) |
+-----------+
| 464306714 |
+-----------+
1 row in set (5 min 17.46 sec)

Is this normal ?

Regards,
Paul

[LenR]

I'm not sure it's normal to have almost half a billion rows in history_text, that's not one of my larger tables. How many rows does history

[pvdzwan]

I just tried it for history. Took almost an hour, that's horrible:

mysql> select count(*) from history ;
+------------+
| count(*) |
+------------+
| 1510898312 |
+------------+
1 row in set (59 min 20.50 sec)

mysql> desc history ;
+--------+-----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------+------+-----+---------+-------+
| itemid | bigint unsigned | NO | PRI | NULL | |
| clock | int | NO | PRI | 0 | |
| value | double | NO | | 0 | |
| ns | int | NO | PRI | 0 | |
+--------+-----------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql> show table status where name like "history%" ;
+--------------+--------+---------+------------+------------+----------------+--------------+-----------------+--------------+------------+----------------+---------------------+---------------------+------------+-------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+--------------+--------+---------+------------+------------+----------------+--------------+-----------------+--------------+------------+----------------+---------------------+---------------------+------------+-------------+----------+----------------+---------+
| history | InnoDB | 10 | Dynamic | 1448925267 | 53 | 76850577408 | 0 | 0 | 5242880 | NULL | 2023-08-30 10:05:19 | 2023-10-30 14:38:55 | NULL | utf8mb3_bin | NULL | | |
| history_log | InnoDB | 10 | Dynamic | 0 | 0 | 1097728 | 0 | 0 | 0 | NULL | 2023-10-29 22:55:05 | NULL | NULL | utf8mb3_bin | NULL | partitioned | |
| history_str | InnoDB | 10 | Dynamic | 18743948 | 74 | 1393770496 | 0 | 0 | 244318208 | NULL | 2023-10-29 22:55:06 | 2023-10-30 14:38:55 | NULL | utf8mb3_bin | NULL | partitioned | |
| history_text | InnoDB | 10 | Dynamic | 684694621 | 54 | 37103878144 | 0 | 0 | 1166016512 | NULL | 2023-10-29 22:55:16 | 2023-10-30 14:38:55 | NULL | utf8mb3_bin | NULL | partitioned | |
| history_uint | InnoDB | 10 | Dynamic | 3441660115 | 49 | 169108340736 | 0 | 0 | 4194304 | NULL | 2023-08-30 10:05:20 | 2023-10-30 14:38:55 | NULL | utf8mb3_bin | NULL | | |
+--------------+--------+---------+------------+------------+----------------+--------------+-----------------+--------------+------------+----------------+---------------------+---------------------+------------+-------------+----------+----------------+---------+
5 rows in set (0.04 sec)

[LenR]

1.5 billion rows? How many hosts? What is NVPS? How long has this instance been used?

[pvdzwan]

Hi
We have 1250 active, 120 disabled hosts. Retention for history* is 60d. NVPS is about 1500.
Server has been active for about 2 months after migration from 5.x. Old database was imported from backup.

I tried to setup partitioning ( was setup but removed by import of old data, which dropped the tables).
But the alter table command seemed to block entire zabbix server so that alter table command was terminated.
Housekeeping is enabled.

[LenR]

Can you verify that your history is within the 60 days? It seems you have a lot of history for that size.

Some of the tables show partitioned, some don't. Is housekeeping enabled for history?

[LenR]

This might tell you more, but it will run a long time

SELECT count(*), DATE_FORMAT(FROM_UNIXTIME(`clock`), '%Y %m %d') AS 'date_formatted' FROM `history` group by date_formatted;

[pvdzwan]

Looks like there are no indexes on 'clock' for the history tables. I guess that causes full table scans for these queries.
With tables that size and on an NFS share that's going to take some time.

[cyber]

There should be... at least for PG... should be same for MySql...

Indexes:
"history_pkey" PRIMARY KEY, btree (itemid, clock, ns)
"history_1" btree (itemid, clock)
"history_clock_idx" btree (clock DESC)
​

[pvdzwan]

I only see the primary key, no indexes:
mysql> 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 | Visible | Expression |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| history | 0 | PRIMARY | 1 | itemid | A | 3836404 | NULL | NULL | | BTREE | | | YES | NULL |
| history | 0 | PRIMARY | 2 | clock | A | 2093496320 | NULL | NULL | | BTREE | | | YES | NULL |
| history | 0 | PRIMARY | 3 | ns | A | 2093671296 | NULL | NULL | | BTREE | | | YES | NULL |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.07 sec)

mysql> show create table history ;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| history | CREATE TABLE `history` (
`itemid` bigint unsigned NOT NULL,
`clock` int NOT NULL DEFAULT '0',
`value` double NOT NULL DEFAULT '0',
`ns` int NOT NULL DEFAULT '0',
PRIMARY KEY (`itemid`,`clock`,`ns`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

BTW housekeeping jobs seem to run quite well. About 3 mins needed for hourly housekeeper.