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! 6 8097 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
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
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
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,
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
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: ck388 |
last post by:
For some reason when I enable the callback feature of the gridview I
still get a page refresh, that is it seems like there is a postback
that occurs, not a callback which is just supposed to update...
|
by: Shawn |
last post by:
Hi.
I already have a datagrid where I'm using paging. I have a stored procedure
that fills a temp table with 200-500 rows and then sends back 10 records at
the time. When I go to page 2 the SP...
|
by: asad |
last post by:
Hello friends, i am designing a ASP.NET page where i want to use custom
paging bcoz data is too heavy so pls tell me how can i use custom paging in
ASP.NET
Thanks
|
by: tom fredriksen |
last post by:
Hi
I was doing a simple test of the speed of a "maths" operation and when I
tested it I found that removing the loop that initialises the data array
for the operation caused the whole program to...
|
by: farhad13841384 |
last post by:
Hi , I Hope You fine.
I have some problem with this code for paging in asp.net
this bottom code work correctly without any error but when
I try to place separate code in .VB file then error is...
|
by: anonieko |
last post by:
This approach I found very efficient and FAST when compared to the
rowcount, or Subquery Approaches.
This is before the advent of a ranking function from DB such as
ROW_NUMBER() in SQL Server...
|
by: rn5a |
last post by:
In a shopping cart app, a ASPX page retrieves the order details &
personal details of a user from a MS-Access database table depending
upon the username of the user.
The order details of a...
|
by: Ronald S. Cook |
last post by:
I was told that if calling lots of records from the database (let's say
100,000), that the GridView's paging feature would automatically "handle"
everything.
But the 100,000 records are still...
|
by: Donald Adams |
last post by:
Hi,
I will have both web and win clients and would like to page my data. I
could not find out how the datagrid control does it's paging though I did
find some sample code that says they do it...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |