Re: Storing blobs in database vs filesystem

Niall Litchfield wrote,on my timestamp of 29/09/2006 8:17 PM:

> <snip>
>
> What could be challenge in storing it on file system? Any major
> disadvantage that I should not consider it at all?
>
> Thanks
>
>
>
> Are the BLOB's transactional in anyway, especially are they subject to
> change? If so then consistent recovery requirements might be overly
> complicated.

In our case, they were indeed transactional: the BLOB stored a complex string containing search terms and derived URLs, potentially in a number of different character sets. These were transformed and massaged by the application according to string algorithms for search engine optimisation of advertising campaigns.

Average length around 6000 bytes. Which posed a serious space problem: the db block size was 8K and a blob segment uses a minimum of one block to store each instance of offline LOB.

In retrospect, the 8K block size was a bad choice for this. But we only found out much later what the average blob size was: system test failed to show any indication of this.

In the vicinity of 40GB X 3 of BLOB segments, total db size in the 1.5TB class, non-partitioned, 9ir2, RHEL3, across two servers - no RAC. Not a pretty sight...

> My personal preference for files is to store them on filesystems with a
> pointer to them in the DB (either a BFILE or else just a straight uri
> for them)

Agreed. The keyword here being: "files".

Other large app-specific strings might indeed need in-db storage, with some inline while others end up in the lob segment. Transactional issues being the major concern, IME.

What to me is critical at my current level of knowledge of use of these features is choosing the appropriate block size to store the LOB: too small and there may be excessive random IO rate, too large and a lot of disk space may be wasted. Definitely ground for more research.

-- 
Cheers
Nuno Souto
in sunny Sydney, Australia
dbvision_at_iinet.net.au
--
http://www.freelists.org/webpage/oracle-l