search

OpensourceICTSolutions / zabbix-mysql-partitioning-perl

This script is a script written in Perl to partition the Zabbix database tables in time based chunks. We can use this script to replace the Zabbix housekeeper process which tends to get too slow once you hit a certain database size.
https://oicts.com
GNU General Public License v3.0
33 stars 21 forks source link

dont run :( #12

Closed lanaparadinha closed 1 year ago

lanaparadinha commented 1 year ago

mysql> select version(); +-------------------------+ | version() | +-------------------------+ | 8.0.33-0ubuntu0.22.04.2 | +-------------------------+ 1 row in set (0.00 sec)

mysql> SELECT plugin_status FROM information_schema.plugins WHERE plugin_name ='partition'; Empty set (0.00 sec)

mysql> show variables like '%partitioning%'; Empty set (0.00 sec)

But my table is partitioning:

-rw-r----- 1 mysql mysql 114688 May 15 16:25 trends#p#p2023_01_27.ibd -rw-r----- 1 mysql mysql 114688 May 15 16:25 trends#p#p2023_01_28.ibd -rw-r----- 1 mysql mysql 114688 May 15 16:25 trends#p#p2023_01_29.ibd -rw-r----- 1 mysql mysql 114688 May 15 16:25 trends#p#p2023_06.ibd -rw-r----- 1 mysql mysql 114688 May 15 16:25 trends#p#p2023_09.ibd -rw-r----- 1 mysql mysql 114688 May 15 16:25 trends#p#p2023_08.ibd -rw-r----- 1 mysql mysql 114688 May 15 16:25 trends#p#p2023_07.ibd -rw-r----- 1 mysql mysql 114688 May 15 16:25 trends#p#p2023_12.ibd -rw-r----- 1 mysql mysql 114688 May 15 16:25 trends#p#p2023_11.ibd -rw-r----- 1 mysql mysql 114688 May 15 16:25 trends#p#p2023_10.ibd -rw-r----- 1 mysql mysql 114688 May 15 16:25 trends#p#p2024_02.ibd -rw-r----- 1 mysql mysql 114688 May 15 16:25 trends#p#p2024_01.ibd -rw-r----- 1 mysql mysql 12582912 May 15 16:25 trends#p#p2023_01_30.ibd -rw-r----- 1 mysql mysql 18874368 May 15 16:25 trends#p#p2023_01.ibd -rw-r----- 1 mysql mysql 176160768 May 15 16:26 trends#p#p2023_02.ibd -rw-r----- 1 mysql mysql 197132288 May 15 16:27 trends#p#p2023_03.ibd -rw-r----- 1 mysql mysql 192937984 May 15 16:27 trends#p#p2023_04.ibd

if i run "perl mysql_zbx_part_8.pl", this is a result.

root@zabbix-server:/etc/zabbix# perl mysql_zbx_part_8.pl DBD::mysql::db do failed: MAXVALUE can only be used in last partition definition at mysql_zbx_part_8.pl line 126. DBD::mysql::db do failed: MAXVALUE can only be used in last partition definition at mysql_zbx_part_8.pl line 126. DBD::mysql::db do failed: MAXVALUE can only be used in last partition definition at mysql_zbx_part_8.pl line 126. DBD::mysql::db do failed: MAXVALUE can only be used in last partition definition at mysql_zbx_part_8.pl line 126. DBD::mysql::db do failed: MAXVALUE can only be used in last partition definition at mysql_zbx_part_8.pl line 126. DBD::mysql::db do failed: MAXVALUE can only be used in last partition definition at mysql_zbx_part_8.pl line 126. DBD::mysql::db do failed: MAXVALUE can only be used in last partition definition at mysql_zbx_part_8.pl line 126. DBD::mysql::db do failed: MAXVALUE can only be used in last partition definition at mysql_zbx_part_8.pl line 126. DBD::mysql::db do failed: MAXVALUE can only be used in last partition definition at mysql_zbx_part_8.pl line 126. DBD::mysql::db do failed: MAXVALUE can only be used in last partition definition at mysql_zbx_part_8.pl line 126. DBD::mysql::db do failed: Cannot remove all partitions, use DROP TABLE instead at mysql_zbx_part_8.pl line 149. DBD::mysql::db do failed: MAXVALUE can only be used in last partition definition at mysql_zbx_part_8.pl line 126. DBD::mysql::db do failed: MAXVALUE can only be used in last partition definition at mysql_zbx_part_8.pl line 126. DBD::mysql::db do failed: MAXVALUE can only be used in last partition definition at mysql_zbx_part_8.pl line 126. DBD::mysql::db do failed: MAXVALUE can only be used in last partition definition at mysql_zbx_part_8.pl line 126. DBD::mysql::db do failed: MAXVALUE can only be used in last partition definition at mysql_zbx_part_8.pl line 126. DBD::mysql::db do failed: MAXVALUE can only be used in last partition definition at mysql_zbx_part_8.pl line 126. DBD::mysql::db do failed: MAXVALUE can only be used in last partition definition at mysql_zbx_part_8.pl line 126. DBD::mysql::db do failed: MAXVALUE can only be used in last partition definition at mysql_zbx_part_8.pl line 126. DBD::mysql::db do failed: MAXVALUE can only be used in last partition definition at mysql_zbx_part_8.pl line 126. DBD::mysql::db do failed: MAXVALUE can only be used in last partition definition at mysql_zbx_part_8.pl line 126. DBD::mysql::db do failed: Cannot remove all partitions, use DROP TABLE instead at mysql_zbx_part_8.pl line 149. DBD::mysql::db do failed: MAXVALUE can only be used in last partition definition at mysql_zbx_part_8.pl line 126. DBD::mysql::db do failed: MAXVALUE can only be used in last partition definition at mysql_zbx_part_8.pl line 126. DBD::mysql::db do failed: MAXVALUE can only be used in last partition definition at mysql_zbx_part_8.pl line 126. DBD::mysql::db do failed: MAXVALUE can only be used in last partition definition at mysql_zbx_part_8.pl line 126. DBD::mysql::db do failed: MAXVALUE can only be used in last partition definition at mysql_zbx_part_8.pl line 126. DBD::mysql::db do failed: MAXVALUE can only be used in last partition definition at mysql_zbx_part_8.pl line 126. DBD::mysql::db do failed: MAXVALUE can only be used in last partition definition at mysql_zbx_part_8.pl line 126. DBD::mysql::db do failed: MAXVALUE can only be used in last partition definition at mysql_zbx_part_8.pl line 126. DBD::mysql::db do failed: MAXVALUE can only be used in last partition definition at mysql_zbx_part_8.pl line 126. DBD::mysql::db do failed: MAXVALUE can only be used in last partition definition at mysql_zbx_part_8.pl line 126. DBD::mysql::db do failed: Cannot remove all partitions, use DROP TABLE instead at mysql_zbx_part_8.pl line 149. DBD::mysql::db do failed: MAXVALUE can only be used in last partition definition at mysql_zbx_part_8.pl line 126. DBD::mysql::db do failed: MAXVALUE can only be used in last partition definition at mysql_zbx_part_8.pl line 126. DBD::mysql::db do failed: MAXVALUE can only be used in last partition definition at mysql_zbx_part_8.pl line 126. DBD::mysql::db do failed: MAXVALUE can only be used in last partition definition at mysql_zbx_part_8.pl line 126. DBD::mysql::db do failed: MAXVALUE can only be used in last partition definition at mysql_zbx_part_8.pl line 126. DBD::mysql::db do failed: MAXVALUE can only be used in last partition definition at mysql_zbx_part_8.pl line 126. DBD::mysql::db do failed: MAXVALUE can only be used in last partition definition at mysql_zbx_part_8.pl line 126. DBD::mysql::db do failed: MAXVALUE can only be used in last partition definition at mysql_zbx_part_8.pl line 126. DBD::mysql::db do failed: MAXVALUE can only be used in last partition definition at mysql_zbx_part_8.pl line 126. DBD::mysql::db do failed: MAXVALUE can only be used in last partition definition at mysql_zbx_part_8.pl line 126. DBD::mysql::db do failed: Cannot remove all partitions, use DROP TABLE instead at mysql_zbx_part_8.pl line 149.

thiagomdiniz commented 1 year ago

Hi @lanaparadinha ,

I tested the perl script here with MySQL 8.0.32 and 8.0.33 and it worked correctly in both versions.

Are you using the latest version of the perl script? Did you also follow the existing guidelines in the readme and in the Zabbix Blog?

From the description of your error it seems that some partition was created using ... LESS THAN (MAXVALUE) (like in this example), and from what I saw there is no SQL command that uses this in the perl script or in the guidelines.

You can check how your partitions are created with the SQL command show create table <table_name>;. Example for table history:

show create table history;

Running this command here in my test environment with MySQL 8.0.33 the output was this:

mysql> show create table history\G
*************************** 1. row ***************************
       Table: history
Create Table: 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=utf8mb4 COLLATE=utf8mb4_bin
/*!50100 PARTITION BY RANGE (`clock`)
(PARTITION p2023_05_25 VALUES LESS THAN (1685059200) ENGINE = InnoDB,
 PARTITION p2023_05_26 VALUES LESS THAN (1685145600) ENGINE = InnoDB,
 PARTITION p2023_05_27 VALUES LESS THAN (1685232000) ENGINE = InnoDB,
 PARTITION p2023_05_28 VALUES LESS THAN (1685318400) ENGINE = InnoDB,
 PARTITION p2023_05_29 VALUES LESS THAN (1685404800) ENGINE = InnoDB,
 PARTITION p2023_05_30 VALUES LESS THAN (1685491200) ENGINE = InnoDB,
 PARTITION p2023_05_31 VALUES LESS THAN (1685577600) ENGINE = InnoDB,
 PARTITION p2023_06_01 VALUES LESS THAN (1685664000) ENGINE = InnoDB,
 PARTITION p2023_06_02 VALUES LESS THAN (1685750400) ENGINE = InnoDB,
 PARTITION p2023_06_03 VALUES LESS THAN (1685836800) ENGINE = InnoDB,
 PARTITION p2023_06_04 VALUES LESS THAN (1685923200) ENGINE = InnoDB,
 PARTITION p2023_06_05 VALUES LESS THAN (1686009600) ENGINE = InnoDB,
 PARTITION p2023_06_06 VALUES LESS THAN (1686096000) ENGINE = InnoDB,
 PARTITION p2023_06_07 VALUES LESS THAN (1686182400) ENGINE = InnoDB) */
1 row in set (0.00 sec)
lanaparadinha commented 1 year ago

hmmm....

| history | CREATE TABLE history ( itemid bigint unsigned NOT NULL, clock int NOT NULL DEFAULT '0', value double(16,4) NOT NULL DEFAULT '0.0000', ns int NOT NULL DEFAULT '0', KEY history_1 (itemid,clock) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin /!50100 PARTITION BY RANGE (clock) (PARTITION p1 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) / |

I am migrate my server 5.5 to 6.0 and change server...

Perhaps partition is lost in this migration...

thiagomdiniz commented 1 year ago

Your tables actually have a partition that uses LESS THEN MAXVALUE as I suspected, so the script will not work.

I don't know if this is your production environment, but it's always recommended to test updates in a test replicated environment to try to catch behavior like the one you reported.

And to use the perl script you will need to adjust/recreate the table partitions (it is important to make a backup of the database first).

lanaparadinha commented 1 year ago

Resolvd:

mysql> show create table history_text; +--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | history_text | CREATE TABLE history_text ( itemid bigint unsigned NOT NULL, clock int NOT NULL DEFAULT '0', value text CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL, ns int NOT NULL DEFAULT '0', KEY history_text_1 (itemid,clock) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin /!50100 PARTITION BY RANGE (clock) (PARTITION p1 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) / | +--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)

mysql> SELECT FROM_UNIXTIME(MAX(clock)) FROM history_text; +---------------------------+ | FROM_UNIXTIME(MAX(clock)) | +---------------------------+ | 2023-05-29 09:46:25 | +---------------------------+ 1 row in set (19.03 sec)

mysql> ALTER TABLE history_text PARTITION BY RANGE (clock) -> (PARTITION p2023_05_29 VALUES LESS THAN (UNIX_TIMESTAMP("2023-05-29 00:00:00")) ENGINE = InnoDB, -> PARTITION p2023_05_30 VALUES LESS THAN (UNIX_TIMESTAMP("2023-05-30 00:00:00")) ENGINE = InnoDB, -> PARTITION p2023_05_31 VALUES LESS THAN (UNIX_TIMESTAMP("2023-05-31 00:00:00")) ENGINE = InnoDB); Query OK, 22440754 rows affected (3 min 14.24 sec) Records: 22440754 Duplicates: 0 Warnings: 0

Make that in five tables and resolved to me. Thx people!