See www.zabbix.com for the official Zabbix site.

Docs/howto/postgresql partitioning by range

From Zabbix.org
Jump to: navigation, search

PostgreSQL partitioning tables by range

The basics

Main culprit of slow and long Housekeeping process is usually the size of several tables, history, history_uint, trends and trends_uint. Overall performance is significantly improved by using PostgreSQL table partitioning (inheritance) model and splitting these tables on monthly basis.

This setup is tested with PostgreSQL 9.1.x, 9.0.x. and Zabbix 1.8.5, 1.8.13
This should work with 8.4 too but you need to modify the functions as 8.x doesn't support OR REPLACE:
**Avoid the use of trigger functions with #x like sum(#3) use sum(60) instead, this causes big problems and delays items**

In this example the tables history, history_uint, trends and trends_uint are partitioned on a monthly basis.

 This should be easy to extend or modify.

To partition the tables with this example you need a trigger (the trigger calls the function on every INSERT in the table), a function (the function does all the partition stuff) and an inherited table (the new place for the data).

The "master" table should be empty and have no indexes or check constraints.

Ensure that the constraint_exclusion configuration parameter is not disabled in postgresql.conf. If it is, queries will not be optimized as desired. The default (and recommended) setting of constraint_exclusion is actually neither on nor off, but an intermediate setting called partition, which causes the technique to be applied only to queries that are likely to be working on partitioned tables.

You can install functions with psql -f <name>.sql <dbname>

For this example you need 6 functions and 4 triggers.

create_zbx_partitions_archive function

This function is creating table partitions for records that are already in history, history_uint, trends and trends_uint tables, populates it, and truncates the "master" tables

CREATE OR REPLACE FUNCTION create_zbx_partitions_archive() RETURNS VOID AS $$
DECLARE
        archiveTables text[];
        masterTable text;
        archiveTable text;
        i int;
        query text;

        rec record;
        start_time TEXT;
        end_time TEXT;

        create_line TEXT;
        alter_line TEXT;
        index_line TEXT;
        insert_line TEXT;
        truncate_line TEXT;
BEGIN
        -- Tables to create partitions for
        archiveTables := array['history','history_uint','trends','trends_uint'];

        FOR i in array_lower(archiveTables,1)..array_upper(archiveTables,1) LOOP
                masterTable:=archiveTables[i];
                RAISE NOTICE 'tbl: %',masterTable;

                query:='select to_char(extract(epoch FROM date_trunc(''month'',clock::abstime))::int::abstime,''yyyymm'') as month from only ' || masterTable || ' group by month order by month';

                FOR rec IN EXECUTE query LOOP
                        -- Determining dates for partitions
                        SELECT extract(epoch FROM date_trunc('month',to_date(rec.month,'yyyymm'))) INTO start_time;
                        SELECT extract(epoch FROM date_trunc('month',to_date(rec.month,'yyyymm')+interval '1 month')) INTO end_time;

                        -- Child table
                        archiveTable:=masterTable || '_' || rec.month;

                        -- Checking if table is already created
                        BEGIN
                          EXECUTE 'SELECT * FROM ONLY ' || archiveTable || ' LIMIT 1';
                        EXCEPTION WHEN UNDEFINED_TABLE THEN
                          create_line:='CREATE TABLE ' || archiveTable || '(check ( clock>=' || start_time || ' and clock<' || end_time || '),like ' || masterTable || ' including defaults including storage) with oids';
                          alter_line:='ALTER TABLE ' || archiveTable || ' inherit ' || masterTable || '';
                          index_line:='CREATE INDEX ' || archiveTable || '_1 on ' || archiveTable || '(itemid,clock)';

                          RAISE NOTICE '--- MONTH %', rec.month;

                          RAISE NOTICE '--- % Create: %', masterTable, create_line;
                          execute create_line;
                          RAISE NOTICE '--- % Alter: %', masterTable, alter_line;
                          execute alter_line;
                          RAISE NOTICE '--- % Index: %', masterTable, index_line;
                          execute index_line;
                        END;
                        insert_line:='INSERT INTO ' || archiveTable || ' SELECT * FROM ONLY ' || masterTable || ' WHERE clock>=' || start_time || ' AND clock<' || end_time;
                        RAISE NOTICE '--- % Insert: %', masterTable, insert_line;
                        execute insert_line;
                END LOOP;
                -- Truncating master table
                truncate_line:='TRUNCATE TABLE ONLY ' || masterTable;
                RAISE NOTICE '--- % Truncate: %', masterTable, truncate_line;
                execute truncate_line;
        END LOOP;
