Re: Storing blobs in database vs filesystem
- From: Nuno Souto <dbvision@xxxxxxxxxxxx>
- Date: Sat, 30 Sep 2006 01:31:55 +1000
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@xxxxxxxxxxxx
--
//www.freelists.org/webpage/oracle-l
Other related posts:
