Multiple file systems for database

[rondebbs]

I am running 5.2 connected to an EMC CX700 array. We have a progress database that is spread across 16 disks on our EMC array. The database can leverage the performance of those 16 spindles. The database lives in one AIX vg but there are 7 AIX logical volumes and 7 file systems that make up the database. They have names like db1, db2, db3, db4, db5, db6, db7. When the database needs to grow the dba's will ask me to create a new file system for db8. Each of these file systems stores a bunch of database extents. When the db7 one starts to fill up it is time to create the new db8.

We have many different LPARs that run different versions of the application and database. For each progress database I have to deal with the 7 file systems and 7 aix logical volumes. I'm not sure why we have 7. Why not have one file system called db (instead of db1 thru db7). We could combine all of the database extents there. When we need to grow the database we simply increse the size of the one file system (there is room in the VG to increase the LV and file system). All the database extents would still be spread across the same 16 spindles so performance should not change.

Does this make sense? I'm trying to eliminate some of the many objects I have to manage. I don't think we are getting any performance benifits from having 7 LV's. The advantage comes from the 16 spindles.

 

[khalidaaa]

I might not answer your question directly but i will tell you about my setup in the company.

we used to have 3 LVs for each database data file but when we migrated the databases to our new SAN 4500 (connected to a P5 570), the IBM consultant say that we can use one filesystem for all the database data file because the difference is negligible performance-wise! As the SAN is a more faster storage than what we had!

I'm not a DBA and i consider my self a beginner to use the SAN storage but that's what we over here ended-up doing and it works like a charm

Regards,
Khalid

 

[rondebbs]

Thanks for the info. Does anyone else know whether there is a noticeable performance difference by using seven filesystems for the database rather than one? In either case, the one file system or the multiple file systems would be striped across the same 16 drives.

Can AIX push a lot more data through seven files systems than it can one? The application has many users adding or changing small chuncks of data at the same time.

 

[spamly]

Sounds like you're running Intersystems Cache or Mumps. Maybe something similar.

If that is the case, these particular databases create one write daemon for each filesystem. This allows the database to stream multiple writes simultaneously without having to worry about the different writes stepping on each other. You wouldn't want two write processes running against the same filesystem as this could lead to more seek time. Each additional process will increase performance if your disk subsystem can handle it. As the database is not completely aware of the disk to filesystem correlation, it, in a way, assumes that each filesystem uses separate spindles. This isn't the case for most people on a SAN, but is usually okay because of the total number of separate physical drives in the volume group and the disk subsystem's cache.