Postbix

From SmartMarmot
Jump to: navigation, search

POSTBIX

Postbix logo.png

Authors

Andrea Dalle Vacche
Jason Chatfield

Download & Installation Instructions

About PostBIX

Product Description

PostBIX is a plugin designed to work with Zabbix Enterprise Monitor to provide multi-tiered monitoring, performance and availability reporting and measurement for PostgreSQL Databases, along with server performance metrics.
It provides an effective mechanism to acquire data from numerous PostgreSQL instances, and in turn provides this information for monitoring and performance metrics to your Zabbix server. You can then utilize the reporting capabilities of Zabbix for all data collected, and provide analysis such as graphs and service level agreement metrics for stakeholders.
The current distribution contains a set of pre-defined templates which incorporate alerting and graphing capabilities from initial deployment. However these can be fine tuned to suit your needs and data/monitoring requirements.

File:PostBIX architecture.png
Fig. 1 - PostBIX Architecture

What can it monitor?

Below are some examples of what PostBIX is capable of monitoring:

  • comming soon
PostBIX Alive
Fig. 2 - PostBIX Alive
PostBIX BUFFERS
Fig. 3 - PostBIX Buffers
PostBIX CHECKPOINT
Fig. 4 - PostBIX Checkpoint
PostBIX XACT
Fig. 5 - PostBIX XACT
PostBIX Connections
Fig. 6 - PostBIX SGA
PostBIX Tuples
Fig. 7 - PostBIX Tuple
PostBIX Locks
Fig. 8 - PostBIX Locks

PostBIX Author

Andrea Dalle Vacche: http://www.smartmarmot.com

History

You can read about the history and evolution of the PostBIX plug-in in these threads below:

Original here: http://www.zabbix.com/forum/showthread.php?t=18690

Distribution

You can find the latest versions of PostBIX at the locations listed below:

http://www.smartmarmot.com/product/postbix/download/
or
https://sourceforge.net/projects/postbix/

Requirements

To use PostBIX, you will require the following on your Zabbix Server:

  • Zabbix 1.8.x Server
  • Java Runtime Environment 6

Current known successfully tested PostgreSQL host platforms:

  • RHEL5.X (CentOS 5.4 & 5.5)
  • Windows 2003 (with Java SE 1.6)
  • HP-UX 11.31
  • AIX 5.3

Currently known tested PostgreSQL Versions:

  • 8.3
  • 8.4
 IMPORTANT
 You do NOT need to install an PostgreSQL client of any kind for PostBIX to work.
 PostBIX uses Java objects and connection strings to connect to the PostgreSQL Database, and as such doesn’t require an PostgreSQL client to be installed on your Zabbix Server.
 Installation

Assumptions

The installation instructions have been created based on instructions for most *nix deployments (i.e. RHEL/CentOS), and is assumed that any alterations required for your specific environment are to be taken into account accordingly.

This guide is based upon there being two (2) Hosts, 1x Zabbix Server and 1x PostgreSQL Server. If you are planning on monitoring an PostgreSQL instance that is running on your Zabbix Server, the steps are the same, with minor adjustments required for your connection information.

The steps also assume you are configuring PostBIX to monitor a new installation or setup of PostgreSQL. The installation steps will have you GRANT access for the Zabbix user to all tables, and this will include any USER tables present at the time of execution. If you do not want Zabbix to have access to specific tables or resources within your database, you will need to set a DENY to the Zabbix users access as required. Consult your DBA for details, as this is beyond the scope of these instructions.

