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

Docs/howto/zabbix2 postgresql partitioning

From Zabbix.org
Jump to: navigation, search

Postgresql Database Partitioning with Zabbix 2

Note that only id-based partitioning (on whatever column) is supported! Time based partitioning requires timestamp fields.


With this HowTo you can partition your Postgresql 9.x DB with a few steps.

ATTENTION: This only works with Zabbix 2.x and the database must be empty (the tables that store the history information, to be exact)

First you need a running PostgreSQL DB with Zabbix schema. (this is not part of this how to, take a look in the official install instructions)

These sql script prepare the database and create the needed functions, you can run this with psql -f on your database (as postgres user)

ATTENTION: You need to change the "SET TIME ZONE 'Europe/Berlin';" to your timezone

 psql -d zabbix -f zabbix_partition_init.sql 
  CREATE SCHEMA partitions AUTHORIZATION zabbix;
  CREATE OR REPLACE FUNCTION create_zbx_partitions(part TEXT ,part_mode TEXT, init INT DEFAULT 0 ) RETURNS VOID AS $$
  DECLARE
    current_check TEXT;
    next_check TEXT;
    next_partition TEXT;
    created_partition TEXT;
    name RECORD;
    cons RECORD;
  BEGIN
  IF init > 0 THEN
    EXECUTE 'create table if not exists tpl_' || part || '(like ' || part || ' including defaults including storage including constraints including indexes)';
    FOR cons IN SELECT constraint_name FROM information_schema.table_constraints WHERE table_name = part and constraint_type = 'PRIMARY KEY' LOOP
        EXECUTE 'ALTER TABLE ' || part || ' DROP CONSTRAINT IF EXISTS ' || quote_ident(cons.constraint_name) || ' CASCADE';
    END LOOP; 
    FOR name IN SELECT * from pg_indexes where tablename = part  LOOP
        EXECUTE 'DROP INDEX ' || quote_ident(name.indexname);
    END LOOP;
  END IF;
  IF part_mode = 'daily' THEN
     IF init > 0 THEN
        SELECT extract(epoch FROM date_trunc('day',current_timestamp)) into current_check;
        SELECT extract(epoch FROM date_trunc('day',current_timestamp + interval '1 day')) into next_check;
        SELECT to_char(current_timestamp ,'_yyyymmdd') into next_partition;
     ELSE
        SELECT extract(epoch FROM date_trunc('day',current_timestamp + interval '1 day')) into current_check;
        SELECT extract(epoch FROM date_trunc('day',current_timestamp + interval '2 day')) into next_check;
        SELECT to_char(current_timestamp + interval '1 day','_yyyymmdd') into next_partition;
     END IF;
  END IF;
  IF part_mode = 'monthly' THEN
    IF init > 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;
  END IF;
  created_partition:='partitions.' || part || next_partition;
  EXECUTE 'create table if not exists ' || created_partition ||' (check ( clock >= ' || current_check || ' and clock < ' || next_check || '),like tpl_' || part
 || ' including defaults including storage including constraints including indexes) inherits(' || part || ')';
  EXECUTE 'create table if not exists partitions.emergency_' || part || '( like ' || part || ' including defaults including storage including  constraints including indexes) inherits(' || part || ')';
  END;
  $$ LANGUAGE plpgsql 
  SET TIME ZONE 'Europe/Berlin';
  CREATE OR REPLACE FUNCTION dynamic_insert_trigger()
  RETURNS TRIGGER AS $$
  DECLARE
    timeformat TEXT;
    insert_sql TEXT;
 BEGIN
      IF TG_ARGV[0] = 'daily' THEN
        timeformat:='_yyyymmdd';
      ELSIF TG_ARGV[0] = 'monthly' THEN
        timeformat:='_yyyymm';
      END IF;
        EXECUTE 'INSERT INTO partitions.' || TG_TABLE_NAME || to_char(to_timestamp(NEW.clock),timeformat) || ' SELECT ($1).*' USING NEW;    
      RETURN NULL;
 EXCEPTION
  WHEN undefined_table THEN
    EXECUTE 'INSERT INTO partitions.emergency_' || TG_TABLE_NAME || ' SELECT ($1).*' USING NEW;
  RETURN NULL;
  END;
  $$
  LANGUAGE plpgsql
  SET TIME ZONE 'Europe/Berlin';
  CREATE TRIGGER history_trigger
        BEFORE INSERT ON history
        FOR EACH ROW EXECUTE PROCEDURE dynamic_insert_trigger('daily');
  CREATE TRIGGER history_sync_trigger
        BEFORE INSERT ON history_sync
        FOR EACH ROW EXECUTE PROCEDURE dynamic_insert_trigger('daily');
  CREATE TRIGGER history_uint_trigger
        BEFORE INSERT ON history_uint
        FOR EACH ROW EXECUTE PROCEDURE dynamic_insert_trigger('daily');
  CREATE TRIGGER history_uint_sync_trigger
        BEFORE INSERT ON history_uint_sync
        FOR EACH ROW EXECUTE PROCEDURE dynamic_insert_trigger('daily');        
  CREATE TRIGGER history_str_trigger
        BEFORE INSERT ON history_str
        FOR EACH ROW EXECUTE PROCEDURE dynamic_insert_trigger('daily');      
  CREATE TRIGGER history_str_sync_trigger
        BEFORE INSERT ON history_str_sync
        FOR EACH ROW EXECUTE PROCEDURE dynamic_insert_trigger('daily');
  CREATE TRIGGER history_log_trigger
        BEFORE INSERT ON history_log
        FOR EACH ROW EXECUTE PROCEDURE dynamic_insert_trigger('daily');
  CREATE TRIGGER history_text_trigger
        BEFORE INSERT ON history_text
        FOR EACH ROW EXECUTE PROCEDURE dynamic_insert_trigger('daily');       
  CREATE TRIGGER trends_trigger
        BEFORE INSERT ON trends
        FOR EACH ROW EXECUTE PROCEDURE dynamic_insert_trigger('monthly');    
  CREATE TRIGGER trends_uint_trigger
        BEFORE INSERT ON trends_uint
        FOR EACH ROW EXECUTE PROCEDURE dynamic_insert_trigger('monthly');
  CREATE TRIGGER acknowledges_trigger
        BEFORE INSERT ON acknowledges
        FOR EACH ROW EXECUTE PROCEDURE dynamic_insert_trigger('monthly');
  CREATE TRIGGER alerts_trigger
        BEFORE INSERT ON alerts
        FOR EACH ROW EXECUTE PROCEDURE dynamic_insert_trigger('monthly');
  CREATE TRIGGER auditlog_trigger
        BEFORE INSERT ON auditlog
        FOR EACH ROW EXECUTE PROCEDURE dynamic_insert_trigger('monthly');
  CREATE TRIGGER events_trigger
        BEFORE INSERT ON events
        FOR EACH ROW EXECUTE PROCEDURE dynamic_insert_trigger('monthly');
  CREATE TRIGGER service_alarms_trigger
        BEFORE INSERT ON service_alarms
        FOR EACH ROW EXECUTE PROCEDURE dynamic_insert_trigger('monthly');


