Database Preparation

Added by John Sachs, last edited by Alex Ma on Nov 13, 2007  (view change)

Using a standalone database as the backend for HQ can allow you to leverage existing infrastructure and give you the flexibility to scale HQ to the size your environments demands. HQ Server will run on the following databases:

  • HQ Built-in database
  • Oracle 8i
  • Oracle 9i
  • Oracle 10g
  • PostgreSQL

All options except HQ Built-in database require some further preparation. It is not necessary to read this guide if you plan to use the HQ built-in database for your installation of HQ.

Oracle Preparation

Three things need to be done for Oracle for use with HQ. More preparation is necessary if the advanced Oracle setup will be used. see Advanced Oracle Setup document for details.

Create or determine an Oracle instance to be used for the HQ database

Hyperic recommends that the Oracle server to be used by HQ be running on its own hardware. Install Oracle on the machine to be used, and create a database. The Oracle instance will be ready for the next step.

Create the user HQ will use to access Oracle

There are several ways to create a user in Oracle. One way is with SQL*Plus. First, log into the Oracle instance as the system user with SQL*Plus, then issue the create user command:

SQL> CREATE USER HQUSER IDENTIFIED BY HQPASSWORD;

The user will be created. The above command would create a user named 'HQUSER' with a password of 'HQPASSWORD'.

Grant the required permissions to the Oracle user

This is easily done in SQL*Plus with the grant command:

SQL> GRANT CONNECT, RESOURCE TO HQUSER;

Oracle is now ready to accept an HQ installation.

Advanced Oracle Configuration

This is optional configuration that can help Oracle perform very well with very large HQ environments. This configuration is not necessary for small to medium environment. An example of an environment where this type of configuration would help performance is an environment with hundreds of HQ Agents.

Create a new database

The database can be created by using Oracle Database Configuration Assistant. Select New Database (Includes datafiles = No). Decline to install the Example Schemas to save space.

Configure memory

Oracle should be installed on a dedicated host, so select Typical Memory configuration. Select OLTP as the type of database sizing to use. Allocate as high a percentage of system resources as you can afford. This should be 70-90%, ideally in the higher range.

Create tablespaces

Create the following tablespaces:

  • HQ_DATA (with 2 x 512MB datafiles) - used for all non-metric data
  • HQ_INDEX (with 2 x 512MB datafiles) - used for indexes on above data
  • HQ_METRIC_DATA (with 4 x 1024MB datafiles) - used for all metrics (including response-time)
  • HQ_METRIC_INDEX (with 4 x 2048MB datafiles) - used for all metric related indexes
Manual Segment Space Management

It is important that all tablespaces have locally managed freelists. The default behavior is automatically managed freelists, and that has an adverse effect on HQ's ability to do concurrent inserts. So set Manual Segment Space Management.

Redo Logging should be turned OFF for all of these tablespaces. This is a major bottleneck for the database, and in our scenario we need high throughput, which comes at the expense of recoverability.

This type of setup requires that data be backed up fairly frequently.

Example SQL to create the tablespaces:

CREATE TABLESPACE HQ_DATA NOLOGGING DATAFILE
  '/disk1/path/to/datafile1.dbf' SIZE 512M
    AUTOEXTEND ON NEXT 64M MAXSIZE UNLIMITED,
  '/disk2/path/to/datafile2.dbf' SIZE 512M
    AUTOEXTEND ON NEXT 64M MAXSIZE UNLIMITED
  EXTENT MANAGEMENT LOCAL
  SEGMENT SPACE MANAGEMENT MANUAL;

Create the HQ Oracle database user

Not all HQ tables specify a tablespace at install time, those that do not will end up in the default tablespace for the user. Therefore, make sure to set HQ_DATA as the default tablespace when the user is created:

CREATE USER hquser IDENTIFIED BY password DEFAULT TABLESPACE HQ_DATA;

Grant database permissions

Be sure to grant the following permissions to the newly created user in order for the install to be successful.

GRANT CONNECT, RESOURCE TO hquser;

Asynchronous I/O

Asynchronous I/O is yet another optional configuration that can further increase the performance of an Oracle database serving a large HQ environment. This configuration is only supported by Oracle 9i. On Linux, Oracle 9i supports asynchronous I/O but it is disabled by default because some Linux distributions do not have libaio by default. For Solaris, the following configuration is not required - skip down to the section on enabling asynchronous I/O.

On Linux, the Oracle binary needs to be relinked to enable asynchronous I/O. The first thing to do is shutdown the Oracle server. After Oracle has shutdown, do the following steps to relink the binary:

su - oracle
cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk async_on
make -f ins_rdbms.mk ioracle

The above instructions were taken from this site (not affiliated with Hyperic) where more information about asynchronous I/O on Oracle may be available.

For RedHat 9: download the src rpm for libaio (libaio-0.3.93-4.src.rpm) and patch it with this patch before building and installing. Then, relink Oracle as described above and follow the rest of the instructions to enable asynchronous I/O.

Enabling Asynchronous I/O in Oracle: There are two initialization parameters for asynchronous I/O:

disk_asynch_io = true          # This parameter defaults to true
filesystemio_options = asynch  # This parameter defaults to unset

The second parameter may only apply if you use the file system rather than raw partitions. If your installation uses the init.ora file, or inityourdatabasename.ora, add the above parameters to it and start the database.

Alternatively, if your installation uses the spfile mechanism, which is new in Oracle 9, you have to start the server, log in as SYSDBA through sqlplus, then give the following command:

