mystik has asked for the wisdom of the Perl Monks concerning the following question:

I'm considering a project where I would store large binary files (1-15meg) in a database. (MySQL) There will be many inserts (not usually concurently), few select's and rarely deletes.

My question is this: Can this be done efficently(read: conserving memory) in perl? (Under mod_perl & DBI?) The only solution that comes readily to mind is to slurp the file into one honkin' scalar, and use binding values to stuff it into the database. This poetentially has some nasty memory usage problems.

I'd like to use the database to store files because it allows me to keep all data related to the application in one place. However, If this approach has more drabacks than benefits, I'll just revert to using a sql table to manage on-disk files. Thanks in advance for any ideas or suggestions.

Replies are listed 'Best First'.
Re: Storing files in a database
by TomDLux (Vicar) on Aug 11, 2003 at 01:06 UTC

    If you store data but hardly ever read it back, it hardly seems worth storing.

    Databases are useful when you need to locate moderate sized data in amongst tons of other stuff. Fetching mega-chunks of data will lead to problems about how much you can fetch in one read.

    Why not store the path to the data in the DB, but leave the file in the filesystem. SQL will provide the path to the file that should be read.

    --
    TTTATCGGTCGTTATATAGATGTTTGCA

[reply]
Re: Storing files in a database
by blue_cowdawg (Monsignor) on Aug 11, 2003 at 01:39 UTC

        However, If this approach has more drabacks than benefits, I'll just revert to using a sql table to manage on-disk files.

    This is exactly what I would suggest. Take it from my personal experience with this one. I once tried setting up a database table with all my graphics for my website stored as BLOBs. Loading the pages became very inefficient while waiting for the bits to be dragged into memory and then spit out.

    Using the database as an index to on disk files is a much better way to go IMHO.

    Peter @ Berghold . Net

    Sieze the cow! Bite the day!

    Nobody expects the Perl inquisition!

    Test the code? We don't need to test no stinkin' code!
    All code posted here is as is where is unless otherwise stated.

    Brewer of Belgian style Ales

[reply]
Re: Storing files in a database
by gmax (Abbot) on Aug 11, 2003 at 09:34 UTC

    Personally, I had good results storing binary files in a database. See some technical advice at
    Handling huge BLOB fields with DBI and MySQL.

    Notice that you don't have to store the file as one whole field, but you can split it into easy-to-manage chunks. That node explains how to store and retrieve files that way.

    About using the file system instead of a database, in the same thread there is an exchange of opinions, where both sides are evaluated. In addition to what is stated there, I would say that storing in a database is advisable if you plan to replicate the database to some other machine. Since MySQL has a built-in replication engine, you should consider this fact as well.

     _  _ _  _  
(_|| | |(_|><
 _|
[reply]
Re: Storing files in a database
by mystik (Sexton) on Aug 11, 2003 at 03:32 UTC

    I'll probably end up going the route of putting the files on the disk. But it does have a slight snag (that our app currently does not deal with): if we ever need to reorganize the disk structure (which would be rare), we would need to ensure that the paths in the database are updated appropriately.

    ... Or (thinkig out loud), in our API layer, just provide a function that generates the filename the data would be stored under.

    Storing the file in a RDBMS that supports cascadeing deletes is also handy -- when we delete the primary entry, the DBMS will also clobber the files automatically for us. W/ a disk-on-file approach, we have to run an off-line clean-up script that manually clobbers files for any deleted rows.

    Even still, is there a way to bind a paramater to a filehandle, and have the driver figure out the details to get that file into the DB ?

[reply]
      One advantage of storing the files in the database is that if you have a large number of servers accessing the db and files then it makes syncing updates to the files much easier. Also if those servers are on a DMZ and the db server is on another DMZ you dont need to open ports for sync software or filesharing connections (AFS/NFS/CIFS etc) or need large amounts of space on the servers for duplicated files.

      -Waswas
[reply]

Back to Seekers of Perl Wisdom