long checkpoints with blob inserts
Posted in 2004
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.