Installation guidelines IQ 15.0 and 15.1

This page describes how to install and configure a Sybase IQ server, version 15.0 and 15.1

Get the software

First download IQ.

For a licensed version you need to go http://sybase.subscribenet.com (account required) or to the Sybase eShop http://eshop.sybase.com/eshop/buy?id=48151

Alternatively, you can download an EBF for Sybase IQ 15 at http://downloads.sybase.com (registration required). Then click on “Sybase IQ” and download an EBF/Patch, for instance EBF16926. With an EBF/Patch you can install a full version as well.

Prepare the Operating System

Make sure that the filesystem for the Sybase software is big enough. A full installation needs approx. 850 Mb so 2 Gb should give you enough room to handle upgrades too. In this case we install the software in /opt/sybase/iq150. Database files will be stored on raw devices and in /var/sybase.

Become root and add a group "sybase" and a user "sybase" to the system. Also create the needed directories.

mkdir -p /opt/sybase
mkdir -p /var/sybase
groupadd sybase
useradd -g sybase -d /opt/sybase sybase
chown sybase:sybase /opt/sybase
chown sybase:sybase /var/sybase

Check that your operating system has the required patches and configuration settings. Look in the installation documentation at http://infocenter.sybase.com/help/topic/com.sybase.help.infocenter.iq.15.0/title.htm for detailed instructions. When you run the IQ installation, it will also check for some required packages.

Prepare the Installation

Now, switch to the newly created user:

su - sybase

You should now be in the directory /opt/sybase. Make a work directory and put the downloaded Sybase software in it.

mkdir work
mkdir iq150
cd work
<put the software in this directory>
tar -xf <software-distro>

Installation

Go into the directory with the installed software. Run the installer (for instance ./setupLinuxAMD64).

./setupLinuxAMD64

Answer the questions and install into /opt/sybase/iq150. You can select what license type you need, Licensed or Evaluation. For the licenced version you need an account at http://sybase.subscribenet.com. The evaluation license allows you to run IQ with all its features for 30 days.

To use the installar on Unix you may need to set the DISPLAY variable.

Once the installation is complete you can remove the work directory and its content.

cd ..
rm -Rf work

Setup your environment

In /opt/sybase/iq150/IQ-15_0 you will find a few files, like IQ-15_0.csh and IQ-15_0.sh. Depending on the type of shell you use, you need to source in one of these. For instance, when your default shell is bash you should do this:

. /opt/sybase/iq150/IQ-15_0/IQ-15_0.sh

Is is fairly common to activate such a script from your login script.

Create raw devices

For performance reasons you should use raw devices for the IQ Main and Temporary store. For testing purposes use 2Gb for the Main store and 1 Gb for the Temporary store. You can always add more when needed. Do not forget to chown the raw devices to the sybase user. Check if this setting is persistent after a reboot. Note: contrary to Sybase ASE behaviour, Sybase IQ will only accept raw devices and not "block special". Check the filetype with the "file" command. When needed, bind a block special device to a raw device with the "raw" command.

Start the IQ server (utility database only)

Before you can create a database the IQ server must be started with only the utility database active. Use the start_iq command, but specify the name of server as well using the -n flag. In this example the Sybase recommendation is followed to distinguish the name of the IQ server from the name of the database. For the IQ server IQ2_srv is used, and for the database IQ2_db.

start_iq -n IQ2_srv

Create a database

Create a directory where server and database specific files will be stored

mkdir /var/sybase/IQ2_srv

Create a connection to the IQ server with dbisql (Windows look and feel) or dbisqlc (very basic gui). Specify DBA as the user ID, use “sql” (lower case) as the password and “utility_db” as the name of the database. Once connected you can create a database. The create database command has several option that can have a big impact on performance. See notes below or study the Sybase documentation to get the best result. Example:

create database '/var/sybase/IQ2_srv/IQ2_db.db'
transaction log on '/var/sybase/IQ2_srv/IQ2_db.log'
mirror '/var/sybase/IQ2_srv/IQ2_db_mirror.log'
iq path '/var/sybase/IQ2_srv/IQ2_db_01.iq'
iq size 2000
message path '/var/sybase/IQ2_srv/IQ2_db.iqmsg'
temporary path '/var/sybase/IQ2_srv/IQ2_db_01.iqtmp'
temporary size 1000

When the database has been successfully created, exit from dbisql or dbisqlc. Then shutdown the IQ server.

stop_iq

Notes for the "create database" command.

• Sybase recommends to create the IQ_SYSTEM_MAIN with at least 100Gb for any serious production databases. For development servers you can use approx. 5 % of total expected IQ storage. The size can be specified with the "iq size" clause.
• The default page size for IQ files (.iq and .iqtmp files) is 128 Kb. When a single table in the database is expected to grow above 8 Tb or will have more than 4 billion rows choose the next bigger IQ page size of 256Kb.
• When creating a database or a dbspace you can ommit the fully specified path to the filename. When you use filenames relative to the IQ server it is more easy to move a database to another server.
• When the iq stores are created on raw devices and those raw devices are located on a disk array (as will be the case most of the time) then choose the next greater block size than the default value. When the IQ stores are on filesystem check the I/O size of the filesystem and choose that block size. A non-default block size can improve performance at the expense of disk space. The block size can be specified with the "block size" clause.
• When using raw devices do not use the clause "iq size" and/or "temporary size". IQ will determine the size of the raw device and use it completely.
• When the database is created and you know that it will grow after some time, add the “iq reserve” clause to the “create database” command. This will create a contiguous free list that is already large enough for the future size of the database and so prevents a fragmented free list. Do the same when the user dbspace is created (see below). Then add the “reserve” clause. Not needed for the temporary store.
• Be carefull when using a non-default page size for the catalog store (.db file). It can lead to inefficient memory use.

Create a configuration file

In the directory /var/sybase/IQ2_srv create a configuration file called params.cfg with at least the following content

-n IQ2_srv
-iqmc 60
-iqtc 140
-x tcpip{port=4000}

This configuration file sets the name of the server, the main cache size to 60Mb, the temp cache size to 140Mb and a port number for TCP/IP traffic. Specify as much memory as you can but leave a safe margin for other processes. Sybase recommends to keep the ration between main and temp cache 3:7. Then start the database

start_iq @/var/sybase/IQ2_srv/params.cfg /var/sybase/IQ2_srv/IQ2_db.db

Create a user dbspace

Create a user space where all tables for the application should be created. Example:

create dbspace user_main
using file user_main_1 '/var/sybase/IQ2_srv/user_main_1.iq'
size 10000 MB
iq store;

set option public.default_dbspace = ‘user_main‘;
grant create on user_main to public;
revoke create on iq_system_main from public;

When the user db space becomes to small, additional space can be added by running this:

alter dbspace user_main
add file user_main_x '/var/sybase/IQ2_srv/user_main_2.iq'
size 10000 MB iq store

Try to keep all the files in a user dbspace the same size to achieve a situation that all files are more or less filled at the same capacity. Also, use a reseonable set of files to achive parallellism.

(Optional) Configure striping

For performance reasons it is best to have IQ handle the striped writes rather than a SAN storage system. Doing striped writes from both sides (SAN and IQ) may be counter productive. When you want to do striping within IQ you should prepare the disk layout for it.

IQ Stripe sizes as suggested below are based on the expected write activity to the various IQ stores.

IQ_SYSTEM_MAIN - 32 Kb

IQ_SYSTEM_TEMP - 64 Kb

User defined dbspace - 128 Kb

When the main store has been created striping is on by default but the stripesize is 1 Kb. The IQ main store will not store any user data but only IQ internal information and therefore the amount of data to be written will be far less when compared to iq system temp or a user dbspace.

alter dbspace IQ_SYSTEM_MAIN
striping on
stripesizekb 32

This setting may not have any effect when the IQ store consists of 1 file. Hoewever, when an additional file is added to the main store striping will kick in.

alter dbspace IQ_SYSTEM_TEMP
striping on
stripesizekb 64

Do the same for the user_main db space.

alter dbspace user_main
striping on
stripesizekb 128

Set database options

set option PUBLIC.APPEND_LOAD=’ON’;
set option PUBLIC.FORCE_NO_SCROLL_CURSORS = 'ON';
set option PUBLIC.QUERY_PLAN = 'OFF';
set option PUBLIC.QUERY_DETAIL = 'OFF';
set option PUBLIC.QUERY_TIMING = 'OFF';
set option PUBLIC.QUERY_TEMP_SPACE_LIMIT=5000; -- Value in Mb, recommended value is 1/4 of the total size of the temporary store.
set option PUBLIC.MAIN_RESERVED_DBSPACE_MB=500; -- Recommended value is 20% of the size of IQ_SYSTEM_MAIN.
set option PUBLIC.TEMP_RESERVED_DBSPACE_MB=500;
set option PUBLIC.LOAD_MEMORY_MB=100;

When the IQ stores are on filesystem (not on raw devices) you can set the option OS_FILE_CACHE_BUFFERING to off. Example (IQ reboot required to activate):

set option PUBLIC.OS_FILE_CACHE_BUFFERING = 'OFF';

Notes:

• QUERY_TEMP_SPACE_LIMIT sets the amount of temporary store space that an individual query is allowed to use in the temporary store, for storing intermediate results and for sort operations. This value can be set higher than 5000 if enough temporary store is available. It is not recommended to set this value to 0 (= “unlimited”) because in that case carthesian products can fill the temporary store completely, which will prevent other queries to run.
• In IQ 15 a new option MAX_TEMP_SPACE_PER_CONNECTION has been introduced. When a single connection starts up more queries at the same time use this option.

How to extend the size of the temporary store

Since IQ 15 there can be only 1 dbspace for the temporary store. To increase the size of the IQ temporary store you need to alter the dbspace and add a file to it. Example:

alter dbspace IQ_SYSTEM_TEMP
add file temp_02 '/var/sybase/IQ2_srv/IQ2_db_02.iqtmp'
size 2000 MB ;

Try to keep all the files the same size to achieve a situation that all files are more or less filled at the same capacity. Also, use a reseonable set of files to achive parallellism.

Note: If raw volumes are used for Main/Temporary store, then specify the full paths to the raw volumes and omit the IQ SIZE and TEMPORARY SIZE clauses. The full raw volumes will automatically be used.

Notes

• In this example the mirror of the IQ transaction log is on the same physical device at the transaction log itself. Do not create such a situation on a production system.
• The Sybase IQ logfiles are by default created in the directory $IQDIR15/logfiles. Since the logfiles can become very big, its best to set the variable IQLOGDIR15 to /var/sybase/IQ2_srv before starting the IQ server.