Many Direct Writes to SYSCATSPACE, and each very slow.

[Bruce Pullen]

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

[Joachim Müller]

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

[PM \(pm3iinc-nospam\) CGO]

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

[PM \(pm3iinc-nospam\) CGO]

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

PM

[John]

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

[Bruce Pullen]

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.