END
$$ LANGUAGE plpgsql;

Execute

SELECT create_zbx_partitions_archive();

create_zbx_partitions function

to create partition for current and next month. this one should be executed on the end of every month.

CREATE OR REPLACE FUNCTION create_zbx_partitions(INTEGER default 0) RETURNS VOID AS $$

DECLARE

    history_part TEXT;
    history_uint_part TEXT;
    trends_part TEXT;
    trends_uint_part TEXT;
   
    index_history_part TEXT;
    index_history_uint_part TEXT;
    index_trends_part TEXT;
    index_trends_uint_part TEXT;
   
    alter_history_part TEXT;
    alter_history_uint_part TEXT;
    alter_trends_part TEXT;
    alter_trends_uint_part TEXT;
   
    next_partition TEXT;
    current_check TEXT;
    next_check TEXT;

BEGIN
     
        if $1 > 0 then

 SELECT extract(epoch FROM date_trunc('month',current_timestamp)) into current_check;
 SELECT extract(epoch FROM date_trunc('month',current_timestamp + interval '1 month')) into next_check;
 SELECT to_char(current_timestamp,'yyyymm') into next_partition;

        else

 SELECT extract(epoch FROM date_trunc('month',current_timestamp + interval '1 month')) into current_check;
 SELECT extract(epoch FROM date_trunc('month',current_timestamp + interval '2 month')) into next_check;
 SELECT to_char(current_timestamp + interval '1 month','yyyymm') into next_partition;

        end if;

--History 
        history_part:='create table history_'|| next_partition ||'(check ( clock >= ' || current_check || ' and clock < ' || next_check || '),like history including defaults including storage) with oids';
        alter_history_part:='alter table  history_' || next_partition || ' inherit history';
        index_history_part:='create index history_' || next_partition || '_1 on history_' || next_partition || '(itemid,clock)';

--History Uint 
        history_uint_part:='create table history_uint_'|| next_partition ||'(check ( clock >= ' || current_check || ' and clock < ' || next_check || '), like history_uint including defaults including storage) with oids';
        alter_history_uint_part:='alter table  history_uint_' || next_partition || ' inherit history_uint';
        index_history_uint_part:='create index history_uint_' || next_partition || '_1 on history_uint_' || next_partition || '(itemid,clock)';

--Trends 
        trends_part:='create table trends_'|| next_partition ||'(check ( clock >= ' || current_check || ' and clock < ' || next_check || '), like trends including defaults including storage) with oids';
        alter_trends_part:='alter table  trends_' || next_partition || ' inherit trends';
        index_trends_part:='create index trends_' || next_partition || '_1 on trends_' || next_partition || '(itemid,clock)';

--Trends Uint
        trends_uint_part:='create table trends_uint_'|| next_partition ||'(check ( clock >= ' || current_check || ' and clock <' || next_check || '), like trends_uint including defaults including storage) with oids';
        alter_trends_uint_part:='alter table  trends_uint_' || next_partition || ' inherit trends_uint';
        index_trends_uint_part:='create index trends_uint_' || next_partition || '_1 on trends_uint_' || next_partition || '(itemid,clock)';
 
        execute history_part;
        execute alter_history_part;
        execute index_history_part;
        execute history_uint_part;
        execute alter_history_uint_part;
        execute index_history_uint_part;
        execute trends_part;
        execute alter_trends_part;
        execute index_trends_part;
        execute trends_uint_part;
        execute alter_trends_uint_part;
        execute index_trends_uint_part;
 
 
