Sizing Guidelines for Main and Temporary Stores

The Sybase IQ architecture influences guidelines for data storage.

The IQ_SYSTEM_MAIN dbspace holds all of the database metadata other than IQ table metadata. IQ table metadata is stored in the table's dbspace and the table version (TLV) log. If a node is down, the multiplex must store versions to synchronize them when the node comes back up. These versions may use large amounts of space.
Approximately 20 percent of the IQ_SYSTEM_MAIN dbspace is now used for pre-allocated free list space and not available for user data.
Because this version of Sybase IQ performs more operations in parallel, it uses more temporary space than earlier versions.

Three factors influence the space required for the IQ_SYSTEM_MAIN store:

Versioning – the volume of versions maintained varies.
Nature of data and indexes.
Dynamic nature of the data – the capacity to load more data at any time.

While Sybase can offer general guidelines, the combination of these factors makes each database’s requirements unique. For a development or report server with a total size under 500GB, an IQ_SYSTEM_MAIN file of 10 to 20GB may suffice.

Size guidelines for IQ_SYSTEM_MAIN and IQ_SYSTEM_TEMP in production databases
Task	Guideline	Notes
Loading empty schema from iqunload -n output or for a small test database	10GB main, 5GB temporary	CREATE DATABASE sizes are in MB. The server must be at 12.7 ESD #5 or higher to use iqunload -n.
Creating new production database	If you are migrating a database, and use a raw device for your current IQ_SYSTEM_MAIN, assign a new unused raw device of your standard size. Total size of IQ_SYSTEM_MAIN should be at least 1/100 total database size, with a minimum 100GB main and 100GB reserve. If using raw disks for IQ_SYSTEM_MAIN, use multiple raw disks whenever possible. Multiple raw disks enable Sybase IQ to stripe the data across devices, which improves performance. For IQ dbspaces in production, Sybase recommends that you use a fault-tolerant file system implemented by a high-performance, redundant disk array (for example, RAID 5)	Omit ms_size if specifying a raw device. Always set the main reserve to 20 percent of IQ_SYSTEM_MAIN size. To set the main reserve, use the database option `MAIN_RESERVED_DBSPACE_MB`.
Creating main store for a multiplex	Double the space recommended for a simplex database, or at least 200GB main and 200GB reserve dbspace.

Example 1

In CREATE DATABASE syntax, default size units are in MB, not GB.

The following statement creates a database with 100GB IQ_SYSTEM_MAIN with 100GB reserve (for future expansion):

CREATE DATABASE 'test.db'
IQ PATH 'test.iq'
IQ SIZE 100000
IQ RESERVE 100000
TEMPORARY PATH 'test.iqtmp'
TEMPORARY SIZE 5000

Example 2

MAIN_RESERVED_DBSPACE_MB lets you control the amount of space Sybase IQ sets aside in your IQ main store for certain small but critical data structures used during release savepoint, commit, and checkpoint operations.

Sybase recommends that you set the MAIN_RESERVED_DBSPACE_MB option value to 20 percent of the IQ_SYSTEM_MAIN SIZE. For example, if IQ_SYSTEM_MAIN is 100GB, set it to 20GB, as follows:

SET OPTION PUBLIC.MAIN_RESERVED_DBSPACE_MB = 20000

Example 3

You can specify the IQ_SYSTEM_MAIN size in the database migration command.

The -ms_size parameter requires a value in MB, not GB. Omit -ms_size if specifying a raw device. For a raw device, you must specify an unused raw partition. For more about migration, see the Installation and Configuration Guide.

This statement creates an IQ_SYSTEM_MAIN on a raw device on UNIX:

iqunload -au -ms_filename /dev/rdsk/c1t0d1 -c "UID=DBA;PWD=SQL;DBF=latest.db"

This statement creates an IQ_SYSTEM_MAIN on a raw device on Windows:

iqunload -au -ms_filename \\\\.\\PhysicalDrive1 -c "UID=DBA;PWD=SQL;DBF=latest.db"