Re: Creating Logical logs

In article <3498326C.660B@bloomberg.com>, "Art S. Kagel" <kagel@bloomberg.com> writes >PRAVEEN MOHANAN wrote: >> >> Hi users, >> >> Thanks for the solution. I created a database with log, and >> could load the schema of another db. I know only few things of the DBA >> stuff. But can anyone please tell me > >> 1.how to know how many logical logs are there & what are their >> sizes. > >a) Look in your ONCONFIG file at the LOGFILES and LOGSIZE parameters or >b) Run onstat -l or >c) Run onmonitor STATUS>LOGS > >> 2. How to increase the size of a log. > >You cannot increase the size of an existing log you need to add new, >larger log files, archive level 0 to enable the new logs, onmode -l >repeatedly until the current log is one of the new ones, onmode -c to >make sure the current check point is in the new log, archive logs, and >finally drop the old logs. To be safe you should also archive level 0 >again since a restore of the last archive will restore the old logs you >have just dropped. > >> >> 3. I received an error of long transaction when trying to load about >> 150,000 rows. > >Sure, you have 6 logs 500K each or 3MB of logs with LTXHWM=50 and >LTXEHWM=60 so that you only have 1.5MB of useful logs. To resize your >log pool determine the largest legitimate transaction size, add 10-20% >for log overhead, add 50-100% (or more) to allow for other large >transactions to execute concurrently. This is your total available log >requirements. Now divide by your LTXHWM (0.50 in your case unless you >change it) to get the amount of logspace you need to prevent an LTXHWM >forced rollback. If you are not backing up logs continously (ontape -c) >or with log_full.sh or to /dev/null, you may want to add more to allow >for dirty logs or you will have to make certain that logs are backed-up >before starting a large transaction. Now, to determine an optimal size >for each log file and determine how many logs. Since logs not backed up >are at risk, if you are using continuous log backup make each logfile >small balanced by not wanting to have 1000 log files of some 500K each >to get 500MB of logs. I use from 40 to 70 logfiles of either 50000K or Thats similar to what I do 1000 * 250Kb logs (the smallest possible) to give 250Mb of logs. ONline 7.x allows up to 32767 which could give me almost 8Gb of 250-Kb logs. If Online support is why not use it? > >100000K depending on the individual server (I manage 17) and its >transaction mix. Don't forget to raise LOGSMAX and bounce the engine >before you start this adventure or you will not be able to add any new >logs (I set LOGSMAX=100 since it costs little and gives me lots of >headroom for the future). > >> The parameters in my ONCONFIG file are : >> ROOTNAME rootdbs # Root dbspace name >> ROOTPATH /u02/IFMXDATA/ROOTDBS.000 >> ROOTOFFSET 0 # Offset of root dbspace into device >> (Kbytes) >> ROOTSIZE 20000 # Size of root dbspace (Kbytes)> >> # Disk Mirroring Configuration Parameters > >> MIRROR 0 # Mirroring flag (Yes = 1, No = 0) >> MIRRORPATH >> MIRROROFFSET 0 # Offset into mirrored device (Kbytes)> >> # Physical Log Configuration > >> PHYSDBS rootdbs # Location (dbspace) of physical log >> PHYSFILE 1000 # Physical log file size (Kbytes)> >AAAHHH! Get your physical log, and logical logs, out of the rootdbs! >This is a MAJOR performance bottleneck! The rootdbs is read and updated >constantly by the engine and the logs are being written to constantly >also. Make a dbspace just for your logs and if you have a particularly >high transaction environment make a separate dbspace (small) on a >separate disk just for the physical log also either mirror the disk that >you use for logical logs or make the logical logs in two dbspaces >alternating one log into each as you create them so reading logs for >archive does not interfere with writing the new log. > >Also a small physical log will force checkpoints when the log is 75% >full so if you are seeing frequent checkpoints during normal operations, >you cannot help special circumstances much anyway, increase the physical >log (we use 32000K). > >> # Logical log Configuration >> LOGFILES 6 # Number of logical log files >> LOGSIZE 500 # Logical log size (Kbytes)> >> # Diagnostics > >> MSGPATH /u02/informix/etc/online.log # System message log file >> CONSOLE /dev/console # System console message path >> ALARMPROGRAM /u02/informix/etc/log_full.sh # Alarm program path> >> # System Archive Tape Device > >> TAPEDEV /dev/rmt0 >> TAPEBLK 16 # Tape block size (Kbytes) >> TAPESIZE 10240 # Maximum amount of data to put on tape> >> # Log Archive Tape Device > >> LTAPEDEV /dev/null >> LTAPEBLK 16 # Log tape block size (Kbytes) >> LTAPESIZE 20 # Max amount of data to put on log tape> >> # Optical > >> STAGEBLOB # INFORMIX-OnLine/Optical staging area > >> # System Configuration > >> SERVERNUM 1 # Unique id corresponding to a OnLine >> DBSERVERNAME wonder # Name of default database server >> DBSERVERALIASES # List of alternate dbservernames >> NETTYPE tlitcp,1,, >> DEADLOCK_TIMEOUT 60 # Max time to wait of lock in >> RESIDENT 0 # Forced residency flag (Yes = 1, No = > >> MULTIPROCESSOR 0 # 0 for single-processor, 1 for >> NUMCPUVPS 1 # Number of user (cpu) vps >> SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps>> to one > >> NOAGE 0 # Process aging >> AFF_SPROC 0 # Affinity start processor >> AFF_NPROCS 0 # Affinity number of processors> >> # Shared Memory Parameters > >> LOCKS 2000 # Maximum number of locks >> BUFFERS 200 # Maximum number of shared buffers> >Keep an eye on your read and write cache percentages in onstat -p. Read >% should be > 90% (I try for > 95% myself) and write % should be > 75% >(though I strive for > 85%). If these are both low you may need more >buffers unless your transaction rate is very low. Low write cache % can >also indicate that checkpoints are too frequent cleaning pages before >they can by written to again, in this case increase physical log size. >(Really low LRU_MAX/MIN_DIRTY values can also lower write cache % but if >you need fast checkpoints you'll have to live with that.) > >> NUMAIOVPS # Number of IO vps> >Don't leave NUMAIOVPS blank. Strange things have been reported (like >100's of AIO VPs) when it is blank. See the admin guide for recommended >values. > >> PHYSBUFF 32 # Physical log buffer size (Kbytes) >> LOGBUFF 32 # Logical log buffer size (Kbytes)>> LOGSMAX 6 # Maximu