Block size tuning questions

[Gordon Fong]

Hi all,

Have checked there are many many articles relating to above typical Q here. Currently our system has tune these parms but result in very poor performance. Maybe we have set wrong parm. on this. Hope hope you guys can share points of view on this.

Our system is running 48G memory with 32G dynamic buffer cache ( Yeah, it's huge ^_^ ). Our DB , Gemstone, are running 34 extents, 1 extent is one large file 4.2G each. Besides, we have one application data dir which consists of 80K ( number ) small files ( Yeah, it's also huge ). Our tuning on this block size is from 8K ( default ) to 64K. But it fail as the performance is very poor. Both LV is striped. DB LV is striped with 8K block size, while the data LV is striped with 16K block size. I have checked some doc saying that "When striping with LVM, one should make sure that the file system block size and the LVM stripe size are identical. This will aid performance. " Is that true? It maybe the reason why we get the poor performance after tune but I have no idea on this. Also as a newbie, what counters should I pay attention ( I only get very standard 'sar' to monitor... ) after tuning the parm? As our system is really huge, it's really an headache for me to tune best the system, hope those experts can share way / advise how to tune such an "monster", thx.

Bgds,
Gordon

[Michael Tully]

Down boy down.....

Why would you have 32Gb of buffer cache....
What sort of disk array do you have?

To monitor your system performance do you
have glance installed? (/opt/perf/bin/glance)
This will give some online stats far better
and more easily understood than anything
that sar has to offer. If you don't have it
installed you can easily install it from
your application CD set. There is a 60 day
trial copy there. Use 'swinstall' to install
it with no outage.

[Gordon Fong]

HI Michael,

Thx for your reply, indeed I do notice many experts here recommend to tune down the buffer cache size, maybe 4 to at most 20% buffer cache range is enough, however our DB size is 120G and if we want to make use of this very large buffer cache to cache data ( Our application is read intensive ), so is it setting sensible? I have glance installed but somehow not know what counter should I read. I do check the Page In / Page Fault but somehow it's no big change before / after tuning? Can u give some advise? Thx.

Bgds,
Gordon

[Michael Tully]

Hi,

Somewhere in the vicinity of 300-500Mb is usually a sufficient size. I have a 180Gb database instance here on an rp8400 server running 8gb RAM 2gb Swap and 400Mb of buffer cache. It currently uses less than 50% of memory and buffer cache.

When using glance what is the total amount of memory and buffer cache being used on your system?

Does glance report heavily utilised disk IO %?

Michael

[Gordon Fong]

Hi Michael,

As we are using dynamic buffer cache, our peak days should be Monday to Friday. Monday buffer cache size is somehow 9G and then when in Friday, it usually grow up 30G, it's cumulative. Before tuning, it's 8K block and usually disk util. is around 30-50% in glance. After tuing, 64K, it grow up 80-90%. Is this help? Thx.

Bgds,
Gordon

[Gordon Fong]

Hi Michael,

Supplement. We are using EMC 3930 with 12G disks cache in the array box. As talked , the LV's is already stripped across 2 physical.. Thx.

Bgds,
Gordon

[Tim D Fulford]

Buffer cache issues asside....

If your LVM stripe is 16K but your LUNS are striped with 8K. This could be a problem as when it writes to each LUN it will need to do two IO's! The disk will effectively have 1x seek + 2x latency + 2x transfer. I guess the disks are 10,000 rpm, that means you will be waiting for an extra 6ms for each IO!! EMC You would normally expect low service times say 4ms per IO, so 6ms is alot.

I would tune down the LVM to 8K and see what happens.

Tim

[A. Clay Stephenson]

Since you don't mention the filesystem type, I am going to assume that it is vxfs; if it's hfs change to vxfs. Regardless of filesystem block size, vxfs writes in 64k chunks. I would leave the database block size at 8k to 16k and stripe the LVOL's at 64k. That seems to be the best all-around performer.

I strongly suspect that your 32GB buffer cache is killing you. The search times within the buffer cache may well exceed the time required to do the I/O. I have never founds any benefits
above about 1GB and usually the marginal improvements in buffer cache hit ratio's are very small about about 400MB (10.20,11.0) or 800MB (11.11).

[Gordon Fong]

Hi A.Clay ,

Yes, u are right that we are using vxfs filesystem. Can u share where can I found the research about the large cache size impact to the system? Thx.

Bgds,
Gordon

[Jarle Bjorgeengen]

Hi,

