Re: *Measuring sql performance (elapsed time and scalability) by number of logical reads
- From: Mladen Gogala <gogala@xxxxxxxxxxxxx>
- To: tanel.poder.003@xxxxxxx
- Date: Tue, 09 May 2006 20:11:29 -0400
On 05/09/2006 11:34:22 AM, Tanel PÅder wrote:
> If anybody bothered to read until the end - there is little we can do to fix
> such memory latency related problems, but nevertheless, there are some.
> First, using Oracle's buffer cache and direct IO instead of OS buffer cache
> to reduce system calls. Also using large memory pages and SGA locking into
> memory where available, to reduce the number of VM mapping entries, sharing
> them among processes and perhaps reducing number of levels in OS kernels VM
> mapping index structure - thus improving overall CPU cache efficiency.
>
> Kevin, did I get the last part right? ;)
Well, I'm not Kevin, but I must disagree with the locking part. Contrary to
popular belief, shared memory is "locked in memory" for most of the time.
Page update and swapping processes do not treat all in memory pages as equals.
Different types of pages are treated differently. Read only pages which are not
used are typically thrown out first as they require only minimal handling since
they already have backup on disk. After them, various data pages are first
backed
up and then freed. Shared pages are at the very end of the food chain and are
accessed last, only if there is a severe memory shortage on the system.
Locking page in memory means to set a flag which will prevent updater to throw
it
out when there is a severe memory shortage. That prevents page updater from
freeing
enough memory for system to operate normally, so page updater gives up and calls
for his bully brother - the swapper. The swapper doesn't operate on pages, it
operates on processes. Swapper goes through the process table, picks the lowest
priority process in the "sleep" state, maps all of its segments as buffers and
writes them to the swap partition. Only .u area remains in-core. Then it picks
the
next process until it ends up writing out processes with the status "runable".
When swapper does its dirty work, you're likely to end up with essential
services,
like printer control process to end up in the swap partition. Of course, the
very
next "lp" will page "cupsd" back in and you are back on the beginning.
Thus, locking of SGA may actually cause update process ("paged") to give up and
call the big bad brother. That usually means trouble.
How does "paged" select pages? The algorithm is called "two handed clock". The
first
hand sets the "invalid" flag for the candidate pages. The second hand comes in
after a
specified time interval, usually a parameter, except on Linux, and throws all
the pages
that still have the "invalid" flag set out of memory. If the page was used in
the period
between sweeps, the invalid flag would have been reset back to "valid".
Therefore, a
necessary condition for a page to get thrown out is not to be used for certain
period of
time. Shared global area of an active database is highly unlikely to satisfy
that condition.
Locking doesn't do anything. It may help you with scr..ing your system up,
though.
Kevin knows much more then me and I am interested in seeing his take in this.
--
Mladen Gogala
http://www.mgogala.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
