Groups | Search | Server Info | Login | Register

Groups > comp.databases.mysql > #636

Re: Can MySql database store images?

Show all headers | View raw

On 4/24/2011 6:41 PM, Axel Schwenke wrote:
> "Robert Crandal"<rcranz143101@gmail.com>  wrote:
>> "Axel Schwenke"<axel.schwenke@gmx.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.
>
>>> It's built with PHP and even uses MySQL to store ... no, not the
>>> images themselves, but image metadata.
>>
>> What is image metadata?
>
> The extra information that's neither in the file contents nor the
> (original) file name:
>
> - which user uploaded that image
> - and when
> - what caption to use for the image
> - does it belog to
>    * a series of images?
>    * an article?
>    * or what?
> - what's the mime type
> - what's the dimensions (to write proper<img ...>  tags later)
>
> etc. pp
>

That can be some definition of metadata.  But it's far from complete.

> Remember: if you upload a file to a web server, it gets stored under
> the user account that runs the web server software (normally there
> is no such physical user). It's stored in some temporary directory
> and may even have it's file name changed.
>

Which is completely immaterial, since you never leave it in the 
temporary directory anyway.

> Especially for images: you might want to create thumbnails for them.
> Or maybe scaled versions (for mobile phone use?). Then there will be
> multiple files belonging together (and sharing some metadata)
>

Which has nothing to do with whether the image is stored in the database 
or not.

