Unit of Direct IO LOB and Unit of Add LOB Data Log Record should match? Implication on Recovery of LOB Object?

Harishkumar .Pathangay

Unit of Direct IO LOB and Unit of Add LOB Data Log Record should match? Implication on Recovery of LOB Object?

Hi All,
Unit of Direct IO LOB and Unit of Add LOB Data Log Record should match? Implication on Recovery of LOB Object?

I have a recoverable database with logged LOB column of 4 MB in a table. Import one record inserting data into that LOB column.
IBM KC clearly states that Insert of one LOB object is split into multiple Add LOB Data Log Record with some portion of actual LOB data in case it is logged LOB column.
This is obviously used for recovery purposes or replication purposes.
KC also mentions elsewhere that LOB data is written into Tablespace containers in hard disk without using bufferpools.
So the question is Amount of LOB data written into Log Records should match the DATA IO size written into containers?
My thinking is it should match,otherwise more data might be written into the disk and less data being written into containers might not go well in terms of roll forward at a later point in time.
Even crash recovery involving LOB data might rollback large amount of data from disk because of this mismatch in LOB Data Size being written.

Obviously i am doing a lot of test cases and trying to come up with a number with snap shot monitoring. It is going to take some time.
But conceptually i need to ask how db2 is handling it in the first place.

Any Inputs help will bring significant amount of clarity in managing LOB objects on different IO devices [LOG IO and HARD Disk IO].

thanks,
Harish Pathangay

Harishkumar .Pathangay

RE: Unit of Direct IO LOB and Unit of Add LOB Data Log Record should match? Implication on Recovery of LOB Object?
(in response to Harishkumar .Pathangay)

Hi All,

To Add to my previous question, I am also starting to think about what will happen it both IO's are different.

The Worst case would be to allow Direct IOs to run faster than LOG IO and just wait for the log record writing to complete. if it crashes with more data in Containers but with less data in log files, then any case it is uncommitted and hast to rollback that record for consistency. Probably db2 just leaves this lob data as orphaned records in table space pages. Using LOB under such situation will cause space consumption and nothing much.

But my question is, is it the way it is currently doing? Leaving LOB columns in tablespace pages as orphaned pages. Drop the table to reclaim the pages or extents.

If Direct IO is slower than LOG IO, then during crash recovery any case log records are consulted for replay and bring database to consistent state. So nothing much impact here.

The question is my understanding correct and is db2 behaving that way?

thanks,

harish pathangay 

Harishkumar .Pathangay

RE: Unit of Direct IO LOB and Unit of Add LOB Data Log Record should match? Implication on Recovery of LOB Object?
(in response to Harishkumar .Pathangay)

Hi,

Any Inputs? Help to explain my insane thoughts. I have started doing test cases will share results in a day or two.

thanks

harish pathangay

Harishkumar .Pathangay

RE: Unit of Direct IO LOB and Unit of Add LOB Data Log Record should match? Implication on Recovery of LOB Object?
(in response to Harishkumar .Pathangay)

Hi,

I have did the test cases. Here is the output.

1GB on Matay Fast Disk or Regular Disk
Direct reads                               = 2211594
Direct writes                              = 4423172
Direct read requests                       = 8642
Direct write requests                      = 16321
Direct reads elapsed time (ms)             = 3107
Direct write elapsed time (ms)             = 259592

1GB on Matay Slow Disk 1MBPS
Direct reads                               = 2212376
Direct writes                              = 4423172
Direct read requests                       = 8710
Direct write requests                      = 16321
Direct reads elapsed time (ms)             = 3086
Direct write elapsed time (ms)             = 156599

1GB on Matay Slow Disk 5MBPS
Direct reads                               = 2212520
Direct writes                              = 4423172
Direct read requests                       = 8718
Direct write requests                      = 16321
Direct reads elapsed time (ms)             = 3124
Direct write elapsed time (ms)             = 281783

Why Direct write elapsed time is increasing in a faster disk but decreasing in a slower disk.

DIO is efficient in slower disks?

thanks,

Harish Pathangay

Harishkumar .Pathangay

RE: Unit of Direct IO LOB and Unit of Add LOB Data Log Record should match? Implication on Recovery of LOB Object?
(in response to Harishkumar .Pathangay)

Hi,

DB2 is using Log Ahead Writing Strategy for making sure of minimising loss of data in buffer pools. But this cannot be applied to Logged LOB columns which is Direct Writes with minimal Buffer pool pages for records. That is the source of my question.

Any Helpful Inputs? Will DB2 choose a HDD speed based in LOG IO throughput, even if container HDD has more band width.

thanks,

Harish Pathangay

Harishkumar .Pathangay

RE: Unit of Direct IO LOB and Unit of Add LOB Data Log Record should match? Implication on Recovery of LOB Object?
(in response to Harishkumar .Pathangay)

Hi,

I did multiple scenarios. The unit of Direct Writes and Direct Write Requests do not change at all based on page size or extent size variations.

Example, page size 4k, extent size 32 import  1gb logged blob data column - DW and DWR.

page size 4k, extent size 16 import  1gb logged blob data column - DW and DWR.

page size 8k, extent size 32 import  1gb logged blob data column - DW and DWR

page size 8k, extent size 16 import  1gb logged blob data column - DW and DWR

page size 16k, extent size 32 import  1gb logged blob data column - DW and DWR

Why I am getting same values for variation is page size and extent size. Even with 2 Containers I get same values.

LOB is bludgeoning my brains !!!!

thanks,

harish pathangay

Harishkumar .Pathangay

RE: Unit of Direct IO LOB and Unit of Add LOB Data Log Record should match? Implication on Recovery of LOB Object?
(in response to Harishkumar .Pathangay)

hi,

when I use import utility a table is created in system temporary space for the loaded table with lob column. this is non-logged activity and runs super fast.

After that db2 copies this imported data from system temporary to data table space. this is where log io records are pushed out and data is actually written into containers. 

reached thus far. Any help?

thanks,

harish p