Maintaining Database

The following are the actions that you can take to maintain or optimize the database:

Set up record partitioning in the database

About this task

In a large database, removing old job records and other database maintenance tasks can have a significant effect on database performance. You can improve database performance by enabling record partitioning. Partitioning divides tables within the database into multiple, smaller tables.

Removing job records from smaller tables has less of an impact on the system performance of active tables than removing job records from larger tables.

You can set up partitioning for the following database tables that RTM monitors (all of the following tables are affected):

  • GRID_ARRAYS

  • GRID_JOB_DAILY_STATS

  • GRID_JOBS_FINISHED

  • GRID_JOBS_MEMPERF

  • GRID_JOBS_RUSAGE

Procedure

  1. Click the Console tab.
  2. Under the Configuration section of the Console menu bar, click Grid Settings.
  3. Click the Maint tab.

    The Grid Settings (Maint) page displays.

  4. Under System Maintenance Settings section of the page, you can specify the time in Database Maintenance Time field to indicate when old database records must be removed from the database.

    All partitioned data are always retained based on the retention periods you specified under the section of the page.

  5. Scroll down to the Large System Settings section of the page, and ensure that the following options are set:

    • Enable Record Partitioning - Check this box to enable record partitioning in your database. Partitioning is required if you have many jobs per day or if you want to extend the time to keep Job Summary data.

    • Partition Size - Specify a time between different partitions.

      Increasing the period means that the database contains more data for overall analysis, but also increases the system impact of removing job records.

  6. Click Save to save your database record partitioning settings.

Results

The partitioned tables have a numerical suffix (_v000, _v001, … _vnnn). The newest table has the largest number.

Back up and restore the RTM system settings

RTM saves system and graph settings in the Cacti database. You can use the RTM Console to back up operations of the Cacti database so that you can restore the RTM system later. Having the backups means that if you changed the configuration in your RTM host that you would like to revert (for example, if you incorrectly imported a Cacti template and your graphs no longer update, or you accidentally removed a cluster from the RTM Console), you can use the RTM Console to restore the Cacti database, which restores the RTM system and graph settings and revert to your original, backed-up settings.

Restriction:

The RTM Console cannot restore all of the job performance data of the clusters that RTM monitors. Any such data still in RTM is likely out of date.

If you want to fully back up the entire database, you must use the mysqldump command. In addition, you cannot use the MyISAM table structure if you use the mysqldump command regularly. For more information, refer to your database documentation.

The following is a list of actions you can take to back up or restore the RTM system and graph settings through the RTM Console:

The following files are backed up:
  • lsfpollerd.conf: Database file that contains the credentials
  • lsf.conf: The lsf.conf file that is associated with each cluster.
  • ego.conf (for LSF 8.0 clusters only): The ego.conf file that is associated with each cluster.
  • server.key and server.crt: These files are used as certification to httpd for advocate.
All tables in the Cacti database except the following list are backed up:
  • GRID_ARRAYS
  • GRID_ARRAYS_FINISHED
  • GRID_JOB_DAILY_STATS
  • GRID_JOB_INTERVAL_STATS
  • GRID_JOBS
  • GRID_JOBS_FINISHED
  • GRID_JOBS_JOBHOSTS
  • GRID_JOBS_JOBHOSTS_FINISHED
  • GRID_JOBS_MEMPERF
  • GRID_JOBS_PENDREASONS
  • GRID_JOBS_PENDREASONS_FINISHED
  • GRID_JOBS_REQHOSTS
  • GRID_JOBS_REQHOSTS_FINISHED
  • GRID_JOBS_RUSAGE
  • LIC_DAILY_STATS
  • LIC_FLEXLM_LOG
  • LIC_INTERVAL_STATS
  • LIC_LUM_EVENTS
  • POLLER_OUTPUT
  • POLLER_OUTPUT_BOOST
  • SYSLOG
  • SYSLOG_REMOVED

Back up the RTM system using the RTM Console

About this task

Before you upgrade to a newer version of RTM, you must first back up the existing Cacti database.

It is important to back up the Cacti database during scheduled server maintenance and you can revert to previous RTM system and graph settings.

