Sizing guidelines for main and temporary stores

Several changes in Sybase IQ 15.1 architecture affect data storage:

Three factors influence the space required for the IQ_SYSTEM_MAIN store:

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. For a production database, see Table 5-1 for size guidelines.

Table 5-1: 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.

  • Only use file system files for IQ dbspaces in production if the file system is fault-tolerant and implemented by a high-performance, redundant disk array (for example, RAID 5). For single-server systems, you can use a local file system, but multiplex systems require a cluster file system, ideally on a Storage Area Network device.

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.

Examples

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. See “IQ main store and IQ temporary store space management” for more about MAIN_RESERVED_DBSPACE_MB. 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.

For example, this statement creates an IQ_SYSTEM_MAIN on a raw device:

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

For example, this statement creates an IQ_SYSTEM_MAIN on a raw device:

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