quick help needed to calculate page sizes for logs

Hi, We have a newly transaction logged database. We are going to do some updates that will update all the rows in some tables inside a transaction. So I am trying to ensure we have enough logs do do the transaction. I am approaching this from two points. 1) the logs. We have 12 logs at 12800 pages each, and a high water mark of 45%, which tells me that we can only do a transaction of about 275 megs before a long transaction occurs. No problem here. 2) I need to find how big the transactions will be on each of these tables, and then, from what I understand, double that amount, as many updates will need to store a before image and an after image of each row, in the logs. Ok, so, for instance, oncheck -pt shows a table to have a maxrowsize of 1161, and 53,411 number of rows. I would think that if I multiply those 2 numbers and divide by 4096 ( 4kpage size), I would get the approximate number of pages needed to store that table. However, the number I get is 15,000 some odd pages, and according to the oncheck -pt that table is only using around 5000 pages. Where is thediscrepancy, I must be doing somethign wrong here. Then I figure, if I can get the correct size of the table, multiply that by 2, and just ensure that I add enough logs so that 45% of them will be enough to store that amount of data. Any advice on this would be appreciated. Thanks, floyd ======================== -<<Floyd Wellershaus>>- Database Administrator Unix Administrator email: fwellers@yahoo.com Work: 703-733-4126 Pager: 703-705-9241 Email Pager: 7037059241@my2way.com Home: 703-430-0805 Cell: 703-477-6045 ========================

Art, It sounds like you are saying that the calculations I am using are correct, and the varchar(30) that is in the table may be throwing it off. If that is so, I would be well advised to use the higher number, calculated by assuming the max rowsize, as this will give me a cushion. Does this sound right ? How bout the rest of my scenario, am I going about the sizing correctly ? Thanks, Floyd "ART KAGEL, ...." <KAGEL@bloomberg.net> wrote: Does the table contain variable length columns (ie VARCHARs & LVARCHARs)? The actual storage required for each row will be the actual current size of the row NOT the maximum size. Informix assumes that you will not use more of a VARCHAR column than the current size rounded to the reserve size or that you will use more than the current size of an LVARCHAR column, so the rows are 'compressed' on the page with as many rows as will fit. Art S. Kagel ----- Original Message ----- From: Floyd Welle.... At: 2/10 9:27 > Hi, > We have a newly transaction logged database. We are going to do some updates > that will update all the rows in some tables inside a transaction. So I am > trying to ensure we have enough logs do do the transaction. > I am approaching this from two points. > 1) the logs. > We have 12 logs at 12800 pages each, and a high water mark of 45%, which > tells me that we can only do a transaction of about 275 megs before a long > transaction occurs. > No problem here. > > 2) I need to find how big the transactions will be on each of these tables, and > then, from what I understand, double that amount, as many updates will need to > store a before image and an after image of each row, in the logs. > > Ok, so, > for instance, oncheck -pt shows a table to have a maxrowsize of 1161, and 53,411 > number of rows. I would think that if I multiply those 2 numbers and divide by > 4096 ( 4kpage size), I would get the approximate number of pages needed to store > that table. > However, the number I get is 15,000 some odd pages, and according to the oncheck> -pt that table is only using around 5000 pages. Where is the discrepancy, I > must be doing somethign wrong here. > > Then I figure, if I can get the correct size of the table, multiply that by 2, > and just ensure that I add enough logs so that 45% of them will be enough to > store that amount of data. > > Any advice on this would be appreciated. > > Thanks, > floyd > > > ======================== > -<>- > Database Administrator > Unix Administrator > > email: fwellers@yahoo.com > Work: 703-733-4126 > Pager: 703-705-9241 > Email Pager: 7037059241@my2way.com > Home: 703-430-0805 > Cell: 703-477-6045 > ======================== ======================== -<<Floyd Wellershaus>>- Database Administrator Unix Administrator email: fwellers@yahoo.com Work: 703-733-4126 Pager: 703-705-9241 Email Pager: 7037059241@my2way.com Home: 703-430-0805 Cell: 703-477-6045 ========================

