IDS Forum

long checkpoints with blob inserts

Posted By: STEPHEN LEWIS
Date: Thursday, 15 April 2004, at 10:19 a.m.

One of our machines has a table with tablespace blobs. It is usually OK, apart from a particular workflow application which does a lot (50Mb) of blob inserts or deletes within a big transaction. The whole dbserver can be blocked for up to 10 minutes, waiting for this blob transaction to complete its critical section.

TBLspace Report for cl_prd_amarta:dba.print_blob

Physical Address 14:726
Creation date 12/26/2003 22:34:23
TBLspace Flags c02 Row Locking
TBLspace contains TBLspace BLOBs
TBLspace use 4 bit bit-maps
Maximum row size 60
Number of special columns 1
Number of keys 1
Number of extents 4
Current serial value 5270833
First extent size 1000000
Next extent size 50000
Number of pages allocated 2100000
Number of pages used 2100000
Number of data pages 19122
Number of rows 563253
Partition partnum 5242955
Partition lockid 5242955

Extents
Logical Page Physical Page Size
0 18:3 1000000
1000000 21:3 1000000
2000000 14:69435 50000
2050000 14:266494 50000

TBLspace Usage Report for cl_prd_amarta:dba.print_blob

Type Pages Empty Semi-Full Full Very-Full
---------------- ---------- ---------- ---------- ---------- ----------
Free 75691
Bit-Map 521
Index 4570
Data (Home) 19122
TBLspace BLOBs 2000096 0 3512 277150 1719434
----------
Total Pages 2100000

Unused Space Summary

Unused data slots 28549
Unused bytes per data page 36
Total unused bytes in data pages 688392
Unused bytes in TBLspace Blob pages 188514264

Home Data Page Version Summary

Version Count

0 (current) 19122

Index Usage Report for index i_print_blob_01 on cl_prd_amarta:dba.print_blob

Average Average
Level Total No. Keys Free Bytes
----- -------- -------- ----------
1 1 39 1556
2 39 116 626
3 4530 124 403
----- -------- -------- ----------
Total 4570 124 405


Will conversion to blobspace blobs make a performance improvement? Each blob averages about 30k.

Other options are:
(2)de-blob and put these items in files,
(3) fragment the table
(4) radically re-write the software.
Option 4 is the best but the most expensive.

Is there any other way to reduce the blockages?

Its a big HP-UX server with very fast storage and has been well tuned by lots of people over the past 10 years. It runs a bespoke application consisting of about 7 million lines of 4GL and C in 1400 programs.

IDS Forum is maintained by Administrator with WebBBS 5.12.