Raid 0 or Fragmentation?

We have an OLTP database, with a large end-of-day batch run (in stored procedures). During the batch run, we have an I/O bottleneck in one table, mostly reads. Our reads are already 99.6% cached. The reading is random. We have a 3 level Foreach loop, and the bottleneck table is at the inner level. All access is through indexes. The table is already on its own disk. There are 2 ways to improve performance: Raid 0 and fragmentation (round-robin). I know that the best way is to test, but before investing in a Raid controller and an additional disk, I would like to have some expert opinion. Raid 0 and fragmentation (round-robin) are really made for large sequential transfers. How good are they at random access reads? The record size is around 800 bytes, and a select statement returns on average 4 records. The machine has 2 processors. Thanks -- Bashar Chalabi CTL, London

Bashar Chalabi wrote: > We have an OLTP database, with a large end-of-day batch run (in stored > procedures). During the batch run, we have an I/O bottleneck in one table, > mostly reads. Our reads are already 99.6% cached. > (...) > The reading is random. We have a 3 level Foreach loop, and the bottleneck > table is at the inner level. All access is through indexes. The table is > already on its own disk. I wouldn't call that a I/O bottleneck. It seems more a CPU bottleneck. If i'm right speeding up disk access won't help you. > (...) The record size is around 800 bytes, and a select statement > returns on average 4 records. The machine has 2 processors. This would mean that PDQ won't help also... Assuming you already tried to increase buffers and read_ahead, let me ask this: are the 2 CPU's being used at that time? If not, can't you rewrite the program (in some other language) to use two simultaneous processes so that the 2 CPU's can be used? This is not what you asked about but i hope it helps... Fernando

If your read cache % is >99% then disk access is not likely to be your bottleneck for this app. Have you considered making those three nested loops into a single join of the three tables and letting the optimizer handle the details of getting the data back to you faster? Then you can do a single loop. Unless the application must run over a slow network this MAY be faster, certainly once you fragment that table it will be true. If you do determine that disk access is actually a problem I suggest, much to the chagrin of Informix folk, that you get a wide RAID0 or better yet RAID10 set and then partition it into many chunks and create at least as many dbspaces as there are spindles in the array and fragment the table across the array anyway. This will take best advantage of the speed of the array and allow the engine to multi-thread the accesses to the several dbspaces in parallel. Do not worry about random access I/O. Because of it's superior buffer cache and read ahead capabilities real random access is rare on an Informix server. Art S. Kagel Bashar Chalabi wrote: > > We have an OLTP database, with a large end-of-day batch run (in stored > procedures). During the batch run, we have an I/O bottleneck in one table, > mostly reads. Our reads are already 99.6% cached. > > The reading is random. We have a 3 level Foreach loop, and the bottleneck > table is at the inner level. All access is through indexes. The table is > already on its own disk. > > There are 2 ways to improve performance: Raid 0 and fragmentation > (round-robin). I know that the best way is to test, but before investing in > a Raid controller and an additional disk, I would like to have some expert > opinion. > > Raid 0 and fragmentation (round-robin) are really made for large sequential > transfers. How good are they at random access reads? The record size is > around 800 bytes, and a select statement returns on average 4 records. The > machine has 2 processors. > > Thanks > > -- > Bashar Chalabi > CTL, London

>I wouldn't call that a I/O bottleneck. It seems more a CPU bottleneck. >If i'm right speeding up disk access won't help you. The NT Performance Monitor, shows the disk read time of that specific disk at 100%, most of the time. >Assuming you already tried to increase buffers and read_ahead, let >me ask this: are the 2 CPU's being used at that time? If not, can't >you rewrite the program (in some other language) to use two >simultaneous processes so that the 2 CPU's can be used? I am not sure what is happening. The performance monitor shows that both processors are utilized, but the sum of their utilisation never exceeds 50%. As for writing in another language, we have tried esql/c and 4gl. The process issues around 1 million simple select statements everytime it runs. Anything besides stored procedures proceduces too much overhead. Bashar Chalabi CTL, London

Bashar Chalabi wrote: > >Assuming you already tried to increase buffers and read_ahead, let > >me ask this: are the 2 CPU's being used at that time? If not, can't > >you rewrite the program (in some other language) to use two > >simultaneous processes so that the 2 CPU's can be used? > > I am not sure what is happening. The performance monitor shows that both > processors are utilized, but the sum of their utilisation never exceeds 50%. Apparently PDQ is not used (only one CPU is used at a time) but the task is being switched from one CPU to another. Anyway you are not using both CPU's and the CPU load never passes 50%. > As for writing in another language, we have tried esql/c and 4gl. The > process issues around 1 million simple select statements everytime it runs. > Anything besides stored procedures proceduces too much overhead. Yes, but that overhead could be overcomed if you were able to paralelise the task by using another language. -- Fernando Fernandez http://despodata.pt/ddata/pessoal/ferdez.htm