Re: how many logs do i need

Hi. Details are in the Administrator Guide of the corresponding version. Even there are formulas which lead you to a concrete number, but it's focused on day-to-day performance tunning. I've had same problem - rebuilding huge tables under a transactional database - and I recommend you to recreate table as raw (I mean using "create raw table..." instead of "create table..."), so when you perform a load or insert IDS won't use logical logs, which solves your logical logs dilema and speed up process also. If you have enough disk, even you can create a twin table with another name, perform and insert...select query and save unload time. When you finish, change table using an "alter table <mytable> type(standard)" and then create indexes, as usual. More details are in the SQL Syntax Reference. If your IDS version doesn't allow this kind of stunt (I've used since 9.40) you always can use dbload utility to load big tables by a lot of little transactions. It's not as fast as the "raw table" trick, but you also avoid logical logs concerns. Another thing you should do is recreate table with a proper extent size in order to avoid fragmentation. It helps in load performance also (one table took me 2 hrs without doing this job, vs. 1 hrs) You can figure out how big extent size should be with some admin scripts at IIUG site. Later Omar --- On Mon, 7/20/09, jda <adamski@graceland.edu> wrote: > From: jda <adamski@graceland.edu> > Subject: how many logs do i need > To: informix-list@iiug.org > Date: Monday, July 20, 2009, 11:28 AM > 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 name > ROOTPATH > /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 > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >

Omar thanks for the information, one problem with your suggestions is our 3rd party app requires us to use their 'make' process to rebuild tables. I've never built a RAW table before, but might have to bend the rules on the 3-4 largest table I have and manually rebuild my table and skip their 'make' process. I plan on reviewing my extent setting on all my tables. As I have to dbexport and dbimport between server, is a good time to correct some tables I know are not properly sized. thanks for you the information. John