Steps for Installation

  • Download PostBIX to your Zabbix Server
  • On your Zabbix server, unzip PostBIX to: /opt/postbix
  • Copy file /opt/postbix/init.d/postbixto /etc/init.d/postbix
  • Grant execute permissions to the following files:
 /etc/init.d/postbix
 /opt/postbix/run.sh
  • Create a User (ZABBIX) for PostBIX to access your PostgreSQL Database. You can use the following script:


 CREATE USER zabbix WITH PASSWORD 'passw0rd';
 GRANT SELECT ON pg_stat_activity to zabbix;
 GRANT SELECT ON pg_stat_activity to zabbix;
 GRANT SELECT ON pg_database to zabbix;
 GRANT SELECT ON pg_authid to zabbix;
 GRANT SELECT ON pg_stat_bgwriter to zabbix;
 GRANT SELECT ON pg_locks to zabbix;
 GRANT SELECT ON pg_stat_database to zabbix;


  • For this example on RedHat, run:
 chkconfig -add postbix
  • Verify with:
 chkconfig -list
  • Create a Host entry in Zabbix for the DB Instance/s you are planning to monitor, and import the templates found at: /opt/postbix/template
 Please note, instructions for this step can be found in the Zabbix Manual at
 http://www.zabbix.com/documentation.php
 With this step, ensure the name of your host in Zabbix is the same name of the SID or PostgreSQL instance name.

Configuration

Now we need to configure your PostBIX setup. The tags below are listed as they will appear within the respective configuration files. The first you will need to modify is your config.props file to define your connection properties for Zabbix and PostgreSQL.

config.props

The config.props file can be found at /opt/orabbix/conf/config.props

 ZabbixServerList
 #comma separed list of Zabbix servers

The settings under this tag allow you to configure your Zabbix Server information. PostBIX can also be configured to send your PostgreSQL data to multiple Zabbix servers.

 This can be beneficial for distributed monitoring scenarios, server migrations or replicating PostBIX data  to a Disaster Recovery site or server.

Replace with your Zabbix Server info where appropriate;

 ZabbixServerList=ZabbixServer1,ZabbixServer2
 ZabbixServer1.Address=192.168.0.1
 ZabbixServer1.Port=10051
 
 ZabbixServer2.Address=192.168.0.2
 ZabbixServer2.Port=10051

PostBIXDaemon

Entries under this tag allow you to set your PostBIX Daemon parameters.

 #MaxThreadNumber should be >= than the number of your databases

Set the number of threads the PostBIX Daemon should have inside his internal pool of DB Jobs. This number should be at least equal to (or more than) then number of databases monitored by PostBIX, now this parameter if not set is automatically calculated.

e.g. For 50 Databases, we are using 100 threads

PostBIXDaemon.MaxThreadNumber=100

 #pidFile

Next you can set the location of the Daemons PID file. Default location is: /opt/orabbix/logs

PostBIXDaemon.PidFile=./logs/orabbix.pid

NOTE: Relative path is permitted here

 TIP: With the features of the PostBIX Daemon, it is possible to clone PostBIX and use different configurations. 
      As such, it is therefore possible to have one Daemon that checks your TEST databases, another iteration that checks your
      DEVELOPMENT databases and a third that checks your PRODUCTION databases, each with different timings and check loops!

DatabaseList


This is where you define your database instances. You can specify more than one instance here, separated using a comma.

 DatabaseList=EXAMPLE1,EXAMPLE2

NOTE: The names of the instances must match those you have specified as your HOST name in Zabbix

 #Configuration of Connection pool

From here, you will configure settings that are specific to the connection pool. As the comments in config.props suggest, if you do not specify these values, PostBIX will use default values which have been hard-coded.

 #Maximum number of active connection inside pool

Set the maximum number of connections that can be allocated to this pool at any time,or alternatively set a negative value for no limit.

 DatabaseList.MaxActive=10
 #The maximum number of milliseconds

Here you define how long that the pool will wait (when there are no available connections) for a connection to be returned before throwing an exception, or set the value <= 0 to wait indefinitely.

 DatabaseList.MaxWait=100

Also under the same section, you can define the maximum number of connections that can remain idle within the connection pool, without being released. Alternatively, you can set a negative value for no limit.

 DatabaseList.MaxIdle=1

