MySQL Forums
Forum List  »  Partitioning

Strange values in information_schema.partitions
Posted by: Carlos Simó
Date: August 03, 2022 04:48AM

We have a Zabbix server that uses MySQL 8.0.28 as DB, installed on an instance of Azure MySQL Flexible Server.
We apply a quite usual workaround for Zabbix installations with a large volume of data, which is to partition certain types of tables (the original Zabbix design does not contemplate it) on a daily basis, using,
in our case, a python script that runs regularly every day. This script checks which partitions are older than a pre-set threshold (in my case 6 days) and deletes them.
It also checks if it needs more partitions, in such a way that always ahead of the data partition that corresponds to the current day, we leave a number of empty partitions established in advance (in my case 10 days).
In normal operation of the script, per day, it deletes one partition (the one that is older than the expected threshold) and creates a future partition.

For a few days now, I am watching some "weird" values ​​in the information_schema.partitions table in the create_date and update_date fields.
For example, for this morning's run, for one of the partitioned tables you see the following:

mysql> select table_schema, table_name, partition_name, table_rows, create_time, update_time from partitions where table_name = 'history';
+--------------+------------+----------------+------------+---------------------+---------------------+
| TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | TABLE_ROWS | CREATE_TIME | UPDATE_TIME |
+--------------+------------+----------------+------------+---------------------+---------------------+
| zabbix | history | p2022_07_28 | 53687400 | 2022-08-03 03:32:25 | NULL |
| zabbix | history | p2022_07_29 | 48749688 | 2022-08-03 03:32:25 | NULL |
| zabbix | history | p2022_07_30 | 53768640 | 2022-08-03 03:32:25 | NULL |
| zabbix | history | p2022_07_31 | 52771337 | 2022-08-03 03:32:25 | NULL |
| zabbix | history | p2022_08_01 | 50966961 | 2022-08-03 03:32:25 | NULL |
| zabbix | history | p2022_08_02 | 53817777 | 2022-08-03 03:32:25 | NULL |
| zabbix | history | p2022_08_03 | 21373932 | 2022-08-03 03:32:25 | 2022-08-03 08:56:12 |
| zabbix | history | p2022_08_04 | 0 | 2022-08-03 03:32:25 | NULL |
| zabbix | history | p2022_08_05 | 0 | 2022-08-03 03:32:25 | NULL |
| zabbix | history | p2022_08_06 | 0 | 2022-08-03 03:32:25 | NULL |
| zabbix | history | p2022_08_07 | 0 | 2022-08-03 03:32:25 | NULL |
| zabbix | history | p2022_08_08 | 0 | 2022-08-03 03:32:25 | NULL |
| zabbix | history | p2022_08_09 | 0 | 2022-08-03 03:32:25 | NULL |
| zabbix | history | p2022_08_10 | 0 | 2022-08-03 03:32:25 | NULL |
| zabbix | history | p2022_08_11 | 0 | 2022-08-03 03:32:25 | NULL |
| zabbix | history | p2022_08_12 | 0 | 2022-08-03 03:32:25 | NULL |
| zabbix | history | p2022_08_13 | 0 | 2022-08-03 03:32:25 | NULL |
+--------------+------------+----------------+------------+---------------------+---------------------+
17 rows in set (0.00 sec)

It is observed in the create_time field that all the partitions have today's date, when the partitioning script is executed.
Actually, following the logs of the partitioning script, the only partition that was created today was p2022_08_13.
In the update_time field, it looks like today's partition, obviously registering activity, which is logical, but nevertheless
partitions from previous days, which have data and have had previous similar activity, turn out to have a "NULL" value, when it should, I think,
have an adequate value on the day of the partition.
For example, a logical value for p2022_07_28 could be 2022-07-28 23:59:00 or even 2022-07-29 00:03:00 for transactions that had not been completed before the start of the partition the following day.

For a few days now I have been noticing performance issues with the Zabbix application and the partitioning script.
It turns out that the script takes much longer to perform partition management, specifically deleting the partition that is older than 6 days.
Today, for example, it took about three minutes to delete the p2022_07_27 partition from the history table, when it usually does not reach the minute of execution. It is a partition with a lot of data.
Doing a full system check I don't see errors in MySQL, only evidence that there are I/O performance issues and that there are slow queries, coinciding in time with the execution of the partitioning script.
The partitioning script does not log execution errors.
In Zabbix overloads are observed in the internal process history_syncer that precisely setup monitoring data on the history tables.

That's when I've found these "weird" values ​​in the partitions table. This same situation is systematically repeated every day and I don't know if it really is the origin of my problems.



Any suggestion?

Options: ReplyQuote


Subject
Views
Written By
Posted
Strange values in information_schema.partitions
300
August 03, 2022 04:48AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.