Re: Creating Logical logs

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 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 # Maximum number of logical log files > CLEANERS 1 # Number of buffer cleaner processes See the discussion this week on this subject. You absolutely need more than 1 cleaner, even with only 1 CPU VP. I recommend a value between 6 and 8 to ensure quick cleaning between checkpoints unless you have a large number of disk chunks then increase it somewhat. > SHMBASE 0x0A000000L # Shared memory base address > SHMVIRTSIZE 8000 # initial v