EMC, Oracle, file systems

[Joseph Hoh]

I have bit of a performance issue.

We have a V2250 w/ 16x 240MHz CPU and 16GB RAM.
It has 12 Oracle databases. The largest one is approx. 1.5TB. There is approx. 2TB of EMC disk space connected with 2 fibre interfaces which is balanced with EMC Powerpath software.

Typically you try to spread databases out over several disk drives. However, using EMC disks, which are logical disks, this is not an issue according to EMC because everything is in cache.

My concern is that the DBAs have mixed several of the databases together in the same file systems. When the 1.5TB database is doing loads Glance shows many disk bottle necks and some of the users of the other databases complain about response time. The CPUs are busy but they are not all 100% maxed out. Does having multiple databases on the same file system cause file system contention, assuming that the EMC disks are not a problem. (According to my EMC manager the EMC disks are showing very little I/O)

Thanks.

[harry d brown jr]

Are the filesystems spread across many logical drives, or is it more of a one-to-one relationship? Are you using HP striping or extent based striping?

live free or die
harry

[Christopher McCray_1 · Accepted solution]

Hello,

It is very possible that this will cause disk contention (experienced it a few times myself), especially when you would have indexes and temp on the same vg, or some other mix of the heavily hit filesystems. I would sit down with your dbas and see where they have things situated. You should also, if you have it, pull up the IO stats through measureware and see what it says.

Hope this helps
Chris

[Alan Riggs]

Glance doesn't really do a god job of telling you what is going on in an EMC frame. If the EMC diagnostics themselves are not showing highg disk activity, then you are probably not having a problem with physical location of files on devices.

That doesn't mean you don't have a problem with disk access, though. 2 things to lok at:
1--make sure you are using the Oracle recommended, JFS mount options (Online JFS required). This will prevent "double buffering" of oracle writes in system buffer cache.
2--look at the load on the fibre/switch/SCSI going to the EMC. It is possible that you are exceeding the bandwidth for your connection.

[A. Clay Stephenson]

Hi Jeff:

Let me qualify this by saying that this is an educated guess because I would never consider configuring Oracle instances like this - the backup/restore problems and difficulties alone would be terrible. However, I don't think the filesystem itself is a major constraint because, in general, all the Oracle files are pre-allocated and thus very little in the way of locking has to be done to protect filesystem metadata. Also, Glance can give you misleading results when examining arrays. Remember, all it knows is that an awful lot of I/O is going through what appears to it to be a single disk or a small number of disks. It doesn't know that this single LUN is actually comprised of N physical disks.

It would help to know if you are running the convosync=direct,mincache=direct,nodatainlog,delaylog mount options. Typically, you want to use the options only for datafiles/indices and the more conventional fully buffered mount options for redo/archive logs.

There is just not enough here to determine anything yet but in every reasonably configured database the first thing I do is look at the SQL code itself. In the vast majority of cases that is where the problem lies. Since you mentioned Oracle, the other thing I always look at is the setting of timeslice. Some of the tuned parameter sets adjust it to 1 rather than 10 and that can lead to all sorts of problems.

[Roger Baptiste]

<>>

Not really, because there is always a limit to Cache size and what it can do. Especially on an busy system with number of databases.
SO, generally the good practice is to lay out the Luns of a disk in such a way that two databases do not sit on the same disks! I dont think even the customers/dba would like that situation. Another thing is to make sure that that the luns of the same disk do not have busy tablespaces or indexes+datafiles sitting on the same disks. This would need planning at the emclayout/lun/vgmapping stage itself.
In some cases, there can be exceptions since it is a huge EMC frame and there are many databases.



Which is not good either. But if the VGs themselves are sitting on the same disks, then it doesn't matter, because even if they put databases on different filesystems, it would still be sitting on the same disks.



sar -d is another good measure for getting disk response time. The disk bottle necks could be due to many reasons. Reconfirm again with your EMC engineer whether he is seeing more activity on specific disk adaptors compared to the others. Normally EMC folks check the FA end (host end) and say everything is fine. Dont take that at face value. Tell them to run a detailed performance check using the EMC performance tool to make sure of the figures.
Also, make sure your PVs are really getting distributed by powerpath. (use vgdisplay -v ).

Work at the database end with the DBA to see whether they could figure out somethign at their end and tune up accordingly.

Check what sort of jobs/times the problem occurs.

Revisit your emc layout - disk/lun/vg mapping and see whether it is ok.

This should give you a fair idea on where the problem is. It can be multiple things combined making the issue.

HTH
raj

[Joseph Hoh]

Acoording to Glance we seem to be mostly waiting on IO. I have changed all of my oracle file system mount options to: rw,suid,nolargefiles,delaylog,nodatainlog,mincache=direct,convosync=direct

Everything I read said these were the best options.

Can someone tell me what a good IO rate is. Right now there is a process using 50% CPU , Phys IO Rt 80-88, IO Byte Rt 12692, IO Wait 50%.(Glance)

I am not sure if everything is going as fast as it can or if there is really a bottle neck that can be fixed.

[Joseph Hoh]

Also, the vg contain multiple logical dirves and some have multiple lv's and some have just one lv per vg.

The EMC disks are configured in as RAID S.

[Krishna Prasad]

You mentioned that there is 12 Databases on the machine and 16GB of ram. With one of the databases being 1.5TB I am assuming this is the database using most of the memory?

We had an issue very close to yours several years ago. It came down to a few indexes being in the same filesystem as data. These are the easy ones to find. What can be much more diffcult is knowing if they are on the same physical drive in the EMC frame. I.E. index file is on c2t2d2 and data file is on c4t4d4 it could very easily be on the same physical drive in the frame. One way to track this down is to use EMC control center and map things out. EMC does have a product that can do the maping for you. Show you all the hosts/filesystems for a physical drive in the frame, but of course this is not free.

Another thing to check in glance is the queue length of any drive. In glance type i then IOByDisk (f2). This will let you know if the logical drive ( physical to the OS ) has any requests queued. Glance will also tell you the % of time it has a queue length of zero.

Then of course there is a chance you will do all of this, learn and research a lot about your system and find out it is an application issue. BAD SQL'S as Clay has mentioned.











0

[Joseph Hoh]

I have pulled some information from glance with the extract command about the disks. There are several times where many disks are 100% busy but the physical disk queue is below 1 if not zero. Wouldn't that say the disks are working just fine? They are just busy and from some other things I have read just because Glance says they are 100% busy it doesn't mean they can't do more.

Comments?

[Krishna Prasad]

It tells me that the operating system is asking for I/O and your disk system ( EMC ) is responding just fine.

The next question is when you look at your top process are they takeing a high % of system, user or idle time?

[Byron Myers]

Jeff, make sure the dcb_max_pct and dcb_min_pct kernel vars are set low. The default for max is 50% of memory which would significantly slow down a DB system. For 16 GB RAM, I would set dcb_max_pct to 3 (3%).

[Jim Layton_2]

Regarding performance manageability & security of data bases as seperate entities is the way to go. From a secuirty and data integrity standpoint file systems very large ones at that can become corrupted due to power failures etc. Then you would have restore mutiple data bases. Data bases created on separate spaces or separate luns in the storage facility can address performance because you can slice the space the accross the whole storage warehouse of disk. Thats my quick 2 cents.
Jim