RAID 5 is Killing Performance?

[esmith2112]

We replaced an aging system with a newer (faster 4-way) and presumably
better system. Perfomance for most queries has improved as one would
expect. However, we're getting slaughtered on performance for
summary-style queries. What took 2 minutes now takes 2-3 hours to
complete.

We backed up DB and restored it on a borrowed (somewhat inferior class)
box to see if we could do some diagnostics to determine why we had such
horrid performance. Much to our surprise, there was no performance
problem there. The main difference (other than slightly slower CPUs) is
the disk configuration. Our badly performing system uses RAID 5 on 9
disks. The borrowed machine is fully mirrored on 4 disks. Both machines
are running 7.2 FP12 on AIX 5.

Further investigation showed that when breaking the query down,
commenting out a join to a code lookup table restored performance. The
optimizer plan looked good with or without the code table join so it
wouldn't seem to impact it that much. Is there some wisdom that is
specific to tablespaces and RAID 5 (especially with regard to small
code lookup tables) that we need to take into account here? Are there
other db or dbm cfg parameters that we can look at?

Thanks,
Evan

[Mark A]

"esmith2112" <es********@hotmail.com> wrote in message
news:11********************@f14g2000cwb.googlegrou ps.com...

We replaced an aging system with a newer (faster 4-way) and presumably
better system. Perfomance for most queries has improved as one would
expect. However, we're getting slaughtered on performance for
summary-style queries. What took 2 minutes now takes 2-3 hours to
complete.

We backed up DB and restored it on a borrowed (somewhat inferior class)
box to see if we could do some diagnostics to determine why we had such
horrid performance. Much to our surprise, there was no performance
problem there. The main difference (other than slightly slower CPUs) is
the disk configuration. Our badly performing system uses RAID 5 on 9
disks. The borrowed machine is fully mirrored on 4 disks. Both machines
are running 7.2 FP12 on AIX 5.

Further investigation showed that when breaking the query down,
commenting out a join to a code lookup table restored performance. The
optimizer plan looked good with or without the code table join so it
wouldn't seem to impact it that much. Is there some wisdom that is
specific to tablespaces and RAID 5 (especially with regard to small
code lookup tables) that we need to take into account here? Are there
other db or dbm cfg parameters that we can look at?

Thanks,
Evan

You should have only one container per tablespace for each or each disk
array. Since all your disks are stripped in an array, you should have only
one container.

Make sure that stripped containers registry parm is set to on. If it is not,
you will have to recreate the tablespaces. See that Administration:
Performance Guide for details.

But I suspect that there is something else going on to account for the
difference between 2 minutes and 2 hours.

[Mark Yudkin]

Did you remember to enable proper RAID 5 usage in DB2 (see "Optimizing table
space performance when data is on RAID devices" in the online
documentation)? Summary:

Define a single container for the table space (using the RAID device).
Make the EXTENTSIZE of the table space equal to, or a multiple of, the RAID
stripe size.
Ensure that the PREFETCHSIZE of the table space is:
- the RAID stripe size multiplied by the number of RAID parallel devices (or
a whole multiple of this product), and
- a multiple of the EXTENTSIZE.
Use the DB2_PARALLEL_IO registry variable to enable parallel I/O for the
table space.

---

Is your SCSI RAID controller up to the task (you didn't say what your RAID
hardware was using)? Most of the cheaper devices give awful performance. We
upgraded the card in one problem system to one with a battery backed write
cache after which reorg ran 7 times faster.

"esmith2112" <es********@hotmail.com> wrote in message
news:11********************@f14g2000cwb.googlegrou ps.com...

We replaced an aging system with a newer (faster 4-way) and presumably
better system. Perfomance for most queries has improved as one would
expect. However, we're getting slaughtered on performance for
summary-style queries. What took 2 minutes now takes 2-3 hours to
complete.

We backed up DB and restored it on a borrowed (somewhat inferior class)
box to see if we could do some diagnostics to determine why we had such
horrid performance. Much to our surprise, there was no performance
problem there. The main difference (other than slightly slower CPUs) is
the disk configuration. Our badly performing system uses RAID 5 on 9
disks. The borrowed machine is fully mirrored on 4 disks. Both machines
are running 7.2 FP12 on AIX 5.

Further investigation showed that when breaking the query down,
commenting out a join to a code lookup table restored performance. The
optimizer plan looked good with or without the code table join so it
wouldn't seem to impact it that much. Is there some wisdom that is
specific to tablespaces and RAID 5 (especially with regard to small
code lookup tables) that we need to take into account here? Are there
other db or dbm cfg parameters that we can look at?

Thanks,
Evan

[Art S. Kagel]

NO RAID5!! NO RAID5!! NO RAID5!! NO RAID5!! NO RAID5!! NO RAID5!!

Your experiences do not surprise me.

My personal feeling is that RAID5 and relational databases should never
interact. Please see postings by me and several other more well known
pundits on the subject at the BAARF (Battle Against Any RAID
Five/Four/Free) web site: www.baarf.com

Performance compared to RAID10 is poor, recovery is slow, data
security/safety is nearly non-existent. Read and learn.

Art S. Kagel

"esmith2112" <es********@hotmail.com> wrote in message
news:11********************@f14g2000cwb.googlegrou ps.com...

We replaced an aging system with a newer (faster 4-way) and presumably
better system. Perfomance for most queries has improved as one would
expect. However, we're getting slaughtered on performance for
summary-style queries. What took 2 minutes now takes 2-3 hours to
complete.

We backed up DB and restored it on a borrowed (somewhat inferior class)
box to see if we could do some diagnostics to determine why we had such
horrid performance. Much to our surprise, there was no performance
problem there. The main difference (other than slightly slower CPUs) is
the disk configuration. Our badly performing system uses RAID 5 on 9
disks. The borrowed machine is fully mirrored on 4 disks. Both machines
are running 7.2 FP12 on AIX 5.

Further investigation showed that when breaking the query down,
commenting out a join to a code lookup table restored performance. The
optimizer plan looked good with or without the code table join so it
wouldn't seem to impact it that much. Is there some wisdom that is
specific to tablespaces and RAID 5 (especially with regard to small
code lookup tables) that we need to take into account here? Are there
other db or dbm cfg parameters that we can look at?

Thanks,
Evan

[Mark Yudkin]

My experience (battery backing on controller) was with simple RAID-1 (i.e.
every disk is 2 disks), not RAID-5. RAID-5 adds its own complications (c.f.
baarf).

However, the OP wasn't asking "should I use RAID-5 or some other RAID
level?" but was concerned with a massive performance degradation that was
well in excess of the expected slow-down.

"Art S. Kagel" <ka***@bloomberg.net> wrote in message
news:41**************@bloomberg.net...

NO RAID5!! NO RAID5!! NO RAID5!! NO RAID5!! NO RAID5!! NO RAID5!!

Your experiences do not surprise me.

My personal feeling is that RAID5 and relational databases should never
interact. Please see postings by me and several other more well known
pundits on the subject at the BAARF (Battle Against Any RAID
Five/Four/Free) web site: www.baarf.com

Performance compared to RAID10 is poor, recovery is slow, data
security/safety is nearly non-existent. Read and learn.