By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,609 Members | 2,246 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,609 IT Pros & Developers. It's quick & easy.

Many Direct Writes to SYSCATSPACE, and each very slow.

P: n/a
DB2 7.2 (ESE) on AIX 5.2.

In 3 weeks, we've seen the following :
Tablespace name = SYSCATSPACE
Direct writes = 150306
Direct write elapsed time (ms) = 4295071162

Each write takes on average 28,575 milliseconds!!

IBMDEFAULT BP = 1000 Pages.
SYSIBM tables were RUNSTAT'd 3 weeks ago.
SYSCATSPACE (SMS) container sits on a single (mirrored) disk (the same
disk holds the temporary tablespace, application logs).
NUM_IOSERVERS = 69, NUM_IOCLEANERS = 15.
CHNGPGS_THRESH = 40.
Machine has 4 CPUs.

Not sure if I should:-
1) increase the bufferpool.
2) run db2empfa.
3) relocate SYSCATSPACE to another less busy disk, and create over
more than one container.
4) do something else!

Any tips appreciated.

Many thanks
Bruce
Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Bruce,

1000 pages for the default BP is only 4MB, that's mor than worse,
ioserver and iocleaner depends on the storage system, but it seems to me
that 69 ioserver is a lot.

First increase the bufferpool...

P.S. 7.2 is EEE or EE and 8.x is ESE.

regards,
Joachim Müller

"Bruce Pullen" <br**********@hotmail.com> schrieb im Newsbeitrag
news:9d**************************@posting.google.c om...
DB2 7.2 (ESE) on AIX 5.2.

In 3 weeks, we've seen the following :
Tablespace name = SYSCATSPACE
Direct writes = 150306
Direct write elapsed time (ms) = 4295071162

Each write takes on average 28,575 milliseconds!!

IBMDEFAULT BP = 1000 Pages.
SYSIBM tables were RUNSTAT'd 3 weeks ago.
SYSCATSPACE (SMS) container sits on a single (mirrored) disk (the same
disk holds the temporary tablespace, application logs).
NUM_IOSERVERS = 69, NUM_IOCLEANERS = 15.
CHNGPGS_THRESH = 40.
Machine has 4 CPUs.

Not sure if I should:-
1) increase the bufferpool.
2) run db2empfa.
3) relocate SYSCATSPACE to another less busy disk, and create over
more than one container.
4) do something else!

Any tips appreciated.

Many thanks
Bruce

Nov 12 '05 #2

P: n/a
You may force sync writes because you hit CHNGPGS_THRESH or some other
condition.
Get you a few snapshots and look for logging values and names like *time
waited*, *forced*, *threshold*, *stolen*, etc.

PM

"Bruce Pullen" <br**********@hotmail.com> a écrit dans le message de
news:9d**************************@posting.google.c om...
DB2 7.2 (ESE) on AIX 5.2.

In 3 weeks, we've seen the following :
Tablespace name = SYSCATSPACE
Direct writes = 150306
Direct write elapsed time (ms) = 4295071162

Each write takes on average 28,575 milliseconds!!

IBMDEFAULT BP = 1000 Pages.
SYSIBM tables were RUNSTAT'd 3 weeks ago.
SYSCATSPACE (SMS) container sits on a single (mirrored) disk (the same
disk holds the temporary tablespace, application logs).
NUM_IOSERVERS = 69, NUM_IOCLEANERS = 15.
CHNGPGS_THRESH = 40.
Machine has 4 CPUs.

Not sure if I should:-
1) increase the bufferpool.
2) run db2empfa.
3) relocate SYSCATSPACE to another less busy disk, and create over
more than one container.
4) do something else!

Any tips appreciated.

Many thanks
Bruce

Nov 12 '05 #3

P: n/a

The type of data you find in syscat.* may also explain why you get sync
io's.

PM
Nov 12 '05 #4

P: n/a
1000 pages is miniscule if you have everything else in it. Make it
500mb if that is the case, then monitor hit ratios and prefetch
performance. You should have one prefetcher per container....direct
reads read a sector at a time and do not use the bufferpool....you
have io bottleneck. Check you tablespace layout strategy, how many
disks and adapters do you have.....Either way, your database needs
major surgery if you math is write. Flick


"PM \(pm3iinc-nospam\) CGO" <PM (pm3iinc-nospam)@cgocable.ca> wrote in message news:<pq***************@charlie.risq.qc.ca>...
You may force sync writes because you hit CHNGPGS_THRESH or some other
condition.
Get you a few snapshots and look for logging values and names like *time
waited*, *forced*, *threshold*, *stolen*, etc.

PM

"Bruce Pullen" <br**********@hotmail.com> a écrit dans le message de
news:9d**************************@posting.google.c om...
DB2 7.2 (ESE) on AIX 5.2.

In 3 weeks, we've seen the following :
Tablespace name = SYSCATSPACE
Direct writes = 150306
Direct write elapsed time (ms) = 4295071162

Each write takes on average 28,575 milliseconds!!

IBMDEFAULT BP = 1000 Pages.
SYSIBM tables were RUNSTAT'd 3 weeks ago.
SYSCATSPACE (SMS) container sits on a single (mirrored) disk (the same
disk holds the temporary tablespace, application logs).
NUM_IOSERVERS = 69, NUM_IOCLEANERS = 15.
CHNGPGS_THRESH = 40.
Machine has 4 CPUs.

Not sure if I should:-
1) increase the bufferpool.
2) run db2empfa.
3) relocate SYSCATSPACE to another less busy disk, and create over
more than one container.
4) do something else!

Any tips appreciated.

Many thanks
Bruce

Nov 12 '05 #5

P: n/a
EE.

Thanks for eveyones' comments. I'm learning.

With no config changes, latest snapshot reveals that each write has
taken on average 3,287 milliseconds - down from 28,575 milliseconds.

Only the system catalog tables sit in the IBMDEFAULT bufferpool (this
bufferpool is used by tempspace and userspace although these do not
contain any tables). Unfortunately, I'm restricted memory-wise by
32-bit AIX (other bufferpools are using most of the available memory
segments).

Looking at IO, the SYSCATSPACE resides on the same disks as other
filesystems (not any other tablespaces), so I'm looking at ways to
reduce/spread IO. From the below, BP hit ratios, asynchronous vv.
synchronous writes, the prefetching looks ok I think:-

IBMDEFAULT BP read hit ratios (%):-
Data+Index Data Index
95.06 94.65 95.82
IBMDEFAULT BP writes (asynchronous vv. synchronous variation %):-
Data Index
1.69 4.77
IBMDEFAULT direct reads/writes
Direct Reads Direct Writes
Time Time
Number Total Avg Number Total Avg
19144112 2299507 0.12 2612718 8590287387 3287.87

Any more tips (especially if you disagree with my comments!) much
appreciated.

Thanks, Bruce.
Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.