END
$$ LANGUAGE plpgsql;

To generate the needed partitions for the **NEXT** month call the function

select create_zbx_partitions();

To generate the needed partitions for the **CURRENT** month call the function

select create_zbx_partitions(1);


Additional 4 functions with triggers for Inserts

CREATE OR REPLACE FUNCTION trends_insert_trigger()
        RETURNS TRIGGER AS $$

        DECLARE
                insert_sql TEXT;
        BEGIN
         
                insert_sql:= 'INSERT INTO trends_' || to_char(to_timestamp(NEW.clock),'yyyymm') || '(itemid,clock,num,value_min,value_avg,value_max) VALUES ' || '(' || NEW.itemid || ',' || NEW.clock || ',' || NEW.num || ',' || NEW.value_min || ',' || NEW.value_avg || ',' || NEW.value_max || ')';
                EXECUTE insert_sql;
        RETURN NULL;
        END
$$
LANGUAGE plpgsql;

CREATE TRIGGER insert_trends_trigger
        BEFORE INSERT ON trends
        FOR EACH ROW EXECUTE PROCEDURE trends_insert_trigger();
CREATE OR REPLACE FUNCTION trends_uint_insert_trigger()
        RETURNS TRIGGER AS $$

        DECLARE
                insert_sql TEXT;
        BEGIN
         
                insert_sql:= 'INSERT INTO trends_uint_' || to_char(to_timestamp(NEW.clock),'yyyymm') || '(itemid,clock,num,value_min,value_avg,value_max) VALUES ' || '(' || NEW.itemid || ',' || NEW.clock || ',' || NEW.num || ',' || NEW.value_min || ',' || NEW.value_avg || ',' || NEW.value_max || ')';
                EXECUTE insert_sql;
        RETURN NULL;
        END
$$
LANGUAGE plpgsql;

CREATE TRIGGER insert_trends_uint_trigger
        BEFORE INSERT ON trends_uint
        FOR EACH ROW EXECUTE PROCEDURE trends_uint_insert_trigger();
CREATE OR REPLACE FUNCTION history_insert_trigger()
        RETURNS TRIGGER AS $$

        DECLARE
                insert_sql TEXT;
        BEGIN
         
                insert_sql:= 'INSERT INTO history_' || to_char(to_timestamp(NEW.clock),'yyyymm') || '(itemid,clock,value) VALUES ' || '(' || NEW.itemid || ',' || NEW.clock || ',' || NEW.value || ')';
                EXECUTE insert_sql;
        RETURN NULL;
        END
$$
LANGUAGE plpgsql;

CREATE TRIGGER insert_history_trigger
        BEFORE INSERT ON history
        FOR EACH ROW EXECUTE PROCEDURE history_insert_trigger();
CREATE OR REPLACE FUNCTION history_uint_insert_trigger()
        RETURNS TRIGGER AS $$

        DECLARE
                insert_sql TEXT;

        BEGIN
         
                insert_sql:= 'INSERT INTO history_uint_' || to_char(to_timestamp(NEW.clock),'yyyymm') || '(itemid,clock,value) VALUES ' || '(' || NEW.itemid || ',' || NEW.clock || ',' || NEW.value || ')';
                EXECUTE insert_sql;
        RETURN NULL;
        END
$$
LANGUAGE plpgsql;

CREATE TRIGGER insert_history_uint_trigger
        BEFORE INSERT ON history_uint
        FOR EACH ROW EXECUTE PROCEDURE history_uint_insert_trigger();

Created by Alen Komic
based on Partition tables by range by f.koch