Cause of excessive paging

[nekiv90]

Greetings,

Could someone confirm if the following reasoning is correct (64-bit DB2
on 64-bit AIX v5.3):

=============== =============== =============== =============== ======
For each connection to DB2 db, AIX will reserve a 64GB shared memory
segment for buffer caching.

So if there are connections to 3 different DBs, AIX will reserve a
total of 192 GB shared memory segment and one could quickly run out of
real memory. eg AIX with 5 GB of real memory and 6GB of paging space.

Excessive paging is the result of this.

=============== =============== =============== =============== ======

Many thanks!

[Serge Rielau]

ne*****@hotmail .com wrote:

Greetings,

Could someone confirm if the following reasoning is correct (64-bit DB2
on 64-bit AIX v5.3):

=============== =============== =============== =============== ======
For each connection to DB2 db, AIX will reserve a 64GB shared memory
segment for buffer caching.

So if there are connections to 3 different DBs, AIX will reserve a
total of 192 GB shared memory segment and one could quickly run out of
real memory. eg AIX with 5 GB of real memory and 6GB of paging space.

Excessive paging is the result of this.

I have a hard time believing this.
I do recall issues on AIX 32bit on the _intance_ level where if someone
installs several instances of DB2 they can run out of segments.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Liam Finnie]

Serge Rielau wrote:

ne*****@hotmail .com wrote:

Greetings,

Could someone confirm if the following reasoning is correct (64-bit DB2
on 64-bit AIX v5.3):

=============== =============== =============== =============== ======
For each connection to DB2 db, AIX will reserve a 64GB shared memory
segment for buffer caching.

So if there are connections to 3 different DBs, AIX will reserve a
total of 192 GB shared memory segment and one could quickly run out of
real memory. eg AIX with 5 GB of real memory and 6GB of paging space.

Excessive paging is the result of this.

I have a hard time believing this.
I do recall issues on AIX 32bit on the _intance_ level where if someone
installs several instances of DB2 they can run out of segments.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

AIX has a late swap space allocation policy, so shared memory that has
never been accessed does not contribute to pageing, and does not occupy
real memory. Even though each DB segment will be 64GB, DB2 will only
use however much it is configured to (i.e. look at the size of your
bufferpools, locklist, shared sort heap, package cache, etc). The
excess will only be used if you try to dynamically allocate a new
bufferpool, extend an existing bufferpool, increase the package cache,
etc.

If you want to see how much of the database shared segment DB2 is
configured to use, then connect to your database, and run the
following:
db2 get db cfg show detail | grep DATABASE_MEMORY

The in-memory value will show the maximum number of 4K pages DB2 is
configured to use. DB2 will likely be consuming even less real memory
+ pageing space than this number, as a portion of those 4K pages are
not accessed until they are needed (i.e. memory for the utility heap is
not consumed until you start running backups/loads/restores/etc).

Cheers,
Liam

[nekiv90]

Thanks Serge/Liam.

I agree with you.

However, what would likely contribute to the massive allocation of
shared memory segments? Our Sys admin saw the allocation comes down as
soon as they turn db2_pinned_bp on.

As we never set this variable on for other AIX servers, I tend to
believe the over-allocated shared memory segments is caused by
something else. Excessive buffering for file system may be one of them.

Any suggestions?

Thanks!
Liam Finnie wrote:

Serge Rielau wrote:

ne*****@hotmail .com wrote:

Greetings,

Could someone confirm if the following reasoning is correct (64-bit DB2
on 64-bit AIX v5.3):

=============== =============== =============== =============== ======
For each connection to DB2 db, AIX will reserve a 64GB shared memory
segment for buffer caching.

So if there are connections to 3 different DBs, AIX will reserve a
total of 192 GB shared memory segment and one could quickly run out of
real memory. eg AIX with 5 GB of real memory and 6GB of paging space.

Excessive paging is the result of this.

I have a hard time believing this.
I do recall issues on AIX 32bit on the _intance_ level where if someone
installs several instances of DB2 they can run out of segments.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

AIX has a late swap space allocation policy, so shared memory that has
never been accessed does not contribute to pageing, and does not occupy
real memory. Even though each DB segment will be 64GB, DB2 will only
use however much it is configured to (i.e. look at the size of your
bufferpools, locklist, shared sort heap, package cache, etc). The
excess will only be used if you try to dynamically allocate a new
bufferpool, extend an existing bufferpool, increase the package cache,
etc.

If you want to see how much of the database shared segment DB2 is
configured to use, then connect to your database, and run the
following:
db2 get db cfg show detail | grep DATABASE_MEMORY

The in-memory value will show the maximum number of 4K pages DB2 is
configured to use. DB2 will likely be consuming even less real memory
+ pageing space than this number, as a portion of those 4K pages are
not accessed until they are needed (i.e. memory for the utility heap is
not consumed until you start running backups/loads/restores/etc).

Cheers,
Liam

[Liam Finnie]

Hello,

As soon as you set DB2_PINNED_BP to true, or enable large pages for the
database shared memory set, DB2 will no longer attempt to allocate the
huge 64GB segment. The reason is that in these cases we attempt to
physically pin the entire segment. Pinning a 64GB segment on a system
that doesn't have more than 64GB of physical RAM is not desirable :-)
Therefore, we only allocate how much we are currently configured to
use. This, of course, limits your ability to dynamically grow your
bufferpools, locklist, package cache, etc.

