DGE Database Maintenance On Windows

The DGE databases are located in the directory <TRAVERSE_HOME>\database\mysql\<DB_NAME> with each table for that database represented by a file with the extension .MYD. For the historical performance data collected by the DGE, the <DB_NAME> is aggregateddatadb. For the BVE there is also a database named liveeventsdb, which contains deduplicated events.

Traverse comes with a utility to create a fast snapshot by locking all the databases and then directly saving the raw databases.

Backing up the DGE Database

To back up the DGE database on Windows, you can manually create a backup snapshot with the following commands:

C:

cd <TRAVERSE_HOME>

utils\db_backup.cmd

This creates a new snapshot named <TRAVERSE_HOME>\database\mysql\backup_<DB_NAME> for each of the available databases.

Restoring the DGE Database

To restore the DGE database from a snapshot created by db_backup.cmd, you must restore the .MYD and .FRM files from <TRAVERSE_HOME>\database\mysql\backup_<DB_NAME>, and then rebuild the database indexes by performing the following steps:

1. Shut down all components using the Traverse Service Controller.
2. At a command prompt, execute the following commands, replacing <DB_NAME> with the name of the database you are restoring::

C:

cd <TRAVERSE_HOME>

move database\mysql\<DB_NAME> database\mysql\saved_<DB_NAME>

xcopy /E database\mysql\backup_<DB_NAME> database\mysql\<DB_NAME>\

net start nvdgedb

apps\mysql\bin\mysql --defaults-file=etc\mysql.conf --execute="SHOW TABLES" --database="<DB_NAME>" > tables.txt

FOR /F "skip=1" %G IN (tables.txt) DO @apps\mysql\bin\mysql --defaults-file=etc\mysql.conf --execute="REPAIR TABLE %G USE_FRM" <DB_NAME> >> logs\database_restore.log

Repairing the MySQL DGE Database

Occasionally, because of an unexpected shutdown or a process such as an antivirus program scanning the database directories, database tables might become corrupt. To repair the DGE database tables, perform the following steps:

1. Shut down all components using the Traverse Service Controller.
2. Execute the following commands to rebuild the indexes:

C:

cd <TRAVERSE_HOME>

utils\db_repair.cmd

If the db_repair.cmd script cannot be executed or fails to repair the database (because the extent of damage is too severe), you can manually repair the database as follows:

C:

cd <TRAVERSE_HOME>

del /f database\mysql\aggregateddatadb\*.TMD

for %f in (database\mysql\aggregateddatadb\*.MYI) do apps\mysql\bin\myisamchk --defaults-file=etc\mysql.conf -r %f

If the -r option fails to repair the database tables, try using the -o option to perform a slower but more effective repair method on the affected tables.

Optimizing the MySQL DGE Database Indexes

In some cases, the database indexes for MySQL can become inefficient and can benefit from some optimization. Generally, this is not needed. However, if the database performance suffers and it is not caused by slow disk I/O, lack of memory, or other typical causes, performing an index optimization can improve performance. To optimize the indexes, perform the following steps:

1. Shut down all components using the Traverse Service Controller.
2. Execute the following commands:

C:

cd <TRAVERSE_HOME>

utils\db_optimize.cmd