Using a new *.log file in a IQ multiplex environment

[robert_kratschmann]

Hi,

in SQL Anywhere it is no problem to start the server with a new log file - you can use the '-xo' switch it the is no replication in use.

The *.log file is very large and the free space in the file system of the coordinator machine becomes problematic. What must be done on the coordinator (IQ 16.0 SP08 multiplex environment) so that a new (smaller) *.log file with the same name as the old one will be used? Is the same procedure as in SQL Anywhere possible?

Many thanks

Robert

[saroj_bagai · Accepted solution]

you can follow steps from the following link for managing size of the transaction log:

Transaction and Message Logs - SAP IQ Performance and Tuning - SAP Library

SyBooks Online

Thanks

Saroj

[robert_kratschmann · Accepted solution]

Hi Bagai,

I did a test with -m within the demo database modified into a multiplex and it work fine for the coordinator. Now I found the following note:   1923088 - How to manage the size of the IQ transaction log

The note said "Never use this switch in a multiplex system"! What is the cause for not using the -m switch in a multiplex environment?

Many thanks

Robert

[saroj_bagai · Accepted solution]

Hi Robert,

If your MPX is synched,  and you  are only using -m to switch to truncate log at the startup meaning,  stop Coordinator,  start it with -m switch,  which will truncate the log, stop Coordinator,  and restart it normally without -m switch,  do not leave -m switch permanently in  params.cfg file,  dbbackup -xo is used to truncate transaction log of the live server.

Thanks

Saroj

[markmumy · Accepted solution]

A couple of things come to mind...  First, using -m will force a truncate on every checkpoint.  This will add a significant overhead (CPU and IO) to IQ that most will not want.  Second, the log will get pruned with such high frequency that you have lost all control of the log pruning process and may run into issues with trying to do normal DBA HA/DR type things.  It may get in the way of a proper HA/DR scenario.  Third, using the switch requires an outage for all nodes.  Well, not all nodes, but just every node that you want to prune the log on, which should be all nodes.  Fourth, you've lost all control of just how much the transaction log is growing.  Fifth, you lose all ability to audit IQ since that information is contained in the tran log.

I've been using the dbbackup with -xo options for quite some time.  It allows you to prune the log in the same manner as the -m option (so long as a checkpoint is not currently running).  This method gets around all the above issues: it runs when you want it and does not require any system outage to run.  Most customers with a highly active log need only prune once a week, possibly every day.  But those are quite rare.

The issue that the -m covers up, too, is out of control code and users.  If your tran log is growing that fast, so fast as to need to be pruned every checkpoint or multiple times per day, what is your application and users doing?  Having a runaway tran log points to a bigger issue with your application since nearly all IQ operations are not logged in the tran log, but rather in our TLV in IQ_SYSTEM_MAIN.

If this is not a frequently needed operation (your tran log just grows to be large over a period of months) then there is certainly no need for the -m option as you will be going from one extreme to another, neither of which are good.

Mark

[robert_kratschmann · Accepted solution]

My understanding is now that for a one-time truncation of the log all three methods are possible in a multiplex environment:

1) -m parameter used one-time during start-up but never used permanently while IQ is running

2) dblog -t

3) dbbackup -xo

Point 3) can be done while the server is running for 1) and 2) you have to stop IQ

Robert

[saroj_bagai · Accepted solution]

yes, it is correct