Page Size....
The poster asked whether there is any downside to using page sizes larger than the 2K default, aiming to avoid row chaining for tables with rows over 2K. Replies were broadly positive: bigger pages avoid multiple reads for wide rows, with no real drawback beyond having to manage/monitor an extra buffer pool for each page size. One caution: data pages still allow a maximum of 255 rows (slot 0 is used for the timestamp, each row costs a 4-byte slot entry), so very large pages with small rows waste space; index pages aren't limited this way. Art Kagel cited reports of ~25% throughput gains or ~25% lower CPU use after moving to larger pages. Consensus: go ahead and increase page size.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hello Friends, Any compelling reason not to use a page size on my database greater than 2K (which I understand is the default)? I have some tables where records are larger than 2K and wish to avoid page chaining by going to a 4K page size (or greater..). I was thinking this would make my system more perform..... nah. won't go there. Pros/Cons? Your opinions are most welcome.... Thanks as always. Tam.
On Fri, 17 Nov 2006 01:35:52 GMT, "Tam O'Shanter" <tam@oshanter.com> wrote: >Hello Friends, > >Any compelling reason not to use a page size on my database greater than 2K >(which I understand is the default)? > >I have some tables where records are larger than 2K and wish to avoid page >chaining by going to a 4K page size (or greater..). > >I was thinking this would make my system more perform..... nah. won't go >there. > >Pros/Cons? How wide are your tables? One reason that comes to mind quickly is that you could avoid multiple reads if your table is wider than 2k - 48 bytes (if I remember correctly). JWC
Tam O'Shanter said: > Hello Friends, > > Any compelling reason not to use a page size on my database greater than > 2K > (which I understand is the default)? > > I have some tables where records are larger than 2K and wish to avoid page > chaining by going to a 4K page size (or greater..). > > I was thinking this would make my system more perform..... nah. won't go > there. Yes, please don't. > Pros/Cons? > > Your opinions are most welcome.... I can't really think of a downside, other than managing multiple caches for multiple page sizes confuses old men like me. -- Bye now, Obnoxio "... no bill is required as no value was provided." -- Christine Normile -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.
One caution - Remember that you still have a "number of data rows" limit of 255, regardless of page size. This is due to the ROWID format of 0xLLLLLLSS, where SS = slot # on the page. SS range is 0-255, which is actually 256. But we take slot 0 for the page trailing time stamp. Keep in mind that this is only true for data pages, not index pages. You can cram as many index rows on a page as possible (with much consideration to the index row/page overhead). So creating say a 16K page with small'ish rows would burn space on the page. If you're gonna do the math, remember that each row inserted gets a 4-byte slot table entry. HTH - Mark. Obnoxio The Clown wrote: > Tam O'Shanter said: > > Hello Friends, > > > > Any compelling reason not to use a page size on my database greater than > > 2K > > (which I understand is the default)? > > > > I have some tables where records are larger than 2K and wish to avoid page > > chaining by going to a 4K page size (or greater..). > > > > I was thinking this would make my system more perform..... nah. won't go > > there. > > Yes, please don't. > > > Pros/Cons? > > > > Your opinions are most welcome.... > > I can't really think of a downside, other than managing multiple caches > for multiple page sizes confuses old men like me. > > -- > Bye now, > Obnoxio > > "... no bill is required as no value was provided." > -- Christine Normile > > -- > This message has been scanned for viruses and > dangerous content by OpenProtect(http://www.openprotect.com), and is > believed to be clean.
Tam O'Shanter wrote: > Hello Friends, > > Any compelling reason not to use a page size on my database greater than 2K > (which I understand is the default)? > > I have some tables where records are larger than 2K and wish to avoid page > chaining by going to a 4K page size (or greater..). > > I was thinking this would make my system more perform..... nah. won't go > there. > > Pros/Cons? > > Your opinions are most welcome.... No downside. In fact, yesterday in his Chat With the Labs, Lester Knutsen reported that one of his clients experienced a 25% througput/runtime gain from changing pagesize from the 2K default to 16K for ~4K rows with no downside costs other than, as OTC points out, the need to monitor two caches. Another of Lester's clients increased page size as an experiment (IB their rows were close to 2K) and while performance of SQL did not improve or suffer, they noticed a 25% reduction in CPU usage which means they can live on that server host for longer through DB growth before having to spend $$ on a bigger faster system! I'd add that it probably means they could add more CPU VPs and improve responsiveness and parallel sort times by using that extra CPU power now. Art S. Kagel
"they noticed a 25% reduction in CPU usage which means they can live on that server host for longer through DB growth before having to spend $$ on a bigger faster system" And there in lies the reason hardware manaufacturers hate informix