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 ?
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.
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.
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.
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.
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.