Page Size
Frank, setting up a new 11.50.FC7 instance on Linux (2K default page size), asked whether non-default page sizes (he planned 8K) make sense for physical log, logical log and temp spaces. Art Kagel replied that logical and physical logs must live in default-page-size dbspaces, while temp dbspaces can use varied sizes, and warned that larger pages aren't universally better — test per table/index. Fernando Nunes added that with a 255-rows-per-page limit, 8K pages waste space unless rows are ~32+ bytes; 4K is a reasonable compromise. Frank noted big pages did lower index tree levels for his VARCHAR(255) indexes with no performance loss. Advice given rather than a single fix.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi, All, Build Version: 11.50.FC7 Build OS: Linux 2.6.9-34.ELsmp We are setting up dbspaces for a new Instance. Root dbs must be the default system page size (2k here). We are going to use page size 8k for our data spaces. Any experience or comments on the page size of Physical log, Logical log or temporal spaces....? Thanks a lot, Frank --00163628410c834a430497664339
Logical and physical logs must also be in default page sized dbspaces. You can set up tempdbspaces in different sizes for different purposes. Wide pages sizes have not been shown to be universally better than 2K pages! You should test thoroughly to determine the best page sizes for all important tables and indexes. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Dec 14, 2010 at 5:23 PM, FRANK <yunyaoqu@gmail.com> wrote: > Hi, All, > > Build Version: 11.50.FC7 > Build OS: Linux 2.6.9-34.ELsmp > > We are setting up dbspaces for a new Instance. Root dbs must be the > default system page size (2k here). We are going to use page size 8k for > our data spaces. > > Any experience or comments on the page size of Physical log, Logical log or > temporal spaces....? > > Thanks a lot, > Frank > > --00163628410c834a430497664339 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --20cf3054ace329d5c10497667195
8K for everything? Have you checked your row size? Will you be filling every page or just wasting space? Don't get me wrong, there are many situations where an 8k page size is a good choice, but I still haven't seen a system where it should be the size for all dbspaces. Regards. On Tue, Dec 14, 2010 at 10:23 PM, FRANK <yunyaoqu@gmail.com> wrote: > Hi, All, > > Build Version: 11.50.FC7 > Build OS: Linux 2.6.9-34.ELsmp > > We are setting up dbspaces for a new Instance. Root dbs must be the > default system page size (2k here). We are going to use page size 8k for > our data spaces. > > Any experience or comments on the page size of Physical log, Logical log or > temporal spaces....? > > Thanks a lot, > Frank > > --00163628410c834a430497664339 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --0015174c371c4dc701049766ad81
Art, Thanks!! We did test the bigger page sizes. We have historical nasty indexes whose index keys are VARCHAR( 255).... The tables are also big, so the index tree levels are large/high ( >6) . With a bigger page size, we saw the index level is lower and the performance seems NO big difference, but sounds not worse :-). I have the same feelings as you have.... Just figure crossed! Thanks Frank On Tue, Dec 14, 2010 at 5:35 PM, Art Kagel <art.kagel@gmail.com> wrote: > Logical and physical logs must also be in default page sized dbspaces. You > can set up tempdbspaces in different sizes for different purposes. > > Wide pages sizes have not been shown to be universally better than 2K > pages! You should test thoroughly to determine the best page sizes for all > important tables and indexes. > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > IIUG Board of Directors (art@iiug.org) > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and > do not reflect on my employer, Advanced DataTools, the IIUG, nor any other > organization with which I am associated either explicitly, implicitly, or > by > inference. Neither do those opinions reflect those of other individuals > affiliated with any entity with which I am affiliated nor those of the > entities themselves. > > On Tue, Dec 14, 2010 at 5:23 PM, FRANK <yunyaoqu@gmail.com> wrote: > > > Hi, All, > > > > Build Version: 11.50.FC7 > > Build OS: Linux 2.6.9-34.ELsmp > > > > We are setting up dbspaces for a new Instance. Root dbs must be the > > default system page size (2k here). We are going to use page size 8k for > > our data spaces. > > > > Any experience or comments on the page size of Physical log, Logical log > or > > temporal spaces....? > > > > Thanks a lot, > > Frank > > > > --00163628410c834a430497664339 > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --20cf3054ace329d5c10497667195 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --00163631020d31f3f2049766c428
Thanks, Fernando! This is exactly what I want have other opinions or experiences... ( both good or bad are welcome!) It is not finalized yet. I replied to Art for one suspicious reason. OK. What do you think of we move our DB from AIX 4k page size to Linux 2k page size? Thanks, Frank On Tue, Dec 14, 2010 at 5:52 PM, Fernando Nunes <domusonline@gmail.com>wrote: > 8K for everything? Have you checked your row size? Will you be filling > every > page or just wasting space? > Don't get me wrong, there are many situations where an 8k page size is a > good choice, but I still haven't seen a system where it should be the size > for all dbspaces. > Regards. > > On Tue, Dec 14, 2010 at 10:23 PM, FRANK <yunyaoqu@gmail.com> wrote: > > > Hi, All, > > > > Build Version: 11.50.FC7 > > Build OS: Linux 2.6.9-34.ELsmp > > > > We are setting up dbspaces for a new Instance. Root dbs must be the > > default system page size (2k here). We are going to use page size 8k for > > our data spaces. > > > > Any experience or comments on the page size of Physical log, Logical log > or > > temporal spaces....? > > > > Thanks a lot, > > Frank > > > > --00163628410c834a430497664339 > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > --0015174c371c4dc701049766ad81 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0016363b8d8cea1e39049766de84
On Tue, Dec 14, 2010 at 11:06 PM, FRANK <yunyaoqu@gmail.com> wrote: > Thanks, Fernando! > > This is exactly what I want have other opinions or experiences... ( both > good or bad are welcome!) > It is not finalized yet. I replied to Art for one suspicious reason. > For that reason, it seems a good idea. That's precisely my point (and I think Art also agrees). Different needs will require different solutions. > > OK. What do you think of we move our DB from AIX 4k page size to Linux 2k > page size? > Working for my employer, I think this is not a good decision, and this point of view may not have anything to do with page sizes:) But seriously, 4KB seems to me a reasonable compromise. Not that 2KB is a bad thing, but if you don't want to waste time checking if 2KB is better or worse for which tables, than 4KB will probably not harm you. 8KB however may force you to waste too many space. Let's consider this: - The maximum rows per page is 255 - On an 8KB page, if your row size is around 32 bytes, you'll be able to insert more or less the limit (255) rows. for anything less than 32 bytes you'll waste space in your page... And daily I see several tables with row sizes of much less than 32 bytes. Naturally it will be easy to find much bigger ones (you mention one example). So, clearly this is a case where one size does not fit all Note that my calculations are not exact. 32 * 255 = 8160 ~ around the usable space on an 8KB page. On a 4KB page, the usable space should be around 4068. So 4068 / 255 is about 16. So, yes, there are probably many tables around with less than 16 bytes rowsize, but for those ones the wasted space on a 4KB page is much less than on an 8KB page... Doing the same for 2KB pages, you'll start wasting space for tables with a row size less than 7/8 bytes. So these ones will be much harder to find. Also note that all this rhetoric is based on trying to fill the page with the number of rows allowed (255). In most real situations you'll end up wasting space because you don't have enough bytes to insert another row. Let's consider a row size of 1024. You'll be able to fit only one row per page and you'll waste 996 bytes per page. The more rows you fit on a page, the less I/O you'll end up doing... Regards. > > Thanks, > Frank > > On Tue, Dec 14, 2010 at 5:52 PM, Fernando Nunes <domusonline@gmail.com > >wrote: > > > 8K for everything? Have you checked your row size? Will you be filling > > every > > page or just wasting space? > > Don't get me wrong, there are many situations where an 8k page size is a > > good choice, but I still haven't seen a system where it should be the > size > > for all dbspaces. > > Regards. > > > > On Tue, Dec 14, 2010 at 10:23 PM, FRANK <yunyaoqu@gmail.com> wrote: > > > > > Hi, All, > > > > > > Build Version: 11.50.FC7 > > > Build OS: Linux 2.6.9-34.ELsmp > > > > > > We are setting up dbspaces for a new Instance. Root dbs must be the > > > default system page size (2k here). We are going to use page size 8k > for > > > our data spaces. > > > > > > Any experience or comments on the page size of Physical log, Logical > log > > or > > > temporal spaces....? > > > > > > Thanks a lot, > > > Frank > > > > > > --00163628410c834a430497664339 > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > -- > > Fernando Nunes > > Portugal > > > > http://informix-technology.blogspot.com > > My email works... but I don't check it frequently... > > > > --0015174c371c4dc701049766ad81 > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --0016363b8d8cea1e39049766de84 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --0015174c371c06f4d80497677ef1