Estimate buffers

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

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.

Thanks Andrew, but I think I couldn't explain my real doubt. I have a large index and has already reached 5 levels, then we want to rebuild it, as we'll do this we intend to use another dbspace with a different page size of the current, but this informix server is very critical and we do not have a lot of time to adjust the buffer size, so we want to estimate how much of the current index is in the buffer and use that number to the another buffer.

Run onstat -P during different times of the day and grep for the index's partnum (see systabnames) that will tell you how many pages the index typically has in the current cache and work from there. 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 Thu, Apr 4, 2013 at 3:30 PM, FELIPE CLEMENTE <felipe@mcsoftware.com.br>wrote: > Thanks Andrew, but I think I couldn't explain my real doubt. > > I have a large index and has already reached 5 levels, then we want to > rebuild > it, as we'll do this we intend to use another dbspace with a different page > size of the current, but this informix server is very critical and we do > not > have a lot of time to adjust the buffer size, so we want to estimate how > much > of the current index is in the buffer and use that number to the another > buffer. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae93d962c309f6804d9901fe1

If you have a luxury size of memory, how about get a All In_Memory database ? Frank On Thu, Apr 4, 2013 at 11:05 AM, Andrew Ford <andrew@informix-dba.com>wrote: > 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. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --00235446febc5d8f8204d99f8a59