Hi,

My users have 10000's of images, (if not more).

Currently all the images are stored in a sub folder,
'/user/letter/unique_filename.ext'

But I was wondering if mysql would not be slightly better at retrieving
the files, (the actual data).

The reason I ask is because I have few problems:

1- Backup is a bit all over the place currently, I could loose some/all
my images in the case of a HD failure, I would prefer to have a slave db
with all the images on it.
2- The site is growing, slowly but surely, so in the longer term it
might be better to have all the images in one table.
3- Moving to another server might help.

What do you think?

Is getting a file from the db and 'displaying' it, (serving it to the
browser), faster than getting it off the file server?

Is storing images in the db a better option for a large number of files?

Thanks

Simon

Simon <bad (AT) example (DOT) com> wrote:

<snip>

Congrats! You warm up a "holy war" question. Please read:

http://groups.google.com/group/comp....bd1dfec0473016

Serving a file from disk is a highly optimized operation for a web
server. Much unlike sending content that is stored as BLOB in a DBMS.

For a comparison:

http://mysqldump.azundris.com/archiv...-Database.html


XL

Simon wrote:
Opinions are divided.


You will probably find the files quicker, because Mysql has good indexing.

You may not retrieve them any quicker though, especially under heavy
load...a *nix operating system will have lots of access to RAM for disk
caching that MySQL may not... for example.


This was discussed extensively here a few months ago, and IIRC the
general compromises came down to better performance from flat files but
far cleaner management and backup and general tidyness from MySQL BLOB
stored images.

I have a database with about a thousand images in blobs: for sure on low
level of user activity it works not detectably worse than flat files.





The consensus is that its probably slightly slower. On a typical *nix
system you are using an extra process - mysqld - and a socket
connection to it.

That's where IIRC the general consensus was that the gains were to be
had - better indexing than a *nix file system.

There is also the memory issue..I cant remember exactly how Mysql
handles memory, but depending on how you access the DB you may end up
needing a lot of memory to hold images that you are currently pushing
out to userland. With *nix you have essentially the sum total of all
free RAM to do that if its simply a flat file. Mysql may not be so
fortunate.

With luck someone who knows the internals of MySQL will be able to
expand on this theme more than I can.

But unless its a high usage site, I personally prefer the neatness of
keeping all data in the DB.

On 9/23/2011 4:30 AM, Simon wrote:
Yes, this is a religious war. Some of us have real world experience
doing this, others don't. I've been working with images and RDBMS's for
around 25 years in live environments.

A file system is just a hierarchic database, and not a very smart one at
that. 10,000's of files in one directory will slow down access as file
systems are not really made to handle huge numbers of files.
Additionally, disk space tends to fragment, slowing access even more.

A decent database can handle large numbers of images like that quite
effectively. There will be less fragmentation, access time can improve,
database integrity can be assured (you can't just delete a file from
the file system) and backups are much easier. The only down side is in
a web environment you need to call a short script to fetch and server
the file, but that can be very efficient also.

Now some databases can have some pretty crappy retrieval times, may be
inefficient on how they store the data or have other problems. Perhaps
that's why some people think it's not a good idea to store images in a
database. But with a decent database it can work quite well.


--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

The Natural Philosopher <tnp (AT) invalid (DOT) invalid> wrote:

This really depends on the file system. If the file system in use is
conceptually younger than - lets say - 10 years, then there is a big
chance that it uses a tree structure to organize files. And then
there will be no difference in performance if you ask the file system
to locate a file by name or if you ask the database to locate a BLOB
by key.

The real difference is, that a database can have multiple keys where
a file system has only one. OTOH you can get the same in a file system
too - by using (symbolic) links.

And of course even with old file systems, where directories were lists,
it was no problem to have many files and still be performant. The
canonical solution is a hashed directory structure.

I.e. file foobar.jpg is located in (root)/f/o/foobar.jpg


XL

I have done both and I HIGHLY prefer storing images in a database. A
lot of things become a lot easier and more natural. Backup, access,
fsck, bulk operations, etc etc etc.

If the process is a little slower, I personally do not care. I had to
deal with filesystems with millions of objects, and all I want to say
is NOT AGAIN.

i

Ignoramus14736 wrote:
users, the practical aspects of management far outweigh the alleged
performance hits IMHO.

Axel Schwenke wrote:


I have had first-hand experience of this recently when a less experienced
colleague actually stored dynamically generated PDF documents in BLOBs in a
MySQL database table (I think the version was 5.1, and the engine was
InnoDB) for a WIMP application. (Not only that, but for reasons well beyond
me he also base64-encoded the PDF data beforehand, increasing the footprint
of each file by about 33%.)

After a month or so the table storing the PDF documents generated by about
500 different users reached 2177 records and the formidable size of 1.7
GiB(!), and response times of the Web application for retrieving a single
PDF document by primary key (indexed!) went up to 2 minutes(!).

After I extracted the data to the filesystem, with the table only keeping
the filenames (which took about 2 *hours* to complete), response times of
the application went down to about 2 *seconds* again.

So I can only strongly emphasize this: Avoid storing files in BLOBs!

There has also been a Microsoft study on this in 2006 CE, finding that due
to fragmentation which filesystems appear to handle better than databases,
the break-even point for storing a file in a database is at 256 KiB or even
lower, depending on the DBMS:

<ftp://ftp.research.microsoft.com/pub/tr/TR-2006-45.pdf>

I have found it referred at

<http://mysqldatabaseadministration.b...01/i-will-not-
blob.html>


HTH

--
PointedEars

Please do not Cc: me. / Bitte keine Kopien per E-Mail.

On 2011-09-23, The Natural Philosopher <tnp (AT) invalid (DOT) invalid> wrote:
I have a database with 473,374 images, works great. I am now building a
database that will hold 200,000,000 images or so.

Storing them in files would mean that I will have to reimplement many
database features on a filesystem.

i