SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    Hibernator YuriKolovsky's Avatar
    Join Date
    Nov 2007
    Location
    Malaga, Spain
    Posts
    1,072
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    does storing images in databases really affect performance?

    Storing images in a mysql database can be a great thing, not only is everything easily accessible, it is also password protected and has all the necessary details right on the same row, great for websites where visitors upload their own photos (like vacation photos, party photos)
    it also allows me to easily backup all the photos in one database file.

    But recently Ive heard from forums that storing images in databases really impacts the image fetching performance for the visitor.


    is this true?
    and what are the other pros and cons of storing images in a database?

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

  3. #3
    Hibernator YuriKolovsky's Avatar
    Join Date
    Nov 2007
    Location
    Malaga, Spain
    Posts
    1,072
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    read that, interesting, but why do some still support images in database?

  4. #4
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    For a small site, with a small database, and no or few concurrent users, it doesn't matter. You can do whatever's easier for you.

    It doesn't scale, though.

  5. #5
    Hibernator YuriKolovsky's Avatar
    Join Date
    Nov 2007
    Location
    Malaga, Spain
    Posts
    1,072
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    thanks for the tip Dan, ive worked always with pure images on filesystem but at a certain point i thought it would be a good idea to store the images in a database as then everything is more organized, easier to backup, easier to sort, i've never tried this on a released website, just some local test sites and was wondering what the fuss between the database vs filesystem was.

    p.s. searching sitepoint for "images in databases vs filesystems" did not bring up anything so i thought i would ask around.


    for a small shared site i would think that storing images in a database is actually better, as then you don't run into the shared files problem.

  6. #6
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Fixing the shared files problem without using a BLOB only requires one line of code -- after you run the INSERT query to put the rest of the image's details in the database, you use the generated ID of that row in the move_uploaded_file call to prepend it to the file name before saving it.

    At any kind of scale these days, image hosting tends to be offloaded to a cloud hosting provider like Amazon S3. Not only does it cost less, scale infinitely and serve images faster, but it frees up your CPU and disk IO for scaling the website and not serving images.

  7. #7
    Hibernator YuriKolovsky's Avatar
    Join Date
    Nov 2007
    Location
    Malaga, Spain
    Posts
    1,072
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    on a shared server when uploading files onto it using php, $_FILES and move_uploaded_file, can be very problematic and is usually solved by setting the folder permission levels much lower (even 777) which is a problem considering that everyone on the same shared server has access to the folder now.

    or at least this is what i was told.


    At any kind of scale these days, image hosting tends to be offloaded to a cloud hosting provider like Amazon S3. Not only does it cost less, scale infinitely and serve images faster, but it frees up your CPU and disk IO for scaling the website and not serving images.
    Now this is Interesting!!
    i did not know of such a thing!
    (just shows that self-learning always leaves gaps in knowledge here and there.)

    and how does my server (php) upload files there? and what about backups?
    any more details on that?

  8. #8
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    You send it to them with an HTTP request, just like the user uploaded the file to you (except programatically, rather than with a browser). There's sample code and complete libraries you can download to use or learn from. Everything is well documented on Amazon's site:

    http://aws.amazon.com/s3/

  9. #9
    Hibernator YuriKolovsky's Avatar
    Join Date
    Nov 2007
    Location
    Malaga, Spain
    Posts
    1,072
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    thanks Dan, this does seem like the better option for bulk image storage
    the "You send it to them with an HTTP request" really cleared things up for me.

    one last question though, do you have any personal experience with it?
    (and its privacy/reliability)

  10. #10
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Most of my sites run on the Amazon Elastic Compute Cloud (EC2), and I use Amazon Elastic Block Storage (EBS) for disk space. They have been as reliable as any physical servers of my own for the most part. I don't use Amazon S3 for anything but storing backups, which it works fine for. Access to S3 files is controlled in a pretty fine grained way, you can set access control lists with timeouts and private tokens for individual files... it takes some programming work to integrate into a site, but it'll do what it says it does after that. You won't have to worry about storage or performance of static file serving any longer.


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •