slow performance problem

[Ninad_1]

Hi All,

We have an application running on rp5470-4 sserver with 2 CPUs and 4 GB mem( Server B). This application runs some scripts which populates data in tables and this seems to run very slow as compared to similar setup on another server( Server A).
According to the DBA all the queries are using the same execute PLAN and everything is same as that on Server A.
Can you please let me know how I need to go about solving this problem. In glance the CPU and mem usage is not very high - CPU is about 15% usage and 54% usage. Please find output of iostat attached. There are 6 luns created from a RAID 5 set used to create 6 disk groups which have the database dbf files.
Can you suggest me what statistics I need to analyze and any kernel parameters which need to be tuned.

Regs.
Nad

[Stephen Keane]

What are you using c5t0d0 and c6t0d1 for? You need to post the configuration of your server. e.g. ioscan -Cdisk -fn, bdf, strings /etc/lvmtab etc.

[Steve Steel]

Hi


This link will help

http://forums1.itrc.hp.com/service/forums/questionanswer.do?threadId=786320



Steve Steel

[Ninad_1]

While trying to get the output from the ioscan command it is giving
ioscan: libIO error from ioconfig_lock: /etc/ioconfig - permission denied.

Also vgdisplay is giving vgdisplay: /etc/lvmconf//lvm_lock: Lockf deadlock detection

It seems that I have run into another problem.
Can you please advice on this.

Thanks

[Robert-Jan Goossens]

Hi Nad,

Check if someone is running sam.

# ps -ef | grep sam

Stop or kill the sessions and try ioscan again.

Best regards,
Robert-Jan

[Ninad_1]

There is no one running sam

ps -eaf | grep sam
root 1962 1 0 Jan 18 ? 0:06 /usr/sam/lbin/samd
root 3516 23430 0 11:26:38 pts/7 0:00 grep sam

Please advice

[Keith Bryson]

Hi Nad

Concerning the performance issue, are your mount options (/etc/fstab) identical for both servers you are comparing? Log, mincache and convosync settings have a major affect on system (disk) performance for some RDBMSs.

