Page size

Where do you set the page size when configuring Informix? I don't see it in the ONCONFIG anywhere.

John You don't really, but it depends on your version !! Pre 10, no choice - WinDoze and AIX is 4k, everything else is 2k. 10 and up, critical spaces must follow the above rules but you can specify page sizes for other spaces when they are created. There are BUFFERPOOL lines in the onconfig file to set up memory pages for handling the differing page sizes. Keith On 9 January 2013 15:28, JOHN CALLICOTTE <john.callicotte@ottawa.edu> wrote: > Where do you set the page size when configuring Informix? I don't see it in > the ONCONFIG anywhere. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --20cf307f39aafe156b04d2dce034

I'm running (or trying to) 11.7. I would like to reinitialize my root dbspace and shared memory, but it's not allowing me to change it in onmonitor. I'm on Linux 64-bit.

Hello, John. I suppose you must have some shared memory segment locked, remaining to be freed before starting Informix again. Try to run onclean, as described here: http://publib.boulder.ibm.com/infocenter/idshelp/v117/topic/com.ibm.adref.doc/id s_adr_1073.htm?resultof=%22%6f%6e%63%6c%65%61%6e%22%20 And after running, try to start your engine again (oninit -iv). If your engine does not start, print your informix log message file (last lines, of course) here, and we could try to help you on this. In linux, it´s located (by default) in the path below: $INFORMIXDIR/tmp/online.log Regards. Alexandre Marini IBM Informix Certified Professional v10 / v11.50 / v11.70 IBM Information Management Informix Technical Professional IBM Infosphere DataStage Technical Professional Informix Senior DBA - Orizon Brasil BRIUG website administrator Informix independent consultant > To: ids@iiug.org > From: john.callicotte@ottawa.edu > Subject: Re: Page size [29240] > Date: Wed, 9 Jan 2013 10:42:45 -0500 > > I'm running (or trying to) 11.7. I would like to reinitialize my root dbspace > and shared memory, but it's not allowing me to change it in onmonitor. I'm on > Linux 64-bit. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Oh and please, forget about onmonitor, it´s not used anymore since version 10, I think.... and not recommended anymore, like it was told before.... ok? Regards. Alexandre Marini IBM Informix Certified Professional v10 / v11.50 / v11.70 IBM Information Management Informix Technical Professional IBM Infosphere DataStage Technical Professional Informix Senior DBA - Orizon Brasil BRIUG website administrator Informix independent consultant From: alexandre@briug.org To: ids@iiug.org Subject: RE: Page size [29240] Date: Wed, 9 Jan 2013 14:06:03 -0200 Hello, John. I suppose you must have some shared memory segment locked, remaining to be freed before starting Informix again. Try to run onclean, as described here: http://publib.boulder.ibm.com/infocenter/idshelp/v117/topic/com.ibm.adref.doc/id s_adr_1073.htm?resultof=%22%6f%6e%63%6c%65%61%6e%22%20 And after running, try to start your engine again (oninit -iv). If your engine does not start, print your informix log message file (last lines, of course) here, and we could try to help you on this. In linux, it´s located (by default) in the path below: $INFORMIXDIR/tmp/online.log Regards. Alexandre Marini IBM Informix Certified Professional v10 / v11.50 / v11.70 IBM Information Management Informix Technical Professional IBM Infosphere DataStage Technical Professional Informix Senior DBA - Orizon Brasil BRIUG website administrator Informix independent consultant > To: ids@iiug.org > From: john.callicotte@ottawa.edu > Subject: Re: Page size [29240] > Date: Wed, 9 Jan 2013 10:42:45 -0500 > > I'm running (or trying to) 11.7. I would like to reinitialize my root dbspace > and shared memory, but it's not allowing me to change it in onmonitor. I'm on > Linux 64-bit. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Original post: Where do you set the page size when configuring Informix? I don't see it in the ONCONFIG anywhere. Response: You can't change the page size via the ONCONFIG file. You can create dbspaces that have different page sizes (see following link it's for 11.50 but should be basically the same for 11.70) http://publib.boulder.ibm.com/infocenter/idshelp/v115/topic/com.ibm.admin.doc/id s_admin_0564.htm Note, that the root dbspace and the dbspaces that contain the logical logs and physical log must be the default page size (which is 2k for all systems except aix and windows which is 4k) Jacques Renaut IBM Informix Advanced Support APD Team

On Wed, Jan 9, 2013 at 7:42 AM, JOHN CALLICOTTE <john.callicotte@ottawa.edu>wrote: > I'm running (or trying to) 11.7. I would like to reinitialize my root > dbspace > and shared memory, but it's not allowing me to change it in onmonitor. I'm > on > Linux 64-bit. > You can't change the page size of the root dbspace; that is the most critical of the critical dbspaces. ON-Monitor is not aware of FULL_DISK_INIT, so you can't use ON-Monitor to reinitialize a server (you at least have to edit the ONCONFIG file manually to change the setting of FULL_DISK_INIT). It also is not aware of buffer pools and a variety of other parameters. It also has archaic views on how to specify numbers of VPs in various VP classes. Also, assume ON-Monitor will not be available in Informix versions later than 11.70. -- Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." --047d7b66f51541762304d2de1616

