Optimizing the Accelerator Database

The Accelerator database needs to be optimized to ensure the fastest execution paths are used for queries, which also has an impact on the amount of memory used for queries. The database vendor recommends that you optimize a table when the data has changed by 10%. After the initial optimization of the database, and taking the database vendors recommendation into consideration, we would recommend optimization on the database is performed weekly, for each schema. For high volume databases, the optimization will need to be performed more frequently.

By default, there are two schemas in the database, one for the replication data and one for Hubble. If you have created or are going to create new users/schemas for additional data, these will need to be added to the optimization tasks. Optimizing can be run at table level or schema level. See below for the instructions for both operations.

To use the optimizedb command as demonstrated in the following topics you must be logged on as the actian or root user.

To optimize an individual table

Log on to the Accelerator server and run the following command:

optimizedb -u<DB_UserName> <Database_Name> -r<TableName>

For example, if the default database is db, to optimize the f0911 table in the replication schema run the following:

optimizedb -ureplication -zfq db -rf0911

To optimize all the tables in a schema

Log on to the Accelerator server and run the following command:

optimizedb -u<DB_UserName> -zfq <Database_Name>

For example, if the default database is db, to optimize all the tables in the replication schema run the following:

optimizedb -ureplication -zfq db

Example Output from optimizedb

Following is an example of the output that you would expect to see from running optimizedb:

Typically executions will be silent, returning the user to the command prompt when optimization has completed. However, you will be notified of any tables that contain no rows.

Scheduling Optimization

Cron can be used to schedule the optimization.