Shared Disk Server and partitioned tables

I have a 250 gig database with an ugly mix of OLTP and OLAP processes. The ugly sales comparision queries do a HUGE amount of summing, comparing, calculating on the fly. They are very CPU intensive. The CPU can be pegged at 100% for hours while the disk utilization is around 20 to 30%. When the ugly sales comparision queries get heavy, the performance degrades to the point that the OLTP users say it is unusable. After years of waiting, we are finally able to migrate to new hardware and Informix 11.5. Yea. This is a chance to make any changes to how the data is laid out. I suggesting we start up a second server in read-only mode that reads the same disk as the primary. Then we can move the ugly sales comparision reports to the read only server without having to redesign/split the database. The sales data is very large and is partitioned by division. But there are some OLTP tables that are partitioned too. Currently the partitions of OLAP and OLTP tables are spread out over the same set of dbspace. My question is, does a Shared Disk architecture have any impact on how I should lay out the partitions? Like should I separte the OLAP and OLTP into different sets of dbspaces since they will be accesses in different ways by different servers? Has anyone had any experience with this? Did you find you wanted to make adjustments? On the one hand I'm thinking partitioning is partitioning, shared disk should not make a difference. On the other hand, the system has been so CPU bound, that fixing the CPU problem may shift the bottleneck to I/O. Anyone have any experience at how much there disk I/O increased when they releaved a CPU bottleneck? Since I'm starting at 20 to 30% disk utilization, even doubling it should only put me in the 40 to 60% range.

I do not think that partitioning the data would have any impact on the = fact that the data is on a shared disk. You probably would want to make is partitioned because you would probably want to run the shared disk syst= em as a PDQ system. ------------------------------------- Madison Pruet, STSM IDS Replication Architect = "SCOTT ROBERTS" = <sroberts20@csc.c = om> = To Sent by: ids@iiug.org = ids-bounces@iiug. = cc org = Subj= ect Shared Disk Server and partition= ed 02/21/2009 12:59 tables [14967] = PM = = = Please respond to = ids@iiug.org = = = I have a 250 gig database with an ugly mix of OLTP and OLAP processes. = The ugly sales comparision queries do a HUGE amount of summing, comparing, calculating on the fly. They are very CPU intensive. The CPU can be peg= ged at 100% for hours while the disk utilization is around 20 to 30%. When the= ugly sales comparision queries get heavy, the performance degrades to the po= int that the OLTP users say it is unusable. After years of waiting, we are finally able to migrate to new hardware = and Informix 11.5. Yea. This is a chance to make any changes to how the dat= a is laid out. I suggesting we start up a second server in read-only mode that reads t= he same disk as the primary. Then we can move the ugly sales comparision report= s to the read only server without having to redesign/split the database. The sales data is very large and is partitioned by division. But there = are some OLTP tables that are partitioned too. Currently the partitions of = OLAP and OLTP tables are spread out over the same set of dbspace. My question is, does a Shared Disk architecture have any impact on how = I should lay out the partitions? Like should I separte the OLAP and OLTP = into different sets of dbspaces since they will be accesses in different way= s by different servers? Has anyone had any experience with this? Did you fin= d you wanted to make adjustments? On the one hand I'm thinking partitioning is partitioning, shared disk should not make a difference. On the other hand, the system has been so CPU bo= und, that fixing the CPU problem may shift the bottleneck to I/O. Anyone hav= e any experience at how much there disk I/O increased when they releaved a CP= U bottleneck? Since I'm starting at 20 to 30% disk utilization, even doub= ling it should only put me in the 40 to 60% range. ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. =

Using shared disk , you can think like your system running at the same machine but with opportunity to reorder all database. But at the end you will have separeted machine process. To do this you need start the tunning at the Storage, first you need to understand how works your storage , like: type of RAIDS , buffer cache in the Disk Control and mirror of this buffer cache. Than , maybe, if your database modeling permits, you can separate the OLAP and OLTP between different LUNs (and separating in differ dbspaces/chunks) to avoid any performance issues affect in the cache, and disk I/O at the storage. So, a very superficial suggestion , just configure a set of physical disks (LUNs) to OLTP and other set of physical disks to OLAP data. César --- Em sáb, 21/2/09, SCOTT ROBERTS <sroberts20@csc.com> escreveu: De: SCOTT ROBERTS <sroberts20@csc.com> Assunto: Shared Disk Server and partitioned tables [14967] Para: ids@iiug.org Data: Sábado, 21 de Fevereiro de 2009, 15:59 I have a 250 gig database with an ugly mix of OLTP and OLAP processes. The ugly sales comparision queries do a HUGE amount of summing, comparing, calculating on the fly. They are very CPU intensive. The CPU can be pegged at 100% for hours while the disk utilization is around 20 to 30%. When the ugly sales comparision queries get heavy, the performance degrades to the point that the OLTP users say it is unusable. After years of waiting, we are finally able to migrate to new hardware and Informix 11.5. Yea. This is a chance to make any changes to how the data is laid out. I suggesting we start up a second server in read-only mode that reads the same disk as the primary. Then we can move the ugly sales comparision reports to the read only server without having to redesign/split the database. The sales data is very large and is partitioned by division. But there are some OLTP tables that are partitioned too. Currently the partitions of OLAP and OLTP tables are spread out over the same set of dbspace. My question is, does a Shared Disk architecture have any impact on how I should lay out the partitions? Like should I separte the OLAP and OLTP into different sets of dbspaces since they will be accesses in different ways by different servers? Has anyone had any experience with this? Did you find you wanted to make adjustments? On the one hand I'm thinking partitioning is partitioning, shared disk should not make a difference. On the other hand, the system has been so CPU bound, that fixing the CPU problem may shift the bottleneck to I/O. Anyone have any experience at how much there disk I/O increased when they releaved a CPU bottleneck? Since I'm starting at 20 to 30% disk utilization, even doubling it should only put me in the 40 to 60% range. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. Veja quais são os assuntos do momento no Yahoo! +Buscados http://br.maisbuscados.yahoo.com