Sizing guidelines for main and temporary stores

Several changes in Sybase IQ 15.1 architecture affect 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 TLV log. If a node is down, the multiplex needs to store versions in order 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 preallocated free list space and not available for user data.
Because Sybase IQ 15.1 performs more operations in parallel, it uses more temporary space than previous releases.

Three factors influence the space required for the IQ_SYSTEM_MAIN store:

**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.

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

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"