Can MySql database store images?

#41

On 4/24/2011 6:46 PM, Axel Schwenke wrote:

Quote:

Jerry Stuckle<jstucklex (AT) attglobal (DOT) net> wrote:
On 4/24/2011 3:29 PM, Axel Schwenke wrote:

snip

You're right.

I know

Of course, a large number of DBAs who do store images in databases are
using real RDBMS's like Oracle, SQL Server and DB2. They are the same
ones who consider MySQL to be a "toy database".

That must be the reason why you tell a guy asking in a MySQL(!)
newsgroup if he should store images in a MySQL(!) database, that
this would be a good idea.

As I said: you have zero, nada, niente experience with using MySQL.
Maybe you should just shut up. I might want listen to your answers
when it comes to questions how to use such "real" databases.

Well. Unlikely.

XL

I have more WORKING experience with MySQL than you have with ANYTHING.

Maybe you should just stop showing your ignorance. It's people like you
who help promote the reputation that MySQL is a toy database.

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

#42

Jerry Stuckle wrote:

Quote:

On 4/24/2011 4:40 PM, Norman Peelman wrote:
Jerry Stuckle wrote:
On 4/24/2011 9:45 AM, Tim Watts wrote:
Jerry Stuckle wrote:

Also, file systems don't do well with thousands of users and
hundreds of
thousands of images - they just weren't made to handle that many
individual files. RDBs such as MySQL, OTOH, easily handles millions of
users and hundreds of millions of images.

Remember- a file system is just a non-relational database. It's good
for some things but not everything.

That rather depends on the filesystem.

XFS will happily handle 13TB with over 10 million files - that is from
personal experience.

Obviously VFAT would be a bad idea, and putting *all* the files on one
directory *may* be a bad idea (depends on directory hashing support
in the
FS) - but with a sensible layout (one dir per user, possibly with a
further
sublayer of user's first letters a-z) it will be fine.

Such a layout is very hard to maintain and takes considerable care to
keep from getting screwed up. With a database, no special programming
is required, other than a small (< 10 line) script to serve the image
from the database.

Couple of points IMO - and I cannot speak for MySQL in particular -
but what
will it do for backups?

Much better, because the files are backed up when the database is
backed up. Much less chance of referencing files from the database
that don't exist, or having orphaned files (files not referenced by
the database).

With a small database and a big filesystem of images, the DB dump
will be
fine and the FS can be backed up with an rsync type methodology.

Databases can be backed up quite easily. But rsync methodology does
not solve the problems noted above.

With a huge number of images in a DB, I suspect the possibilities of
doing
incremental backups are vastly reduced and your DB dump file is ging
to be
huge.

Actually an SQL dump file with images included will ZIP right on down
pretty good.

It actually depends on the image. For instance, a .bmp can compress
quite a bit. But a .jpg will gain little from compression (and might
actually grow a little larger).

However, it really is no more or less than if the images were in the
file system.

Not that bad, but if you really want, you could do incremental backups.

However, I always do full backups of both the file system and
databases - incremental backups are fast and take less space, but are
very tedious and error prone to restore because you have to go back to
the last full backup then restore from the incremental backups in
order. Additionally, incremental backups done with rsync usually end
up restoring files which had been deleted, wasting more space.

Storage is cheap.

Just my opinion, based on Postgresql - but the principles mostly
apply to
MySQL unless it has some particular features to help with the
problems I
posed.

Cheers,

Tim

Just my opinions based on 25 years of experience, starting with DB2
but also including MySQL, Oracle and SQL Server.

My current setup includes a db with approx 460 images (I know, not
huge) with a mixture of file formats (.jpg .png .gif) with an average
size of 5393kb (again not huge, all images are 10240kb or less).

The SQL dump file for this entire db is 29.8MB and the resulting .zip
file is 1.7MB.

--
Norman
Registered Linux user #461062
-Have you been to www.mysql.com yet?-

#43

Jerry Stuckle wrote:

Quote:

You can have the same problem with files. A poor host is no reflection
on the tools. It is only a reflection on the host.

It is not a case of a "poor host" - it is a case of what that host was
designed for.

That's like saying a train is a poor car because it doesn't allow for door
to door or flexible timings and the personal space is cramped. It does
however carry hundreds of people effieciently.

Just because a host was designed and installed to provide a 100 odd MB of DB
storage to 100's users who also have 1-2GB of pure filestore each does not
make it poor - it makes it a suitable solution for that job.

If one user wanted a DB to store 100's GB, they could have that, but their
research funds would be paying a few 1000 pounds for a suitable host for it.

--
Tim Watts

#44

Jerry Stuckle wrote:

Quote:

On 4/24/2011 3:51 PM, Tim Watts wrote:
Jerry Stuckle wrote:

Totally practical. Just back it up locally then transfer whenever you
want. That's how I do filesystem backups, also. That way the backup
isn't being delayed by the network.