Also check the buffer cache sizing, as experience tells me that this can affect Oracle (I assume it's Oracle). You can also check this if you have glance (process 'wait state' high on Cache, or 'wait reason' CACHE).

All the best - Keith

[Stephen Keane]

Please confirm you are running ioscan as user root, otherwise you will get the error

ioscan: libIO error from io_init: /dev/config - permission denied.

as you have reported. If you cannot run ioscan as root (maybe you are not allowed to log in as root) add the -k flag to ioscan

ioscan -kfn

[Ninad_1]

Keith,

Yes the mount options are same for both servers.- delaylog option is the only option used. Yes we are using Oracle database. In glance the output shows about 55% mem usage. Buf cache is 10% ( dbc_max_pct - 10 )
I did not understand by (process 'wait state' high on Cache, or 'wait reason' CACHE)in glance. No such message is displayed by glance. Do the mincache and convosync settings help a lot?


Stephen,

Attached is output of ioscan -kfnC disk.
Thanks for the alternative.
Can you suggest anything from the output


Thanks

[Keith Bryson]

Nad

Going off the track slightly, take a look at this Oracle pdf file that details tuning mount options to help Oracle performance (be aware though that this is ONLY for your datafile and index mount points, not archive/redo or application mount points).

http://www.oracle.com/technology/deploy/performance/pdf/TWP_Oracle_HP_files.pdf

All the best - Keith

[Stephen Keane]

I'm assuming from your responses that you don't have root access.

Can you post the results of the following

# ll /etc/lvmconf

# vgdisplay -v

# bdf

[Ninad_1]

There was some file locking problem. Got it resolved after getting the server rebooted ( for some other purpose ). I do have root access. Please find the output of bdf and vgdisplay.

Keith,

I had gone through that document , but then I also cam across some notes where the users did not find any better results , or sometimes only a bit better. Also according to standards used by the customer these options arent used uptill now. That is why I wanted to be sure if using michache and convosync would really help.


Thanks.

[Ninad_1]

Sorry I think I missed the vgdisplay.
Please find it here

[Keith Bryson]

Hi Nad

Concerning the bdf output. Are both servers running 80-90% on a lot of the filesystems? High usage won't be helping performance. You may want to try (assuming these are vxfs filesystems) 'fsadm -F vxfs -D -E -s /{mountpoint}' (careful with the switches on this command). This will show you extent and directory fragmentation and there are a few things to look out for (see 'man fsadm_vxfs').

Also, if you compare both kernel settings side-by-side (using SAM is sometimes easier) are there any differences?

Are there identical RAID systems on both? You say you populate tables, how do database read operations compare? Your DBAs should be able to check this.

Questions questions!! Keith

[Stephen Keane]

You are aware that u015, u021 and u022 are 100% full? Is this a quirk of Oracle, or is it actually a problem?

[Jean-Luc Oudart]

Could I suggest the DBA runs statspack reports on both system (for same operation) and then compare. Tjis should give you an hint (from Oracle prospective) as where you are spending time waiting !

Regards
Jean-Luc

[Ninad_1]

Keith,Stephen,

The filesystem should not be fragmented as they are created newly and only dbf files have been created by the DBA in these filesystems. In Oracle database the filesystem can be 100% full without considering it as a problem because it is the size of the dbf file created inside it which is managed by Oracle. Similar filesystem usage is there on the other server.

Jean,

The DBA has already ran stats pack and it shows high time spent in db file sequential read.


Can anyone tell me how to analyse whether disk io is bottleneck or not.how to analyze this.

Thanks

[Keith Bryson]

Hi Nad

I had another look at some of the postings. You have 8 large main LVs on each RAID LUN, which is why the iostat figures are quite high for c5t0d0 and c6t0d1. That's a lot of data to pump through to 2 LUNS. I would take another look at the LUN and LV setup and also the RAID LUN configuration on the HITACHI. I do remember problems with performance on HITACHI RAID from other contracts I have worked on, specifically read performance. A 'sar -d 3 10' may highlight further issues with these 2 LUNs.

It's difficult without seeing/touching the system, hope you understand why we seem to be fumbling around a bit.

All the best - Keith

[Ninad_1]

Hi,

Here is the sar output. What are the acceptable values for each parameter like avserv, avque etc, are these values dependant on any disk specifications like size. and how should these values be interpreted.
One more query I have is if we are using RAID5 and have 5 Luns ( one Luns for eacg disk group ), will distributing dbf files to different disk groups help , becuase what I feel is that the data will anyhow be striped accross the disks in RAID5 so does it really help ?

Rgs.

Nad

[Keith Bryson]

Your figures aren't bad for the amount of data you're shifting.

At this stage I would advise running up glance and run the Oracle table population procedure. In the (global) process table in glance (hit letter 'g'), make a note of the PID for your process. Hit letter 's' (Enter PID). Type in the PID. Hit F2 to view the wait states for the process. Hit C (capital c) for cumulative figures and take notes. Hit F4 for the process open-files screen and take notes. You will get a better idea what this process is doing and what is holding it up.

Keith

[Ninad_1]

Keith,

Thanks for the procedures.
I ran glance.
Actually its the oracle process which is running and excuting the scripts and there is no separate process as such. I used the options you specified for the top oracle process and it showed waiting for CACHE. also in disk statistics it shows cache hit ratio around 58% , but since its oracle scanning so many tables do we need to concentrate on this parameter , how is it significant?
One more thing , in HP how do I check what is the max i/o size that can be done in one i/o read or write request. Where is it configured, how can i check ? What is the limitation of OS and that of disk
Please advice.

[Keith Bryson]

If the process is waiting for cache mostly, you may need to tune your buffer cache. Try up'ing the dbc_min_pct value. I think Oracle likes 300-600Mb.

I'm not sure about your other question yet.

Keith

[Ninad_1]

Keith,

Oracle process will be scanning so many tables, so do you feel that the cache is getting used and helping in any way ? I thought that cache will be useful in normal read/write of files. How does it help in case of oracle ?
You recommended to increase dbc_min_pct - but I have still enough free memory and my dbc_max_pct is 10% which gives around 400Mb of buffer.
Do you still recommend increasing the dbc_min_pct ?

The second query I asked was that I have been asked by the DBA what is the max io size (what is maximum bytes transferred for one io request ), so that he can use it to configure the db_block_size and multi_block_read count.

[Keith Bryson]

Nad

Just because you have dbc_max_pct set at 10% doesn't mean that the OS will have the buffer cache set at 400Mb. This is the maximum value that the OS will dynamically grow the buffer cache to, if it sees fit.

Check in Glance (hit letter m) "buf cache" is reported at the bottom of the screen. You have to force the OS to set the buffer cache at 300-600Mb minimum, by tuning the dbc_min_pct kernel parm. Set this to 10% and max_pct to 15 or something. The OS will then create the buffer cache at 400Mb minimum, rather than growing it when it sees necessary.

I'm trying to find info on max i/o - hoping that someone else out there will know.

Cheers - Keith

[Ninad_1]

The glance shows buff to be 400 MB which is 10% of memory - 4GB. I guess that the max buf is allocated if memory is free. Now if processes require memory and there is no free mem , then the buf mem is freed to give for processes , the buf can be reduced max till buf reaches min pct.

What I feel is that we can increase max buf but that wont be the actual solution because the queries will again take up all the space in buf. can you provide your comments on this.
I am now thinking of increasing the dbc_max_pct to 20%