Pinned and large-page memory should be used with caution - you need to
really know what type of workload you'll have, and carefully provision
memory on the machine. Otherwise, if you're not careful, pinning
memory could actually *cause* pageing of hot data (if some infrequently
used memory is pinned, and the system is low on memory, it will have to
start pageing more frequently used memory).

If you haven't changed your default file cache settings, you should
probably investigate that. The default file cache configuration is not
likely suitable when running a database server, since database servers
maintain their own cache of file system data (i.e. the DB2
bufferpools). Having a large file cache ends up giving you
double-caching - the OS caches the page in its file cache, and DB2
caches the same page in its bufferpools. You can run 'vmstat -v', and
look at the minperm, maxperm, and numperm settings. numperm will tell
you the percentage of memory currently used for the file system cache.
If it's lower than maxperm, or minperm is set fairly high, then that
may end up causing pageing - the OS might page out working pages to
maintain a large file cache.

Two things to consider if your system is using a large amount of file
cache:
1) Lower minperm/maxperm. This will leave more room for working
memory, which will usually benefit database servers. This could also
reduce pageing, since the system may then favor evicting clean file
cache pages - this is very cheap since the page can just be discarded
without writing any data back to disk. When evicting working memory,
the OS must write the data to swap space, which is very slow.

2) Look into Direct IO/Concurrent IO. This will avoid double-buffering
of data, since DB2 will avoid using the OS file cache to read in data
pages to the bufferpool.

You should probably consider doing both. Lowering maxperm will favor
keeping working pages in memory, which should help reduce pageing.
Enabling Direct IO/Concurrent IO will reduce file cache usage for data
that DB2 will be cacheing in its bufferpools, leaving the file cache
free to cache other data.

Cheers,
Liam

[nekiv90]

Liam,

Thank you so much for the in-depth explanation.

Any reasons the system will favor evicting CLEAN file cache pages after
lowering minperm/maxperm?

Thanks again!
Liam Finnie wrote:

Hello,

As soon as you set DB2_PINNED_BP to true, or enable large pages for the
database shared memory set, DB2 will no longer attempt to allocate the
huge 64GB segment. The reason is that in these cases we attempt to
physically pin the entire segment. Pinning a 64GB segment on a system
that doesn't have more than 64GB of physical RAM is not desirable :-)
Therefore, we only allocate how much we are currently configured to
use. This, of course, limits your ability to dynamically grow your
bufferpools, locklist, package cache, etc.

Pinned and large-page memory should be used with caution - you need to
really know what type of workload you'll have, and carefully provision
memory on the machine. Otherwise, if you're not careful, pinning
memory could actually *cause* pageing of hot data (if some infrequently
used memory is pinned, and the system is low on memory, it will have to
start pageing more frequently used memory).

If you haven't changed your default file cache settings, you should
probably investigate that. The default file cache configuration is not
likely suitable when running a database server, since database servers
maintain their own cache of file system data (i.e. the DB2
bufferpools). Having a large file cache ends up giving you
double-caching - the OS caches the page in its file cache, and DB2
caches the same page in its bufferpools. You can run 'vmstat -v', and
look at the minperm, maxperm, and numperm settings. numperm will tell
you the percentage of memory currently used for the file system cache.
If it's lower than maxperm, or minperm is set fairly high, then that
may end up causing pageing - the OS might page out working pages to
maintain a large file cache.

Two things to consider if your system is using a large amount of file
cache:
1) Lower minperm/maxperm. This will leave more room for working
memory, which will usually benefit database servers. This could also
reduce pageing, since the system may then favor evicting clean file
cache pages - this is very cheap since the page can just be discarded
without writing any data back to disk. When evicting working memory,
the OS must write the data to swap space, which is very slow.

2) Look into Direct IO/Concurrent IO. This will avoid double-buffering
of data, since DB2 will avoid using the OS file cache to read in data
pages to the bufferpool.

You should probably consider doing both. Lowering maxperm will favor
keeping working pages in memory, which should help reduce pageing.
Enabling Direct IO/Concurrent IO will reduce file cache usage for data
that DB2 will be cacheing in its bufferpools, leaving the file cache
free to cache other data.

Cheers,
Liam

[Liam Finnie]

Hello,

That's starting to get deep into VMM layer of the OS. If numperm is
still above minperm, my assumption would be that clean file cache pages
(as long as they're not too hot) will likely be favored for eviction,
since the page-out cost is zero (i.e. no data has to be written out to
disk, since the on-disk copy of the file already exists, so the memory
page can simply be discarded). Pageing out a working memory page, or a
dirty file cache page, will require a physical I/O before that memory
page can be re-used by someone else.

I believe AIX keeps stats on re-pageing rates (i.e. if the OS pages out
or discards a particular page, but finds that it needs to read it back
in again shortly afterwards) for both working and file cache pages.
These re-pageing rates, along with minperm/maxperm, help determine
whether file cache pages or working pages get evicted.

Cheers,
Liam.