Thanks. So I can do it the way I am doing it, but probably do not need to estimate DOUBLE the page size, correct ? Martin Fuerderer <MARTINFU@de.ibm.com> wrote: Hi, there are datatypes that have variable size, i.e. not each row needs to use the maximum (rowsize) space (think e.g. varchar columns). Also, in log records we do only keep the actually changed data (and some overhead to know where this data belongs to). We do not store the whole page (of a changed row) in the log files. Therefore if you e.g. just update an integer column, you could get by with pretty little log space ... I think the calculation you're trying to do needs to take into account some more and different factors (which in the end doesn't make it simpler). Of course you could say by assuming all data in each row will be changed you'd be on the safe side ... Regards, Martin -- Martin Fuerderer IBM Informix Development Munich, Germany Information Management forum.subscriber@iiug.org wrote on 10.02.2005 15:20:03: > Hi, > We have a newly transaction logged database. We are going to do some updates that will update all the rows in some tables inside a transaction. So I am trying to ensure we have enough logs do do the transaction. > I am approaching this from two points. > 1) the logs. > We have 12 logs at 12800 pages each, and a high water mark of 45%, which tells me that we can only do a transaction of about 275 megs before a long transaction occurs. > No problem here. > > 2) I need to find how big the transactions will be on each of these tables, and then, from what I understand, double that amount, as many updates will need to store a before image and an after image of each row, in the logs. > > Ok, so, > for instance, oncheck -pt shows a table to have a maxrowsize of 1161, and 53,411 number of rows. I would think that if I multiply those 2 numbers and divide by 4096 ( 4kpage size), I would get the approximate number of pages needed to store that table. > However, the number I get is 15,000 some odd pages, and according to the oncheck -pt that table is only using around 5000 pages. Where is thediscrepancy, I must be doing somethign wrong here. > > Then I figure, if I can get the correct size of the table, multiply that by 2, and just ensure that I add enough logs so that 45% of them will be enough to store that amount of data. > > Any advice on this would be appreciated. > > Thanks, > floyd > > > ======================== > -<>- > Database Administrator > Unix Administrator > > email: fwellers@yahoo.com > Work: 703-733-4126 > Pager: 703-705-9241 > Email Pager: 7037059241@my2way.com > Home: 703-430-0805 > Cell: 703-477-6045 > ======================== > > > ======================== -<<Floyd Wellershaus>>- Database Administrator Unix Administrator email: fwellers@yahoo.com Work: 703-733-4126 Pager: 703-705-9241 Email Pager: 7037059241@my2way.com Home: 703-430-0805 Cell: 703-477-6045 ========================

This was very helpful, thank you. Khaled Bentebal <khaled.bentebal@consult-ix.fr> wrote: ----- Original Message ----- From: "Floyd Welle...." To: Sent: Thursday, February 10, 2005 3:20 PM Subject: quick help needed to calculate page sizes for logs [4230] > Hi, > We have a newly transaction logged database. We are going to do some updates that will update all the rows in some tables inside a transaction. So I am trying to ensure we have enough logs do do the transaction. > I am approaching this from two points. > 1) the logs. > We have 12 logs at 12800 pages each, and a high water mark of 45%, which tells me that we can only do a transaction of about 275 megs before a long transaction occurs. > No problem here. > > 2) I need to find how big the transactions will be on each of these tables, and then, from what I understand, double that amount, as many updates will need to store a before image and an after image of each row, in the logs. > > Ok, so, > for instance, oncheck -pt shows a table to have a maxrowsize of 1161, and 53,411 number of rows. I would think that if I multiply those 2 numbers and divide by 4096 ( 4kpage size), I would get the approximate number of pages needed to store that table. > However, the number I get is 15,000 some odd pages, and according to the oncheck -pt that table is only using around 5000 pages. Where is thediscrepancy, I must be doing somethign wrong here. > >>>>>>>>>>>>>>>> If your table occupies 5000 pages of 4K, then you must have VARCHARS in your table. If your row is 1161 >>>>>>>>>>>>>>>> bytes long with no VARCHARS, then you would store 3 rows at most per page (4096 - 28 overhead= 4068 >>>>>>>>>>>>>>>> usable; the 4 bytes are reserved per row in the slot table); so you would need 17804 pages. So it obvious that >>>>>>>>>>>>>>>> rows contain VARCHARS. I didn't take care of indexes that could also be in the 5000 pages that you mention >>>>>>>>>>>>>>>> since you didn't precise whether they are data pages, index pages or both. >>>>>>>>>>>>>>>> This is for the the number of pages that your table occupies. > Then I figure, if I can get the correct size of the table, multiply that by 2, and just ensure that I add enough logs so that 45% of them will be enough to store that amount of data. > >>>>>>>>>>>>>>>> As far as the log is concerned, the calculation is not that easy but doable. Does your database use >>>>>>>>>>>>>>>> UNBUFFERED logging or BUFFERED loging. This has a significant impact on the number of logs used. In order >>>>>>>>>>>>>>>> to minimize the log space used, you could either change to BUFFERED log for the database or use the following >>>>>>>>>>>>>>>> SQL statement in your session : SET BUFFERED LOG; >>>>>>>>>>>>>>>> Remember that each low level call in the log occupies some bytes going from 24 to let us say 150 bytes at most. I >>>>>>>>>>>>>>>> didn't calculate the exact size of the low levels calls stored in the logs. Each instruction occupies a certain number >>>>>>>>>>>>>>>> of bytes + the size of the row before and after the UPDATE + an instruction per index in that table. take a look at >>>>>>>>>>>>>>>> you log using the onlog command which will give you an idea. >>>>>>>>>>>>>>>> You can see that it is not easy to calculate teh size precisely. >>>>>>>>>>>>>>>> Other things that will have an impact on the log size needed is the size of your transaction and the size of your >>>>>>>>>>>>>>>> BUFSIZE (look in the onconfig file). >>>>>>>>>>>>>>>> If you are updating all of the rows, can you drop your indexes before you do this operation? This will save you >>>>>>>>>>>>>>>> time ans log space. I HOPE THAT HELPS YOU AND NOT CONFUSE YOU. Khaled Bentebal ConsultiX Tél: 33 (0) 1 39 72 17 00 Fax: 33 (0) 1 39 72 17 01 Mobile: 33 (0) 6 07 78 41 97 Email: khaled.bentebal@consult-ix.fr Site Web: http://www.consult-ix.fr > Any advice on this would be appreciated. > > Thanks, > floyd > > > ======================== > -<>- > Database Administrator > Unix Administrator > > email: fwellers@yahoo.com > Work: 703-733-4126 > Pager: 703-705-9241 > Email Pager: 7037059241@my2way.com > Home: 703-430-0805 > Cell: 703-477-6045 > ======================== > > > > ======================== -<<Floyd Wellershaus>>- Database Administrator Unix Administrator email: fwellers@yahoo.com Work: 703-733-4126 Pager: 703-705-9241 Email Pager: 7037059241@my2way.com Home: 703-430-0805 Cell: 703-477-6045 ========================

