06-06-08, 11:22 #1Registered User
- Join Date
- Mar 2007
Unanswered: How do I correct a recurring SQL1218N bufferpool problem?
My environment is DB2 UDBv8.1 Fixpak 16 on a RedHat Linux OS. I have tried to resolve this by trippling my default and 4K bufferpools size from 1000 to 3000 and I am still getting the SQL1812N error. I am desperate to solve this problem because very critical applications/reports depend on this. I have some questions:
(1) How do I go about sizing my bufferpool correctly? What are the input needed to do a correct bufferpool sizing? What are the steps?
(2) What tools are (hopefully freeware/shareware or trials) there that can help with bufferpool sizing?
Please can someone respond to this as soon as they have a minute or two to spare
06-06-08, 13:17 #2Registered User
Provided Answers: 5
- Join Date
- Jan 2003
You are getting this error because your entire bufferpool is full of dirty pages. There are several ways to remedy this depending on your situation. If the bufferpool is too small and all the pages are getting dirty quickly, you need to increase the size. 3000 is a very small bufferpool. How big it should be should be based on the size of the tablespaces that use it and the volatility of those databases.
Another thing that can affect the dirty pages are the chngpgs_thresh and the num_iocleaners database configuration parameters. If the chngpgs_thresh is too high or the num_iocleaners is too low, then you can get this error.
We cannot give you a specific answer, because it really depends on your particular situation. If you provide us with the size of the tablespaces (in pages) and the values for the two parameters and how much RAM, number of CPUs, 32-or-64 bit?
06-08-08, 05:25 #3Registered User
- Join Date
- Jan 2007
- Jena, Germany
I recommend that you run the DB2 Configuration Advisor to get an initial base configuration for your system.Knut Stolze
IBM DB2 Analytics Accelerator
IBM Germany Research & Development
06-08-08, 12:05 #4Registered User
- Join Date
- May 2003
The default bufferpool size of 1000 was established in about 1992 (?) when the first version of DB2/6000 (on AIX) was introduced (it was 250 when OS/2 Database Manager first came out and that is still the default on DB2 for Windows). In 1992 an AIX server with 64 MB of memory was quite large. The defaults are completely inadequate for just about any real world application.
Typically, if you have a database server with no other major applications running on it, you would allocate about 50% (or more) of the total physical memory to the DB2 bufferpools. These would leave about 10-25% of the system memory for remaining DB2 memory needs, and the rest for the operating system and other miscellaneous processes running on the server.
So if you have a server with 1 GB of memory, then the total of all bufferpools for all databases on the server would be 512 MB or about 125,000 4K pages (one half of the memory). Obviously, if you have more than 1 GB of memory then your bufferpools should be proportionally larger, although the bufferpool doesn’t need to be larger than the sum total of all the tables and indexes assigned to a particular bufferpool (based on which tablespace it is in).
If your database server is also hosting your application or web tier (not recommended), then you need to take that into account. DB2 needs real memory available, and not virtual memory.
Last edited by Marcus_A; 06-08-08 at 12:08.M. A. Feldman
IBM Certified DBA on DB2 for Linux, UNIX, and Windows
IBM Certified DBA on DB2 for z/OS and OS/390