Opsview Knowledge Center

Database Partitioning

An overview of Database Partitioning in Opsview Monitor.

Opsview Monitor 5.3.0 allows for specific tables within the runtime database to have partitioning applied. This partitioning vastly reduces the time taken to perform nightly housekeeping and speeds up some queries in the UI.

NOTE: Partitioning will only be applied when used with MySQL version 5.5 or newer. This is due to performance issues in MySQL 5.1 (used on CentOS 6 and RHEL6). If you currently use MySQL 5.1 we recommend you either upgrade MySQL or migrate your databases to a different server running a newer version of MySQL.

Installing Opsview Monitor 5.3 will automatically apply partitioning if you are using MySQL 5.5 or newer. When using MySQL 5.1 the system will continue to install correctly, just without partitioning applied.

Upgrading to Opsview Monitor 5.3 will produce a notice such as the following if any of the specific tables are not currently partitioned:

############################################################
If you want to speed up housekeeping, run the following:
  /usr/local/nagios/installer/apply_runtime_partitions
############################################################

After the upgrade is complete you may then run this script. Opsview does not need to be shut down while this script runs.

Notes:

  • You must have applied basic MySQL performance tuning before the script will run. See the section on 'Performance Tuning' on 3.1.2.8. Databases.
  • You must make sure you have adequate free disk space in the data partition for the Runtime database on your MySQL server. Usually this is /var/lib/mysql/runtime. The script will suggest how much space may be required:

When running the script you should see output similar to the follow:

This script will amend specific tables within the Runtime database by adding
partitions to them.  This will allow for much faster housekeeping on this
database.
Please ensure your database server has enough free space to recreate these tables:
        nagios_hostchecks: 0.05MB
     nagios_notifications: 0.05MB
      nagios_statehistory: 0.08MB
     nagios_servicechecks: 0.22MB
:::::::::::::::::::::::::: ::::::::::::
         total space used: 0.39MB
  required for conversion: 0.13MB
Only 2 days of data will be kept in these tables during the conversion, so you
will need at least 0.13 MB of free space on the MySQL data partition
for Runtime.
You should also ensure you have adequate database backups to restore Runtime
if problems occur.
Are you happy to continue? [yN]:

The process will move the table to one side, create the new one and then copy 2 days worth of data back from the old table. This process allows Opsview Monitor to keep working while the partitioning is taking place.

Note: Depending on the size of your system, this process may take a considerable length of time. Progress will be output as each table is converted, but you can also check what is happening within the database by running the following command as the nagios user:

/usr/local/nagios/utils/cx runtime "show processlist"

Database Partitioning

An overview of Database Partitioning in Opsview Monitor.