Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
General
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Ayaz Ahmad
Occasional Advisor

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

We are having a performance issue, we suspect I/O bottleneck, can anybody help this may be because of our OS f_frsize is 1Kb and Database db_block_size is 8Kb
output below:
From OS:
fstyp -v /dev/vx/dsk/r1sp1dbdg/sapdata31
vxfs
version: 5
f_bsize: 8192
f_frsize: 1024

From Oracle Database:
DB_BLOCK_SIZE=8192

Please also clear what is the difference between f_bsize & f_frsize. My file system is vxfs.
OS-HPux version 11.23
DB- Oracle 9i

SAP recomendation regarding the above mentioned parameter of vxfs.
18 REPLIES
T G Manikandan
Honored Contributor

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

f_bsize block size (8K) largest possible block size (8K)
f_frsize fragment size (1K) actual block size (usually 1K)


So your filesystem is running with 1k blocksize. When you create the filesystem using newfs command you need to use -b to provide the blocksize.

You cannot change the block size of a filesystem without recreating it.
Duncan Edmonstone
Honored Contributor

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

running fstyp for a VxFS filesystem can be a little confusing. In the case of VxFS its the f_frsize that represents the filesystem blocksize that you need to match against Oracle's block size.

You get a clearer indication from "mkfs -m"

mkfs -F vxfs -m /dev/vx/dsk/r1sp1dbdg/sapdata31

Don't worry, mkfs -m is non-destructive.

In your case it looks like you'll have to recreate your filesystem with the correct newfs options for a 8k blocksize

HTH

Duncan

HTH

Duncan
Ayaz Ahmad
Occasional Advisor

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

Thanks for the info.

But does it anyway may hamper io performance of the system.

What is the sap recomendation for block size of the vxfs file system where database files are residing.

I found a difference between db_block_size & f_frsize on my system. Is this setings creating the performance issue?
T G Manikandan
Honored Contributor

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

Yes definitely, but I dont have the stats with me for the percentage drop with performance.

Oracle recommends that your database block size be equal-match, or be multiples of your operating system block size.

1k to 8k is quite significant.
Duncan Edmonstone
Honored Contributor

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

So as you are currently configured, every Oracle IO is going to generate 8 filesystem IOs - not ideal, but maybe not an issue depending on your IO load profile.

Re-creating the filesystems with 8K blocksize might help, or it might just slightly reduce the amount of SYS CPU time for Oracle processes, but it certainly won't make the situation worse.

Given that its a lot of work to do this (you have to newfs your filesystem so a full backup and restore will be required), you probably want to have some confidence that this is actually causing you a problem first.

Do you actually have a performance issue on this system?

HTH

Duncan

HTH

Duncan
Ayaz Ahmad
Occasional Advisor

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

Hi Duncan,

Yes, we are facing a major performance issue, sap team is telling Disk Average Write time on the system is very high, as per them system is taking 5ms for 8kb and 30ms for 64kb whereas it should take around 10-12 ms for 64kb instead. from OS perspective we get value of AVSERV within 6ms from sar -d output, AVWAIT is less than 0.5ms. I believe that AVSERV counts for both read/Write operation,
What is the way to find out average service time for Disk write operations only?
Duncan Edmonstone
Honored Contributor

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

If you have measureware/performance agent you can pull out these stats by looking at the BYDSK_AVG_WRITE_SERVICE_TIME metric.

Do you have measureware/ovpa installed?

You can check using:

mwa status

if that comes back with something meaningful I can talk you through using measureware to get this data.

HTH

Duncan

HTH

Duncan
T G Manikandan
Honored Contributor

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

# time dd if=/dev/zero of=/testfile bs=1024k count=1024

This will provide you the write time into the disk for creating a 1G testfile.
Hein van den Heuvel
Honored Contributor

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

Is it not time to move forward to Oracle 10g first and foremost? The system is missing out on 5+ years of development!

IMHO the files system block size should be entirely irrelevant for a properly setup system using mount options mincache=direct and convosync=direct.

What is behind this sapdata31?
Your data is almost reasonable for a single direct connect disk. but typically it is NOT just a direct connect simple disk , but a lun on a controller with some level of writeback cache probably connect with a fibre.
When the latter operates under its limits, you should really expect WRITE IO times of 1 or 2 milliseconds for 64kb, not 10+, because the write will be to controller memory.
Of course there need to be enough real disks behind the lun to suck up the throughput requirements.
The reads will typically involve getting data of a spinning disks, so 5+ ms there is fine.

If the write are slow, then either the write back cache is not operational (check batteries!?) or under configured (MBs), or the load in MB/sec is sustained higher than the combined disks can comfortably handle.

Ask your storage specialist on their opinion on the situation: IO/Sec, MB/sec and (thus) KB/IO, Response times.

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

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)
Duncan Edmonstone
Honored Contributor

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

HTH

Duncan
Duncan Edmonstone
Honored Contributor

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

HTH

Duncan
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