Are there any implications by choosing a bigger page size for tablespaces? This system we are designing is a hyrid of OLTP and DSS, so even though most of our tables max. row size is below 4k, I want to use 8k tablespaces since I can have bigger pages and cram more rows into the same page. What are the disadvantages of doing this?
My concerns primarily are
1. Will there be any difference in how DB2 will handle lock escalations and lock waits by going with a 8k tablespace instead of a 4k tablespace?
The reason I sak this is, from my previous testing, DB2 (even with the RS isolation level) sometimes blocks out users when concurent users are inserting
and selecting at the same time. Will this behavior get worse or better by going to a bigger pagesize?
2. I heard that Indexes have a max. threshold of 256 entries per page. So, am I wasting space by creating a 8k tablespace to hold Index data?
1. I don't think that it will effect lock escalation, since DB2 for Linux, Unix, and Windows locks at the row or table level. But not 100% sure on that one. If you are not using version 8.1, you should consider doing so because the index locking scheme has been significantly improved to have less contention when inserting.
2. For best performance you should have separate tablespaces for indexes (regardless of page size) and therefore you can use 4K page size and bufferpools for indexes even if you choose 8K for the tables. Using page size > 4K for indexes would only be advisable with DSS that did a lot of index space scans (reading the entire index instead of using the b-tree). Using a larger page size for indexes would not only waste disk space, but also waste bufferpool memory, since un-needed index data could potentially be read into the bufferpools.
For tables with smaller row sizes that are usually accessed via indexes (not tablespace scans), I would use the 4K page size, perhaps using the same bufferpool as the indexes.