Procedure

  1. Click the Console tab.
  2. Under the Configuration section of the Console menu bar, click Grid Settings.
  3. Click the Maint tab.
  4. Scroll down to the Database Backups section of the page, and ensure that the following options are set (if some of them are not needed, you can leave them blank):
    • Backup Cacti Database - Check this box to ensure that the Cacti database is backed up when the maintenance script runs.
    • Backup Schedule - Specify backup schedule as Daily or Weekly
    • Weekly Backup Day - If you chose Weekly backup, specify the day that you want the backup to take place
    • Backup Generations - Specify the number of backup file copies you want to maintain
    • Database Backup Location - If a backup directory does not yet exist, provide a location. (If the directory exists, the message “[OK: DIR FOUND]” displays under the directory field.)
    • Post Backup Command - Provide the post backup script to be run after the Cacti database and the scheduled backup are completed. Ensure that the file is both executable and readable by the RTM service account.
  5. Under the Utilities section of the Console menu bar, click Grid Utilities.
  6. In the Database Administration section of the Grid Utilities page, click Force Cacti Backup.

    The Backup Files table at the bottom of the page updates with the newly created backup file, modification date, and file size.

  7. Click the backup file name to download it to a specified location.
  8. Verify that the .tgz file contains the following files after the download is complete:
    • cacti_db_backup.sql
    • cacti_db_struct_backup.sql
    • rtm/etc/rtm.lic
    • rtm/etc/lsfpollerd.conf
    • rtm/etc/server.crt
    • rtm/etc/server.key
    • rtm/etc/cluster_id/lsf.conf
    • rtm/etc/cluster_id/ego.conf (for LSF 8.0 clusters only)

    After all files are successfully verified and backed up, you can upgrade to a new RTM version or perform server maintenance without fear of losing or corrupting your existing database.

Enable automatic data archiving

About this task

Enable data archiving to save existing job and job-related data to an archive database during scheduled server maintenance.

Procedure

  1. Click the Console tab.
  2. Under the Configuration section of the Console menu bar, click Grid Settings.
  3. Click the Archiving tab.
  4. To enable data archiving of existing job and job-related data, select the Enable Data Archiving box and specify the data archiving settings.
  5. Click Save to save your data archiving settings.

Restore the RTM system using the RTM Console

About this task

After you complete any scheduled server maintenance, or finish upgrading your RTM version, you must restore the Cacti database that you previously backed up.

You can also restore the Cacti database to revert to a previous RTM system and graph setting.

Procedure

  1. Click the Console tab.
  2. Under the Configuration section of the Console menu bar, click Grid Settings.
  3. Click the Maint tab.
  4. Scroll down to the Database Backups section of the page, and find the Database Restore option.
  5. Browse to the location of your previously backed-up database, and then click Save to upload and restore the .tgz or .zip backup file.

    If the file is successfully restored, the message “Save successful” displays.

Manually back up or restore the database RTM

For later versions, all components are backed-up and packed into a .tgz file by the RTM Console. However, previous versions of RTM require that you manually back up your database. The following steps describe the manual backup and upgrade process:

  1. Back up the website
  2. Back up the database
  3. Back up the RTM poller
  4. Upgrade the Cacti website and grid plug-in
  5. Upgrade the database
  6. Upgrade the RTM poller

Back up the website

It is important to create a backup whenever you upgrade Cacti. You must back up all directories except the log and rra directories. If you are not concerned about the size of the backup, you can continue to back up those directories though. The following is a command example:
cd /var/www/html
tar --exclude=*.rrd --exclude=*.log -cf cacti_backup.tar cacti
If your RTM upgrade does not involve the Cacti product, you can greatly speed and simplify your backup by using the following command:
cd /var/www/html/plugins
tar -exclude=*.rrd -cf grid_backup.tar grid

Back up the database

