Re: blobspaces

Sujit Pal wrote: > > Maria > > Yes there are. When a blob is stored in a regular tablespace, any = > changes are logged in the physical and logical log and they are also = > brought in through the buffer cache. When a blob is stored in a = > From: Maria L. Wilson[SMTP:m.l.wilson@larc.nasa.gov] Subject: blobspaces > > What are the advantages of setting up blobs in blobspaces versus a > regular tablespace? Is there some performance advantage? Conversely, if you will have many users actively querying a relative few BLOB pages then the buffer cache of tablespace BLOBs can gain you as much during fetches as you lose during updates. Also BLOBspaces are locked during archives since there are no log pages to insure Archive consistency. Also when BLOBs are updated ALL of the BLOB pages are actually copied to another location with the changes then the original BLOB pages are marked to be freed. The pages are not actually available for reuse until the logfile containing the transaction has been backed up. For large BLOBs which are actively updates this can be VERY expensive compared to tablespace BLOBs. Since BLOBspace BLOBs always allocate complete BLOB pages to a BLOB if your BLOBs average >1 & <1.5 BLOB pages you are wasting 25% of the BLOBspace etc. You must calculate your expected wastage to determine if tablespace BLOBs will gain you in this way. So, in summary: 1) If the BLOBs are to be repeatedly FETCHED over a relatively short timeframe (like the whole department looking at this morning's status report) favor tablespace BLOBS. 2) If a large percentage of BLOBs will be updated at least once during their lifetime, favor tablespace BLOBS. 3) If you cannot perform Archive offline, or at least without inserting/updating BLOBS, favor tablespace BLOBS. 4) If a large percentage of BLOBspace is being wasted (and your disk budget is not unlimited, favor tablespace BLOBS. 5) Otherwise BLOBspace BLOBS can be faster so favor BLOBspace BLOBS. Weight any INSERT performance gain against these other factors. Is this an archival database only, fine. If an actively queried database then look to the advantage of using the BUFFER CACHE and just add more buffers. We run our news machines (stories in tablespace BLOBS) with 200000 buffers on a 3.5GB RAM machine. Performance is MUCH better than when we used 50000 buffers and BLOBspace BLOBS since the news stories arrive 1K at a time from the news services and we insert and update the partial story several times. We can now continue to update during Archives which is important since the news never stops. Hope that this helps. Art S. Kagel