Direct reads & In memory Database

[Alex Greem]

Dear all,

Our database (DB2 Workgroup 7.2 FP12) is constantly under heavy load.
Most time CPU usage (1 Pentium3 1Ghz) is more 50% busy.
We have 3GB RAM memory
Our normal workload is 200-300 dyn.sql/s.

The action question is there way to eliminate direct I/O?
By definition direct I/O is I/O that bypass bufferpools and used for
LONG VARCHAR and LOBs data.
But there a lot of LONG VARCHARs in system catalog tables.
size of system catalog is 64 mb so i want to place it all in RAM
memory

Is this possible?

Thanks in advance,
Oleg

[Ian]

Alex Greem wrote:

Dear all,

Our database (DB2 Workgroup 7.2 FP12) is constantly under heavy load.
Most time CPU usage (1 Pentium3 1Ghz) is more 50% busy.
We have 3GB RAM memory
Our normal workload is 200-300 dyn.sql/s.

The action question is there way to eliminate direct I/O?
By definition direct I/O is I/O that bypass bufferpools and used for
LONG VARCHAR and LOBs data.
But there a lot of LONG VARCHARs in system catalog tables.
size of system catalog is 64 mb so i want to place it all in RAM
memory

Is this possible?

Thanks in advance,
Oleg

What's the catalog cache hit ratio? Are your direct reads all taking
place on SYSCATSPACE, or are they occurring in other tablespaces, too?

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

[Alex Greem]

Ian <ia*****@mobileaudio.com> wrote in message news:<40********@corp.newsgroups.com>...

What's the catalog cache hit ratio? Are your direct reads all taking
place on SYSCATSPACE, or are they occurring in other tablespaces, too?

SYSCATSPACE have 99.99 bufferpool hit ratio.
Most of direct reads happens in SYSCATSPACE.
approximately 1000000(syscatspace) vs 10000 (other tablespaces)
I suspect this because of dinamyc sqls, we have many SQL functions
(their bodys stored in LONG VARCHAR column in catalog), views, etc...

So my intention is eliminate direct i/o as much is possible.

[Pierre Saint-Jacques]

If one looks at how LONGVACHAR is handled in DB2, it will always be
handled by direct I/O and thus will NEVER go to a buffer pool. So,
loading all syscatcpace tables in a private syscat.spce buffer pool
wouldn't be the right approach.
There's no sense to load LONGVARCHAR in bp's as they would flood the bp
and impact all other apps.
They are therefore retrieved one by one, on demand, to the appl. private
memory of the agent requesting them. Thepointer is read from the row in
the page in thwe bp but the data is directly retrieved, using the
pointer, by reading the file and the long data is put in appl. private
memoty.

HTH, Pierre.

Alex Greem wrote:

Ian <ia*****@mobileaudio.com> wrote in message news:<40********@corp.newsgroups.com>...

What's the catalog cache hit ratio? Are your direct reads all taking
place on SYSCATSPACE, or are they occurring in other tablespaces, too?

SYSCATSPACE have 99.99 bufferpool hit ratio.
Most of direct reads happens in SYSCATSPACE.
approximately 1000000(syscatspace) vs 10000 (other tablespaces)
I suspect this because of dinamyc sqls, we have many SQL functions
(their bodys stored in LONG VARCHAR column in catalog), views, etc...

So my intention is eliminate direct i/o as much is possible.

--
Pierre Saint-Jacques - Reply to: sesconsjunk at attglobaljunk dot com
Reconstruct address: Remove the two junk and replace at and dot by
their symbols.
IBM DB2 Cerified Solutions Expert - Administration
SES Consultants Inc.

[Ian]

Pierre Saint-Jacques wrote:

If one looks at how LONGVACHAR is handled in DB2, it will always be
handled by direct I/O and thus will NEVER go to a buffer pool. So,
loading all syscatcpace tables in a private syscat.spce buffer pool
wouldn't be the right approach.
There's no sense to load LONGVARCHAR in bp's as they would flood the bp
and impact all other apps.
They are therefore retrieved one by one, on demand, to the appl. private
memory of the agent requesting them. Thepointer is read from the row in
the page in thwe bp but the data is directly retrieved, using the
pointer, by reading the file and the long data is put in appl. private
memoty.

Isn't this mitigated by the catalog cache? I asked the OP about his
catalog cache, not the bufferpool hit ratio for the catalog.

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

[Pierre Saint-Jacques]