If you run this script for the first time, it creates a schema named partitions, here lives all the partitions, so the normal schema is not spammed.

It also creates template tables named tpl_* for all tables who are partitioned, and removes all indexes and constraints from the original tables (the templates are needed for easily generating the partitions with all needed indexes)

Then the create_zbx_partitions , the trigger function and the triggers for each table are created . Also one emergency partition for each table is created, if the needed partition is not there (for what ever reason) the emergency partition is used, so no application problem is created, and no data are lost.

Now you can use the function to create the needed partitions. I the normal mode the partitions for the next day or month are created. You need to pass the table name and the partition interval:

for the daily run:

 select create_zbx_partitions('history','daily')

for monthly run: select create_zbx_partitions('history','monthly')

For the initial install you also need to create the partitions for the current day / month, so you need to call the function with the init parameter set to 1

 select create_zbx_partitions('history','daily','1',)

i have a python script for this, i run this initial with the --init parameter and then with cron.daily (you need to adjust the connect settings) (you need the psycopg2 driver installed )

#!/usr/bin/python

import psycopg2
from optparse import OptionParser

tables = {
  'history':'daily',
  'history_sync':'daily',
  'history_uint':'daily',
  'history_uint_sync':'daily',
  'history_str':'daily',
  'history_str_sync':'daily',
  'history_log':'daily',
  'history_text':'daily',
  'trends':'monthly',
  'trends_uint':'monthly',
  'acknowledges':'monthly',
  'alerts':'monthly',
  'auditlog':'monthly',
  'events':'monthly',
  'service_alarms':'monthly',
}

#change these settings
db_user = 'zabbix'
db_pw = 'zabbix'
db = 'zabbix'
db_host = 'localhost'
#####

parser = OptionParser()
parser.add_option("-i", "--init", dest="init",help="partitioning init",action="store_true", default=False)

(options, args) = parser.parse_args()

if options.init:
	init = 1
else:
        init = 0

db_connection = psycopg2.connect(database=db, user=db_user, password=db_pw,host=db_host)
db_cursor = db_connection.cursor()

for table_key, table_value in tables.iteritems():

	db_cursor.execute('''select create_zbx_partitions(%s,%s,%s)''',[table_key,table_value,init])

db_connection.commit()
db_cursor.close()
db_connection.close()


I hope this HowTo is helpful, if you have any questions or suggestions you can contact me via the discussions page or in the zabbix IRC nick f0.