Accelerator Database Maintenance Tasks

The Hubble Accelerator, hosts a columnar database that is designed for high performance.

• Column Based Storage

• Vectorized Query Execution

• Data Compression

• Maximizing CPU cache for execution (Uses private CPU core and caches as execution memory)

Database Vendor: Actian Vector 5.0

Hubble Default Configuration:

• Database: db

• Database user created for use by the Hubble application: hubble

• Database user created for Replication: replication

To achieve consistent performance on the database you need to make sure the statistics on the database are up to date, failure to do so will lead to slower running queries and also has an impact on the amount of memory used on a query.

The instructions in this document, unless otherwise stated, are commands that need to be run on the underlying Hubble accelerator directly (Linux). We connect to the Linux server directly with PuTTY, so instructions like logon to the accelerator refer to connecting to the Linux server with a PuTTY session.

Optimizing the Accelerator Database

The Accelerator database needs to be optimized to ensure the fastest execution paths are used for queries, this also has an impact on the amount of memory used on the query. 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 is performed weekly on the database, for each schema. For high volume database the optimization would need to be performed more frequently. By default, we have 2 schemas on the database one for the replication data and one for Hubble. If you have or are going to create new users/schemas for additional data, then these will need to be included in the optimization tasks.

Optimizing can be run at a table level or schema level, the instructions are as follows for both operations.

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

Optimizing an Individual Table

Log onto the accelerator server and run the following command:

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

For example, the default database is db. If we wanted to optimize the f0911 table in the replication schema we would run the following:

optimizedb -ureplication -zfq db -rf0911

Optimizing 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, the default database is db. If we wanted to optimize all the tables in the replication schema we would run the following:

optimizedb -ureplication -zfq db

Example Output from optimizedb

The 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. You will however be notified of any tables that contain no rows.

Scheduling the Running of Optimization

If you have deployed the Accelerator as a Docker container, then you can schedule the optimization using 'cron' or similar in your host server. Here is an example of the command you need the scheduler to run:

docker exec accelerator_hubble_accelerator_l sh -1 -c "source /etc/profile && /opt/Actian/VectorVW/ingres/bin/optimizedb -ureplication -zfq db"

This assumes that your scheduler service has permission to run the docker command, and that the user whose cron job it is has the docker command in its path. It also assumes that the schema you want to optimize is called 'replication'.

If you have deployed the accelerator using a VMWare OVA or AWS AMI image, then you can schedule optimization via another Linux server using 'cron' and 'sshpass' (for information on sshpass, see https:// sourceforge.net/projects/sshpass/ and https://linux.die.net/man/1/sshpass). However, with sshpass you must store the Accelerator login credentials in a plaintext file on that second server, so make sure that you secure that file properly. Public key authentication for SSH is not available with the accelerator.

Example command line using sshpass:

sshpass -f <password_file> ssh -t <accelerator_IP> 'source /etc/profile &&

/opt/Actian/VectorVW/ingres/bin/optimizedb -ureplication -zfq db

sysmod - Modifying the System Catalogs

As well as optimizing the Accelerator database, it is recommended that periodically that sysmod command is run. The sysmod command modifies the system catalogs of a database to their currently defined storage structure.

Doing so removes the overflow and deleted pages, which results in accelerating query processing.

To use the sysmod command as illustrated in the following sections you must be logged on as the actian or root user.

Running sysmod over the Hubble Database

The sysmod operation requires exclusive access to the database

To ensure you have exclusive access to the database, you will need to follow the instructions in the following order:

1. Shut down Replication.

2. Disable new Connections to the Accelerator Database.

3. Log on to the accelerator server and run the following command:

   sysmod <database>

   For example, the default database is db:

   sysmod db

4. Manual Accelerator Database Startup.

   The replication tasks can be restarted in two modes:

   • Resume: will read the source database log and resume reading the changes from where it left off.

   • Reload: will reload the data from the source database and then read the changes from the source database log.