Rails - Oct 2006 - Db vs Filesystem for Binary contnet

Hi,

I''m debating between storing binary content (i.e. images) in the db  
(mysql) or the filesystem.  I like the idea of the database because  
everything is in one place, but I''ve heard using the filesystem has  
better performance.  Is storing binary content in the database a  
performance hit vs. the file system, or is it just an old wives  
tale?   If I''m going to be writing queries against a table''s
non-
binary fields will I face performance hits because binary columns  
appear in the table?

Anyone have any thoughts?

Thanks in advance.  Scott.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---

Scott Walter wrote:
> Hi,
> 
> I''m debating between storing binary content (i.e. images) in the
db
> (mysql) or the filesystem.  I like the idea of the database because
> everything is in one place, but I''ve heard using the filesystem
has
> better performance.  Is storing binary content in the database a
> performance hit vs. the file system, or is it just an old wives
> tale?   If I''m going to be writing queries against a
table''s non-
> binary fields will I face performance hits because binary columns
> appear in the table?
Almost any curcumstance you will find the filesystem quicker for 
accessing binary files than a DB.  Depending on how big your files are 
you can easily end up choking your DB.

I think this was a fad started by over ambitious DBAs that wanted every 
piece of content under their control.

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---

On Oct 1, 2006, at 5:02 PM, Scott Walter wrote:
> I''m debating between storing binary content (i.e. images) in the
db
> (mysql) or the filesystem.  I like the idea of the database because
> everything is in one place, but I''ve heard using the filesystem
has
> better performance.  Is storing binary content in the database a
> performance hit vs. the file system, or is it just an old wives
> tale?   If I''m going to be writing queries against a
table''s non-
> binary fields will I face performance hits because binary columns
> appear in the table?
The big difference is that if it''s in the filesystem, the front-end web
server can serve the file without any dynamic content at all, whereas
if it''s in the database, you involve the front-web server, application
server, and DB server.

All of which are not that much of a problem, except for the DB. Every
scaling problem I''ve ever seen that was non-trivial to fix was related
to DB contention.

-- 
-- Tom Mornini


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---

I hate managing backups of databases with lots of binary content.
Resurrecting a damaged one is next to impossible. OTOH, everything really is
in one place, which makes insert and delete more atomic. From a performance
perspective, it will depend on how hard you expect your database to be hit.
If the database is local, you shouldn¹t suffer much performance. If the
database is remote, the round-trip cost adds up in a hurry. If performance
is your concern, measure a sample under your projected load on hardware
similar to what you plan to deploy to.

Steve 


On 10/1/06 5:02 PM, "Scott Walter"
<scott-APWf0AbNa2kIjDr1QQGPvw@public.gmane.org> wrote:
> 
> Hi,
> 
> I''m debating between storing binary content (i.e. images) in the
db
> (mysql) or the filesystem.  I like the idea of the database because
> everything is in one place, but I''ve heard using the filesystem
has
> better performance.  Is storing binary content in the database a
> performance hit vs. the file system, or is it just an old wives
> tale?   If I''m going to be writing queries against a
table''s non-
> binary fields will I face performance hits because binary columns
> appear in the table?
> 
> Anyone have any thoughts?
> 
> Thanks in advance.  Scott.
> 
> > 



--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---

Tom Mornini wrote:
> On Oct 1, 2006, at 5:02 PM, Scott Walter wrote:
>
>   
>> I''m debating between storing binary content (i.e. images) in
the db
>> (mysql) or the filesystem.  I like the idea of the database because
>> everything is in one place, but I''ve heard using the
filesystem has
>> better performance.  Is storing binary content in the database a
>> performance hit vs. the file system, or is it just an old wives
>> tale?   If I''m going to be writing queries against a
table''s non-
>> binary fields will I face performance hits because binary columns
>> appear in the table?
>>     
>
> The big difference is that if it''s in the filesystem, the
front-end web
> server can serve the file without any dynamic content at all, whereas
> if it''s in the database, you involve the front-web server,
application
> server, and DB server.
>
> All of which are not that much of a problem, except for the DB. Every
> scaling problem I''ve ever seen that was non-trivial to fix was
related
> to DB contention.
>
>   
Wouldn''t the file system be a concern if it wasn''t locally
attached
storage in a multi-server setup?
Cheers
Mohit.



--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---

Yeah, dealing with dumps with a lot of binary data will lose its appeal 
very quickly.

Also, doing file operations -- like imagemagick -- on binary data just 
makes things so much more complicated.

Besides the weak "keeping everything in one place" argument, I
can''t
think of any pros of storing binary files in the db.

Joe

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---

> Besides the weak "keeping everything in one place" argument, I
can''t
> think of any pros of storing binary files in the db.
Well, an extension of that argument is that if your images are to be
dynamically resized on a regular basis or anything like that (so image
requests will have to be dynamic whether on the db or not), it makes
it so much easier to program. But I still think having images on the
file system is well worth the extra time and effort in almost any
circumstance.

This is one of those subjects that comes up every 2-4 weeks.

-Nathan

On 02/10/06, Joe Ruby MUDCRAP-CE
<rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
wrote:
>
> Yeah, dealing with dumps with a lot of binary data will lose its appeal
> very quickly.
>
> Also, doing file operations -- like imagemagick -- on binary data just
> makes things so much more complicated.
>
> Besides the weak "keeping everything in one place" argument, I
can''t
> think of any pros of storing binary files in the db.
>
> Joe
>
> --
> Posted via http://www.ruby-forum.com/.
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---

unknown wrote:
>> Besides the weak "keeping everything in one place" argument,
I can''t
>> think of any pros of storing binary files in the db.
> 
> Well, an extension of that argument is that if your images are to be
> dynamically resized on a regular basis or anything like that (so image
> requests will have to be dynamic whether on the db or not), it makes
> it so much easier to program.
HUH?!? Easier how? Dealing with image *files* is stupid easy.

Joe


 But I still think having images on the
> file system is well worth the extra time and effort in almost any
> circumstance.
> 
> This is one of those subjects that comes up every 2-4 weeks.
> 
> -Nathan

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---

I''ve done projects where I''ve stored images both ways. The
first time, I
stored images in the database. That''s an alright approach, because you 
can use caching to speed things up.

However, in a more recent project, I''m storing images in the file
system
along with any resized renditions of them. The thinking behind this is 
that I can serve image files statically without hitting Rails at all. 
More, since I''m using a very limited set of sizes, after a while, all 
the renditions I need will already be created statically. The tradeoff 
here is CPU time versus storage space. I think I''ll ALWAYS have more 
storage than CPU.

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---