Home / server / Questions / 152183
Accepted
telstone
Asked: 2010-06-18 08:40:10 +0800 CST

db2 tablespace size and performance impact

  • 772

Originally when we began moving to db2 LUW we ran into some issues where our tables were too big to fit into the default 4K table space. As a result of "pressure" to get it done we just went with a 32K default table space and put ALL of our tables there.

What impact would that have if any? I talked to one person who said that it would possible make out database MUCH larger than it needed to be. Is that true? What about memory? Would there be any benefit to moving the smaller tables back to a 4K table space? I have looked around in forums and what not but cannot seem to find a good answer.

database-administration db2

2 Answers

  1. Best Answer

    In short if you have relative low requirements (smallish database, the amount of transactions low) I would not bother setting up distinct table spaces for different page sizes. However there is a performance and space requirement related problem that will escalate if your database is large or your amount of transactions rises.

    DB2 can save in maximum constant amount of rows in one page (source):

    A table with a row length of 12 bytes placed in a table space with 32K page size utilizes only about 10% of each page, which is calculated as (255 rows * 12 bytes) + 91 bytes of overhead) / 32k page size = ~10%. This is only a consideration if the table is large, which means the wasted space is significant. It also makes I/O and buffering less efficient, because the actual useful content of each page is small.

    There won't probably be noticeable difference in terms of performance if the database is small enough to fit completely into your bufferpools. Also if the database is small you won't probably notice a few extra gigabytes of spent space.

    What you could optimize (probably what the IBM thought) is that you can set tablespaces on different IO devices, and also optimize bufferpools for exact use cases. Using the same bufferpool for all of your tables can lead for instance into some secondary function (for instance some janitor task in applications) flushing the most commonly needed data often out of the bufferpools. Putting for instance things like temporary tables into different tablespaces and bufferpools you can control what your valuable memory is used for. However as said earlier, these things start mattering only after certain point.

    • 1

  2. According to IBM there is not a huge difference size or performance wise on a base install. Any gains would come from having multiple table spaces and optimizing them individually.

    • 0