>> This website lists the pros and cons, but it doesn't say that using
>> a database to store images is a huge mistake and to avoid this option
>> whatsoever.  The only major drawback it mentions is the fact that
>> it could possibly created a bloated database and "MAY slow down
>> other connections to the database."
>
> It *WILL* slowdown the delivery of the image. Consider the difference:
>
> 1. static image
>
> - HTTP request comes in
> - content URL is mapped to a file name
> - web server sends response headers to network device
> - web server calls sendfile() to have the operating system kernel
>    copy the file contents to the network
>
> 2. image in database (and assuming PHP script)
>
> - HTTP request comes in
> - content URL is mapped to a script name
> - script is loaded into execution engine (i.e. PHP compiler + bytecode
>    interpreter)
> - script extracts the key (i.e. the image number) from HTTP params
>    (this part is tricky, because we don't want SQL injection)
> - script opens database connection
> - script constructs SQL query and sends it to the database
> (here the control flow changes to a second process)
> - database parses query, may have to load an index to locate the
>    record, probably has to load the record into memory
> - database extracts the BLOB from the record and sends it to the script
> (control flow is back at the web server)
> - script receives a copy of the BLOB
> - script sends response headers to network device
> - script sends BLOB to network device (probably requiring multiple
>    calls to write() due to buffer size restrictions)
> - script ends (database connection is closed implicitly)
>

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.

And in the case of a DECENT RDBMS, data retrieval will be much faster 
for images in a large table (i.e. millions of rows) than it will be in 
ANY file system.  But then we already know from your "expert" opinion 
that MySQL is not a decent RDBMS - just a toy one.

> There is a lot more work to do for the second case. A big problem for
> bigger BLOBs is the fact that multiple copies are stored. In the case
> of MySQL it is certain that the MySQL server will have two copies: one
> in the record buffer and one in the send buffer. Plus there will be a
> copy in the client receive buffer (and unless using the relatively new
> mysqlng connector, PHP will make a second copy in the variable holding
> the query result). There is latency added: the script can do nothing
> (not even send response headers!) before the BLOB was processed and
> is ready to be sent.
>

There a lot more work than you've shown in the first case, also.  For 
instance - just searching the file system can take a much longer time 
than accessing the database and retrieving the appropriate data - at 
least with a non-toy database.

> More to come? Yes! Most web caches (including your browser) will not
> cache responses for urls of the sort /foo/image.php?nr=12345. They
> will however cache responses for /foo/17/23/1234561723.png.
>

Wrong again.  That was true 10 years ago, but not there have been huge 
advancements since then.

> Web servers (and their local file systems) scale easily. One web
> server cannot serve images fast enough? Put a second one, and invest
> some intelligence on how to distribute content to web servers. Works.
> Google does it. Amazon. Facebook. $YOUNAMEIT
>

No, file systems do NOT scale easily.  And no, you can't just "put in a 
second one" and expect it to work.  It takes a LOT of background 
programming.  Google does it.  Amazon.  Facebook.

But then of course you've never been involved in any project which takes 
more than 3 web pages (if that), so you would have no idea.

> A database does not scale so easily. Distributing writes onto a cluster
> of machines is still a hard problem. Distributing reads is easier and
> in fact this and the dead-simple replication made MySQL the #1 of web
> databases.
>

A GOOD database scales much more easily.  A TOY database doesn't.

> Now what is the claimed(!) advantage of the database? It can use an
> index (read: a tree) to find the image record based on the number.
> For all file systems younger than 10 years, this is exactly the same.
> Directories and files in a file system are stored as tree. Finding a
> file by name does exactly the same as the database does. And of course
> there is RAID and caches for file systems too.
>

Wrong again.  A GOOD database will access large amounts of data much 
faster than any file system.

>
> Having said all that: current hardware is fast. Memory is cheap,
> networks are wide. Delivering content (not just images, also HTML
> snippets) from a database works. But it scales badly. If you plan
> for 100 users and 1000 page views per day - it might work for you.
> But keep in mind that it is not the real thing. Besides of being
> fancy there is little advantage.
>
>
> XL

And lets see you maintain database integrity when you have files 
external to the database (with MySQL, at least - other databases such as 
DB2 can do it).

As usual, you have absolutely no idea what you're talking about.  But 
then that's not surprising, since you've never worked in the real world.

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

Back to comp.databases.mysql | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Can MySql database store images? "Robert Crandal" <rcranz143101@gmail.com> - 2011-04-23 23:47 -0700
  Re: Can MySql database store images? "J.O. Aho" <user@example.net> - 2011-04-24 09:23 +0200
    Re: Can MySql database store images? "Robert Crandal" <rcranz143101@gmail.com> - 2011-04-24 01:19 -0700
      Re: Can MySql database store images? "J.O. Aho" <user@example.net> - 2011-04-24 11:13 +0200
  Re: Can MySql database store images? The Natural Philosopher <tnp@invalid.invalid> - 2011-04-24 11:17 +0100
  Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-24 13:06 +0100
  Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-24 08:42 -0400
    Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-24 14:45 +0100
      Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-24 10:27 -0400
        Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-24 19:04 +0100
          Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-24 14:44 -0400
            Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-24 20:51 +0100
              Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-24 17:03 -0400
                Re: Can MySql database store images? Hans Castorp <REWYRLXHEGHO@spammotel.com> - 2011-04-24 23:18 +0200
                Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-24 19:55 -0400
                Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-25 08:30 +0100
                Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-25 06:32 -0400
                Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-25 12:04 +0100
                Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-25 07:18 -0400
                Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-25 13:32 +0100
                Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-25 09:09 -0400
                Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-25 14:17 +0100
                Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-25 19:29 -0400
                Re: Can MySql database store images? The Natural Philosopher <tnp@invalid.invalid> - 2011-04-25 12:30 +0100
                Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-25 13:59 +0100
                Re: Can MySql database store images? The Natural Philosopher <tnp@invalid.invalid> - 2011-04-25 18:10 +0100
        Re: Can MySql database store images? Norman Peelman <npeelman@cfl.rr.com> - 2011-04-24 16:40 -0400
          Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-24 17:05 -0400
            Re: Can MySql database store images? Norman Peelman <npeelman@cfl.rr.com> - 2011-04-25 02:57 -0400
              Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-25 08:59 +0100
                Re: Can MySql database store images? Norman Peelman <npeelman@cfl.rr.com> - 2011-04-25 21:41 -0400
                Re: Can MySql database store images? Norman Peelman <npeelman@cfl.rr.com> - 2011-04-26 06:20 -0400
                Re: Can MySql database store images? dougatmilmacdotcom@example.com (Doug Miller) - 2011-04-26 14:10 +0000
                Re: Can MySql database store images? Norman Peelman <npeelman@cfl.rr.com> - 2011-04-26 22:24 -0400
                Re: Can MySql database store images? The Natural Philosopher <tnp@invalid.invalid> - 2011-04-27 08:24 +0100
                Re: Can MySql database store images? Norman Peelman <npeelman@cfl.rr.com> - 2011-04-27 06:41 -0400
                Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-27 12:32 +0100
                Re: Can MySql database store images? The Natural Philosopher <tnp@invalid.invalid> - 2011-04-27 15:07 +0100
                Re: Can MySql database store images? "Peter H. Coffin" <hellsop@ninehells.com> - 2011-04-27 07:11 -0500
                Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-27 13:34 +0100
                Re: Can MySql database store images? "Peter H. Coffin" <hellsop@ninehells.com> - 2011-04-27 09:25 -0500
                Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-27 16:32 +0100
      Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-24 10:28 -0400
        Re: Can MySql database store images? Norman Peelman <npeelman@cfl.rr.com> - 2011-04-24 16:43 -0400
          Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-25 08:36 +0100
            Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-25 06:34 -0400
              Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-25 11:50 +0100
                Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-25 07:20 -0400
          Re: Can MySql database store images? The Natural Philosopher <tnp@invalid.invalid> - 2011-04-25 12:42 +0100
    Re: Can MySql database store images? Luuk <Luuk@invalid.lan> - 2011-04-24 16:27 +0200
      Re: Can MySql database store images? The Natural Philosopher <tnp@invalid.invalid> - 2011-04-24 15:38 +0100
        Re: Can MySql database store images? Luuk <Luuk@invalid.lan> - 2011-04-24 16:41 +0200
        Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-24 19:07 +0100
          Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-24 14:46 -0400
            Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-24 21:01 +0100
              Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-24 17:10 -0400
                Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-25 08:23 +0100
                Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-25 06:39 -0400
                Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-25 12:00 +0100
                Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-25 07:27 -0400
                Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-25 13:42 +0100
                Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-25 09:12 -0400
                Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-25 14:23 +0100
                Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-25 19:31 -0400
                Re: Can MySql database store images? "Mr. B-o-B" <mr.chew.baka@gmail.com> - 2011-04-25 20:30 -0500
          Re: Can MySql database store images? The Natural Philosopher <tnp@invalid.invalid> - 2011-04-25 12:21 +0100
            Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-25 13:46 +0100
      Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-24 13:23 -0400
        Re: Can MySql database store images? Luuk <Luuk@invalid.lan> - 2011-04-24 20:31 +0200
          Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-24 14:48 -0400
            Re: Can MySql database store images? Luuk <Luuk@invalid.lan> - 2011-04-24 21:11 +0200
    Re: Can MySql database store images? Axel Schwenke <axel.schwenke@gmx.de> - 2011-04-24 21:24 +0200
      Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-24 17:34 -0400
        Re: Can MySql database store images? Luuk <Luuk@invalid.lan> - 2011-04-25 12:04 +0200
          Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-25 06:40 -0400
            Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-25 11:48 +0100
              Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-25 07:28 -0400
                Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-25 13:36 +0100
                Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-25 09:17 -0400
                Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-25 14:30 +0100
                Re: Can MySql database store images? The Natural Philosopher <tnp@invalid.invalid> - 2011-04-25 18:13 +0100
                Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-25 19:37 -0400
                Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-25 19:35 -0400
    Re: Can MySql database store images? "Robert Crandal" <rcranz143101@gmail.com> - 2011-04-24 13:13 -0700
      Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-24 17:35 -0400
  Re: Can MySql database store images? Axel Schwenke <axel.schwenke@gmx.de> - 2011-04-24 21:29 +0200
    Re: Can MySql database store images? "Robert Crandal" <rcranz143101@gmail.com> - 2011-04-24 14:37 -0700
      Re: Can MySql database store images? Axel Schwenke <axel.schwenke@gmx.de> - 2011-04-25 00:41 +0200
        Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-24 20:16 -0400
          Re: Can MySql database store images? Axel Schwenke <axel.schwenke@gmx.de> - 2011-04-25 12:12 +0200
            Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-25 07:31 -0400
        Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-25 08:43 +0100
        Re: Can MySql database store images? Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2011-05-01 09:59 +0200
      Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-24 20:03 -0400
    Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-24 17:39 -0400
      Re: Can MySql database store images? Axel Schwenke <axel.schwenke@gmx.de> - 2011-04-25 00:46 +0200
        Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-24 20:18 -0400
          Re: Can MySql database store images? Luuk <Luuk@invalid.lan> - 2011-04-25 12:13 +0200
            Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-25 06:43 -0400
              Re: Can MySql database store images? Luuk <Luuk@invalid.lan> - 2011-04-25 13:40 +0200
                Re: Can MySql database store images? The Natural Philosopher <tnp@invalid.invalid> - 2011-04-25 12:43 +0100
                Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-25 09:19 -0400
                Re: Can MySql database store images? Luuk <Luuk@invalid.lan> - 2011-04-25 16:46 +0200

csiph-web