Re: inserting BLOB data

On Wed, 2007-12-12 at 08:45 -0500, Patrick Galbraith wrote:

> Kiran,
> 
> May I recommend that you not store blobs such as files in the database? 
> The database will handle it, but it you would get much better 
> performance by having the files in a directory structure and a fast 
> lookup table to store the file's location on disk. Also, it'll make 
> backups much faster as well as use less memory - when you retrieve/store 
> a blob, it requires that much memory in the MySQL server.

I realise that this is the conventional wisdom. However let me share an
experience that goes against the grain. We use the 'dbamil' mail / imap
server, which stores all mail in a database ( MySQL for us ).

Performance is much, much, much faster than it ever used to be with a
file-based imap server. In fact, I was starting to worry when we had
about 2 GB of email, because the server had *constant* disk activity
from people checking their email, and retrieving a message would
sometimes take up to a minute, and other times simply fail. Since
migrating to dbmail, folder listing is instantaneous, and email
retrieval is *always* under 5 seconds, and usually within 2 seconds.
Also, disk activity is way down. We now have 20 GB of email. People
insist on emailing us huge PDFs, and then we insist of keeping these
emails around ... forever.

On the issue of backups, it might be slower to back up a DB with huge
blobs in it, but it sure is easier. And there's no 'directory sprawl',
permissions worries ( ie people being able to access the blobs because
they're just sitting there on the network ), etc, etc.

Having said all that, for our *internal* PDF archiving, I have done as
recommended, and stored the files on a server, linked with a lookup
table in MySQL. To be perfectly honest, it freaks me out. Users must
have permission to create, overwrite and delete PDFs ( for example, in
case we render a PDF, see a mistake, then make a change and re-render
it ). It happens all the time. Since users need all these permissions,
there's nothing to stop them from 'accidentally' deleting PDFs en-masse.
Or renaming them. Or dragging them somewhere else. If they were in a
database, we'd have strict control over when and how they're deleted /
updated.

--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: [email protected]
website: http://www.nusconsulting.com.au


-- 
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe:    http://lists.mysql.com/[email protected]