Re: inserting BLOB data

Daniel,

Very interesting. Do you also use replication? What storage engine are 
your tables using?

regards,

Patrick

Daniel Kasak wrote:

>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
>
>
>  
>


-- 
Patrick Galbraith, Senior Programmer 
Grazr - Easy feed grazing and sharing
http://www.grazr.com 

Satyam Eva Jayate - Truth Alone Triumphs
Mundaka Upanishad




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