IDS Forum

RE: Estimate buffers

Posted By: Andrew Ford
Date: Thursday, 4 April 2013, at 11:05 a.m.

In Response To: Estimate buffers (FELIPE CLEMENTE)

Informix will buffer the most recently accessed data/index pages in memory
because those are the ones most likely to be needed again. So you can't
really base your buffer configuration on table/index size unless you know
that only this table will be selected/modified and no other tables will be
accessed and kick least recently used pages out of the buffers.

I would base the buffer configuration on the following formula (Total KB RAM
on Server - KB RAM needed for OS/other apps - KB RAM needed for SHMVIRTSIZE)
/ 2 (assuming a 2K page size). Basically max out the number of buffers based
on what is left on the machine.

But to try and answer your question, if you want to find out how many data
pages fit into a single buffer or data page, use this formula:

trunc((Page Size Bytes - 28) / (Row Size Bytes + 4))

So for a page size of 2048 bytes and a row size of 124 bytes, you can fit
trunc((2048 - 28) / (124 + 4)) = 15 rows per page.

If you wanted to know how many buffers you need to fit 1 million of these
row in memory (and you assume that all rows are tightly packed and no holes
are in your data pages) you would need 1 million / 15 = 66667 2K buffers.

Index pages are a little more complicated and there is an algorithm in the
Performance Guide that shows how to estimate the number of pages required to
store a btree index.

You could also select dbsname, tabname, sum(size) from sysmaster:sysextents
group by 1, 2 to find out how many pages are allocated to each table/index
to find the upper bound of buffers needed to hold each table or index.

Andrew

-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of FELIPE
CLEMENTE
Sent: Thursday, April 04, 2013 9:35 AM
To: ids@iiug.org
Subject: Estimate buffers [29982]

HI folks,

Anyone knows a query to estimate how much BUFFERS i need to a certain table
or index?

Thanks

****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.

Messages In This Thread

• Estimate buffers
  FELIPE CLEMENTE -- Thursday, 4 April 2013, at 10:34 a.m.
  • RE: Estimate buffers
    Andrew Ford -- Thursday, 4 April 2013, at 11:05 a.m.
    • Re: RE: Estimate buffers
      FELIPE CLEMENTE -- Thursday, 4 April 2013, at 3:30 p.m.
      • Re: RE: Estimate buffers
        Art Kagel -- Thursday, 4 April 2013, at 6:02 p.m.
    • Re: Estimate buffers
      FRANK -- Friday, 5 April 2013, at 12:26 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.