how to reduce "Wait for Log Writer"

[Former Member]

Hi,

in a production system using MaxDB 7.6.03.07 i checked follow acitivities about Log:

Log Pages Written: 32.039

Wait for Log Writer: 31.530

the docs explains that "Wait for Log Writer", Indicates how often it was necessary to wait for a log entry to be written.

then what steps i must follow to reduce this?

thanks for any help

Clóvis

[Former Member · Accepted solution]

Hi,

when the log Io queue is full all user tasks who want to insert entries into the log io queue have to wait until the log entries of the queue have been written to the log volume - they are wiating for LOG Writer

First you should check the size of the LOG_IO_QUEUE Parameter.

Think about increaseing the parameter value.

Second will be to check the time for write I/O to the log -> use DB-Analyzer and activate time measurement via x_cons <DBNAME> time enable command.

Then you will get time for write I/O on the log in the DB-Analyzer log files (expert)

You will find more information about MaxDb Logging and Performance Analysis on maxdb.sap.com -> [training material|http://maxdb.sap.com/training] chapter logging and performance analysis.

Regards, Christiane

[Former Member]

Hi,

a wait for a logwriter is neccessary when a transaction is either commited or rollbacked. When the number of written logpages is nearly equal to the number of waits (i.e. the number of commits) then I assume that the transactions in your system are that short, that they fit into one logpage. That means all changes of one transaction including the commit are written into one logpage of 8 kbyte.

Kind regards, Martin

[Former Member]

Hi Clovis,

waiting for the log writer is one of the heaviest performance impacts.

There must be 0 of them in an productive system.

If the DB is waiting for the logwriter, the whole System is waiting.

Be sure that the Log Space of your Database is not an the same Raid like your DB Devspaces, nor

the OS Swap.

And do like Christiane said (check LOG_IO_QUEUE)!

edit

Ubs maybe i'm wrong, i was talking about "Log I/O Queue Overflow". This must be 0.

Regards

Manuel

Edited by: Manuel Herr on Apr 21, 2008 10:02 AM

[Former Member]

Hi Manuel,

Here the count of Log I/O Queue Overflow is 0, the parameter LOG_IO_QUEUE will change anything about "Wait for Log Writer"? Wait for Log Writer maybe a problem with how i defined the log storage?

regards

Clóvis

[lbreddemann]

Hi Clovis,

it's quite likely that the I/O write performance to the log volumes is the problem here.

More data to get behind this will deliver the analysis steps that Christiane already described.

With the time measurement enabled it's not too hard to see how long single writes to the log take.

With that information you can check if there is any opportunity to improve it.

E.g. setting up the log volume as a raw device or removing other I/O from the LUN etc.

But first, you will need to have the I/O statistics.

KR Lars