Re: blobspaces

Art, Your response is well written and I will keep a permanent record of your post. However, I must disagree with some of your statements. Art S. Kagel wrote: > > 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. This is based upon the assumption that the length of your blob data is small and it is feasible to use buffer to cache blobs in tablespace. As far as I can remember, the "l" in blob stands for "large." We use blobspace for images. For one system, the average image size is 50K. The average image size for the other system is 80K. Secondly, the volume for both insert and retrieval is high (something like 5 to 6 digits number). > Also BLOBspaces are > locked during archives since there are no log pages to insure Archive > consistency. I am not sure I understand you correctly. We do database backup during office hour. It usually takes a whole day to backup one server. Occasionally, users may experience locking errors. But most of times they are just because of end-of-tape marks have been reached. Relacing a new tape can resolve the locking problems. > 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. I totally agree with you. > 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. As stated above, I do not agree on this part. > 2) If a large percentage of BLOBs will be updated at least once during > their lifetime, favor tablespace BLOBS. Again, I do not agree on this part. Considering our image size 80K in a single blobpage, both read and write require a single disk access (actually, two disk read/writes, I believe). If it is allocated on tablespace, chances are the image may not occupy contigeous disk space and require more time to load into buffer. Even though they are cached, due to the high volume access of images, and hence the high volume swaps out, the advange of cache may not exist. > 3) If you cannot perform Archive offline, or at least without > inserting/updating BLOBS, favor tablespace BLOBS. As stated above, I do not agree. > 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. As I stated above, it is not feasible to buffer large volume of large size images. > 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 Chao Y. Din