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.
Scott W. 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.
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
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
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
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
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.