Question on IDS 10 page size
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.
bozon said: > 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. It's difficult to say anything without a better understanding of your application behaviour. However, in general, I'd keep to smaller page sizes unless essential, really. If you have rows over 1K, I'd probably only go for a 4K page and if you have rows over 2K, you should be taken outside and shot. :o) -- Bye now, Obnoxio "There were a myriad of problems which conspired to corrupt your reason and rob you of your common sense. Fear got the best of you, and in your panic you turned to the Labour Party. They promised you order, they promised you peace, and all they demanded in return was your silent, obedient consent." -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.
On Jan 28, 11:20 am, "Obnoxio The Clown" <obno...@serendipita.com> wrote: > bozon said: > > > > > 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. > > It's difficult to say anything without a better understanding of your > application behaviour. However, in general, I'd keep to smaller page sizes > unless essential, really. If you have rows over 1K, I'd probably only go > for a 4K page and if you have rows over 2K, you should be taken outside > and shot. :o) > > -- > Bye now, > Obnoxio > > "There were a myriad of problems which conspired to corrupt your reason > and rob you of your common sense. Fear got the best of you, and in your > panic you turned to the Labour Party. They promised you order, they > promised you peace, and all they demanded in return was your silent, > obedient consent." > > -- > This message has been scanned for viruses and > dangerous content by OpenProtect(http://www.openprotect.com), and is > believed to be clean. Not me I didn't create the big stupid tables. I am just the idiot who gets to keep it all working. We do have a couple of tables with a row size over 2K.
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 ===========================================================================================