DBforBIX2

From SmartMarmot
Jump to: navigation, search

About DBforBix

DBforBIX Architecture
Fig. 1 - DBforBIX Architecture

DBforBIX is a daemon designed to work in combination with Zabbix Enterprise Monitor to provide multi-tiered monitoring, performance and availability reporting and measurement for the many kind of different databases, along with server performance metrics.

It provides an effective mechanism to acquire data from numerous databases installation, 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.

DBforBIX can run as a Windows Service and is able to work on many different environments. Where is available a JRE 1.7 or a JRE 1.8 you can run DbforBIX2. History

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

Original Orabbix here: http://www.zabbix.com/forum/showthread.php?t=13666
Latest Orabbix here: http://www.zabbix.com/forum/showthread.php?t=16391
Original DBforBIX here: https://www.zabbix.com/forum/showthread.php?t=21730&highlight=dbforbix
The origins of DBforBIX can be traced though Orabbix, PostBIX, MySQLBIX and DB2Bix, and as such inherits all its predecessors benefits.

Author

DBforBIX2 was developed and written by:
Andrea Dalle Vacche : http://www.smartmarmot.com

Supported Databases

DBforBIX2 can acquire and retrieve every kind performance parameter and vital statistic that is supported or available through standard queries.

DBforBIX support the following databases:

  • Oracle
  • MySQL Server
  • PostgreSQL
  • MS SQL Server
  • DB2
  • Sybase Anywhere
  • HP Allbase

All information gathered from the monitored databases is retrieved by query, using the Java JDBC layer. This ensures compatibility and diversity with future database versions and types.

Monitoring Capabilities

Below are some examples of what DBforBIX is capable of monitoring.

on Oracle Database The following are just some of parameters that DBforBIX is capable of monitoring on Oracle:

Availability

  • DB Version (for every vendor)
  • Archiving (Archive log production with trend analysis)
  • Event Waits (Files I/O, single block read, multi-block read, direct path read, SQLNet Messages,
  • Control file I/O, Log Write)
  • Hit Ratio (Hit Ratio on Triggers, Tables/Procedures, SQL Area, Body)
  • Logical I/O (Server performance on Logical I/O of: Current Read, Consistent Read, Block Change)
  • Physical I/O (Redo Writes, Datafile Writes, Datafile Reads)
  • PGA
  • SGA (In particular; Fixed Buffer, Java Pool, Large Pool, Log Buffer, Shared Poolm Buffer Cache)
  • Shared Pool (Pool Dictionary Cache, Pool Free Memory, Library Chache, SQL Area, MISC.)
  • Pin Hit Ratio (Oracle library cache pin are caused by contention with the library cache, the area used to store SQL executables for re-use)
  • Sessions / Processes
  • Sessions (Active Sessions, Inactive Sessions, System Sessions)
  • DBSize/DBFileSize (DBSize size of database really used space and of Filesize) on MySQL server

The following are just some of parameters that DBforBIX is capable of monitoring on MySQL. The list was truncated due to the sheer volume of list items:

  • Dbversion
  • table/thread cache
  • Com created/dropped/changed/committed
  • Innodb statistics
  • Key read/write/requests
  • Qcache statistics

on PostgreSQL server The following are just some of parameters that DBforBIX is capable of monitoring on PostgreSQL:

  • Availability
  • Buffers (Backend, checkpoint, clean, allocated)
  • Checkpoint (requested, timed)
  • Connections
  • Tuples (deleted, updated, fetched, returned, inserted)
  • Xact (rollback, commit)
  • Locks (every kind of exclusive locks and every kind of general locks)

on MS SQL server The following are just some of parameters that DBforBIX is capable of monitoring on Microsoft SQL Server:

  • Cachehit
  • I/O Pending
  • waittime
  • dbsize/logsize
  • log usedsize
  • Page reades/writes

On DB2 Coming soon to a DBforBIX distribution near you!

On Sybase Anywhere Coming soon to a DBforBIX distribution near you!

On HP ALLBASE Coming soon to a DBforBIX distribution near you!


Distribution

You can find the latest versions of DBforBIX locations listed below:

or

Technical Details

