Performance

[Roger Baptiste]

Hi,


(N-class, 11.00, oracle 8.6 database server)
6Gb RAM/ 8-CPU
swap - 7Gb (including pseudo-swap)
dbc_max - 10% ; dbc_min - 5%

Swapusage doesn't go beyond 70%
in swapinfo -mt output (device swap is not atall being used);

Memory usage doesnt go beyond 80% (max) (glance output);

But, i see some paging activity in vmstat -n ouput (po value is occasionally 3 );
vmstat -s shows 272230 page outs . This seems high, since the system was rebooted two days back and this value is cumulative since that time.

Cache hit rate is absymal. (both read and write) - ranging from 20% to 40%. (sar -b output)


In addition:

sar -q shows up high values under runq-sz and runocc: Here is a sample:

13:28:05 runq-sz %runocc swpq-sz %swpocc
13:28:10 4.6 39 0.0 0
13:28:15 2.7 100 0.0 0
13:28:20 2.8 100 0.0 0
13:28:25 2.8 100 0.0 0
13:28:31 3.3 95 0.0 0
13:28:35 2.8 82 0.0 0
13:28:40 3.0 97 0.0 0
13:28:45 2.4 80 0.0 0
13:28:50 2.3 90 0.0 0
13:28:55 3.7 80 0.0 0

Average 2.9 86 0.0 0
*****

My question is :
any reason for the paging to occur, even though memory and swap usage is not high ??
This system is CPU-intensive and the plan is to add more CPUs in the near future. But, can CPU load affect memory usage?

thanks
raj

[harry d brown jr]

Rajman,

I'd normally say that maybe it's due to "stale" pages, but I just checked some of my machines that have been running for almost 5+ months, without any pages out. I would change dbc_max to 5% and min to 2%, but that isn't your problem. I'd blame oracle!

live free or die
harry

[Printaporn_1]

Hi,
In Oracle , try increase database buffer cache.

[Bill Hassell]

>> But, i see some paging activity

Swap will be used when processes request memory mapped files...perfectly normal and has nothing to do with paging due to lack of RAM. Also, page-outs in the single digit range are unimportant. You would want to look more carefully if the page-out rates are in the 50-100 range on a regular basis.


>> Cache hit rate is absymal. (both read and write)

Generally, this is due to a small buffer cache. However, if RAM usage is not at 100%, then your buffer cache is probably close to the 10% max value or 600 megs, so you might try bumping the value to 15% or 20% (900 to 1200 megs) since you have a lot of RAM and fast processors.

>> sar -q shows up high values under runq-sz and runocc:

These values (runq-sz = 2.9) are VERY LOW! A runq-sz of 6 would be normal (all 6 processors are maxed out), and runq's in the 10 to 20 range may not be bad...it all dfepends on what the processes are doing. runq depth is not a simple metric for analyzing performance issues.

>> But, can CPU load affect memory usage?

No, there is no correlation at all. A process asks for whatever memory it needs. If you are running Oracle, then your config file defines the size of the SGA (shared mnemory) and Oracle will request this amount. Whether you have 2 CPUs or 64 CPUs makes no difference to the behavior of a single program. Multiple CPUs do only one thing: run multiple processes at the same time.

The majority of performance issues are resolved by changing the application or the way in which the application requests or writes data. A poorly written set of SQL statements, or a database with an inadequate number of indexes will uselessly overload the system and while disk striping and multiple channels and faster processors may improve the performance, fixing the app almost always results in significant improvements in performance.

[Carlos Fernandez Riera]

Hi Raj:

Once more time it seems ( to me) to bea a misconfigured swap area

1- You'd said swap space is 7GB including pseudo-swap... so first thing to do is raise swap space to at least 6GB plus pseudo-swap.

2- Memory ut. is not over 80% max. I dont recall exactly when paggin begins ( 7/9 or 6/7 or something like this), but 80% should be a closed value to.

3- The effect on yor server should be in two ways:
a- reducing cache size, from dbc_pct_max
b- pagging. vhand process must have work to do. run top and see if it is on the first page.


4- I think attached file is realy intresting...

[Roger Baptiste]

Bill,
<>

The buffer cache was 20% (1.2Gb) earlier and it had the same poor hit rates. Since, 1.2 Gb seemed too high, i reduced it to 10%. But, the cache hits remains the same.

thanks
raj

[Roger Baptiste]

Carlos,

I don't think Swap is a problem here. Here is swapinfo -mt output:
Mb Mb Mb PCT START/ Mb
TYPE AVAIL USED FREE USED LIMIT RESERVE PRI NAME
dev 1024 0 1024 0% 0 - 1 /dev/vg00/lvol2
dev 1024 0 1024 0% 0 - 1 /dev/vg00/lvol16
reserve - 2048 -2048
memory 4704 2384 2320 51%
total 6752 4432 2320 66% - 0 -

As you can see, the device swap is not even touched! (0% usage). vhand process is normal. Not heavy.

Thanks for the document. I remember seeing it earlier.

-raj

[Wodisch]

Hi,

