[UPHPU] Storing 20mb files in mysql... is it a bad idea?

[Sean Thayne]

Good stuff,

Thanks!


- Sean Thayne

On Jul 30, 2009, at 10:04 AM, Lonnie Olson <lists at kittypee.com> wrote:

> On Thu, Jul 30, 2009 at 9:26 AM, Sean Thayne<sean at skyseek.com> wrote:
>> What kind of fits would you expect?
>>
>> The only thing with replication I would expect is that there would  
>> be a
>> little delay because a 20mb is going to take a little longer to  
>> transfer
>> than a 1kb text field...
>
> Yes, that is a problem.  The longer binary log transfer times will
> introduce higher delay, so your slaves will be behind the master even
> more.  And depending on the transfer speeds and activity of your
> database, it could get bad, possibly even bad enough where you will
> never catch up.
>
> I really want to know if storing files in a database is considered a
> bad idea, in regards to best practices too.  I admin a database
> server, and my developers started down the path to storing files in
> the database without talking to me about it first (there are
> understandable reasons for this however).
>
> Anyway, here is a list of pros and cons I have experienced:
> Pros:
> * Easy to share files between web applications, including disparate
> servers/networks
> * Simpler to setup (no NFS/Samba)
> * One less service to administer/monitor (NFS/Samba)
> * Centralized data storage (keeps files with database data)
> Cons:
> * Replication can be slow
> * Unexpected increase in storage usage in a MySQL database volume
> * Backups take forever
> * Re-sync of a failed slave takes forever and requires more downtime
> on the master
> * mysqldump not only takes forever but will often fail on large  
> datasets
>
> However, there are many things you can do to mitigate some these
> problems.  I am currently in process of building the file storage
> infrastructure so I can encourage the devs to migrate out of the
> database.  I don't like it.
>
> --lonnie