Runtime and ODW database partitioning

The Runtime and ODW database are installed with partitioning enabled by default. This allows more efficient use of the database to store the long term availability and performance metrics of all Opsview’s service checks. Anyone upgrading from previous versions of Opsview will have to manually enable partitioning.

We recommend all users enable partitioning. Take note of the following considerations:

• You must have applied basic MySQL performance tuning before the script will run; see the section ‘Performance Tuning’ within Database Storage.
• You must have sufficient disk space for partitioning to work.
• Partitioning will take effect immediately, although historical data will be migrated into the partitions over time. Opsview will continue to monitor during this period.

The amount of space needed to complete the partitioning will be the same as the largest database table you have from the lists given below.

To see the size of the tables within your database, you may run the below command as the root user in your database. Please note this command is running against your Runtime database - you would need to change the table_schema to check for odw tables

mysql> SELECT TABLE_NAME, round(((data_length + index_length) / 1024 / 1024),2) Size_in_MB FROM information_schema.TABLES WHERE table_schema = 'runtime' order by Size_in_MB desc;

Runtime

The following tables will be partitioned by Opsview:

• nagios_servicechecks
• nagios_hostchecks
• nagios_notifications
• nagios_statehistory

When upgrading Opsview Monitor a notice such as the following will be displayed if any of the tables are not currently partitioned:

############################################################
If you want to speed up housekeeping, run the following:
  /opt/opsview/coreutils/installer/apply_runtime_partitions
############################################################

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

Take note of the following considerations:

• Only the last 2 days worth of Runtime data will be migrated into the new partitioned tables. If you do not use ODW, then the nagios_servicechecks table will be truncated.
• If you use ODW, imports must be up to date to avoid losing data in ODW.

ODW

The following tables will be partitioned by Opsview:

• performance_data
• servicecheck_results
• state_history
• performance_hourly_summary
• service_availability_hourly_summary

When upgrading Opsview Monitor a notice such as the following will be displayed if any of the tables are not currently partitioned:

############################################################
If you want to speed up housekeeping, run the following:
/opt/opsview/coreutils/installer/apply_odw_partitions
############################################################

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

Take note of the following considerations:

• All ODW data is retained so you must be extra vigilant about sufficient disk space being available.
• Depending on the size of your system the process may take a considerable length of time, depending on the amount of data you have. Progress will be output as each table is converted, but you can also monitor your database server using show processlist within the database.