Optimizing the MySQL database

This topic is for sites that use a MySQL database.

Optimize the Management Station database after each purge task using the nuance-optimize-ms-database script file.

After a purge task, the purged records, though no longer accessible, are still maintained in the database tables. By running the nuance-optimize-ms-database script, you can reclaim unused space and defragment the table data. Failure to optimize the database, especially in situations with heavy call volumes, could lead to a degradation in Management Station performance.

Note: Make sure you have enough free disk space on the Management Station before optimizing the database. This amount must be greater than the largest database table in the Management Station database, located in mysql_installation_path\mserver. On Linux, the default path is /var/lib/mysql/mserver.

When to optimize

Run nuance-optimize-ms-database during off-peak times and only on those tables you actually purged. Optimizing the Management Station database can take several hours for large amounts of data. During that time, the Management Station is offline and unable to receive alarms, collect vital signs, or control the Nuance network.

Always optimize the database after purging call log data. Typically, the purge/optimize frequency would be on a monthly basis. If you have heavy call volumes and need to purge more frequently, say every week, then run nuance-optimize-ms-database at the same frequency.

You can optimize the database on a monthly basis for vital signs and the task history, even if you purge these more frequently.

This table shows recommended frequencies and what option to use:

Data

Purge frequency

Optimize frequency

Option

Call logs

Monthly / weekly

Monthly /weekly

nuance-optimize-ms-database calllog

Task history

Monthly / weekly

Monthly

nuance-optimize-ms-database task-history

Vital signs

Monthly / weekly

Monthly

nuance-optimize-ms-database vital-signs

All tables

Monthly / weekly

Monthly / weekly

nuance-optimize-ms-database all

Using the script

Before running nuance-optimize-ms-database, Nuance recommends that you back up the Management Station database. See Backing up and restoring a database for details.

To optimize the Management Station database, open a command-prompt window and run nuance-optimize-ms-database from the %MSTATION_HOME%\scripts directory. The syntax is:

> nuance-optimize-ms-database [option]

Where option can be one of the following:

  • -h, -help, help—Describes valid options (this list)
  • alarms—Optimizes the alarms tables
  • basic—Optimizes the subset of the call log tables used by call analysis reports (default)
  • calllog—Optimizes all call log tables
  • clapi—Optimizes the CLAPI log tables (not applicable)
  • task-history—Optimizes the task history tables
  • vital-signs—Optimizes the vital-signs tables
  • all—Optimizes tall tables in the database (use this option to reclaim unused disk space)

To ensure that no data is written to tables while optimization is taking place, the nuance-optimize-ms-database script:

  • Stops the Nuance Management Station and Nuance Management Station Data Collection services
  • Locks the appropriate database tables

When finished, the script starts the Nuance Management Station and Nuance Management Station Data Collection services.