IDS Forum

Re: Page size

Posted By: FRANK
Date: Thursday, 17 January 2013, at 2:52 p.m.

In Response To: Re: Page size (Art Kagel)

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

Messages In This Thread

• Page size
  JOHN CALLICOTTE -- Wednesday, 9 January 2013, at 10:28 a.m.
  • Re: Page size
    Keith Simmons -- Wednesday, 9 January 2013, at 10:40 a.m.
    • Re: Page size
      JOHN CALLICOTTE -- Wednesday, 9 January 2013, at 10:42 a.m.
      • RE: Page size
        Alexandre Marini -- Wednesday, 9 January 2013, at 11:06 a.m.
      • RE: Page size
        Alexandre Marini -- Wednesday, 9 January 2013, at 11:08 a.m.
      • Re: Page size
        Jonathan Leffler -- Wednesday, 9 January 2013, at 12:06 p.m.
      • Re: Page size
        Art Kagel -- Wednesday, 9 January 2013, at 12:11 p.m.
        • Re: Page size
          FRANK J. COMPUTER -- Wednesday, 16 January 2013, at 9:09 p.m.
          • Re: Page size
            Art Kagel -- Wednesday, 16 January 2013, at 10:20 p.m.
            • Re: Page size
              FRANK -- Thursday, 17 January 2013, at 2:52 p.m.
  • Re: Page size
    JACQUES RENAUT -- Wednesday, 9 January 2013, at 11:20 a.m.
  • Re: Page size
    Spokey Wheeler Gmail -- Thursday, 10 January 2013, at 1:50 a.m.
  • Re: Page size
    BENJAMIN GARCIA VALLEJO -- Thursday, 10 January 2013, at 8:27 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.