Re: Does DB2 really need this many filesystems? (822 in one case)

From: Vikrant (icanhearyou_at_rediffmail.com)
Date: 12/27/03 

Date: 26 Dec 2003 20:26:02 -0800

73blazer <yoyo@ma.com> wrote in message news:<vtSdnZsITuKxDHGiRVn-tA@centurytel.net>...
> Short answer no.
> Better answer, no.
> A few filesystems spread over different disks for different parts of the
> DB files, fine, but 822 is, well, way too many. Somewhere around 75
> filesystems I've noticed AIX really takes a crap when it comes to a df,
> any mount or unmount, even the cdrom, the overhead associated to
> maintaining all those filesystems, just from an OS perspective, not
> considering the administrator, far exceeds any performance benifit you
> would recieve from so many filesystems.
> Here's what I do, take a single instance. Say I have 14 tablespaces in
> that instance. Say, 3 of them get hit fairly heavily and the rest just
> occasionally. I would, make a filesystem for the instance on one disk, a
> filesystems for each of the heavily hit tablespaces on different disks,
> and put the rest on 1 or two filesystems, on other disks. If your using
> an SSA array, well, things are different. Just make the SSA array one
> big Volume Group, then make 1 filesystem for all of DB and it's
> instances, since the SSA management part of the OS will take of
> spreading things accros the disks.
>
> Ken
>
> Tonij wrote:
> > Hi all,
> >
> > We have a few IBM boxes running DB2, some 4.3.3 older boxes and some P
> > Series running 5.2
> >
> > What I have noticed in this environment is the DBAs always ask for
> > TONS of filesystems for their databases; when questioned about it we
> > are told there is no other way to do it. We have one box that has 822
> > (that is not a typo) filesystems! It takes about 15 minutes to unmount
> > them all.
> >
> > Is this common for DB2 or are we stuck in some old fashioned way of
> > doing things?
> >
> > Just curious what other boxes running DB2 look like...

Hi Tonij,

Surely, its not good to have so many FS. however I can understand why
DBA used so many filesystems (I also agree that sometimes DBA do not
understand AIX FS!).
 Well, During March - May 1996 , I was also doing the same thing with
SAP R/3 system !!! . Reason? so call AIX expert told me to have better
DB2 performance make 'unique' file system for each tablespace (good
thinking, but when number of SSA disks are limited, one has to
consider that too, this guy 'claimed' that you get better performance
if you can have 'unique' file system for each DB2 container. I should
say that in 'uni-direction thinking' the statement may be correct, but
forgot the effect of RAID5 / limited SSA disks (I have not worked on
ESS so can not say about it).

After spending unproductive time DB2 & AIX, my current views are that
increasing number of file systems more than 7 or 8 per database may be
counter productive in managing & performance (loss of AIX memory too).
one of the way is to have Large File system - Well tune it by RAID5
and have Larger container. on other side a 'good paranoid benchmark
performance guy may prefer to have more file system (or may even go
for Raw device). It looks odd to have 822 file systems, but one has to
understand the real reasons. Its upto AIX, Storage & DB2 guys to
understand and take actions. Its trade off, if you can justify. This
is the time to understand whole AIX FS , AIX Memory management ,
NUM_IOSERVERS ,BUFFPAGE , Disk Storage , DB2 and application
performance trade off issue.

A probing question may be "Please justify more than 104 (8 x 13) file
systems from DB2 performance point of view, under currently installed
hardware environment?" to AIX , Disk Storage & DB2 guys.

Enjoy,
VKG

Relevant Pages

  • Re: Newbie on AIX
    ... To me, AIX logical volume management, now going on 11 years old is the ... it was the first time I did a chfs command to add more file system space ... volumes (usually individual hard disks). ... Free PPs is free partitions - free space more or less, ...
    (comp.unix.aix)
  • Re: What we have here... is failure... to communicate (AIX floppies under W2K)
    ... I've never installed the Unix services, ... What you need is some kind of a file system driver. ... surprised if such a thing exists for AIX minidisks or disks in AIX ...
    (comp.sys.ibm.ps2.hardware)
  • Re: Adding third drive to existing raid0 set
    ... If I want to be able to enlarge a file system like this in the future could ... I use lvm to do this and how does lvm work? ... between the physical drives and the filesystems. ... I have 4 disks in a raid5 ...
    (Debian-User)
  • Big VG
    ... I am away from those two servers so I cannot try the chvg -B just ... I was lead to believe that I had to go to AIX 5.2 before I ... There must be enough free partitions available on each physical ... We wanted to add disks to each of these VGs and, maybe, add a VG ...
    (AIX-L)
  • ZFS "stalls" -- and maybe we should be talking about defaults?
    ... I have converted a significant number of filesystems to ZFS over the ... I set up four disks under GPT, ... on UFS filesystems they were set up as a 0+1 RAID array under the ARECA ... I began to get EXTENDED stalls with zero I/O going on, ...
    (freebsd-stable)