you use the file-system to store your Oracle-database, right? (Don't we all these days? :-)
Then you should try to use the Online-JFS's mount-options "mincache=direct,convosync=direct" at least, since I guess the biggest part of your disk I/O is done by Oracle, and hence your UNIX buffer-cache is quite useless there...

Then what is your kernel's "timeslice" value? Not the dreaded "1" from that template, I hope!

Next is your Oracle instance's "init*.ora" file, i.e. the parameters in there (8.0.6, I think - or do you really run 8.6???). Check for
- shared_pool_size > 500Mb
- db_block_size >= 8192
- db_block_buffers * db_block_size > 1GB
- log_buffer_size >= 1MB
- sort_area_size >= 16MB
- sort_area_retained_size >= 4MB
- lock_sga = true
- pre_page_sga = true
- cpu_count > 3

What application are you running, btw? Does it use "memory mapped files"? That would explain your paging (AFAIK mmap-ed file-I/O is done using the paging routines).

Just some 0.0002,
Wodisch

[Carlos Fernandez Riera]

Raj:

Just try it....

see at reserved... it is the same as your swap space... i am asking why you dont get messages as ' no enough memory' on syslog.

Out of jokes... you must have at least swap size as memory size. Of course your swap space is not used, you have enough memory to do pseudo swap, and it will remaing while you dont add swap.

[Roger Baptiste]

Woodi,


Yes, it is a FS based database.
timeslice is 10.
Yes, it is 8.0.6 ;-)

A grep on the params you mentioned gives the following values in the init.ora file:
db_block_size = 16384
db_block_buffers = 30000
shared_pool_size = 80000000
sort_area_size = 10485760
sort_area_retained_size = 1048576
(other parameters are not set)

The system is a database server, hosting three databases, mostly dataware stuff.
The database/queries/sql stuff obviously needs lot of re-working and that will be taken care by the app folks.
But, i am trying to make sure whether i can tweak things at the O/S end too.

thanks
raj

[Carlos Fernandez Riera]

Hi Raj:

I still think you must add disk swap space... but be yourself...

2- The reserved size is 2GB so you have a lot of memory free.. about 4GB. I think you have read yet following doc, but see that again:

http://www.hp-partners.com/edaweb_public/html/technical_support/tuning.html#memwindo

So you can increase each SGA

3- Oracle:
set :
db_file_multiblock_read_count = 32

Try to increase sort_area_size

Analyze all tables and indexes, and specialy after a masive load. It is a known source of problems in Oracle8 when you use optimizer_mode = choose


And good Luck.

[Tom Geudens]

Hi,
I don't want to talk against someone here, but be carefull if you consider using the mincache=direct,convosync=direct options. Yes, they do work (and very well in fact) on most databases ... but I know for a fact that they don't work well for the Openview database and our Datawarehouse database.

Just my 0.02???
Regards,
Tom Geudens

[Krishna Prasad]

It seems to me that your problem can be Oracle,Disk System, or Code.

In Oracle check to see what you SGA hit ratio is. You want to have about a 90% hit ratio.

What kind of disk are you using? Is it a external RAID device? If so does it have a buffer cache? If you do have a system like EMC,Shark, etc...make sure that you don't have indexes and datafiles on the same physical drive. You can't check this by just looking at the luns seen by the OS. Of course you can check each filesystem/raw device and make sure you have no conflicts there.

Do you have any Oracle tools to check for poorly written SQL statements. I know Oracle has a tool that tells you how expensive an SQL statement is. It will show you if you are using indexes or doing full table scans. This software is called the Performance Manager for Oracle Enterprise Manager.

[Bill Hassell]

The special mount options MUST be limited to just the database mountpoints, no others. You must allow the opsystem to buffer rollback and archive logs as well as the Oracle binaries directory. It is very important to NOT mix pure database directories with the very busy log directories, and by mix, I mean the same physical disk. And you'll need the optional Online JFS product to use the special options such as mincache and convosync and as mentioned, these options bypass the buffer cache for the entire mountpoint. If any other apps are directly using it, they will experience a significant impact. Oracle has it's own buffer cache but other apps may not.

Also, if your Oracle is running in 32 bit mode, it will be crippled as far as SGA. Not only will the maximum size for SGA be limited to less than 1Gb (almost 2Gb if you relink the binaries) but Oracle must contend with all other applicatins using the same (small) shared memory map used by all 32 bit apps. So increasing the sort area in memory is very good (to bypass the temp sort area on disk) but Oracle may not be able obtain enough shared memory (SGA).

This area can easily become fragmented so that Oracle reports out of memory when starting, even though there is more than enough in smaller pieces. To avoid this, you can implement memory windows. Be sure to read the 3 documents on memory management in /usr/share/doc: proc_mgt, mem_mgt and mem_wndws. Note that SGA limitations disappear with 64bit Oracle.

[Mark Greene_1]

It looks like the problem is due, in part at least, to the way the database is laid down on the disks. Have you done any iostats to see how disperse the disk usage is? If you have logically related tables on different disks, then you are going to have lots of extra i/o going for every read and write.

HTH
mark

[Craig Rants]

I think that this may be an application problem.

- You are paging although there is no memory pressure
- Your Buffer Cache hit rate remains the same no matter what the size, thus 10% is a good figure and Buffer Cache is not the problem
- You are not swapping or "deactivating" processes

There is a problem known in 11.x where applications perform operations on memory mapped files.

I just attended a Performance OR from Stephen Ciullo, and he talks about all this. Let me tell you he is "The Man" when it comes to performance. Don't take my word for it, see what others have to say as well

http://bizforums.itrc.hp.com/cm/QuestionAnswer/0,,0xaaae6af52b04d5118fef0090279cd0f9,00.html

Anyway, he has a guide that explains all this and things make a lot of sense know. So check it out, I am sure that he has the answer somewhere in there.

Also, that's where I got my hunch about the app problem

http://www.hp-partners.com/edaweb_public/html/technical_support/tuning.html

GL,
C

[Craig Rants]

Another link of interest

http://info-library.hp-tcsc.com/documents/uxperfcookbook.pdf

GL,
C

[Wodisch]

Hi again,

Tom: maybe you have used these options for $OV_DB or such? Or you are running the install setup of Oracle (rather bad idea: growing datafiles by single megabytes)

Raj: I think your shared_pool_size is still way too small (80MB is hardly enough for the whole Data Dictionary of Oracle itself)

Just my $0.02,
Wodisch