Re: Question on IDS 10 page size

On Feb 4, 6:17 am, "Art S. Kagel (Oninit LLC)" <a...@oninit.com> wrote: > bozon wrote: > > Can any of you give me your opinion and experience on the following > > matter? > > > We have an OLTP database that we are moving to linux. One of the DBA's > > wanted to change all of the tables with row size greater than 100 > > bytes to 16k pages. I balked at this because I was worried about > > thrashing the cache and wasting a lot of IO because in an OLTP > > application you may only use a small percent of rows per table per > > query. I was also worried about running into some bug with the new > > page sizing. > > > I was had recommended anything greater than 1000 bytes moved to 16k. > > This moves fewer tables and gets rid of the inefficient allocation of > > space. > > > Thanks, I would prefer more opinions on this matter than just mine. > > First, I know of no bugs in the larger pagesize code.  Next:  Indexes > almost always benefit from larger pages as more nodes are read in > together without the overhead of read-ahead pulling in unneeded pages. > Also this tends to isolate index pages from data pages preventing either > from thrashing the other in to/out of cache. > > As for data pages, that will largely depend on many factors.  The most > important are: how much waste is on a current page?  If it's a > significant percentage of a row, increasing pagesize to coalesce that > waste into one or more rows of data will be good thing.  Next do you > have larger rows that exceed 2020 bytes?  Yes?  Then larger pages for > those tables will improve performance as well by gathering all of rows > data onto a single page or even perhaps allowing for more than one row > on a page improving cache performance.  Last, do you frequently access > multiple rows on pages that are colocated?  If yes, say because a large > report pulls in a whole day/week/month's worth of data or warehouse apps > access multiple day's data, then yes, larger pagesizes will likely > improve performance for the same reasons that index performance is > enhanced.  Namely because data will be more likely to be found in cache > while minimizing readahead waste.  As always, you'll have to test it, > but these guidelines will give you somewhere to start, YMMV is the > watchword.  Oh, and 16K pages for everything is overkill, for certain, > except maybe in a large data warehouse system. > > Art S. Kagel > Oninit > > =========================================================================== ================ > Please access the attached hyperlink for an important electronic communications disclaimer: > > http://www.oninit.com/home/disclaimer.php > > =========================================================================== ================ One important technical point for this discussion - don't forget when considering a larger page size that there is STILL the 255 limit for rows on data pages(due to ROWID format). That is an important consideration no doubt. When I was speaking on the IDS 10 tour, every so often a client would say "I"ll just put everything on 16K pages", which of course, would not be a great choice unless thought through and appropriate. HTH - Mark