RTM provides a utility for backing up your RTM database. When used, it backs up your database schema and important Cacti and RTM tables to disk. It does not back up some rather large tables in RTM because of their size and purpose. The Cacti/RTM tables that are not backed up include:
  • GRID_ARRAYS
  • GRID_ARRAYS_FINISHED
  • GRID_JOB_DAILY_STATS
  • GRID_JOB_INTERVAL_STATS
  • GRID_JOBS
  • GRID_JOBS_FINISHED
  • GRID_JOBS_JOBHOSTS
  • GRID_JOBS_JOBHOSTS_FINISHED
  • GRID_JOBS_MEMPERF
  • GRID_JOBS_PENDREASONS
  • GRID_JOBS_PENDREASONS_FINISHED
  • GRID_JOBS_REQHOSTS
  • GRID_JOBS_REQHOSTS_FINISHED
  • GRID_JOBS_RUSAGE
  • LIC_DAILY_STATS
  • LIC_FLEXLM_LOG
  • LIC_INTERVAL_STATS
  • LIC_LUM_EVENTS
  • POLLER_OUTPUT
  • POLLER_OUTPUT_BOOST
  • SYSLOG
  • SYSLOG_REMOVED
Note:

SSH private key pair files for grid cluster control authentication are not backed up; the backup process backs up only the file path to the key file.

Even though the contents of the tables are not backed up, their structure is. To run the database backup, you must first specify a backup location under Grid Settings > Maint (in the Configuration section of the Console menu bar). After you verify that the backup is complete, open a shell and do the following steps:
cd /var/www/html/cacti/plugins/grid
php -q database_backup.php

Running that utility creates three backup files: the database itself, the Cacti/RTM Schema, and Critical RTM Tables. These backup files can be used in the case of a disaster to restore full function to the system.

Back up the RTM poller

To support the ever growing list of features and to provide for bug fixes, the IBM Spectrum LSF RTM pollers must be upgraded from time to time. Therefore, before you upgrade IBM Spectrum LSF RTM, first make a backup copy of your IBM Spectrum LSF RTM pollers.

To back up your pollers, run the following commands on each poller, or back up one poller and distribute the changes to all other pollers.
cd /opt/rtmtar -cf grid_poller_backup.tar bin lic lsf8 lsf91 lsf101

Upgrade the Cacti website and grid plug-in

To upgrade the Cacti website, you must pay particular attention to a few points:
  1. You must make a backup copy of your <path_to_cacti>/include/global.php file. This file is important as it contains your database connect string and your list of installed plug-ins.
  2. You must know whether you modified the default RTM poller scripts. If you modified them, you must make a separate backup of your <path_to_cacti>/scripts directory. If you added new scripts that are not a part of the standard RTM deployment, it is not necessary to back up this directory.
  3. If you modified default RTM Data Queries, you must back up your <path_to_cacti>/resource directories. Again, if you did not modify the default installation of RTM Data Queries, it is not necessary to proceed with this step.

After completing these steps, note the ownership of all files in RTM. The RTM installer prefers that the cacti user account own the Apache site, and that the Apache binaries be run by the same. If so, then all files in the /var/www/html/cacti directory and subdirectories are “cacti:cacti” regardless of file ownership, not the ownership, and permissions.

When you receive your RTM upgrade, there are two directories in the upgrade path. They include both the “cacti” and “poller” directories. To upgrade the Cacti Website and Grid Plug-in, run the following command:
cp -rp <upgrade_path>/cacti /var/www/html
chown -R cacti:cacti /var/www/html

Verify that your config.php remains unchanged. Then, refresh your browser that is already pointed to the RTM website.

Upgrade the database

After you upgrade your Cacti Website and Grid Plug-in, you must run a Database Upgrade script. As RTM matures, several changes are made to the RTM tables to accommodate new features.

Note:

Larger tables such as the grid_jobs and grid_jobs_rusage do not typically undergo structural changes. If one of these larger tables were to require modification, you are informed up-front to advise you of the downtime that might be required to upgrade any of those tables.

To upgrade your database schema, run the following commands from a shell:
cd /var/www/html/cacti/plugins/grid
php -q database_upgrade.php

Upgrade the RTM poller

  1. Verify the permissions on all file objects within the RTM poller directory.

    They are typically owned by “cacti:cacti”. If you chose another method, note those file permissions.

  2. Run the following commands to upgrade the pollers:
    cp -rp <upgrade/_path>/poller /usr/local
chown -R cacti:cacti /usr/local/grid