Hello All,
I was approached by DBAs, and they want me to create a large_page on AIX and also change oracle file systems to CIO.
They are complaining the system is taking longer time for processing and we are unnecessary wasting memory and CPU for file system cache.
To do that I have to change lgpg_regions and lgpg_size under VMO (though they are not restricted parameters, is it recommended to change them?)
I never changed that parameter for oracle and not sure how the impact will be.
I know wrong values or setting will degrade the performance.
I looked online and there is a pdf called 'AIX_tuning_for_Oracle_DB.pdf" it says create large pages for oracle.
Has anyone really changed those parameters and if they did, under what circumstances and did it really improved the performance of the database.
I increased the size of paging space as alternative (for now) as the system was very high on file cache.
AIX 7.1 TL01 SP06
Oracle 11gr2
Power7+ (770)
I was told disks are raid 10.
Database system
System has 4 entitled and virtual CPUs
Real mem: 85 gigs and Swap is 42 gigs (I increased it for time being)
See the difference after I increased the swap space. Now the consumption of swap is 1%.
I am not sure increasing the swap space helps and, as you know, whenever you are hitting the paging space, it's not optimum for performance. I think the the key performance is having a good history of what you're system is doing and also qualifying the performance problems. I would ask them to quantify what they mean by slow. Is their some transaction they can conduct to demonstrate reponse time/performance. What numbers are they using the qualify their statements.
I am advocate for changing parameters if you can know the history of system performance, qualify the response issue, and can measure the change after.
We followed the "Oracle Architecture and Tuning on AIX (v 2.30).pdf" guide, using CIO and Large Pages and gain a performance increase. Not a Big Bang but our Oracle team was satisfied.
Thanks XrAy!
Looking at the graph, can we conclude something?
While referring the doc I came to know that redo logs should be created in 512B blocks and rest can be default (4K). That means I have to recreate redologs file system.
Even though we I have shared CPU, its one to one (as entitled and Virtual is equal).
Ok,
It all depends on the type of system, large pages are intended for high performance computing applications which are memory intensive (such as oracle). Enabling large pages help and this is due to ....
"The large page performance improvements are attributable to reduced translation lookaside buffer (TLB) misses due to the TLB being able to map a larger virtual memory range. Large pages also improve memory prefetching by eliminating the need to restart prefetch operations on 4KB boundaries"
Now you need to know, whether your system is that memory intensive. Remember changing vmo parameter has it's affect if changed for no real reason and on guessing.
As asked by the admin in the forum, you need to know what is going slow and where.
CIO, you can do it, basically it will omit the layer of file system caching, thus saving you some memory and cpu time. But again it depends on the environment.
I looked at the graph no 2, you said real memory is 24 but the graph says otherwise (16 gigs).
Look at the error report is it complaining on memory?
The graph should reduce the green area (cache) once you enable CIO . And you know what to do with redo logs file system.
Ok,
AIX6.1 and AIX7.1 combined with oracle >= 11.2.0.2 introduced a new open flag O_CIOR which is same as O_CIO, but this allows subsequent open calls without CIO. The advantage of this enhancement is that other applications like cp, dd, cpio can access database files in read only mode without having to open them with CIO. Starting with oracle 11.2.0.2 when AIX6.1 or AIX7.1 is detected, Oracle will use O_CIOR option to open a file on JFS2. Therefore you should no longer mount the filesystems with mount option -o cio.
I can see you have AIX7.1 and 11gR2, so that should hold good.
As far as i know you need still set the following DB parameter:
To use CIO with JFS2 for Oracle 10g / 11g together with
ASYNC I/O operation, set the initialization parameter
FILESYSTEMIO_OPTIONS=SETALL in the (s)pfile.
One person has to set that parameter and that person is DBA. He/She (DBA) will set FILESYSTEMIO_OPTIONS=SETALL parameter in init.ora file, they will have to bounce the database after setting that value.
Again, one can go with ASYNC or SETALL, depending on the type and size of DB.