TIP: You can specify the Database connection parameters for each database you wish to monitor individually, underneath your Database Connection Parameters. This allows you to customise your connection settings based on the Database constraints I.e one connection pool for your PRODUCTION DB and another for you TEST DB, for example;

 DB1.MaxActive=10
 DB1.MaxWait=100
 DB1.MaxIdle=1

Note that any settings defines this way will override the general settings for your generic connection pool.

Database Connection Parameters

This section sets your connection string to the PostgreSQL Database. This string invokes a Java Database Connector (JDBC) to your PostgreSQL Databases, and as such does not need the PostgreSQL Client to be installed.

 #define here your connection string for each database

Here you will define the connection string. These are formatted as: DBName.Url(as specified in your DatabaseList) followed by the jdbc string and your Database Server information.

 EXAMPLE1.Url=jdbc:oracle:thin:@SERVER1.EXAMPLE.COM:1521:DB1

Set your Database username and password below. e.g.

 EXAMPLE1.User=zabbix
 EXAMPLE1.Password=zabbix_password

TIP: Setting connections to multiple databases is made easy by adding more connection strings, with their corresponding credentials. Example given below.

 EXAMPLE2.Url=jdbc:oracle:thin:@server2.domain.example.com:<LISTENER_PORT>:EXAMPLE2
 EXAMPLE2.User=zabbix
 EXAMPLE2.Password=zabbix_password
 EXAMPLE3.Url=jdbc:oracle:thin:@server3.domain.example.com:<LISTENER_PORT>:EXAMPLE3
 EXAMPLE3.User=zabbix
 EXAMPLE3.Password=zabbix_password

NOTE:After these entries, you can set your MaxActive, MaxWait and MaxIdle for the individual database connections if you so wish.

There is a pair of default parameter:

 DefaultUser = <USERNAME>
 DefaultPassword = <PASSWORD>

this default parameter can be overridden using the following pair

 <DB_NAME>.User and <DB_NAME>.User

It is only possible to override Username or Password.

Query List File

This defines where the file containing the PostgreSQL (SQL) queries can be found. This is a customisable file. You can find the query parameter file at:

 QueryListFile=/opt/orabbix/conf/query.props

you can also specify :

 <DBNAME>.QueryListFile=./confQueryTest.props this file specify the query 	file of a database relative path are allowed

TIP: This is really useful to use different query file for different databases or to have a pool of query file one for each
PostgreSQL Release (if you have different release) and/or to have different query files for Production environment, Test environment, Developement environment etc..
the following parameter

 <DB_NAME>.ExtraQueryListFile ==./confQueryTest_2.props

adds another custom query to the default query file. If there are duplicates the ExtraQueryListFile will override them.

query.props

Configuration of query.props file

The query.props file can be modified or added to, so you can supply your own customised queries through PostBIX against your PostgreSQL instances/databases. Each query created has an associated 'Item' or item name that Zabbix will use to identify the query.

NOTE: Item names must be unique.

The configurable items are formatted as follows.


You have to set the query name under the QueryList in the query.props file. Each query name is comma separated. For example;

 QueryList=queryName1,queryName2,queryName3


You must identify the query by the unique item name you specified in the QueryList, followed by '.Query' for PostBIX to recognize that this is the query string. Its important to remember NOT to add the semi-colon “;” to the end of your custom query.

 customQueryItemName.Query=yourQueryHere

Now you can define what you want PostBIX to return to your Zabbix Server if no data is found for your query.

 customQueryItemName.NoDataFound=none

TIP: In the example above, PostBIX would send the string “none” to the Zabbix Server.

You can specify if you want a different execution period for your query

 customQueryItemName.Period=<Express a period in minute>

TIP:You can change the execution time of dbversion that don’t change often

