I/O tuning for oracle


 
Thread Tools Search this Thread
Operating Systems AIX I/O tuning for oracle
# 1  
Old 08-26-2009
I/O tuning for oracle

is it a good practice to enable AIO (Async I/O) and mount the oracle file system with DIO with JFS2 (Direct I/O) option?
please help
# 2  
Old 08-27-2009
Pchangba,

does 'it depends' count as an answer? Smilie In most cases CIO would be more benefitial for your database but its really hard to answer as long as you don't tell much about the OS and Oracle version, the kind of workload, the other specifics of your system and much more.

IBM says that direct I/O (DIO) tends to benefit heavily random access workloads while CIO tends to benefit heavily update workloads.

Generally:
Not all workloads benefit from the use of CIO (i.e. they get more benefit from filesystem caching). In some cases, it may be desirable to create multiple filesystems and allocate files based on their anticipated workload characteristics.

Therefore, individual filesystems could be mounted in CIO mode, or default caching mode, depending on the I/O characteristics of the files within the particular filesystem.


When CIO is used, the standard filesystem level I/O serialization mechanisms are disabled, which avoids inode contention issues.

If planning to use JFS2 CIO, be sure to isolate online redo logs and control files in a separate file system that was created with agblksize=512.

File systems which contain Oracle data (.dbf) files should be created with agblksize=4096 if db_block_size >= 4K or agblksize=2048 if db_block_size=2K.

On CIO-mounted filesystems that contain datafiles, the database block size needs to be multiple of agblksize.

Make sure that your oracle binaries, archivelogs and dumpfiles are NOT in the filesystems running on cio/dio.

If you are not sure what kind of DB you have, try both - cio and dio - and see what is more performant. Try as well using cio or dio for your 'dbf' filesystem while still using filesystem buffering for your redo's.

If you have AIX 5.3/6.1 and oracle 10.2.0.4 - try to use other mount options for your archivelogs: we always always use rbrw for archivelogs and in many cases for the redo's with great success (rbr = release behind sequential read, rbrw = release behind sequential read and write).

Please be aware all this will be of not much benefit at all when your data is not spread across as much devices as you can if its on SAN space and your hot tables moved to separate disk. A good practice is as well, to use inline logs for your datafilesystems instead of separate external logs.

If you are running AIX 6.1 you even might want to switch off logging completely for your archivelogs and dumps (when you create your filesystems with the nolog capability) - but this obviously is a risk in case of a server crash ... what isn't logged, cannot be restored. Anyways, worth to mention in case you want more perfomance when dumping out or restoring from backups. You can for example switching off the logging before you start the task and switch it on once completed.

In any case - if you decide for cio, make sure you go away from the default async io settings that AIX 5.3 has as a default since they are way too low.
Choose minservers = amount of your lcpus / threads, choose maxreqs = 65536 and try out what value of maxservers seems optimal for you - on most of our trading boxes, 200 maxservers seem to be perfect but some OLTP boxes were much better off after setting 300 maxservers.

Again - there are no settings that suit all ... you have to try what suits best to your database.

Hope this helps,
kind regards
zxmaus

.
# 3  
Old 08-27-2009
Thanks for the reply it will help me a lot.
I am actually using AIX 6.1 with Oracle 11g. Running a Banking Application (I-Flex).
I am sill in installation phase I can do as much R&D as i can, before going for production.

As far as i know if i use DIO it will not use filesystem caching and oracle has its own caching.
If both oracle caching and FS caching are used the performance will degrate..
So i am thinking of using DIO & AIO . what do u think.
you are absolutely correct there is no particular way for tuning IOs we have to test it and do according to the result.
I just want some suggestion coz planning of one mind can be incorrect also.
# 4  
Old 08-27-2009
Hi,

we're running your application in our company too - we just call it slightly different Smilie

Try to use cio and aio ... works fine for us. DIO was choice during JFS filesystems - these days its rather cio (btw as well what oracle rather recommends).

If you're able to reproduce the expected workload - try both in a stress test and a test batch and take the timings.

Rgds
zxmaus
Login or Register to Ask a Question

Previous Thread | Next Thread

8 More Discussions You Might Find Interesting

1. AIX

Tuning AIX for oracle

Dears i want to have a clear view about this tuning parameters and what they related to FS or Oracle , and how to figure the percentage of them . maxperm% maxclient% v_pinshm = 1 lgpg_regions = 0 lpgp_size = 0 (3 Replies)
Discussion started by: thecobra151
3 Replies

2. Solaris

nxge tuning

Hi all, I would like to tune the nxge card as suggested by this link, but got some confusion. Can anyone advise me ? We have SunOS hsbc02 5.10 Generic_137137-09 sun4v sparc SUNW,Netra-CP3260 Do I have to install any patches ? The tunning link Networks - Siwiki In our nxge.conf, I... (0 Replies)
Discussion started by: dehetoxic
0 Replies

3. Shell Programming and Scripting

Tuning function

This is my function in UNIX file. In this function I am -> first replacing spaces in character 19-27 with 0 -> then if it's all zeros ( 9 zeros) replace it with space The reason I have to make it to 0 first is that my requirement is that if this field is having value of 0 , replace it... (4 Replies)
Discussion started by: varunrbs
4 Replies

4. Web Development

MySQL Tuning Tools with mysqltuner.pl and tuning-primer.sh

We have been tuning MySQL lately and I ran accoss two useful tools that you might be interested in: mysqltuner.pl tuning-primer.sh Both of these scripts are quite useful for MySQL tuning. Here is some sample output of mysqltuner.pl >> MySQLTuner 0.9.8 - Major Hayden... (3 Replies)
Discussion started by: Neo
3 Replies

5. Shell Programming and Scripting

Performance Tuning

Hi All, In last one week, i have posted many questions in this portal. At last i am succeeded to make my 1st unix script. following are 2 points where my script is taking tooooo long. 1. Print the total number of records excluding header & footer. I have found that awk 'END{print NR -... (2 Replies)
Discussion started by: Amit.Sagpariya
2 Replies

6. Solaris

tuning

hw to increse nfs perfomence tuning? (4 Replies)
Discussion started by: naresh.sun
4 Replies

7. Shell Programming and Scripting

Oracle-performance tuning

Sorry, This is out of scope of this group.But I require the clarification pretty urgently. My Oracle database is parallely enabled. Still,in a particular table queries do not work "parallely" always. How is this? (9 Replies)
Discussion started by: kthri
9 Replies

8. UNIX for Dummies Questions & Answers

Performance tuning.

can someone tell me a good site to go to in order to learn this. please do not recommen nay books because i dont have interest in that. if you know of any good sites with good straight forward explanation on how to split loads on machines that has excessive loading, please let me know Also,... (1 Reply)
Discussion started by: TRUEST
1 Replies
Login or Register to Ask a Question