I/O Wait on LogWriter

[naveen_nalla]

Hi ,

We are inserting 500K rows into a table and process is taking so long. I think some of the parameters were not set correctly.

I can see a task status LogIOWait (LogQueue:UserTaskEOTReady) on this process and very few savepoints were issued even the log is filling up.

Below numbers are increasing while the processig is running as I believe there were few savepoints issued to write to data area.

Log Pages written 163,000

Waiting for Log writer 145,300

LogIOClusterSize = 8

LogQueueSize=3000

MaxSavepointTimeInterval 600

dbmcli on xdb>info log

OK

END

Name | Value

Log Mirrored = NO

Log Writing = ON

Log Automatic Overwrite = OFF

Max. Size (KB) = 8292096

Backup Segment Size (KB) = 2796200

Used Size (KB) = 1619952

Used Size (%) = 20

Not Saved (KB) = 1619952

Not Saved (%) = 20

Log Since Last Data Backup (KB) = 0

Savepoints = 73

Checkpoints = 0

Physical Reads = 0

Physical Writes = 162252

Queue Size (KB) = 24000

Queue Overflows = 851

Group Commits = 0

Waits for Logwriter = 145903

Max. Waits = 1

Average Waits = 0

OMS Log Used Pages = 0

OMS Min. Free Pages = 0

Please through some guidance on improving the process more efficient.

DB Version : 7.7.06.10 on Linux 64 bit.

Thanks

Naveen

[lbreddemann]

> I can see a task status LogIOWait (LogQueue:UserTaskEOTReady) on this process and very few savepoints were issued even the log is filling up.

When you see a lot of LogIOWait you should check the write I/O performance for the filesystem the log area is stored into.

A common mistake is to save the log volumes on RAID 5 or similar devices.

Make sure that this is not the case.

> Below numbers are increasing while the processig is running as I believe there were few savepoints issued to write to data area.

The savepoints have nothing to do with the waiting for LogIO.

They are written when necessary or every ten minutes.

> Log Pages written 163,000

> Waiting for Log writer 145,300

> LogQueueSize=3000

3000 8K pages of log information can be put into the queue - that's really a lot, so this is not your problem.

Anyhow, no DB parameter will make your I/O faster - your storage team will/should.

Show us your log area setup and check the I/O options used to mount the filesystem.

Also make double sure that you've got he disks for the log volumes exclusively.

regards

Lars

[naveen_nalla]

Hi Lars,

This a test database that we are planning to move away from Oracle to Maxdb and everything is on a single hard drive, so there is nothing I can do here other than working on any db parameter tweaking.

I will try to move the log area to a seperate disk and see how it works.

Thanks

Naveen

[lbreddemann]

> This a test database that we are planning to move away from Oracle to Maxdb and everything is on a single hard drive, so there is nothing I can do here other than working on any db parameter tweaking.

>

> I will try to move the log area to a seperate disk and see how it works.

Well, as I said - the parameters won't ever make your I/O faster (the same is true for Oracle).

If you want to do a realistic test, then do it on realistic hardware.

Playing around on a laptop and performance tuning for huge loads usually don't go together...

regards,

Lars