Zabbix 3.4 Partitioning on MariaDB

[christianlll]

Hi people, did someone partitioned tables on zabbix 3.4? I will follow the following tutorial:

https://zabbix.org/wiki/Docs/howto/M...oning_(variant)

Any aditional information or tips would be very appreciated.

Txs

[ingus.vilnis]

This is the way to go - https://zabbix.org/wiki/Docs/howto/mysql_partitioning But you need to decide if you want to manage the partitions with MySQL stored procedures or a Perl script.

[christianlll]

Thanks for your reply. Should I do the partition for the proxy database too? Is the one bothering

du -sh zbx*

28G zbx
783G zbx_proxy

[ingus.vilnis]

I don't know what exactly the proxy is doing and how is it configured but 783G of database looks way to large, and possibly the proxy is not deleting the data at all.

For smaller setups and not busy proxies I would not recommend to partition proxies but in your case first of all you have to find out why is the DB that big. Most likely housekeeper process is disabled there for some reason and the disk usage is just constantly growing.

[christianlll]

I've just partitioned the proxy_history table after truncate. Everything started up but issues still persist: all agents unreachable for several days. Any idea? zabbix is 3.4.13

This is the procedure:

TRUNCATE TABLE proxy_history;

SET GLOBAL event_scheduler = ON;

ALTER TABLE `zbx_proxy`.`proxy_history` DROP PRIMARY KEY , ADD PRIMARY KEY ( `id` , `clock` , `itemid` );

ALTER TABLE `proxy_history` PARTITION BY RANGE (clock)
-> (PARTITION p2019_01_23 VALUES LESS THAN (UNIX_TIMESTAMP("2019-01-23 00:00:00")) ENGINE = InnoDB,
-> PARTITION p2019_01_24 VALUES LESS THAN (UNIX_TIMESTAMP("2019-01-24 00:00:00")) ENGINE = InnoDB,
-> PARTITION p2019_01_25 VALUES LESS THAN (UNIX_TIMESTAMP("2019-01-25 00:00:00")) ENGINE = InnoDB,
-> PARTITION p2019_01_26 VALUES LESS THAN (UNIX_TIMESTAMP("2019-01-26 00:00:00")) ENGINE = InnoDB,
-> PARTITION p2019_01_27 VALUES LESS THAN (UNIX_TIMESTAMP("2019-01-27 00:00:00")) ENGINE = InnoDB,
-> PARTITION p2019_01_28 VALUES LESS THAN (UNIX_TIMESTAMP("2019-01-28 00:00:00")) ENGINE = InnoDB,
-> PARTITION p2019_01_29 VALUES LESS THAN (UNIX_TIMESTAMP("2019-01-29 00:00:00")) ENGINE = InnoDB,
-> PARTITION p2019_01_30 VALUES LESS THAN (UNIX_TIMESTAMP("2019-01-30 00:00:00")) ENGINE = InnoDB,
-> PARTITION p2019_01_31 VALUES LESS THAN (UNIX_TIMESTAMP("2019-01-31 00:00:00")) ENGINE = InnoDB,
->
-> PARTITION p2019_02_01 VALUES LESS THAN (UNIX_TIMESTAMP("2019-02-01 00:00:00")) ENGINE = InnoDB);



-rw-rw----. 1 mysql mysql 130023424 Jan 24 12:58 proxy_history#P#p2019_01_25.ibd
-rw-rw----. 1 mysql mysql 114688 Jan 24 12:04 proxy_history#P#p2019_01_26.ibd
-rw-rw----. 1 mysql mysql 114688 Jan 24 12:04 proxy_history#P#p2019_01_27.ibd
-rw-rw----. 1 mysql mysql 114688 Jan 24 12:04 proxy_history#P#p2019_01_28.ibd
-rw-rw----. 1 mysql mysql 114688 Jan 24 12:04 proxy_history#P#p2019_01_29.ibd
-rw-rw----. 1 mysql mysql 114688 Jan 24 12:04 proxy_history#P#p2019_01_30.ibd
-rw-rw----. 1 mysql mysql 114688 Jan 24 12:04 proxy_history#P#p2019_01_31.ibd
-rw-rw----. 1 mysql mysql 114688 Jan 24 12:04 proxy_history#P#p2019_02_01.ibd

Thanks!

[Tatiana jandira]

boa tarde pessoal.

Estou homologando um particionamento na tabela history_uint em um banco MARIADB. Porem ao tentar executar a procedure que
ira criar o particionamento automático apresenta a msg: : (conn=64) Coluna desconhecida 'db_zabbix_teste' na 'lista de campos'
Desconbri que o problema está no CONCAT. Porem não estou conseguindo resolver.


Podem auxiliar ?


DELIMITADOR//

PROCEDIMENTO CREATE create_partition_by_month(
EM IN_SCHEMANAME VARCHAR(64),
EM IN_TABLENAME VARCHAR(64)
)
COMEÇAR
DECLARAR ROWS_CNT INT NÃO ASSINADO;
DECLARE O CARIMBO DE DATA/HORA BEGINTIME;
DECLARE ENDTIME INT SEM SINAL;
DECLARE PARTITIONNAME VARCHAR(16);
SET BEGINTIME = DATE(NOW() - INTERVALO DAY(NOW()) DIA + INTERVALO 1 DIA + INTERVALO 1 MÊS);
SET PARTITIONNAME = DATE_FORMAT(BEGINTIME, 'p%Y_%m');
DEFINIR ENDTIME = UNIX_TIMESTAMP(BEGINTIME + INTERVAL 1 MÊS);
SELECIONE COUNT(*) EM ROWS_CNT
DE information_schema.partitions
ONDE table_schema = IN_SCHEMANAME E table_name = IN_TABLENAME E partition_name = PARTITIONNAME;
SE ROWS_CNT = 0 ENTÃO
SET @SQL = CONCAT('ALTER TABLE '', IN_SCHEMANAME, ''.'', IN_TABLENAME, ''',
' ADICIONAR PARTIÇÃO (PARTIÇÃO ', NOME DA PARTIÇÃO, ' VALORES MENORES QUE (', ENDTIME, '));');
PREPARE O STMT A PARTIR DE @SQL;
EXECUTAR STMT;
DESALOQUE PREPARE STMT;
MAIS
SELECT CONCAT('Partition '', PARTITIONNAME, '' for table '', IN_SCHEMANAME, '.', IN_TABLENAME, '' already exists') AS result;
FIM SE;
FIM//

DELIMITADOR;