from what I have experienced a smaller buffercache is recommended for DB systems (preferably static i.e. dbc_min=dbc_max=2-5%), and bigger BC for file-server systems. After all the DB software offers buffering in memory, for fast data access. Why not shrink the BC, and increase the DB shared memory segments ?

Also when setting up database servers for SAP, the SAP R3 operations centre in HP Boblingen recommends using distributed filesystems, also referred to as extent based striping. This gives the best performance for the random I/O that is typical for DB access.

The filesystem blocksize should be equal to the DB blocksize.

Rgds Jarle

[Stefan Farrelly]

I doubt you have 32Gb of HP-UX buffer cache. This will prove it;

sysdef | grep bufpages

And take the output number, multiply by 4096 (page size) and this is the size of your current cache in bytes. Normally the max is 300-350 Megabytes. Anymore than that actually slows performance.

I suspect either your EMC has 32Mb of disk cache, or your Oracle SGA size (shared memory) is set to 32Gb. Can you confirm ?

The ideal LVM stripe size is 64k, although I have ocassionally seen 32 or 128k slightly better. 16 seems far too low to me.

The following command will report memory/swap util;
swapinfo -mt

And this command will show if any paging is going on;
vmstat 1 1

(look at the pi/po fields - should be zero)

[Byron Myers]

Knock down that buffer cache! It hurts peformance on large Oracle instances. With The Symmetrix UNIT you are getting a tripple hit in performance for each cache miss. Hit #1: Data not in the ORACLE SGA, so it goes to the file system to get data. Hit #2: HP-UX scans its buffer cache for data (very big performance hit) - miss number 2, so HP-UX goes to disk on the Symmetrix unit. Hit #3: cache miss in Smmetrix cache - so data read from disk. If the data is not in the ORACLE SGA, then it's probably not in the file system cache. And if the data is not in the 30 GB of file system cache - it's not going to be in the Symmetrix cache. Get that buffer cache below 1 GB and you will probably see a big performance gain.

[Gordon Fong]

Many thx for your advice.

Stefan, we do have 32GB buffer cache in our Superdome domain, I have double verify it with your command provide and glance also. Our read / write hit ratio for this big cache is over 90% / 50% respectively in most time, as talked our application is read intensive. This behaviour is expected.

No paging is observed in our domain.

Memory info as below:

Mb Mb Mb PCT START/ Mb
TYPE AVAIL USED FREE USED LIMIT RESERVE PRI NAME
dev 1504 0 1504 0% 0 - 1 /dev/vg00/lvol2
dev 16800 0 16800 0% 0 - 1 /dev/vg00/lvdump
reserve - 6112 -6112
memory 38130 35092 3038 92%
total 56434 41204 15230 73% - 0 -


Jarle,

Our superdome is quite complicated to classify as application server / filesystem server. I would say our server is both type of server. DB is located in this domain, also it has some huge filesystem ( over 60000 files in dir ) which serve as an NFS server to 4 Sun Micro domains. From your information provide, sounds the standard for tuning on this 2 type of server is totally different, right? Since our DB is 32bit single thread process, so at most I can only achieve 1.9G shared memory by setmemwindow...that's why we need to discover the huge BC...

Thx again for all your input.

Bgds,
Gordon

[Wodisch_1]

Hi Gordon,

I do not know "GemStone", but I can only guess that it behaves pretty much like every other database when accessing data:
- due to transaction processing write access must be synchronous quite often
- read access will be buffered by the database itself
So, a huge UN*X buffer cache will simply NOT be used for all the synchronous writing (called "write-through-buffer-cache" feature) and help much less than an increased database shared memory.
Hence, get the documentation about that database system of yours, and read specifically about those two areas!

Just my $0.02,
Wodisch

[John Payne_2]

Gordon,

This is what a large buffer cache gets you. You want to do a read. The system does the read request, and you have to sit and wait for the system to decide if the file is in the cache. For 32GB, you sit and sit and sit. Then you either get your file, or your then have to go to disk and get the file, read it into the cache, and then give the data to the process. When you are doing many transactions, that is alot of overhead waiting while you are traversing the buffer. A word of advice:

"Just read from the disk already!!!"

Disks today are faster than a few years ago. The buffer cache really protected us from those older slower disks. With faster disks, a buffer cache really becomes even less needed than in the past. Tack a cache on your SAN, and the buffering your DB does, and you efectively have buffering on top of buffering on top of buffering. Not good.

I hope I have been able to explain why you do not want such a large buffer..

Hope it helps

John