Floyd, Even if you remove the varchar(30) column, the formula you are using nets out 14748 pages (maxrowsize * nrows)/4096, just over 60MB. There is still a discrepancy. You definitely have enough log space for that table and several more. The 9.4 Admin Reference states that the output of oncheck -pT is the be used, ' For an accurate count of the number ofpages currently used, refer to the detailed information on tblspace use (organized by page type) that the -pT option provides.' There must be a formula somewhere. Anyone? Kevin Struckhoff Customer Analytics Mgr. NewRoads West Office 818.253.3819 Fax 818.834.8843 kevin.struckhoff@newroads.com -----Original Message----- From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] On Behalf Of Floyd Welle.... Sent: Thursday, February 10, 2005 7:06 AM To: ids@iiug.org Subject: Re: quick help needed to calculate page sizes for logs [4230 [4231] [4232] Art, It sounds like you are saying that the calculations I am using are correct, and the varchar(30) that is in the table may be throwing it off. If that is so, I would be well advised to use the higher number, calculated by assuming the max rowsize, as this will give me a cushion. Does this sound right ? How bout the rest of my scenario, am I going about the sizing correctly ? Thanks, Floyd "ART KAGEL, ...." <KAGEL@bloomberg.net> wrote: Does the table contain variable length columns (ie VARCHARs & LVARCHARs)? The actual storage required for each row will be the actual current size of the row NOT the maximum size. Informix assumes that you will not use more of a VARCHAR column than the current size rounded to the reserve size or that you will use more than the current size of an LVARCHAR column, so the rows are 'compressed' on the page with as many rows as will fit. Art S. Kagel ----- Original Message ----- From: Floyd Welle.... At: 2/10 9:27 > Hi, > We have a newly transaction logged database. We are going to do some updates > that will update all the rows in some tables inside a transaction. So I am > trying to ensure we have enough logs do do the transaction. > I am approaching this from two points. > 1) the logs. > We have 12 logs at 12800 pages each, and a high water mark of 45%, which > tells me that we can only do a transaction of about 275 megs before a long > transaction occurs. > No problem here. > > 2) I need to find how big the transactions will be on each of these tables, and > then, from what I understand, double that amount, as many updates will need to > store a before image and an after image of each row, in the logs. > > Ok, so, > for instance, oncheck -pt shows a table to have a maxrowsize of 1161, and 53,411 > number of rows. I would think that if I multiply those 2 numbers and divide by > 4096 ( 4kpage size), I would get the approximate number of pages needed to store > that table. > However, the number I get is 15,000 some odd pages, and according to the oncheck> -pt that table is only using around 5000 pages. Where is the discrepancy, I > must be doing somethign wrong here. > > Then I figure, if I can get the correct size of the table, multiply that by 2, > and just ensure that I add enough logs so that 45% of them will be enough to > store that amount of data. > > Any advice on this would be appreciated. > > Thanks, > floyd > > > ======================== > -<>- > Database Administrator > Unix Administrator > > email: fwellers@yahoo.com > Work: 703-733-4126 > Pager: 703-705-9241 > Email Pager: 7037059241@my2way.com > Home: 703-430-0805 > Cell: 703-477-6045 > ======================== ======================== -<<Floyd Wellershaus>>- Database Administrator Unix Administrator email: fwellers@yahoo.com Work: 703-733-4126 Pager: 703-705-9241 Email Pager: 7037059241@my2way.com Home: 703-430-0805 Cell: 703-477-6045 ========================