Question & Answer
Question
This document provides examples of how the creation of an oversized database bufferpool can cause paging space exhaustion. It also offers recommendations for preventing and addressing the problem.
Cause
Different symptoms exist. You may experience a simple warning first, followed by the paging space exhaustion during the next database activation. In general, however, the problem is typified by an oversized bufferpool leading to paging space exhaustion. The following is one possible scenario:
Example 1
David has DB2 LUW Version 8 on an AIX® 5 server, which has 2GB RAM and 2GB paging space. He set up a 64-bit instance. While setting up database objects, he wanted to create a 500MB bufferpool. He made a typing error, however, and ended up creating a 5GB bufferpool. The command seemed to run for a long time, and the server became very slow. Soon, his telnet session lost the connection to the server. Later, his system administrator told him that the server ran out of paging space, and that it had to be rebooted. Now, whenever David tries to activate the database, the paging space runs out.
The reason is simple. 5GB is just too big for a server with 2GB RAM and 2GB paging space.
When you issue a CREATE BUFFERPOOL command, DB2® Universal Database™ (DB2 LUW),
- Asks the operating system for a memory segment that can harbour the bufferpool. (DB2 LUW actually asks for the database shared memory segment, in which a bufferpool is usually the biggest portion.)
- If step 1 is successful, DB2 LUW initializes the bufferpool pages. If step 1 is unsuccessful, DB2 activates the database with very small bufferpools, returning a SQL1478W warning: "The defined buffer pools could not be started. Instead, one small buffer pool for each page size supported by DB2 has been started".
How the operating system responds to step 1 above can cause some different results. The following is a comparison of two different paging space allocation algorithms, an "early allocation algorithm" and a "deferred allocation algorithm":
Early allocation algorithm vs. Deferred allocation algorithm.
In a very nutshell, the early allocation algorithm checks to make sure there is enough paging space for all memory allocations. The deferred allocation algorithm does not check this. This allows much flexibility in resource management. However, it also introduces the possibility of paging space exhaustion.
On AIX Version 5, the default is the deferred allocation algorithm. (Up until AIX 4.3.2, the default was the late allocation algorithm, which works similar to the deferred allocation algorithm. Refer to the white paper referenced in the Related Information section for more details). This allows any software to create a memory segment bigger than the paging space. This is fine as long as the memory segment does not need to be paged out. If, however, the overall memory consumption increases in the system such that this memory segment needs to be paged out, it will lead to paging space exhaustion and will leave the server in an unusable state.
Linux® also works the same way.
Solaris, however, takes the early allocation algorithm.
In short, step 1 (described above) is usually successful on AIX and Linux. This allows the creation of the memory segment, but may later cause paging space exhaustion while the bufferpool is being initialized. On Solaris, if there is insufficient paging space, step 1 is unsuccessful. This allows the database activation to succeed, but results in the SQL1478W warnings.
Though the paging space algorithm is the biggest factor in how oversized database bufferpools are handled, there is another factor that is worth considering: whether the address space is 32-bit or 64-bit.
Example 2
Suppose David has a 32-bit instance, and his AIX server has 200MB RAM and 200MB paging space. If he tries to create a 500MB bufferpool, the paging space will run out for the same reason as above. If he tries to create a 4GB bufferpool, however, step 1 will be unsuccessful. This is because a process in a 32-bit environment cannot utilize an address space that is bigger than 4GB. Hence, the database will be activated and return the SQL1478W warning.
In short, this problem exists in 32-bit environments, but does not occur as often as 64-bit environments.
Answer
How to prevent the paging space exhaustion
The big assumption behind the deferred allocation algorithm is that the administrators are capable of controlling the memory usage in a system. That is, David and his system administrator should have made sure that an oversized bufferpool was not created.
If this assumption cannot be met, and if a server failure is intolerable, they should convert to the early allocation algorithm. (Refer to the document under Related Information for how this conversion can be accomplished on AIX).
What to do after this problem occurs
As mentioned in the examples, once an oversized bufferpool is created, the paging space will run out whenever the database is activated. To get around this problem, use the DB2_OVERRIDE_BPF registry variable. Here's an example:
db2stop
db2set DB2_OVERRIDE_BPF=10
db2start
db2 connect to <database-name>
db2 drop bufferpool <oversized-bufferpool-name>
db2 terminate
db2stop
db2set DB2_OVERRIDE_BPF=
This overrides the size of the oversized bufferpool to 10 pages, allowing the database activation to succeed.
Related Information
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21238869