Setting Up Sybase IQ Database on Raw Devices

I’ve been working with Sybase IQ for quite a while (well SAP IQ now, but I still have a hard time calling it that!) and as an Analytic Database it can be very powerful and technically rewarding, but also a challenge when you move beyond some of the defaults. For example, one way to increase production performance of the database is to start with raw partitions.

There is not a ton of information on this topic and as I have just completed this exercise for a healthcare client, and it’s not something you do every day, I thought I’d capture the process here:

We typically deploy to standard operating system files in development, but a production environment can benefit from using raw partition installation for increased processing performance and better recovery capabilities. However, this production set-up requires a bit more work to install successfully.

As you may know, Sybase IQ is a Columnar Database that has a huge benefit for Business Intelligence query processing. It uses index-only access plans to process only the data needed to satisfy any type of query requirement and the entire Sybase IQ database and indexing is stored in less space than raw data. One advantage of using raw devices is that they do not compete for the SMP memory like the file system devices. Raw Devices I/O operations are much cheaper as they bypasses the file system overhead. Sybase IQ database comes with the database buffer cache manager and doesn’t need the OS’s buffer cache. So, raw devices does the buffer cache only once whereas the file system does the same thing twice. As raw devices bypasses the I/O to kernel space, we see twice better performance difference on most of the large databases. OK, but how do we implement that with IQ? I’ll go through a Linux install process…

Business Value

Overview of architecture: Sybase IQ saves a lot of time on maintenance and performance tuning when compared to the other RDMS software in the market as it is built intelligent enough to create indexes and retrieve only the data required. Creating the database files on the raw devices further maximizes the performance twice in terms of I/O operations. Raw devices are very cheap to set up and maintain when it comes to the large databases.

Main Store, Temp Store and user dbspaces are created on the raw devices. Catalog store, IQ message, transaction log usually resides on the database server. For production environments, transaction log and the mirror transaction log shouldn’t be on the same machine.

Implementation

1. Ensure the Sybase IQ system and the Sybase IQ account has access to the raw devices by using the command: ls –al /dev

2. Edit the /etc/raw file by adding the devices.

Ex: raw1:sdb1 raw2:sdc1 raw3:sdd1 etc…

3. Edit the /etc.init.d/after.local file by adding the following content

Mkdir /dev/raw 
Cd /dev/raw 
mknod raw1 c 162 1 
mknod raw2 c 162 2 
mknod raw3 c 162 3 
etc….(commands should be added depending on the number of raw devices) 
modprobe raw 
raw /dev/raw/raw1 /dev/sdb1 
raw /dev/raw/raw2 /dev/sdc1 
raw /dev/raw/raw3 /dev/sdd1 
#etc….(commands should be added depending on the number of raw devices) 

/etc/init.d/raw start 
chkconfig raw on 

#raw device permissions 
chown iq-operator:iq-management /dev/raw 
chown iq-operator:iq-management /dev/raw/raw1 
chown iq-operator:iq-management /dev/raw/raw2 
chown iq-operator:iq-management /dev/raw/raw3 
#etc….(commands should be added depending on the number of raw devices) 

chmod 660 /dev/raw 
chmod 660 /dev/raw/raw1 
chmod 660 /dev/raw/raw2 
chmod 660 /dev/raw/raw3 
#etc….(commands should be added depending on the number of raw devices)

4. Now, after the reboot, all the raw devices are configured and ready to use

5. Create the Soft links for the raw devices on the necessary database folders using the ln command

Eg: ln –s /dev/raw/raw1 RAWDEVICE01

6. These soft links will be used as the paths while creating the database, adding dbspace etc.,

7. Start the Sybase IQ Utility DB.

8. Create the database Using the create database statement.

9. For performance reasons, raw devices are used for IQ MAIN and TEMPORARY Stores.

10. Dbspace can be added on each raw device accordingly depending on the size of the database.

In the next installment of this blog I will show some before and after metrics to show the difference between file system and raw device installation.

The Kingfisher Advantage

Using a tried-and-true playbook, Consultancy by Kingfisher, Inc., has steered many successful projects in Sybase IQ, Data Services, Information Steward, upgrades and migrations to the latest BI4.2 technology. Included in this history of success is planning, execution, and support of .UNX universes.

Whether conversion from the legacy format, or creation of a new semantic layer from scratch, Kingfisher has the resources to lead another successful project.

For questions regarding a possible switch to newer technologies, please call (404) 531-4116.

See more Business Intelligence insights or get future articles sent right to your inbox

increase your knowledge
increase your knowledge

About the Author:

Mohan has been consulting for over 9 years in ETL Architecture, Data Warehousing, OLAP and BI Reporting tools. His extensive experience provides a strong business and technical experience with multiple BI and ETL tools and databases; honed in a variety of industries including Healthcare, Manufacturing, Pharmaceuticals, Logistics, and Finance.