how many logs do i need

HP BL870c Itanium running HPUX 11.23 (11iv2) IDS 10.00.FC9 I'm getting ready to migrate our IDS databases and 3rd party application from our current HP PA-RISK server (IDS 10.00.HC9) to a new HP Itanium server. And in the process I'm trying to correct some of our past sins. Right now I'm trying to configure the new instance so I can rebuild our largest tables without turning off logging then turn it back on after I’m done. We really don’t have a DBA position; I’m just the one that took a class 8 years ago so I get to cleanup everyone else’s mess. The largest table I have has 12768006 rows 80 char wide and 54 char for indexes and grows between 40% and 80% each year or until we run out of space (for some reason that’s the only time my co-workers listen that we have to delete old records – but I digress) My best guess on the table size is about 1.7GB might as well use 2GB for simplicity. If my foggy memory is correct when figuring out how many logs you need to process a large transaction like a table rebuilt you take the size and multiply by at least 3 if not 4. So that means I need 6GB -8GB of logs. If my LOGSIZE is 8192 this would mean I need 768-1024 logs. To me these numbers seem wrong. Is there a document on IBM site or iiug that explains how to figure out how many logs you need? Or am I totally off my rocker and going down the wrong path? A snip-it from out onconf file # Root Dbspace Configuration ROOTNAME root # Root dbspace nameROOTPATH /opt/informix/dev/root.1 # Path for device containing root dbspa ce ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes) ROOTSIZE 2048000 # Size of root dbspace (Kbytes) # Disk Mirroring Configuration Parameters MIRROR 0 # Mirroring flag (Yes = 1, No = 0) MIRRORPATH # Path for device containing mirrored root MIRROROFFSET 0 # Offset into mirrored device (Kbytes) # Physical Log Configuration PHYSDBS physlog_dbs # Location (dbspace) of physical log PHYSFILE 135138 # Physical log file size (Kbytes) # Logical Log Configuration LOGFILES 50 # Number of logical log files LOGSIZE 8192 # Logical log size (Kbytes)LOG_BACKUP_MODE MANUAL # Logical log backup mode (MANUAL, CONT) Snip-it of onstat -l IBM Informix Dynamic Server Version 10.00.FC9 -- On-Line -- Up 11 days 18:06:20 -- 1056768 Kbytes Physical Logging Buffer bufused bufsize numpages numwrits pages/io P-2 0 16 4424465 321753 13.75 phybegin physize phypos phyused %used 2:53 67569 27500 0 0.00 Logical Logging Buffer bufused bufsize numrecs numpages numwrits recs/pages pages/io L-3 0 16 7883176 508103 37673 15.5 13.5 Subsystem numrecs Log Space used OLDRSAM 7883176 1008389196 address number flags uniqid begin size used %used c000000027ac2fc0 1 U-B---- 251 1:729870 4096 4096 100.00 c000000029ad4110 2 U-B---- 252 1:733966 4096 4096 100.00 c000000029ad4180 3 U-B---- 253 1:738062 4096 4096 100.00 c000000029ad41f0 4 U-B---- 254 1:742158 4096 4096 100.00 c000000029ad4260 5 U-B---- 255 1:746254 4096 4096 100.00 John

jda wrote: > HP BL870c Itanium running HPUX 11.23 (11iv2) > IDS 10.00.FC9 > > I'm getting ready to migrate our IDS databases and 3rd party > application from our current HP PA-RISK server (IDS 10.00.HC9) to a > new HP Itanium server. And in the process I'm trying to correct some > of our past sins. > > Right now I'm trying to configure the new instance so I can rebuild > our largest tables without turning off logging then turn it back on > after I’m done. We really don’t have a DBA position; I’m just the one > that took a class 8 years ago so I get to cleanup everyone else’s > mess. > > The largest table I have has 12768006 rows 80 char wide and 54 char > for indexes and grows between 40% and 80% each year or until we run > out of space (for some reason that’s the only time my co-workers > listen that we have to delete old records – but I digress) > > My best guess on the table size is about 1.7GB might as well use 2GB > for simplicity. If my foggy memory is correct when figuring out how > many logs you need to process a large transaction like a table rebuilt > you take the size and multiply by at least 3 if not 4. So that means > I need 6GB -8GB of logs. If my LOGSIZE is 8192 this would mean I need > 768-1024 logs. To me these numbers seem wrong. > > Is there a document on IBM site or iiug that explains how to figure > out how many logs you need? Or am I totally off my rocker and going > down the wrong path? Why not go for 100 logs of 20MB each? I don't know if I'm missing something, if you rebuild a 1.7GB table, I can't really see it using much more than 1.7GB in logs ... ? However, it will be a LOT slower, so you may not want to stop switching it off. -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.

> > Why not go for 100 logs of 20MB each? I don't know if I'm missing > something, if you rebuild a 1.7GB table, I can't really see it using > much more than 1.7GB in logs ... ? > > However, it will be a LOT slower, so you may not want to stop switching > it off. > One of my concerns is will adding however many more logs at what ever size - is will it slow down the response time. also I have a developer that loves to add/delete a million rows from this table during normal buisness hours cause he forgets to put a 'where' clause in his test sql. haveing the small low number of logs I currently have stops him in his tracts with a long transaction error. maybe not the best way to keep him in line but it does work. I think this is a can of worms I'm not sure I want to open.

jda wrote: > also I have a developer > that loves to add/delete a million rows from this table during normal > buisness hours cause he forgets to put a 'where' clause in his test > sql. That's a major problem in itself. In fact it's a couple of problems. The first (I read "this table" as referring to the table in the production database) is that the developer should have at least one separate development/test database. Given the risk of his activity running into long transactions it should be in its own server instance so that it can't interfere with production. The other problem is that the developer forgets little details like WHERE clauses. -- Ian Hotmail is for spammers. Real mail address is igoddard at nildram co uk

Ian, we do have a separate train database on the same instance (different dbspace) and also a separate dr/test server. however the developers are managed by another manger and they have never really listen to me as I have no power to do anything when they screw up. I can yell, jump up & down, call them names and all it gets me is in trouble for being insensative. Since we are an academic institution I think the mind set it you can do whatever they want. I think it’s time for me to get back into corporate world and let these people paint themselves into a corner. John