Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2002
    Posts
    58

    Unanswered: DB2 & disk/storage config question

    Hi,

    I am wondering what design would be best given my current hardware available :

    I have a server (HP/COMPAQ DL580, 2CPU, 4gb ram, win2003, DB2v8.2) connected to a SAN (MSA-1000) with 14 * 146gb.
    What would be the best setup for the arrays/partitions on the SAN and the placement of logfiles and tablespace containers ? Would 1 big LUN/partition with a good folder structure on OS-level (Windows2003) vs. other structures make a big difference and why ?
    What do I have to take into account ?


    Thanks already for a reaction,

    Peter

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Without details about your database (is it OLTP- or DW-type database? how many tablespaces, what types, and how big they are? how many database partitions? how many concurrent users? is there any middleware involved, or are users connecting to it directly? what kind of data is being stored?) any answer would be right (or wrong).

    Generally speaking, you would probably want to allocate RAID1-0 volumes made up of separate physical devices for logs and temp. spaces. Consider also placing index and data tablespaces on separate physical devices as well. Dual attachment of the storage system would also be beneficial.

    As for the "good folder structure", I think this should be the last thing on your mind.
    Last edited by n_i; 10-27-05 at 14:34.

  3. #3
    Join Date
    Oct 2005
    Posts
    109
    n_i is quite right, that is very little information.

    but anyway - your database logfiles should be local for performance reasons (local I/O is faster than the SAN), and only for HA reasons mirrored to your SAN. I also have seen weired things happen to a database when SAN creashes and same caches did not do their job correctly ...

    Tablespaces will be in the SAN I guess.
    Don't forget: Extendsize of the tablespace a multiple of your RAID physical stripe size (or whatever disc format you use).

    Many claim that today with so much caching going on in a SAN this is not important. Still - I believe it is good for your I/O performance, but it may very much depend on the underlaying disc structure.
    Juliane

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by juliane26
    local I/O is faster than the SAN
    This is not necessarily true, nor is it important. If performance analysis doesn't indicate that log writes create a bottleneck then it doesn't matter whether the log storage is local or networked.

  5. #5
    Join Date
    Oct 2005
    Posts
    109
    Quote Originally Posted by n_i
    This is not necessarily true, nor is it important. If performance analysis doesn't indicate that log writes create a bottleneck then it doesn't matter whether the log storage is local or networked.
    Could be, yes. You don't expect a relative newbie being able to do find this bottleneck right in the first app, do you ?
    Supporting many projects here I make that the default with the mirror on SAN, then you have less trouble later.
    We don't know, whether its OLTP or not, For OLTP I would always start right away with local log files.
    Juliane

  6. #6
    Join Date
    Mar 2004
    Posts
    448
    In a san ,I prefer not to create many mount points, usually 2 or 3.
    We are using the policy of san to distribute the datafiles and seperating
    the logs file.It is totally transparent for db2.
    we have all raid 5 ,2 mount points use by data/index and 1 mount point for logs.


    I hope this will help.


    regards,

    mujeeb

  7. #7
    Join Date
    Jul 2005
    Location
    Irvine, CA
    Posts
    23
    Like everyone else has said, you need to know the layout of the database, its data characteristics, and data access trends. With that information, you can figure out what you need to do, but usually you need to test different configurations under load. That's what load testing and tuning before going to production is all about. Not a lot of people do it and crud performance is the result.

    Options for you? Try 7 RAID1s - use 3 for data - 2 for indexes - 1 for logs - 1 for temp. Backups can go on the temp of log area. This arrangement has no hot spares though. Test. Then try putting the indexes and data shared across 5 RAID1s, then test and compare. Then try one large RAID 10 of 7 + 7 drives - put everything on this one lun and test/compare. Lastly, try an 8PS RAID5 (7 drives + 1 parity + 1 spare) and 2 RAID1s. Put data and indexes on the RAID5 and logs and temp on RAID1. Test and compare. Whichever is the best, go with that.

    There are many other ways to arrange the drives than just these. What is important is to get as many physical read/write heads working for you as possible. Having 14 drives for 2 cpus is a good start. IBM recommends in their publications that you want from 6 to 10 drives per CPU. Still at 14 drives, you are a little short if you count hot spares and parity drives.

    Which RAID level to use? RAID1 and RAID10 are great, but cost you one drive per mirror. This can get really expensive. If you did a RAID 10 only, you would essentially be losing 7 drives. RAID1 (10) will allow you to read the mirrors seperately, so you can get the benefit of 14 drives of read ouput. You must write to both together though, so no added benefit there.

    RAID5 is a good lower cost option (you lose only one drive per lun), but it's performance can be around 20% less than RAID1 and 10. SAN Cache is supposed to help this out, but I find that to be over stated. Write cache helps a lot, but read cache only works if the transactions are small - at least in my experience.

    I usually go with RAID 5 for data in indexes and RAID 1 or 10 for logs and temp.

    Other things to consider:

    1. OLAP or OLTP or mixed? (i.e. many reads, many writes, or both?)
    2. Huge tables, or small ones?
    3. Complex SQL or easy?
    4. Big data transformations/migrations at night?
    5. Large user base, or just a few users?
    6. How much storage do you need. Performance is about the number of drives, so keep that in mind.

    Basically, it gets down to how much you are going to rattle those drives. A lot of reads and hardly any writes? Then go with all RAID1 or 10. A lot of writes and fewer reads, RAID 5 or 10. This is not a hard and fast rule though. It all depends.


    Soap Box - How many of you have had to argue for more disks? Most companies will look at you cross eyed if you say you need 50 disks for a project. "That's 3.6 Terabytes for a 200 GB database! No way!" they say. You have to convince them it is not the total storage that matters, but the total number of heads needed. I wish someone would bring back 8 GB drives.

    -- Steve

  8. #8
    Join Date
    Dec 2002
    Posts
    58
    Hi all,

    Thanks for the great input ... It's a lot to chew on but I'm sure that you guys pushed me in the right direction. I'll do some more homework and if necessary I'll post a follow-up message.

    Thanks again,

    Peter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •