Realizando particionamento de tabelas para melhora de performance das consultas do Zabbix 2.0.5 e PostgreSQL 9.0.1

Recentemente atualizei meu NMS de Zabbix 1.8.2 (Debian Squeeze) para o 2.0.5, um dos principais motivos que me levou a esta versão foi poder realizar o particionamento de tabelas, já que o desempenho no meu cenário (128 hosts monitorados, retenção de um ano e NVPS=110) estava começando a ficar sofrível em alguns momentos.

O propósito do particionamento de tabelas é dividir a tabela history do zabbix em intervalos pré-definidos, como diário, semanal ou mensal. A escolha do intervalo a ser utilizado deve se basear na quantidade de dados inserido no banco em determinado período.

Após o particionamento será criado um novo schema onde ficaram as tabelas contendo os dados separados pelo intervalo selecionado, isso facilita bastante a busca das informações na base.

Todos os passos são realizados no PostgreSQL, tornando a solução totalmente transparente ao Zabbix.

Criação do novo schema.


CREATE SCHEMA partitions
AUTHORIZATION zabbix;

Criar a função que cria as partições:


-- Function: trg_partition()

-- DROP FUNCTION trg_partition();

CREATE OR REPLACE FUNCTION trg_partition()
RETURNS TRIGGER AS
$BODY$
DECLARE
prefix text := 'partitions.';
timeformat text;
selector text;
_interval INTERVAL;
tablename text;
startdate text;
enddate text;
create_table_part text;
create_index_part text;
BEGIN

selector = TG_ARGV[0];

IF selector = 'day' THEN
timeformat := 'YYYY_MM_DD';
ELSIF selector = 'month' THEN
timeformat := 'YYYY_MM';
END IF;

_interval := '1 ' || selector;
tablename :=  TG_TABLE_NAME || '_p' || TO_CHAR(TO_TIMESTAMP(NEW.clock), timeformat);

EXECUTE 'INSERT INTO ' || prefix || quote_ident(tablename) || ' SELECT ($1).*' USING NEW;
RETURN NULL;

EXCEPTION
WHEN undefined_table THEN

startdate := EXTRACT(epoch FROM date_trunc(selector, TO_TIMESTAMP(NEW.clock)));
enddate := EXTRACT(epoch FROM date_trunc(selector, TO_TIMESTAMP(NEW.clock) + _interval ));

create_table_part:= 'CREATE TABLE IF NOT EXISTS '|| prefix || quote_ident(tablename) || ' (CHECK ((clock >= ' || quote_literal(startdate) || ' AND clock < ' || quote_literal(enddate) || '))) INHERITS ('|| TG_TABLE_NAME || ')';
create_index_part:= 'CREATE INDEX '|| quote_ident(tablename) || '_1 on ' || prefix || quote_ident(tablename) || '(itemid,clock)';

EXECUTE create_table_part;
EXECUTE create_index_part;

--insert it again
EXECUTE 'INSERT INTO ' || prefix || quote_ident(tablename) || ' SELECT ($1).*' USING NEW;
RETURN NULL;

END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION trg_partition()
OWNER TO postgres;

Criar as triggers para cada tabela que se queira particionar e o intervalo de particionamento – diário ou mensal.


CREATE TRIGGER partition_trg BEFORE INSERT ON history           FOR EACH ROW EXECUTE PROCEDURE trg_partition('day');
CREATE TRIGGER partition_trg BEFORE INSERT ON history_sync      FOR EACH ROW EXECUTE PROCEDURE trg_partition('day');
CREATE TRIGGER partition_trg BEFORE INSERT ON history_uint      FOR EACH ROW EXECUTE PROCEDURE trg_partition('day');
CREATE TRIGGER partition_trg BEFORE INSERT ON history_str_sync  FOR EACH ROW EXECUTE PROCEDURE trg_partition('day');
CREATE TRIGGER partition_trg BEFORE INSERT ON history_log       FOR EACH ROW EXECUTE PROCEDURE trg_partition('day');
CREATE TRIGGER partition_trg BEFORE INSERT ON trends            FOR EACH ROW EXECUTE PROCEDURE trg_partition('month');
CREATE TRIGGER partition_trg BEFORE INSERT ON trends_uint       FOR EACH ROW EXECUTE PROCEDURE trg_partition('month');

Manutenção da retenção e remoção de partições antigas

A seguinte função é utilizada para remover partições antigas e poderá ser agendada para rodar via cron.

-- Function: delete_partitions(interval, text)-- DROP FUNCTION delete_partitions(interval, text);

CREATE OR REPLACE FUNCTION delete_partitions(intervaltodelete INTERVAL, tabletype text)
RETURNS text AS
$BODY$
DECLARE
result RECORD ;
prefix text := 'partitions.';
table_timestamp TIMESTAMP;
delete_before_date DATE;
tablename text;

BEGIN
FOR result IN SELECT * FROM pg_tables WHERE schemaname = 'partitions' LOOP

table_timestamp := TO_TIMESTAMP(substring(result.tablename FROM '[0-9_]*$'), 'YYYY_MM_DD');
delete_before_date := date_trunc('day', NOW() - intervalToDelete);
tablename := result.tablename;

-- Was it called properly?
IF tabletype != 'month' AND tabletype != 'day' THEN
RAISE EXCEPTION 'Please specify "month" or "day" instead of %', tabletype;
END IF;

--Check whether the table name has a day (YYYY_MM_DD) or month (YYYY_MM) format
IF LENGTH(substring(result.tablename FROM '[0-9_]*$')) = 10 AND tabletype = 'month' THEN
--This is a daily partition YYYY_MM_DD
-- RAISE NOTICE 'Skipping table % when trying to delete "%" partitions (%)', result.tablename, tabletype, length(substring(result.tablename from '[0-9_]*$'));
CONTINUE;
ELSIF LENGTH(substring(result.tablename FROM '[0-9_]*$')) = 7 AND tabletype = 'day' THEN
--this is a monthly partition
--RAISE NOTICE 'Skipping table % when trying to delete "%" partitions (%)', result.tablename, tabletype, length(substring(result.tablename from '[0-9_]*$'));
CONTINUE;
ELSE
--This is the correct table type. Go ahead and check if it needs to be deleted
--RAISE NOTICE 'Checking table %', result.tablename;
END IF;

IF table_timestamp <= delete_before_date THEN
RAISE NOTICE 'Deleting table %', quote_ident(tablename);
EXECUTE 'DROP TABLE ' || prefix || quote_ident(tablename) || ';';
END IF;
END LOOP;
RETURN 'OK';

END;

$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION delete_partitions(INTERVAL, text)
OWNER TO postgres;

Exemplos de chamada da função:


SELECT delete_partitions('7 days', 'day')
SELECT delete_partitions('11 months', 'month')

2 Replies to “Realizando particionamento de tabelas para melhora de performance das consultas do Zabbix 2.0.5 e PostgreSQL 9.0.1”

  1. ola, eu uso o postgre 9.3 com zabbix 2.2

    e possivel criar um banco do zero, executar o particionamento e importar o meu banco antigo pra esse novo?

    desde ja obrigado.

    1. Oi, Anderson!
      Acredito que para fazer o que você mencionou seja necessário migrar os dados através de um script elaborado por você, visto que os dados da base podem não conhecer as dimensões lógicas do particionamento. Se forem schemas de mesmo banco será uma coleção de inserts no seu schema de partições. Caso forem bancos ou gerenciadores diferentes, recomendaria o Kettle

Leave a Reply

Your email address will not be published. Required fields are marked *