Paging steadily increasing over time

[ArtF]

Background
UDB 8.2, EEE, Lots of users, runing on AIX with an Switch 2, running
QP, running datastage PX

Here is the behavior we observe. Over time the paging on the AIX box
steadily increases, very rarely there is a slight decrease. Even after
a long period (1 hour+) of relative inactivity it doesn't go down.
When we reboot the paging goes all the way back down. We have looked
at a lot of things but can't quite put our finger on the cause. It
looks like something is going on in UDB but not sure what it is. Any
and all thoughts appreciated.

[stefan.albert]

Of course, adding more RAM can also help in this case - but...

First you should find out which process(es) use much RAM which causes
the paging. There is also the possibility that too many agents are
running (temporarily) - each agent needes RAM, sometimes much
sometimes not much. So have a look with ps at the RAM usage. Also
db2pd can help to find out, which agents (or the engine itself) need
much RAM.

Do you have your agents in a pool and keep fenced processes? This
might also hold resources (RAM) and cause paging.

A reboot should always help for some time.
What happens if you (only) restart the instance(s)?

[darko]

ArtF wrote:

Background
UDB 8.2, EEE, Lots of users, runing on AIX with an Switch 2, running
QP, running datastage PX

Here is the behavior we observe. Over time the paging on the AIX box
steadily increases, very rarely there is a slight decrease. Even after
a long period (1 hour+) of relative inactivity it doesn't go down.
When we reboot the paging goes all the way back down. We have looked
at a lot of things but can't quite put our finger on the cause. It
looks like something is going on in UDB but not sure what it is. Any
and all thoughts appreciated.

Could it be that AIX is using a lot (in fact, too much) of memory for
file system cache? By default, AIX tends to behave like that, being
very aggressive in using RAM for file system caching. For RDBMS
servers like DB2 it is not desirable behavior, since DB2 already has
its caching/buffering mechanism.

In a similar situation to yours (AIX 5.3, DB2 EE 8.2) with settings
that I've found before tuning:

lru_file_repage 1
maxclient 80
maxperm 80
minperm 20

I've got substantial improvements with setting like:

lru_file_repage 0
maxclient 20
maxperm 20
minperm 5

effectively preventing paging and improving performance. You can set
parameters listed above from smitty, for example.

Darko Krstic

[ArtF]

On Jul 24, 12:01*pm, darko <[e-mail]:

ArtF wrote:

Background
UDB 8.2, EEE, Lots of users, runing on AIX with an Switch 2, running
QP, running datastage PX

Here is the behavior we observe. *Over time the paging on the AIX box
steadily increases, very rarely there is a slight decrease. Even after
a long period (1 hour+) of relative inactivity it doesn't go down.
When we reboot the paging goes all the way back down. *We have looked
at a lot of things but can't quite put our finger on the cause. *It
looks like something is going on in UDB but not sure what it is. *Any
and all thoughts appreciated.

Could it be that AIX is using a lot (in fact, too much) of memory for
file system cache? By default, AIX tends to behave like that, being
very aggressive in using RAM for file system caching. For RDBMS
servers like DB2 it is not desirable behavior, since DB2 already has
its caching/buffering mechanism.

In a similar situation to yours (AIX 5.3, DB2 EE 8.2) with settings
that I've found before tuning:

lru_file_repage * * * 1
maxclient * * * * * * 80
maxperm * * * * * * 80
minperm * * * * * * *20

I've got substantial improvements with setting like:

lru_file_repage * * * 0
maxclient * * * * * * 20
maxperm * * * * * * 20
minperm * * * * * * *5

effectively preventing paging and improving performance. You can set
parameters listed above from smitty, for example.

Darko Krstic

Thanks much. I will look in to it though we haven't changed anything
on file behavior lately.
ART

[ArtF]

On Jul 24, 5:23*am, "stefan.albert" <[e-mail]:

Of course, adding more RAM can also help in this case - but...

First you should find out which process(es) use much RAM which causes
the paging. There is also the possibility that too many agents are
running (temporarily) - each agent needes RAM, sometimes much
sometimes not much. So have a look with ps at the RAM usage. Also
db2pd can help to find out, which agents (or the engine itself) need
much RAM.

Do you have your agents in a pool and keep fenced processes? This
might also hold resources (RAM) and cause paging.

A reboot should always help for some time.
What happens if you (only) restart the instance(s)?

We do pool agents, I will check on fenced processes. Thanks much,
greatly appreciated.
Art