Operating System - HP-UX
1747984 Members
4816 Online
108756 Solutions
New Discussion юеВ

Re: What should be the value f_frsize if my db_block_size is 8Kb in oracle database

 
Ayaz Ahmad
Occasional Advisor

Re: What should be the value f_frsize if my db_block_size is 8Kb in oracle database

Please find the attached performance data (sar -u & sar -d )which is collected through scheduled cron job. we do not have measureware/open view.

All the LUNs are SAN disks.

Please answer my initial question: What is the impact if f_frsize & oracle db_block_size is not same and at what extent it can be reason for system slow performance (Write I/O Wait is very high)

Re: What should be the value f_frsize if my db_block_size is 8Kb in oracle database

Ayaz,

This is performance monitoring and tuning - there is no simple answer to these questions - we can only make broad handwaving statements about what might happen without going into a LOT more detail than is possible on a forum.

I've already given my opinion that using the same block size (f_frsize) for the filesystem as for the database will help some. I've no idea whther it will make 1%, 5% or 50% difference... what I do know is that if you are writing 64K, on a system with a DB block size of 8K and a filesyetm block size of 1K, you are likely going to be doing too much physical IO and generating too much system CPU. In your sar data you didn't break out the system vs user space CPU utilisation it would be nice to see that. Also you haven't indicated whther you have glance or measureware available which could usefully show some more interesting metrics.

Hein,

Caveat Emptor: I'm not a performance specialist and I know you are, so please correct me if my thinking here is wrong...

I can't see how having a filesystem configured to do unbuffered IO is going to change the block size of data written into the filesystem? In fact in some cases I could see it making things worse if the filesystem block size and write block size are different (for buffered IO at least there's the chance to roll things up into a more reasonable size in cache). Can you explain your thinking here a little more?

HTH

Duncan

I am an HPE Employee
Accept or Kudo

Re: What should be the value f_frsize if my db_block_size is 8Kb in oracle database

Hein,

OK ignore me... just read the following in the JFS tuning WP:

Note that outside of Direct I/O, the block size is not important as far as reading and writing data
is concerned. The block size is an allocation policy parameter, not a data access parameter.

HTH

Duncan

I am an HPE Employee
Accept or Kudo
Hein van den Heuvel
Honored Contributor

Re: What should be the value f_frsize if my db_block_size is 8Kb in oracle database

>> Please find the attached performance data (sar -u & sar -d )which is collected through scheduled cron job. we do not have measureware/open view.

Unfortunately the attached sar data was reduced and prettyfied to the point of becoming utterly useless. Just give us the facts 'mam! No interpretation or parsing. The raw sar data.
There is no context for the averages. How many IO/sec were being done? 1?
How many MB/sec? What lun is doing what?

Also, the time window selected is suspect. 8pm - 9am. Is that indeed the time of the day when the system performance is critical?

>> All the LUNs are SAN disks.

Good, now go on... How many drives behind them? How much cache? Write back caching enabled? Storage observed stats? read-write ratio? how big are the pipes (fiber speed?),...

>> Please answer my initial question: What is the impact if f_frsize & oracle db_block_size is not same and at what extent it can be reason for system slow performance (Write I/O Wait is very high)

Absolutely ZERO effect.
frsize is only relevant during ALLOCATION of the disk blocks to the files. For oracle those are allocated or extended in big whopping chunks on (typically) relatively clean disks.
The (perceived but unrealistic) risk is that when the fragment size is less than the oracle page size, a dp page could potentially be split over multiple fragments and thus, deep down, cause multiple physical IOs for 1 page IO. Not going to happen!
But check the specific file fragmentation for proof.

Your tasks as a SA is to try to connect the DBA information to the system information and onwards to the the storage. That's where you can add value, explain, and potentially influence. The system information on its own is largely irrelevant until use to confirm/explain/question what Oracle statistics indicate and how that maps onto the system as a whole.

>> SAP recomendation regarding the above mentioned parameter of vxfs

Ask them why, where that is documented, and _when_ it was believed to be relevant. It ain't 1992 no more. 100 GB is not a great deal of storage no more.

I suspect it is just folklore and fluff.

Regards,
Hein van den Heuvel ( at gmail dot com )
HvdH Performance Consulting
Hein van den Heuvel
Honored Contributor

Re: What should be the value f_frsize if my db_block_size is 8Kb in oracle database

Reply collision!

Duncan wrote> The block size is an allocation policy parameter, not a data access parameter.

Right. Wish you had written that 20 seconds earlier, then I would have seen it before typing that all up :-)

Cheers,
Hein

Ayaz Ahmad
Occasional Advisor

Re: What should be the value f_frsize if my db_block_size is 8Kb in oracle database

Thanks for all your relevant replies..

1. We do not have measureware/Openview.

2. We have a sap implementaion in cluster environment. DB Package is running on database server & CI Package is running on SAP Central Instance Server.

3. SAP team is telling jobs are taking a lot of time to complete and as per them there is WIO while writing.

4. We are collecting performance data through cron job script.
for disk :
cat sar.sh
#! /bin/sh
TERM=vt100
export TERM
DATE=`date "+DATE:%m_%d_%y"`
cTIME=`date | awk '{print $4}'`


# ******* IO ******
echo $cTIME >>/var/itc/SAR/$DATE
sar -d 1 60 | grep -i Average >>/var/itc/SAR/$DATE

