Regarding Non-Default Page Sizes
Keith Simmons (IDS 10 on Solaris, 2K default page size) has a table with 1079-byte rows wasting most of each page and hitting the ~16.7M row limit. He plans to move it to 8K dbspaces and asks whether temp dbspaces should be 2K or 8K. Replies: each page size has its own buffer pool (onstat -P / -g buf), so a non-default temp space can reduce buffer contention; but one poster cited John Miller as saying temp tables always use the default page size, so a larger temp dbspace may be wasted. No definitive answer or test result is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I have IDS 10.00.FC5 running on Solaris 5.8 thus I have a default page size of 2k. One of the table (NOT my design !!) has a row size of 1079 so I can only get one row per page. Fragmentation is difficult (except round robin) so I am limited to around 16.77 million rows and I am wasting an awful load of space. My intention is to create some dbspaces with 8k page size, increase my number of potential rows seven fold and significantly reduce the waste and thus I/O. Question is, what do I do with regard temp dbspaces. Admin Guide says all dbspaces must be the same size, so should I make them 2k (O/S default, current size and the same size as the majority of the dbspaces in the database) or 8k (same size as the new spaces). Is there any advantage/disadvantage either way from a performance or space point of view ? How does an 8k page size map down to a 2k temp dbspace, will is still just hold 1 (of my large) record(s) per page ? I can find nothing relevant either online or in the documentation. Keith
John Miller III covered this at one of the IIUG conferences. He said that temp tables use the default page size no matter what. So creating your temp dbspaces at 16K will be a waste because the temp tables will be created at the default anyway. This was a couple of years ago and I was using version 10 at the time. Not sure if this has changed in later versions or not. Kernoal
I have one temp dbspace and it is a 16k page size.
I just selected * from a rather large table and saw free space in that
dbspace go down (onstat -d), so the engine does use it.
This is 11.70.TC5IE on Win2k8 .
Someone on this list has stated that one advantage of having a temp space in
a non-default-size dbspace is that it will be in a different buffer pool.
I don't know how to test that.
-----Original Message-----
From: KERNOAL STEPHENS
Sent: Tuesday, July 31, 2012 8:19 AM
To: ids@iiug.org
Subject: Re: Regarding Non-Default Page Sizes [27877]
John Miller III covered this at one of the IIUG conferences. He said that
temp
tables use the default page size no matter what. So creating your temp
dbspaces at 16K will be a waste because the temp tables will be created at
the
default anyway. This was a couple of years ago and I was using version 10 at
the time. Not sure if this has changed in later versions or not.
Kernoal
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
It will use the 16k dbspace, but it will only use 2k of each 16k page. Create the temp table and then dump the pages to see if it fills the 16k or just the default page size.
Kernoal I'm surprised, would this be the same for system tables used for sort/merge operations ? Keith On 31 July 2012 14:19, KERNOAL STEPHENS <informixdba@gmail.com> wrote: > John Miller III covered this at one of the IIUG conferences. He said that temp > tables use the default page size no matter what. So creating your temp > dbspaces at 16K will be a waste because the temp tables will be created at the > default anyway. This was a couple of years ago and I was using version 10 at > the time. Not sure if this has changed in later versions or not. > > Kernoal > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
Each pagesize has its own buffer pool. You can see this in the onstat -P
and onstat -g buf output.
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 Tue, Jul 31, 2012 at 10:44 AM, Bill Hamilton <garage_dba@verizon.net>wrote:
> I have one temp dbspace and it is a 16k page size.
> I just selected * from a rather large table and saw free space in that
> dbspace go down (onstat -d), so the engine does use it.
> This is 11.70.TC5IE on Win2k8 .
>
> Someone on this list has stated that one advantage of having a temp space
> in
> a non-default-size dbspace is that it will be in a different buffer pool.
> I don't know how to test that.
>
> -----Original Message-----
> From: KERNOAL STEPHENS
> Sent: Tuesday, July 31, 2012 8:19 AM
> To: ids@iiug.org
> Subject: Re: Regarding Non-Default Page Sizes [27877]
>
> John Miller III covered this at one of the IIUG conferences. He said that
> temp
> tables use the default page size no matter what. So creating your temp
> dbspaces at 16K will be a waste because the temp tables will be created at
> the
> default anyway. This was a couple of years ago and I was using version 10
> at
> the time. Not sure if this has changed in later versions or not.
>
> Kernoal
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--14dae934044578112304c62143bc
I know that.
I was referring to testing the performance improvements of using 16k vs 4k
page size.
How does the buffer pool speed things up for 'group by', 'order by', or temp
tables ?
-----Original Message-----
From: Art Kagel
Sent: Tuesday, July 31, 2012 9:54 AM
To: ids@iiug.org
Subject: Re: Regarding Non-Default Page Sizes [27884]
Each pagesize has its own buffer pool. You can see this in the onstat -P
and onstat -g buf output.
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 Tue, Jul 31, 2012 at 10:44 AM, Bill Hamilton
<garage_dba@verizon.net>wrote:
> I have one temp dbspace and it is a 16k page size.
> I just selected * from a rather large table and saw free space in that
> dbspace go down (onstat -d), so the engine does use it.
> This is 11.70.TC5IE on Win2k8 .
>
> Someone on this list has stated that one advantage of having a temp space
> in
> a non-default-size dbspace is that it will be in a different buffer pool.
> I don't know how to test that.
>
> -----Original Message-----
> From: KERNOAL STEPHENS
> Sent: Tuesday, July 31, 2012 8:19 AM
> To: ids@iiug.org
> Subject: Re: Regarding Non-Default Page Sizes [27877]
>
> John Miller III covered this at one of the IIUG conferences. He said that
> temp
> tables use the default page size no matter what. So creating your temp
> dbspaces at 16K will be a waste because the temp tables will be created at
> the
> default anyway. This was a couple of years ago and I was using version 10
> at
> the time. Not sure if this has changed in later versions or not.
>
> Kernoal
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--14dae934044578112304c62143bc
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Ahh, because it may eliminate contention for buffers with data on other
page sizes.
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 Tue, Jul 31, 2012 at 3:35 PM, Bill Hamilton <garage_dba@verizon.net>wrote:
> I know that.
> I was referring to testing the performance improvements of using 16k vs 4k
> page size.
> How does the buffer pool speed things up for 'group by', 'order by', or
> temp
> tables ?
>
> -----Original Message-----
> From: Art Kagel
> Sent: Tuesday, July 31, 2012 9:54 AM
> To: ids@iiug.org
> Subject: Re: Regarding Non-Default Page Sizes [27884]
>
> Each pagesize has its own buffer pool. You can see this in the onstat -P
> and onstat -g buf output.
>
> 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 Tue, Jul 31, 2012 at 10:44 AM, Bill Hamilton
> <garage_dba@verizon.net>wrote:
>
> > I have one temp dbspace and it is a 16k page size.
> > I just selected * from a rather large table and saw free space in that
> > dbspace go down (onstat -d), so the engine does use it.
> > This is 11.70.TC5IE on Win2k8 .
> >
> > Someone on this list has stated that one advantage of having a temp space
> > in
> > a non-default-size dbspace is that it will be in a different buffer pool.
> > I don't know how to test that.
> >
> > -----Original Message-----
> > From: KERNOAL STEPHENS
> > Sent: Tuesday, July 31, 2012 8:19 AM
> > To: ids@iiug.org
> > Subject: Re: Regarding Non-Default Page Sizes [27877]
> >
> > John Miller III covered this at one of the IIUG conferences. He said that
> > temp
> > tables use the default page size no matter what. So creating your temp
> > dbspaces at 16K will be a waste because the temp tables will be created
> at
> > the
> > default anyway. This was a couple of years ago and I was using version 10
> > at
> > the time. Not sure if this has changed in later versions or not.
> >
> > Kernoal
> >
> >
> >
> >
>
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
> >
> >
>
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --14dae934044578112304c62143bc
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--14dae9340e1bfcf8e804c625b142
Related threads
- IDS 10 table-level restore
- Informix Development Webinar December 11, 2007
- ontape -p/r with changed ROOTPATH
- Migrate from HP PA-RISC to HP ITANIUM by ontape