DBforBIX has been made to monitor and control every kind of principal database with just one daemon.

Points of force of this daemon are:

  • Apache DBCP connection pool
  • logging realized by Log4J
  • Hyper Threading
  • JSVC Apache framework to make it run as Daemon on Linux or Windows
  • Superscalar (you can monitor a huge amount of databases of different kind)
  • Refresh parameter while running (you don't need to restart the daemon)
  • unlimited support of Zabbix servers (send the retrieved items to all Zabbix server
  • Items collision free

Pros: You can customize connection pooling for each database with different parameters. This means that connections are reused (constantly making a new connection introduces an overhead for all databases) and when idle or dropped. You can customize your logfile format as you see fit, so it can be parsed to/from your own software to maintain control of information gathered by DBforBIX logging and assist in error troubleshooting. Hyper Threading: If a database is slow, it won’t impact your other databases as every job is a thread and all generated threads run independently. DBforBIX can scale on multiple processors and is really lightweight. You can definitively use it in your mission critical environment, since the architecture allows you to send all retrieved items to any number of Zabbix servers, without limitations. All the Items retrieved are collision free. e.g. Alive is a true common item that can have collision with another Item with the same name.

Here is an example of the collision problem, solved by DBforBIX2 automatically introducing a prefix that is always in the form of:

<db type>.itemkey

<dbtype> can be Oracle, MySQL, PostgreSQL, DB2, MSSQL and are predefined (you don't need to configure or modify anything)

Requirements

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

  • Zabbix 1.8.x Server or later, Zabbix 3.x is fully supported
  • Java Runtime Environment 7 or later
  • DBforBIX2 distribution binaries

Current known successfully tested DBforBIX host platforms:

  • RHEL5.X 6.X 7.X (CentOS 5.X 6.X &7.X)
  • Windows 2003-2008-1012 (with Java SE 1.6)
  • HP-UX 11.31
  • AIX 5.3

Currently known tested Oracle Versions:

  • 9i
  • 10g
  • 10.2
  • 11G
  • 12c

Currently known tested PostgreSQL Versions:

  • 8.3
  • 8.4
  • 9.X

Currently known tested MySQL Versions:

  • 5.x

Currently known tested SQLServer Versions:

  • 2010
  • 2005
  • 2000


IMPORTANT You do NOT need to install any client of any kind for DBforBIX to work. You need to download and place under /lib/ directory the following library:

  • db2jcc_license_cu.jar (DB2)
  • db2jcc.jar (DB2)
  • ojdbc6.jar (Oracle)
  • jconn4d.jar (Sybase Anywhere)
  • mbfjdbc2.jar (HP Allbase)



DBforBIX uses Java objects and connection strings to connect to the Oracle Database, and as such doesn’t require any database client to be installed on your Zabbix Server.

Generated Graphs

There are a significant number of graphs generated by the default templates included in DBforBIX, for each kind of database.

Some examples are coming soon.

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 database Server. If you are planning on monitoring a database 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 DBforBIX to monitor a new installation or setup of Oracle. 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.

Common (Universal) Installation Steps

Common Part

Download DBforBIX to your Zabbix Server

On your Zabbix server, unzip DBforBIX to: /opt/dbforbix

Install the JAVA JVM >= 1.7 (both Oracle and OpenJDK works properly)

Install JSVC (Java daemon launcher)

Copy config.properties.sample to config.properties and change it adding your databases.

Initd

Now for all the distribution that are still using initd use the following procedure:

Copy file /opt/dbforbix/init.d/dbforbix to /etc/init.d/dbforbix

Grant execute permissions to the following files:

  • /etc/init.d/dbforbix
  • /opt/dbforbix/run.sh

For this example on RedHat, run:

 chkconfig -add dbforbix

Verify with:

 chkconfig -list

Create a Host entry in Zabbix for the DB Instance/s you are planning to monitor, and import your needed templates found at:

 /opt/dbforbix/template

Systemd

If your distribution uses systemd, like most of the nowadays here are the steps:

Copy the systemd included files

 cp systemd/dbforbix.service /etc/systemd/system/dbforbix.service

Notify systemd that a new dbforbix.service file exists by executing the following command as root:

 systemctl daemon-reload
 systemctl start dbforbix.service

To configure the service to start at each boot run (from root console):

 systemctl enable name.service


If you would like to test dbforbix from command line you can simply type:

 java -jar dbforbix.jar -a start -C /opt/dbforbix

Installation on Windows

DBforBIX implements an Apache daemon and offers the capability to run as a service on Microsoft Windows environments.

Install DBforBIX on Windows is really easy just follow these steps:

  • Create a directory on c: called dbforbix
  • extract the distribution inside “c:\dbforbix”
  • double click on “install.cmd”
  • Install a JRE >= 1.7 on the system
  • Copy config.properties.sample to config.properties and modify
  • Run install.cmd contained with the distribution

after you'll find a service called “DBforBIX Universal Database Monitor for Zabbix”

You can use “dbforbixctl.exe” to customize service's parameters if required.

Refer Figures 1.a and 1.b for screenshots that show dbforbixctl.exe at work.


DBforBIX General
Fig. 2 - DBforBIX General
DBforBIX Logging
Fig. 3 - DBforBIX Logging
DBforBIX Java
Fig. 4 - DBforBIX Java
DBforBIX Startup
Fig. 5 - DBforBIX Startup
DBforBIX Shutdown
Fig. 6 - DBforBIX Shutdown

Install steps for Oracle

Create a User (ZABBIX) for DBforBIX to access your Oracle Database. You can use the following script:

  CREATE USER ZABBIX
  IDENTIFIED BY <REPLACE WITH PASSWORD>
  DEFAULT TABLESPACE SYSTEM
  TEMPORARY TABLESPACE TEMP
  PROFILE DEFAULT
  ACCOUNT UNLOCK;
  -– 2 Roles for ZABBIX
  GRANT CONNECT TO ZABBIX;
  GRANT RESOURCE TO ZABBIX;
  ALTER USER ZABBIX DEFAULT ROLE ALL;
  –- 5 System Privileges for ZABBIX
  GRANT SELECT ANY TABLE TO ZABBIX;
  GRANT CREATE SESSION TO ZABBIX;	
  GRANT SELECT ANY DICTIONARY TO ZABBIX;
  GRANT UNLIMITED TABLESPACE TO ZABBIX;
 GRANT SELECT ANY DICTIONARY TO ZABBIX;

NOTE : If you are using Oracle 11g, you will need to add the following:

  exec dbms_network_acl_admin.create_acl(acl => ‘resolve.xml’,description => ‘resolve acl‘, principal =>’ZABBIX’,is_grant => true, privilege => ‘resolve’);
  exec dbms_network_acl_admin.assign_acl(acl => ‘resolve.xml’, host =>’*');

You can verify the above is correct by running:

  select utl_inaddr.get_host_name(’127.0.0.1′) from dual;

NOTE: To create a User (ZABBIX) for DBforBIX with MINIMAL grants you can use the following script:

 CREATE USER ZABBIX
  IDENTIFIED BY <REPLACE WITH PASSWORD>
  DEFAULT TABLESPACE USERS
  TEMPORARY TABLESPACE TEMP
  PROFILE DEFAULT
  ACCOUNT UNLOCK;
  GRANT ALTER SESSION TO ZABBIX;
  GRANT CREATE SESSION TO ZABBIX;
  GRANT CONNECT TO ZABBIX;
  ALTER USER ZABBIX DEFAULT ROLE ALL;
  GRANT SELECT ON V_$INSTANCE TO ZABBIX;
  GRANT SELECT ON DBA_USERS TO ZABBIX;
  GRANT SELECT ON V_$LOG_HISTORY TO ZABBIX;
  GRANT SELECT ON V_$PARAMETER TO ZABBIX;
  GRANT SELECT ON SYS.DBA_AUDIT_SESSION TO ZABBIX;
  GRANT SELECT ON V_$LOCK TO ZABBIX;
  GRANT SELECT ON DBA_REGISTRY TO ZABBIX;
  GRANT SELECT ON V_$LIBRARYCACHE TO ZABBIX;
  GRANT SELECT ON V_$SYSSTAT TO ZABBIX;
  GRANT SELECT ON V_$PARAMETER TO ZABBIX;
  GRANT SELECT ON V_$LATCH TO ZABBIX;
  GRANT SELECT ON V_$PGASTAT TO ZABBIX;
  GRANT SELECT ON V_$SGASTAT TO ZABBIX;
  GRANT SELECT ON V_$LIBRARYCACHE TO ZABBIX;
  GRANT SELECT ON V_$PROCESS TO ZABBIX;
  GRANT SELECT ON DBA_DATA_FILES TO ZABBIX;
  GRANT SELECT ON DBA_TEMP_FILES TO ZABBIX;
  GRANT SELECT ON DBA_FREE_SPACE TO ZABBIX;
  GRANT SELECT ON V_$SYSTEM_EVENT TO ZABBIX;

Install steps for PostgreSQL

Create a User (ZABBIX) for DBforBIX 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;

Steps for Installation on MySQL

Create a User (ZABBIX) for DBforBIX to access your MySQL Database.
You can use the following script:

  CREATE USER 'zabbix_monitor'@'%.mydomain.com' IDENTIFIED BY 'zabbixpassword';
  GRANT SELECT, SHOW VIEW ON *.* TO 'zabbix_monitor'@'%.mydomain.com';


Steps for Installation on Microsoft SQL Server

Coming soon

Steps for Installation on IBM DB2

Coming soon

Configuration

Now we need to configure your DBforBIX 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 your databases.

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

Config.props

The file which is included in DBforBIX distribution if is the first installation need be renamed removing “.sample”. With this way you will never find out overwritten your configurations file updating DBforBIX with a new release. First of all let's start with the general settings ad so the LogLevel:


Here you nneed to specify the Log level, the possible values are: [All|Debug|Info|Warning|Error] DBforBix.LogLevel=Info

Here you specify the logfilesize (used by the log4J rolling appender) the relative location. DBforBix.LogFileSize=1MB DBforBix.LogFile=./logs/dbforbix.log


The section that follows is dedicated to the connection pool configuration. Then you need to Maximum number of active connection inside pool Pool.MaxActive=10 The pool timeout (the value is expressed in seconds) Pool.TimeOut=15

Then you find the section that defines the ZabbixServer you want to send the item to:

ZabbixServer.1.Address=<ip address of your Zabbix server> ZabbixServer.1.Port=<port used by your Zabbix server, usually 10051> Here to add one more ZabbixServer you should write the following parameters: ZabbixServer.2.Address=<ip address of the second Zabbix server> ZabbixServer.2.Port=<port used by the second Zabbix server>

DBforBIX FAQ

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

 /etc/init.d/dbforbix start

or on systemctl

 systemctl start dbforbix.service


To stop the DBforBIX Daemon, run:

 /etc/init.d/dbforbix stop

or on systemctl

 systemctl stop dbforbix.service

How does Logging work?

Is there a way for DBforBIX to monitor RAC or DataGuard? Yes is possible, for example; If you have two hosts, RAC1 and RAC2, in one instance of 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)))

Currently, the only known items that don't dynamically update are the ZabbixDaemon.MaxThreadNumber, and changes to Connection Pool info. Is it possible divide monitoring for based on database type and/or environment? Yes it's possible and it's easy! Basically, you just need to copy your installation directory into a new one e.g. you can have

 /opt/dbforbix_prod
 /opt/dbforbix_test
 /opt/dbforbix_devel

and then you need to copy /etc/init.d/dbforbix into

 /etc/init.d/dbforbix_prod
 /etc/init.d/dbforbix_test
 /etc/init.d/dbforbix_devel

After this, you need to customize start/stop script to locate the right directory for each instance of DBforBIX e.g.

 dbforbix=/opt/dbforbix

should be changed into:

 /opt/dbforbix_prod
 /opt/dbforbix_test
 /opt/dbforbix_devel

Now you have completely divided your monitoring solution to the most common scenarios of: development environment test/quality/pre-production environment production environment

After that you can customize your queries file for you different environments, ensure each fulfills the requirements based on their varying needs. You can play around with the suggestions above and do the same as needed.

Document Copyright

Document Copyright © Andrea Dalle Vacche and Jason Chatfield

License Information

DBforBIX.png

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