Uniprint Technote: Secure Release Service SQLite Database

Overview

Starting from Uniprint 8.3 Hotfix Revision 411 and Uniprint 8.4, performance enhancement has been added to the Secure Release Service to store a copy of the SQLite job database in memory, providing significant increase in job processing and job information access, and reducing the impact of unrelated environmental issues such as disk I/O pressure.

This document explains the in-memory SQLite database implementation for Uniprint, including backup strategies and related registry settings.

In-memory SQLite Database

In addition to the Pharos Database, which is used to store configuration, user, costing and activity data, Uniprint includes a Secure Release Service SQLite database file to store individual records for each print job submitted to Uniprint.

In previous versions, the SQLite database file was held on disk. This implementation was fine for environments under small load. However, it had the potential to present performance issues in certain environments processing large volumes of print jobs. High disk I/O can adversely affect the overall performance of Uniprint. Users may experience delays when submitting or releasing their print jobs from Terminals or iMFPs.

Beginning with Uniprint 8.4, a copy of the SQLite database is now held in-memory by default instead of on disk. Keeping the SQLite Database in-memory will significantly improve the overall performance of the Uniprint system. In-memory mode allows for faster queries to the SQLite database than disk-mode. It will also reduce disk I/O contention, which causes delays when querying the Secure Release Service SQLite database. Pharos in-house testing indicated that an in-memory SQLite database can process between 4 to 5 times as many jobs compared to the same system using SQLite database on disk mode.

Switching to disk mode SQLite Database

It is possible to switch between in-memory and disk mode, and back again if desired. You can also configure when to write back changes to disk. Both actions require changing the Uniprint registry settings. For more information, refer to the “Changing the Registry Settings” section on page 3.

Vacuuming the SQLite Database

Uniprint has also been updated to execute the ‘vacuum’ command when the SQLite database is in in-memory mode. This occurs whenever the SQLite database is loaded and saved back to disk. Vacuuming never occurs when operating on disk mode.

The ‘vacuum’ command removes spaces left behind by ‘Delete’ and ‘Update’ SQL statements and reclaims them for reuse, thus reducing the size of the SQLite job database. It also brings index information that is spread across the database file together so that it is contiguous (i.e. brings data closer together). Reduced database size and contiguous database index information make read and write operations faster, further improving performance.

For more information about the ‘vacuum’ command, refer to the SQLite website

Persistence Strategies for the in-memory SQLite Database

As with any memory based storage, there is the potential risk of losing data in the case of a power failure, an operating system failure, etc. To prevent data loss in such events, the SQL Lite database is regularly backed up to disk after every 5,000 updates or after 5 minutes provided at least one change has been made.

The backup to disk mechanism used by Uniprint includes a number of protective measures to mitigate system failures. These include:

  • When writing the in-memory SQLite Database back to disk, the Secure Release Service first makes a second copy of the database in memory. This way, one copy gets backed up to disk while the other memory copy can continue being used. This helps protect against being affected by delays in writing back to disk.
  • When saving to disk, the in-memory copy is first saved as a time-stamped database file, and then a series of file moves are done to replace the default SQLite database file with the updated copy. This helps protect the integrity of files until the service is sure the update has been successful.
  • If backup to disk is slow for some unknown reason and takes longer than the defined period between backups, the Secure Release Service will wait for the current one to complete before attempting the next.

Additionally, while it's expected that the backup to disk is highly unlikely to fail, additional handling has been added for further robustness:

  • Unable to update main SQLite database file: If the existing database file cannot be updated (for any reason), the timestamped file first created as part of the backup will be retained. On restart of the service, the Secure Release Service will always load the newest file, so it will always get the most up to date job list. A system Event Log error would be raised.
  • Unable to save to disk: If the Secure Release Service cannot write the database back to disk at all for some reason, it will retry every 60 seconds. Failure also results in an Event Log error being raised.

Enhanced disk mode access

Disk mode access has also been enhanced in this version. Uniprint 8.3 Hotfix Revision 411 and Uniprint 8.4 now use version 3.7 of SQLite, which offers “Write-Ahead Log” (WAL) mode. WAL mode allows multiple read/write access; it does not let “readers” block “writers” and vice versa. Previously, only one “reader” or “writer” can access the SQLite database at any given time.

For more information about WAL mode, please refer to the SQLite database website.

Changing the Registry Settings

These settings are applied to Uniprint by default. If you want to change the default values, you can add the following registry entries for the SQLite Database at:

HKEY_LOCAL_MACHINE\SOFTWARE\PharosSystems\SecureRelease\Database.