John: Again, NEVER USE ONMONITOR FOR ANYTHING. The thing doesn't always work right in my experience and it is going away. In addition, it does not understand any newer features like creating dbspaces that have a pagesize different from the default for your platform. Manage your ONCONFIG file and dbspaces manually, it is MUCH better. That said: As has been mentioned, you cannot change the pagesize for the root dbspace nor for any dbspace that will contain logical or physical logs. Those must be the default pagesize for your platform which is 2K on all platforms except Windows, AIX, and MacOS-X where it is 4K. For other dbspaces, you can specify their pagesize when you create them using the onspaces utility or using the SQL API commands. Pagesizes must be a multiple of the default pagesize for your platform, so 2, 4, 6, 8, 10, 12, 14, 16K on most UNIXs & Linux, 4, 8, 12, 16K on WIndows, AIX, & MacOS-X. Example: onspaces -c -d mydbspace -p /path/to/chunk/file-or-device -o 0 -s2048000000 -k 16 This creates a new dbspace named 'mydbspace' with an initial chunk at the specified path with an offset from the beginning of the file/device or zero and a size of a bit under 2TB with a 16K pagesize. Before doing that you should have modified the ONCONFIG file to include a BUFFERPOOL entry line defining the size of the 16K pagesize buffer pool and bounced the instance. If you do not, the engine will create a default buffer pool for the new dbspace using defaults which is probably only about 1000 pages. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) 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 Wed, Jan 9, 2013 at 9:42 AM, JOHN CALLICOTTE <john.callicotte@ottawa.edu>wrote: > I'm running (or trying to) 11.7. I would like to reinitialize my root > dbspace > and shared memory, but it's not allowing me to change it in onmonitor. I'm > on > Linux 64-bit. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae934111fafaeaf04d2de2516

On 9 Jan 2013, at 15:28, JOHN CALLICOTTE <john.callicotte@ottawa.edu> wrote: > Where do you set the page size when configuring Informix? I don't see it in > the ONCONFIG anywhere. onspaces is your friend.

Hi, first ,use onspaces with option k to create a dbspace with your desired page size second,in the onconfig define a bufferpool for the desired page size regards

Does a larger page size provide better performance with larger tables? Is an 11.70 dbspace still limited to a max of 16,777,216 pages?

First, there was never a limit on the size of a dbspace except the maximum number of chunks in a server instance and the maximum chunk size. For 11.70 that is up to 327675 chunks of up to 4TB each or about 128PB. Tables whose rows are currently wider than the pagesize of the dbspace containing them will definitely improve if moved in to a dbspace with a pagesize that can hold full rows or even multiple rows. For tables with rows smaller than a page, performance improvement from wider pages for the tables themselves (as opposed to indexes) will depend mostly on how much of each page is wasted. Moving a table to wider pages may waste less space per KB of disk and so pull in more rows with a single IO. Whether this improves performance depends mostly upon the likelyhood that the additional rows will be accessed thereby eliminating IOs and IO waits. Indexes almost always do best on 16K pages. The main exception being indexes on tables with very few rows. FYI, setting MAX_FILL_DATA_PAGES to 1 and rebuilding the table can also have a significant effect on IO performance by reducing wasted space on pages for tables that have variable length columns that are rarely filled to their maximum length. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) 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 Wed, Jan 16, 2013 at 9:10 PM, FRANK J. COMPUTER <frank_in_pr@hotmail.com>wrote: > Does a larger page size provide better performance with larger tables? > Is an 11.70 dbspace still limited to a max of 16,777,216 pages? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --bcaec517cbe091b34e04d3737a1c

We did a number of tests/evaluations on 2k pageszie vs. 8k pagesize, the conclusion is: 8k pagesize gives MUCH better performance on both data and index retrieval in all our SQL jobs !(IDS11.50FC8/Redhat Linux) Frank On Wed, Jan 16, 2013 at 10:20 PM, Art Kagel <art.kagel@gmail.com> wrote: > First, there was never a limit on the size of a dbspace except the maximum > number of chunks in a server instance and the maximum chunk size. For > 11.70 that is up to 327675 chunks of up to 4TB each or about 128PB. > > Tables whose rows are currently wider than the pagesize of the dbspace > containing them will definitely improve if moved in to a dbspace with a > pagesize that can hold full rows or even multiple rows. > > For tables with rows smaller than a page, performance improvement from > wider pages for the tables themselves (as opposed to indexes) will depend > mostly on how much of each page is wasted. Moving a table to wider pages > may waste less space per KB of disk and so pull in more rows with a single > IO. Whether this improves performance depends mostly upon the likelyhood > that the additional rows will be accessed thereby eliminating IOs and IO > waits. > > Indexes almost always do best on 16K pages. The main exception being > indexes on tables with very few rows. > > FYI, setting MAX_FILL_DATA_PAGES to 1 and rebuilding the table can also > have a significant effect on IO performance by reducing wasted space on > pages for tables that have variable length columns that are rarely filled > to their maximum length. > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > 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 Wed, Jan 16, 2013 at 9:10 PM, FRANK J. COMPUTER > <frank_in_pr@hotmail.com>wrote: > > > Does a larger page size provide better performance with larger tables? > > Is an 11.70 dbspace still limited to a max of 16,777,216 pages? > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --bcaec517cbe091b34e04d3737a1c > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --20cf306f7504e04b0e04d3815275