|
I installed DB2 Express-C on a PC with 2 GB RAM and running Windows Server 2008 R2 OS. While creating a new database, there is an option to set Buffer Pool size with default set to 4K. How much is the recommended pool size for the above mentioned configuration? I also want to know whether to use default Buffer Pool or a separate Buffer Pool for every database. |
||||
|
|
|
The default size is 4K. You can also specify 8K, 16K, or 32K. The size of the buffer pool should really be based on the size of your tables (ie, how many bytes is it possible for one row of data to hold). If a row of your data won't fit into a 4K page, then it won't fit into a 4K buffer pool. And hence you would need larger. The same is also true of table spaces (the "physical containers" that your tables are stored in). A table that needs an 8K buffer pool, needs to reside in an 8K table space. And actually, you would then associate that table space to the buffer pool as well.... That being said, don't just "oversize" and go to 32K table spaces and buffer pools if you really only have 4K tables. You will waste a lot of disk space and memory then. Also, you can have as many buffer pools as you want (I'm sure there is an upper limit, though I wouldn't know off hand). But you can have any combination of them. You could have five 4K pools, one 8K pool, one 16K pool, and three 32K pools. The biggest thing is more based on your table sizes and understanding that as mentioned before. Although, here is the nice thing....after you create your buffer pools and table spaces, DB2 is smart enough to put the table into the correct size table space (pending it exists). You can specify it in your DDL code, but DB2 is smart enough to put an 8K table in an 8K table space (and thus it will also be associated with an 8K buffer pool). OK...now to answer your last two questions. There is no "recommended" size really. The default of 4K is fine. Most of our tables where I work fit in a 4K table space and 4K buffer pool. What we tend to do though, as a best practice... We set up our own 4K, 8K and 32K table spaces and buffer pools (including temporary tables spaces for reorgs). Why we don't set up 16K, I'm not sure. But we set up several sizes in all our databases. This way if any developer creates a table that will be bigger than 4K, it will automatically get placed into the appropriately bigger tables pace (and thus buffer pool). Hopefully this was clear enough for you. I have also found this article helpful with my own understanding of buffer pools and table spaces. |
|||
|
|
|
Other than the bufferpool there is also other consumer of the 2GB memory from DB2. Things like the UTILHEAP, SORTHEAP, etc. So there is actually more to consider in the overall picture than just bufferpool. Have you thought about using AUTOMATIC? DB2 has a feature called Self-tuning memory (STMM), what you do is you can configure your instance memory to be AUTOMATIC or some fixed value and the STMM manager will figure out the memory settings for you on the fly. If you are not interested in STMM (might not be available for Express-C), then things you should consider when designing a bufferpool is how much for your workload about how much data you would need to be in memory. For example if you have a hot table that needs to be accessed all the time and the entire table needs to be in the bufferpool, then you better have the bufferpool big enough so that you can hold most of the pages from that table. There is best practice that suggests separating the temporary data from your permanent data.. so if you do a lot of things that involve temporary data usage like sort, user temp, etc, then you are better off separating the two bufferpools so the temporary data will not pollute your real data bufferpool. Hope this helps. |
|||
|
|
