RE: Question on IDS 10 page size
Last time I checked - which was three or four days ago, so I may be wrong. 6,10,12 and 14K pagesizes were not options. Do you have other experience? cheers j. -----Original Message----- From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org]On Behalf Of Tilman Sent: Monday, January 28, 2008 3:29 PM To: bozon Cc: informix-list@iiug.org Subject: Re: Question on IDS 10 page size 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. My opinion: 16k for everything is probably not a good idea. If you have 16K pages, you'll probably end up using only a small percentage of the 16K I/O and waste a lot of memory in the bufferpool and/or waste time during I/O. There is probably an optimum pages size beyond which performance decreases again - as you suggested. If you have got unlimited resources and time: Create an OLTP performance test close to my real life application The run it with several page sizes and mixtures of pages sizes - a 10 dimensional test matrix of some 100.000 single test scenarios should do. Pick the one combination with the best performance. As this is probably not possible, here is my gut feeling: - 4K page size for most tables might be ok - as a default. - Then I'd try to identify a handful of key / hotspot tables and consider each one separately whether 8k or even 16k might be beneficial. Finally - to make my life as a DBA easier and cause I like powers of 2 - I'd use 2,4,8 or 16K pagesizes but probably no 6k, 10k or 12 k or 14k pages. I have to admit, that I cannot offer test results that support this approach HTH Tilman _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list
On Jan 28, 7:36 pm, "Jack Parker" <jack.park...@verizon.net> wrote: > Last time I checked - which was three or four days ago, so I may be wrong. > 6,10,12 and 14K pagesizes were not options. Do you have other experience? > > cheers > j. > > -----Original Message----- > From: informix-list-boun...@iiug.org > > [mailto:informix-list-boun...@iiug.org]On Behalf Of Tilman > Sent: Monday, January 28, 2008 3:29 PM > To: bozon > Cc: informix-l...@iiug.org > Subject: Re: Question on IDS 10 page size > > 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. > > My opinion: > 16k for everything is probably not a good idea. If you have 16K pages, > you'll probably end up using only a small percentage of the 16K I/O and > waste a lot of memory in the bufferpool and/or waste time during I/O. > > There is probably an optimum pages size beyond which performance > decreases again - as you suggested. > > If you have got unlimited resources and time: > Create an OLTP performance test close to my real life application > The run it with several page sizes and mixtures of pages sizes - a 10 > dimensional test matrix of some 100.000 single test scenarios should do. > Pick the one combination with the best performance. > > As this is probably not possible, here is my gut feeling: > - 4K page size for most tables might be ok - as a default. > - Then I'd try to identify a handful of key / hotspot tables and > consider each one separately whether 8k or even 16k might be beneficial. > > Finally - to make my life as a DBA easier and cause I like powers of 2 - > I'd use 2,4,8 or 16K pagesizes but probably no 6k, 10k or 12 k or 14k > pages. > > I have to admit, that I cannot offer test results that support this approach > HTH > Tilman > > _______________________________________________ > Informix-list mailing list > Informix-l...@iiug.orghttp://www.iiug.org/mailman/listinfo/informix-list I just created a dbs with a 6k page size. Now this isn't possible on a windows NT box or others because it must be a multiple of the default page size for a port, which is 4k on a windows machine. On my port the default page size is 2k so I have these other options. I am as superstitious as the next DBA but I wouldn't rule out the 6k, 10, 12k, or 14k if they fit my needs and they tested faster. I was hoping to see if a general rule was used when creating these page sizes. I am pretty sure greater than 100 bytes isn't a good rule.
Fascinating. Of course that would work on Unix. My own efforts are currently restricted to Windows - hence the disconnect. I just created a 12K. Thanks for the correction. I happen to agree with you that powers of 2 are easier to work with. cheers j. -----Original Message----- From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org]On Behalf Of bozon Sent: Tuesday, January 29, 2008 9:57 AM To: informix-list@iiug.org Subject: Re: Question on IDS 10 page size On Jan 28, 7:36 pm, "Jack Parker" <jack.park...@verizon.net> wrote: > Last time I checked - which was three or four days ago, so I may be wrong. > 6,10,12 and 14K pagesizes were not options. Do you have other experience? > > cheers > j. > > -----Original Message----- > From: informix-list-boun...@iiug.org > > [mailto:informix-list-boun...@iiug.org]On Behalf Of Tilman > Sent: Monday, January 28, 2008 3:29 PM > To: bozon > Cc: informix-l...@iiug.org > Subject: Re: Question on IDS 10 page size > > 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. > > My opinion: > 16k for everything is probably not a good idea. If you have 16K pages, > you'll probably end up using only a small percentage of the 16K I/O and > waste a lot of memory in the bufferpool and/or waste time during I/O. > > There is probably an optimum pages size beyond which performance > decreases again - as you suggested. > > If you have got unlimited resources and time: > Create an OLTP performance test close to my real life application > The run it with several page sizes and mixtures of pages sizes - a 10 > dimensional test matrix of some 100.000 single test scenarios should do. > Pick the one combination with the best performance. > > As this is probably not possible, here is my gut feeling: > - 4K page size for most tables might be ok - as a default. > - Then I'd try to identify a handful of key / hotspot tables and > consider each one separately whether 8k or even 16k might be beneficial. > > Finally - to make my life as a DBA easier and cause I like powers of 2 - > I'd use 2,4,8 or 16K pagesizes but probably no 6k, 10k or 12 k or 14k > pages. > > I have to admit, that I cannot offer test results that support this approach > HTH > Tilman > > _______________________________________________ > Informix-list mailing list > Informix-l...@iiug.orghttp://www.iiug.org/mailman/listinfo/informix-list I just created a dbs with a 6k page size. Now this isn't possible on a windows NT box or others because it must be a multiple of the default page size for a port, which is 4k on a windows machine. On my port the default page size is 2k so I have these other options. I am as superstitious as the next DBA but I wouldn't rule out the 6k, 10, 12k, or 14k if they fit my needs and they tested faster. I was hoping to see if a general rule was used when creating these page sizes. I am pretty sure greater than 100 bytes isn't a good rule. _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list