for CPU: on every 5 Min.
sar 1 7 | grep -i Average >>

For Free Memory:
echo $cTIME >/var/tmp/test
top -f /var/tmp/test
grep Memory /var/tmp/test | awk '{print $8}' >>/var/itc/Free_mem/$DATE

5. The data attached previously is all from data disks (sar -d, average queue, wait & Service time)

6. For more analysis please check the attached docs.

7. As there are different teams like storage, application, and OS administration. I have control only part on OS perspectives. Please suggest me what information I would need to collect from other team if required. Better you guide me in detail.
8. mount -p output. < output trucated>
Total 49 File systems
/dev/vx/dsk/r1sp1dbdg/sapdata1 /oracle/SP1/sapdata1 vxfs ioerror=mwdisable,largefiles,delaylog,dev=100d6d8 0 0
/dev/vx/dsk/r1sp1dbdg/sapdata2 /oracle/SP1/sapdata2 vxfs ioerror=mwdisable,largefiles,delaylog,dev=100d6d9 0 0
/dev/vx/dsk/r1sp1dbdg/sapdata3 /oracle/SP1/sapdata3 vxfs ioerror=mwdisable,largefiles,delaylog,dev=100d6da 0 0
/dev/vx/dsk/r1sp1dbdg/sapdata4 /oracle/SP1/sapdata4 vxfs ioerror=mwdisable,largefiles,delaylog,dev=100d6db 0 0
/dev/vx/dsk/r1sp1dbdg/sapdata5 /oracle/SP1/sapdata5 vxfs ioerror=mwdisable,largefiles,delaylog,dev=100d6dc 0 0
/dev/vx/dsk/r1sp1dbdg/sapdata6 /oracle/SP1/sapdata6 vxfs ioerror=mwdisable,largefiles,delaylog,dev=100d6dd 0 0
/dev/vx/dsk/r1sp1dbdg/sapdata7 /oracle/SP1/sapdata7 vxfs ioerror=mwdisable,largefiles,delaylog,dev=100d6de 0 0

9. one system output:
$ kctune |grep -i dbc
dbc_max_pct 4 4 Immed
dbc_min_pct 2 2 Immed

Please share if you have better script for performance monitoring.
Hein van den Heuvel
Honored Contributor

Re: What should be the value f_frsize if my db_block_size is 8Kb in oracle database

Thank you for those details. Those pictures say more than a thousand words. With a setup like that, it is reasonable to expect very fast write IOs in the 1 ms range, or even lower.

Yes, I expected those teams to be in place. Teh challenge for an SA, or performance consultant, it to speak enough of the special lanuage they each use to connect the dots. Not trivial!

What I'm missing most is selecting direct-io
Mount options: log,mincache=direct,convosync=direc

Google for +directio +oracle +site:hp.com
http://www.orafaq.com/node/27

Once you get a bit more data out there you may well have done all you can at this point. Time to call in the troops! If you can, go back to the folks who designed the systems and check with them that all is functioning as expected. Or maybe an independent performance consulting excercise is in order, this looks like several hours, if not days, of work to get to the bottom of it all. Well beyond free advice in a forum.

Also, SAP collects OS data very nicely, and may allow you all to correlate the timing of load and measurement better.

Good luck,
Hein

Ayaz Ahmad
Occasional Advisor

Re: What should be the value f_frsize if my db_block_size is 8Kb in oracle database

I just check Our live cache server (LCP) is configured with direct io

/dev/vx/dsk/r1lcpdg/sapdata1 /sapdb/LCP/sapdata1 vxfs ioerror=mwdisable,largefiles,mincache=direct,delaylog,convosync=direct,dev=1007d00 0 0
/dev/vx/dsk/r1lcpdg/sapdata2 /sapdb/LCP/sapdata2 vxfs ioerror=mwdisable,largefiles,mincache=direct,delaylog,convosync=direct,dev=1007d01 0 0

$ kctune |grep -i dbc
dbc_max_pct 20 20 Immed
dbc_min_pct 2 2 Immed

As LCP is for MaxDB(SAP Database)

Few times back we tried to put Direct IOs on the other servers also, the performance degraded more we rolled back.

The performance issue is with both the servers (LCP & SP where oracle DB is running)
Bill Hassell
Honored Contributor

Re: What should be the value f_frsize if my db_block_size is 8Kb in oracle database

The 8192 block size is meaningless for performance. It is a bookeeping feature for VxFS and has virtually no effect on the size of actual I/O. 20 years ago, the database block size and the filesystem layout was fairly important. But today, even a terrible program that reads or writes very small records (like 8k) will find HP-UX collapsing these sequential blocks together and creating 128KB (or larger) I/O's to/from the disk. Add to that the filesystem buffer cache and disk I/O is probably not a significant issue. When the avserv time is less than 10-15, you need to look elsewhere.

The first thing to ask is the DBAs how they know the performance is bad. Compared to what? Was perfroamnce much faster in the past and now it is very slow? I would check the Oracle stats. Are row accesses completing with full indexed reads, partial indexes or completely sequential? An unbalanced index can create thousands of unnecessary disk I/O's -- the system is doing what it was asked to do, and at reasonable performance, but there may 100 times more disk I/Os than necessary.


Bill Hassell, sysadmin