Some time ago I was working and involved in some proyects conformed by several servers each one, that servers had to be monitored and after search and review some OpenSource proyects like Zabbix, Nagios, Cacti and so on I decided to use Zabbix to implement monitoring and alerting tasks, I liked Zabbix so much because it store collected data into a PostgreSQL database and that offered another advantage for me because I was interested in store that data for a long time period and use it to leverage capacity planing effors. Then I had to modify Zabbix database schema to adapt it for partition tables use and aditionally implement tablespaces.
Implement partition tables and tablespaces bring some advantages to leverage database performance because it makes possible store data and indexes in differente file systems, indexes can be stored in a very fast but expensive file system while data can be stored in traditional file system and partition tables allow PostgreSQL engine to load few indexes to satisfy a query instead of load a unique large index improving query execution time.
This repository contains scripts to modify zabbix 3.2 PostgreSQL scheme file, create.sql.gz, in order to implement PostgreSQL tablespaces and inherited tables aka partition tables, the main target is to store items colected by Zabbix agents into several history tables, one table peer month, but keeping original zabbix querys's unmodified, This script will modify every create table and create index statement into create.sql.gz file adding tablespace clauses to allow store data into diferent locations, data and indexes objects, remember that you should locate index data into very fast disk if it is possible to leverage database performance.
This script will create two aditional files, one file called create_tablespaces.sql containing all statements to create tablespaces into zabbix database and other file called create_functions.sql that contains statementes to define functions and triggers needed to re-direct inserts data clauses into history inherited tables.
Zabbix server installation with PostgreSQL
-
Install all Perl dependencies.
-
git clone https://github.com/jorgesanchez-e/zabbix-parttition-tables
-
Move file zabbix32-transform.pl into same directory than create.sql.gz file is.
-
Execute zabbix32-transform.pl file with follow flags:
--index=/path/to/index/directory/tablespace --data=/path/to/data/directory/tablespace
Supousing you have /opt/zabbix/data for data objects and /opt/zabbix/index for index objects you have execute following statement:
./zabbix32-transform.pl --data=/opt/zabbix/data --index=/opt/zabbix/data
At the end it will create a new version file for create.sql.gz and original file will be renamed to create.sql.gz.old aditionally two new files will be created, create_tablespaces.sql that will contains statements to create tablespaces and create_functions.sql file containing all functions and triggers needed to full fill inherited tables.
-
Create user database zabbix as just as zabbix installation manual says:
sudo -u postgres createuser --pwprompt zabbix
-
Create zabbix database and assign it to zabbix user as just as zabbix manual says:
sudo -u postgres createdb -O zabbix zabbix
-
Execute _create_tablespace.sql file to create all needed tablespaces, you have to execute it with postgresql user as just as shown below:
cat ./create_tablespaces.sql | sudo -u postgres psql -
Execute create.sql.gz file as just as zabbix manual says:
zcat ./create.sql.gz | sudo -u zabbix psql zabbix -
Execute create_functions.sql file as just as shown below:
cat ./create_functions.sql | sudo -u zabbix psql
At this point you can follow instructions showed in Zabbix server installation with PostgreSQL to complete installation and at the end don't forget disable Zabbix house keeper process.
Once you have up and running your Zabbix server with PostgreSQL partitions you need a way to create every month tables and it's index automatically to address this issue you can configure zabbix32-history-tables.pl script into a cronjob task putting line showed above into PostgreSQL user account.
0 0 25 * * /path/to/zabbix32-history-tables.pl --host <IP> --port <port>This cronjob task will be execute every 25th day of every month and the script zabbix32-history-tables.pl will create all tables and index needed for zabbix to store monitoring data for next inmediatly month.
Host and port arguments are optional, if PostgreSQL engine listens on default port over localhost you can ommit those arguments completly.
If you don't want to do all steeps I did a quite simple ansible playbook for this project and put it in this link.