To my knowledge, the catalog cache is used to store table and object
descriptors (from the catalog tables) that are required to resolve,
compile SQL statements.
When a stmt is to be compiled, DB2 does a loookup in the catalog cache
for the descriptors of the objects required for the work and will insert
the ones it does not find.
From your previous post where you mention function bodies being held in
longvarchar, it is possible that the function is folded as a descriptore
on the catalog cache (but I don't really know this) but it would still
be a direct read as the only buffered read that I know of are the reads
that are done in the bp (for syscatspace) in this instance.
From your previous post, you mention a syscatcpace of 64MB and wonder
if you could het it all in memory. Yes you could could by building a
64MB buffer pool and assigning syscatcpace to it.
However, this still does not solve your problem of eliminating the
direct i/o reads. They are longvarchar and they still won't get stored
in the row and will have to read from the file in which they are stored.

Instead of giving that memory to a bp for syscatcpace, try to see what
happens if you assign the same space to catalog cache.
You are in V7 so be careful as the catalog cache is assigned pages from
the dbheap and you will have to increase it by 64MB before you update
the catalog cahe to 64MB.
HTH, Pierre.
Ian wrote:

Pierre Saint-Jacques wrote:

If one looks at how LONGVACHAR is handled in DB2, it will always be
handled by direct I/O and thus will NEVER go to a buffer pool. So,
loading all syscatcpace tables in a private syscat.spce buffer pool
wouldn't be the right approach.
There's no sense to load LONGVARCHAR in bp's as they would flood the
bp and impact all other apps.
They are therefore retrieved one by one, on demand, to the appl.
private memory of the agent requesting them. Thepointer is read from
the row in the page in thwe bp but the data is directly retrieved,
using the pointer, by reading the file and the long data is put in
appl. private memoty.

Isn't this mitigated by the catalog cache? I asked the OP about his
catalog cache, not the bufferpool hit ratio for the catalog.

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

--
Pierre Saint-Jacques - Reply to: sesconsjunk at attglobaljunk dot com
Reconstruct address: Remove the two junk and replace at and dot by
their symbols.
IBM DB2 Cerified Solutions Expert - Administration
SES Consultants Inc.

[robert]

ol************@yahoo.com (Alex Greem) wrote in message news:<ac**************************@posting.google. com>...

Dear all,

Our database (DB2 Workgroup 7.2 FP12) is constantly under heavy load.
Most time CPU usage (1 Pentium3 1Ghz) is more 50% busy.
We have 3GB RAM memory
Our normal workload is 200-300 dyn.sql/s.

The action question is there way to eliminate direct I/O?
By definition direct I/O is I/O that bypass bufferpools and used for
LONG VARCHAR and LOBs data.
But there a lot of LONG VARCHARs in system catalog tables.
size of system catalog is 64 mb so i want to place it all in RAM
memory

Is this possible?

Thanks in advance,
Oleg

the trick is to use the o/s buffers, and pin same.

IIRC, on *nix anyway, one can memmap the file, then pin the o/s
cache. i've no recollection of the syntax. on windoze, no recollection.

[Alex Greem]

> the trick is to use the o/s buffers, and pin same.

IIRC, on *nix anyway, one can memmap the file, then pin the o/s
cache. i've no recollection of the syntax. on windoze, no recollection.

Yes, OS cache should help here - but i'm on windows and i just don't
know if it possible explicitly manage windows file cache's - may
somebody knows it?

So i see there no easy way to eliminate direct i/o on syscatspace...
That mean views,triggers,sql functions bodies always retrieved from
disk (or OS cache, if possible).
That's can quite limiting issue is high dynamic sql scenario, isn't
that so?

[robert]

ol************@yahoo.com (Alex Greem) wrote in message news:<ac**************************@posting.google. com>...

the trick is to use the o/s buffers, and pin same.

IIRC, on *nix anyway, one can memmap the file, then pin the o/s
cache. i've no recollection of the syntax. on windoze, no recollection.

Yes, OS cache should help here - but i'm on windows and i just don't
know if it possible explicitly manage windows file cache's - may
somebody knows it?

So i see there no easy way to eliminate direct i/o on syscatspace...
That mean views,triggers,sql functions bodies always retrieved from
disk (or OS cache, if possible).
That's can quite limiting issue is high dynamic sql scenario, isn't
that so?

http://www.peg.com/lists/dba/history.../msg00325.html

this describes how progress did/does it. but i gather that this is an
application switch, i.e. they implement it themselves.
there is also this from an aix site:

v_pinshm Purpose:
If set to 1, will allow pinning of shared memory segments.
Values:
Default: 0 (off)
Range: 0 or 1.
Type: Dynamic
Diagnosis:
Change when there is too much overhead in pinning or unpinning of AIO
buffers from shared memory segments.
Tuning
Useful only if application also sets SHM_PIN flag when doing a shmget
call and if doing async I/O from shared memory segments.
Refer To:
Miscellaneous I/O Tuning Parameters

so, it is implemented; but perhaps not at user level. wouldn't be the
first time.

[Ian]

Alex Greem wrote:

the trick is to use the o/s buffers, and pin same.

IIRC, on *nix anyway, one can memmap the file, then pin the o/s
cache. i've no recollection of the syntax. on windoze, no recollection.

Yes, OS cache should help here - but i'm on windows and i just don't
know if it possible explicitly manage windows file cache's - may
somebody knows it?

So i see there no easy way to eliminate direct i/o on syscatspace...
That mean views,triggers,sql functions bodies always retrieved from
disk (or OS cache, if possible).

Access plans for dynamic SQL statements are cached, too, which should
help eliminate the need for constant access.
That's can quite limiting issue is high dynamic sql scenario, isn't
that so?

Are you actually having a problem, or is this just a perceived problem?
What is your average service time for each direct read?


-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

[Alex Greem]

Ian <ia*****@mobileaudio.com> wrote in message news:<40fee7e4>

Access plans for dynamic SQL statements are cached, too, which should
help eliminate the need for constant access.

You mean pckcachesz memory?
we have 40000*4kb = 160Mb, but sometimes during the working day
"package cache hit rate" is dropped under 50%.
Unfortunally i can't control our programmers to force them to use
parameterized query instead of hard-coded values in sql. This makes
pckcachesz memory inefficient.

That's can quite limiting issue is high dynamic sql scenario, isn't
that so?

Are you actually having a problem, or is this just a perceived problem?
What is your average service time for each direct read?

Well i just trying to make things faster... and direct i/o is just one
thing among others to optimize.
How can i measure average service time for direct i/o request?
I have these figures now (for SYSCATSPACE):
direct read requests 1186045
direct reads 2725124
Direct reads elapsed time (ms) 3183837