Next you can specify a query that will be executed and if return RaceCondiftionValue the query customQueryItemName is executed otherwise is skipped

 customQueryItemName.ACTIVE=[true|false] if true query is executed otherwise skipped


 archive.Query=select round( A.LOGS*B.AVG/1024/1024/10 ) \
 from ( SELECT  COUNT (*)  LOGS FROM V$LOG_HISTORY WHERE \
 FIRST_TIME >= (sysdate -10/60/24)) A, \
 ( SELECT Avg(BYTES) AVG,  Count(1), Max(BYTES) Max_Bytes,Min(BYTES) Min_Bytes  FROM  v$log) B
 archive.RaceConditionQuery=select value \
 from \
 v$parameter where name='log_archive_start'
 archive.RaceConditionValue=FALSE

TIP:In the example above, PostBIX would execute “archive.Query” only if the query “archive.RaceConditionQuery” return the value “archive.RaceConditionValue”. Pratically the “Archive” query is execute only if database is in arhivelogmode (Parameter “log_archive_start” set to TRUE)

 <QueryName>.Trim=[true|false]

if true the resultset is trimmed (default is true)

 <QueryName>.AddSpaces=[true|false]

if true the add a space between columns of resultset(default is true)

 <QueryName>.ExcludeColumnsList=[1,2,3,..n]

exclude from result set the 1st, the 2nd etc.. columns from resultset

 <QueryName>.Period
 <QUERY_NAME>.WhenNotAlive = <VALUE>

is used to specify a value or a string to send if a database is not working, doing such will "clean" the graphs generated in Zabbix with a "set" value.


Added the following parameter on main configuration file:

 DefaultUser = <USERNAME>
 DefaultPassword = <PASSWORD>
 <QueryName>.ExtraQueryListFile = <VALUE>


on query's properties file I've added the following parameter:

 <QUERY_NAME>.WhenNotAlive = <VALUE>

with these parameter you can define a default username and password for all the database.

 DefaultUser = <USERNAME>
 DefaultPassword = <PASSWORD>

this default parameter can be overridden using the couple

 <DB_NAME>.User and <DB_NAME>.User

Is possible to override only Username or Password.

the following parameter

 <DB_NAME>.ExtraQueryListFile =

add another custom query to a default query file. if there are duplicate the ExtraQueryListFile will override them.

the following parameter

 <QUERY_NAME>.WhenNotAlive = <VALUE>

is used to specify a value or a string to send if a database is not working, doing so you are going to "clean" the graphs generated in Zabbix with a "set" value.

How to Use PostBIX/FAQ

How do I start/stop the Daemon?
To start the PostBIX Daemon, simply run:

 /etc/init.d/orabbix start

To stop the PostBIX Daemon, simply run:

 /etc/init.d/orabbix start

How does Logging work?
The PostBIX daemon outputs its log file to the default location of /opt/orabbix/logs/

How to modify Log format?
Logging properties can be modified by making your required changes to

 /opt/orabbix/conf/log4j.properties

The property setting responsible for defining the output location is:

 log4j.appender.PostBIX.File=logs/orabbix.log

TIP:From this properties file, you can modify the location, file name and log format as desired. For additional information, please refer to the official log4j documentation, found at: http://logging.apache.org/log4j/1.2/index.html

Is there a way for orabbix to connect to RAC or DataGuard?
Yes is possible.
e.g.
If you have two host RAC1 and RAC2 and one instance RACINST
you should write the connection string as follow:

 RACINST.Url=jdbcracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=RAC1.EXAMPLE.COM) \
 (PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=RAC2.EXAMPLE.COM)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=RACINST)))

What can I modify without restarting the daemon?

The parameters that are dynamically read at each iteration of “PostBIXDaemon.Sleep” are as follows;

  • Any Query added to query.props
  • Modifications to config.props
  • Database List

Currently, the only known items that don't dynamically update are the ZabbixDaemon.MaxThreadNumber, and changes to Connection Pool info.

LICENCE Information

Orabbix logo.png

PostBIX is released under and according to the
GNU GENERAL PUBLIC LICENSE
Version 3, 29 June 2007