Yeah - I was kind of considering the case where the total dataset size
exceeds the capacity of the link over the backup periodicy - which could
be a problem for a very large dataset and a very weak link. But see
below:

No more so than for files.

Yes more so for files.

Using any rsync wrapper of your choice, you may have 10TB of data total and
find you only need to transfer 1-10GB of updates per day (real world figure
from a university environment).

OK - you can keep shifting your binlogs from MySQL but sooner or later you
are going to want to run another full backup and suddenly you have to shift
all that data again. With files you always get "last night's full backup
image" for the cost of the changed files.

Quote:

And what happens when you delete a file - but later need to restore it
from backup?

You copy it from one of the previous incrementales - specifically the one
that was triggered due to the file deletion on the source end.

You manage how many incrementals you keep by deleting the ones you don't
want, either consolidating the incremental set to the nearest weekly/monthly
- or your use the "every incremental looks like a full but virtue of
hardlinks" and just delete the ones you no longer want. Usually in some
patter like having the last 7 dailys, 4 weeklys, 12 monthlys or whatever.

--
Tim Watts

#45

Norman Peelman wrote:

Quote:

And will be cached by MySQL (depending on settings) anyway, so every
request won't go to the disk every time.

And to be fair, a decent FS on decent OS will do the same

--
Tim Watts

#46

Axel Schwenke wrote:

Quote:

"Robert Crandal" <rcranz143101 (AT) gmail (DOT) com> wrote:
"Axel Schwenke" <axel.schwenke (AT) gmx (DOT) de> wrote

There are already applications for that. Unless you tackle this
problem for educational purposes, you'd better use one of them.

i.e. this one: http://gallery.menalto.com/

Do you mean that I should store my images on web sites like this?

Have you read this?

"gallery" is a software packet that you can install on your own web
server. It's written in PHP and uses MySQL (at least MySQL is one
option for the database engine to use). Full source available.

While we're on the tangent, I've been running Gallery2 for a while. It's
nice enough, except for one brain damaged limitation - it does not easily
allow for arranging photos in arbitrary virtual galleries (a prefectly
reasonable request). Gallery2 fudges it with "Create Replica" which clones
the metadata *once* but does not maintain the link after that. Gallery3
seems to have no such facility at all.

I've been looking at Zoph http://www.zoph.org/ which is the same basic idea
as Gallery but, on paper, looks rather better. Haven't had a chance to try
it yet.

Cheers

Tim

--
Tim Watts

#47

Norman Peelman wrote:

Quote:

My current setup includes a db with approx 460 images (I know, not
huge) with a mixture of file formats (.jpg .png .gif) with an average
size of 5393kb (again not huge, all images are 10240kb or less).

The SQL dump file for this entire db is 29.8MB and the resulting .zip
file is 1.7MB.

I'm intrigued - you have 460 * 5.3MB of images which is around 2.2GB and yet
your DB dump is 29.8MB and whilst I expect SQL text to zip well, I wouldn't
expect already lossy-ly compressed image data to zip well.

Are you sure the images are being included in the dumps?

Cheers

Tim

--
Tim Watts

#48

On 24-04-2011 23:34, Jerry Stuckle wrote:

Quote:

Additionally, RDBMS's can partition data for faster access, i.e. across
multiple disks or even multiple systems. Filesystems cannot do that
transparently.

File systems can do that, they invented something like directory's for that.

A different toy needs different tools...

--
Luuk

#49

Jerry Stuckle <jstucklex (AT) attglobal (DOT) net> wrote:

Quote:

On 4/24/2011 6:41 PM, Axel Schwenke wrote:

It *WILL* slowdown the delivery of the image. Consider the difference:
1. static image
....
2. image in database (and assuming PHP script)

This is such a shallow example of what happens, it's totally hilarious.
The same amount of work has to be done in both cases. The only
difference is whether it is done by the web server or a script.

Yet again you demonstrate nothing but your ignorance. If you wanted
to know, you easily could - fire up the web server and database
server in strace and then compare both cases.

But we had this "discussion" long ago. You did not even understand
what I was saying back then. You still not understand.

It makes more sense to talk to the neighbors dog than talking to you.

EOD

#50

On 25-04-2011 02:18, Jerry Stuckle wrote:

Quote:

I have more WORKING experience with MySQL than you have with ANYTHING.

congrats.....

Quote:

Maybe you should just stop showing your ignorance. It's people like you
who help promote the reputation that MySQL is a toy database.

Maybe you should stop showing your ignorance. It's people like you who
help themselves by ignorging the opinion of others...

Just because you have done something the 'right' way does not mean you
did it the RIGHT way!

Different is'nt always better, but better is always different.

Quote:

--
==================
Remove the "x" from my email address
Jerry Stuckle

--
Luuk