SQL> ALTER SYSTEM SET filesystemio_options = ASYNCH;

This default invocation changes the parameter value both in memory and on disk in the actual spfile (this is assumed to take effect immediately). Display a parameter's value by using the SHOW PARAMETER parametername; command. You will know that your installation uses spfiles if:

  • Editing init.ora doesn't accomplish anything
  • There exists a file named spfileyourdatabasename.ora in ${ORACLE_HOME}/dbs

This concludes the section on Advanced Oracle Configuration for Hyperic HQ.

Advanced PostGres Configuration

Setting up HQ to use a external PostGres DB usually assumes that you have a DBA on hand for setup and maintenance.

Here are the details for this setup example:

  • OS Version - Redhat Enterprise Linux 4
  • DB Version - Postgres 8.1
  • DB IP - 192.168.1.4
  • HQ IP - 192.168.1.6
  • DB User name - admin
  • DB Password - hqadmin
  • DB location - /var/lib/pgsql/data/
  • PostGres Configuration file - /var/lib/pgsql/data/postgresql.conf
  • PostGres HBA file - /var/lib/pgsql/data/pg_hba.conf

There are 8 steps needed to configure HQ to use a external PostGres DB.

  1. Install PostGres on external DB
  2. Start Database and Create user for HQ
  3. Configure PostGres to accept Network connections and setup user accounts with permissions
  4. Tune PostGres for HQ
  5. Setup HQ to use external PostGres DB
  6. Create PLPG language
  7. Start HQ
  8. Regular backups.

Install PostGres on external DB

Since we are using Redhat for the DB OS, we can use Yum to simply the install. ( If not Yum, then configure and build from source from the PostGres Website ).

	yum install postgresql postgresql-server

This will install PostGres in:

  • /etc/init.d/
  • /usr/bin/
  • /usr/share/doc/
  • /var/lib/pgsql/

Start up the database with the yum provided init scripts. This will initialize the database and setup some basic configuration files.

	/etc/init.d/postgresql start

Start Database and Create user for HQ

The default permissions on the database only allow a local connection for the postgres user.
Change user to postgres and connect to database locally.

	# su postgres
	> psql
Basic PostGres commands to get you around

\h for help with SQL commands
? for help with psql commands
\du to list roles/users
\l to list databases
\c to choose a database
\d to list tables once in a database
\q to quit

/usr/bin/pg_ctl start -D /var/lib/pgsql/data ( to restart postgres and re-read the configuration files )

Create a user named admin( or whatever name you like ) with permission to login and create databases, also create a default database for Hyperic( remember to use quotes for HQ so the db is created in uppercase )

	create role admin with login createdb password 'hqadmin';
	CREATE DATABASE "HQ" OWNER admin;

Configure PostGres to accept Network connections and setup user accounts with permissions

PostGres needs to be told explicitly to listen on all network interfaces, not just the local loopback address.

Edit the PostGres configuration file and change the default commented listen address entry to:

listen_addresses = '*'

The PostGres HBA configuration file defines the methods allowed for connection. We need to configure PostGres to allow both local connections and connections from the HQ Server.

The fields in this file define access granularly for local/network connections, database and users allowed to connect, IP's or subnets allowed and authentication. You can read more about this here:
http://www.postgresql.org/docs/8.2/interactive/auth-pg-hba-conf.html

Add this to the pg_hba.conf file.

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
local   all     all		                                     ident sameuser
host    all     all             192.168.1.6/32          password

This will allow users to connect locally and the HQ Server to connect remotely.

Troubleshooting

You can tail -f the log files if network connections are failing
/var/lib/pgsql/data/pg_log/postgresql-day.log
/var/lib/pgsql/pgstartup.log

Tune PostGres for HQ

Edit the postgresql.conf file and change or add the following:

## performance changes for HQ
fsync=false
shared_buffers=10000
work_mem=2048
statement_timeout=30000

It is a good idea to monitor the PostgreSQL database with HQ and that requires some further configuration. If the PostgreSQL database will not be monitored by HQ, these changes are not necessary.

stats_start_collector = true
stats_block_level = true
stats_row_level = true
stats_reset_on_server_start = false

Setup HQ to use external PostGres DB

We can quickly install and then remove the postgres client on the HQ server for the sake of testing connectivity.

yum -y install postgresql
psql -d postgres -h 192.168.1.4 -U admin -W

Untar the HQ installer and run the setup script with the postgres option and follow the options till you get to the database connection

./setup.sh -postgresql


Enter the JDBC connection URL for the PostgreSQL database [default 'jdbc:postgresql://localhost:5432/HQ?protocolVersion=2']:
jdbc:postgresql://192.168.1.4:5432/HQ?protocolVersion=2

HQ Server install finishes but do not start the server yet.

Create PLPG language

Note for PostgreSQL 8.0

HQ creates a language in the PostgreSQL database. HQ is not able to create the language automatically in PostgreSQL 8.0, so if you plan to use this version, you must run the following command on the HQ database before starting the HQ Server.

createlang plpgsql [DATABASE NAME]
createlang plpgsql HQ

The createlang executable is located in the bin directory of your PostgreSQL installation.

Start HQ

Start the HQ server and import a agent. Watch the postgres logs for any connection failures or errors. If you have followed the steps above, there should be none.

Regular backups.

Next Steps

Labels

 
(None)
System Monitoring Software
SourceForge.net Logo