SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Enthusiast xhat's Avatar
    Join Date
    Jun 2004
    Location
    USA
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post Efficient gallery display methods

    I've hit upon a scheme to store my images and display them in a gallery, but I don't know if I've created an efficient scheme. I've done a lot of reading on the web, but there doesn't seem to be a consensus about "best practices" when it comes to storing images and serving them back as a gallery.

    What I'm attempting to accomplish is a classified ads site that will have thumbs of items being offered for sale. The thumbs will be links to the larger original image.

    I've made the decision to allow just about any file size upload (within reason - say 3 MBs or less). I'll use PHP's built in functions to resize my image, make a thumb and then discard what the user sends. That way, I'll have control over my storage needs by setting a top limit on what will be stored. I'll be able to set a target for original image size, as well as thumb size. (Or so I think!)

    Next, I'm going to store the images as blobs in a MySQL database. I've written a script to do that, as well. Finally, I'll create the gallery by using image tags and setting source equal to another PHP script which will retrieve the image, render it and serve it back to the gallery page.

    This all sounds well and good, but I got to thinking. If I have 20 thumbs displayed, for example, for a user browsing items, that's 20 img tags, each of which makes a call to my PHP script to render the image. The script, in turn, makes 20 calls to MySQL database to retrieve the blob. If the site experiences any kind of decent traffic (say an average of 200 hits an hour), that's 4,000 calls an hour to my page, plus 4,000 calls to MySQL, all of which has to be handled by the server. At first blush, this doesn't sound very efficient. But what scheme would you use to replace it?

    4,000 hits is 4,000 hits, whether your images are stored as files in a folder on your server, or stored as blobs in a database. They still have to be served. So the question becomes: Which is more efficient, having the server process a small script and a SQL select statement, or navigate a directory tree and fetch an image? I just don't know. I don't know what the industry "best practice" is, or if there even is one. Or perhaps there's another method that I'm not even aware of.

    Any comments on this topic would be greatly appreciated.

  2. #2
    Non-Member Frozentoast's Avatar
    Join Date
    Apr 2002
    Location
    Manchester, UK
    Posts
    4,758
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    Welcome to the forums!

    This kind of topic does not belong here and will be moved to a more appropriate forum soon

  3. #3
    SitePoint Enthusiast xhat's Avatar
    Join Date
    Jun 2004
    Location
    USA
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, sorry. What is the appropriate forum for this question/topic?

  4. #4
    SitePoint Wizard bronze trophy
    Join Date
    Apr 2003
    Posts
    4,095
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Probably General Development Issues, or else one of the language-specific subforums if you have language-specific questions.


  5. #5
    Non-Member Frozentoast's Avatar
    Join Date
    Apr 2002
    Location
    Manchester, UK
    Posts
    4,758
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    Somewhere in the 'Program Your Site' forum, I'm not that sure myself. Don't worry though, a moderator will move this post for you shortly. (promise)

  6. #6
    »-(¯`v´¯)-» macarthur's Avatar
    Join Date
    Jul 2003
    Location
    B.K.A.C
    Posts
    2,469
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    Rats, I was kinda hoping that there would be a tutorial on that.
    `·.¸¸.·´´¯`··._.·FLAMING`·.¸¸.·´´¯`··._.·
    -==׺°”˜`”°º×MONARCH׺°”˜`”°º×==-

  7. #7
    SitePoint Enthusiast xhat's Avatar
    Join Date
    Jun 2004
    Location
    USA
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah, mac, I was hoping, too! I keep reading - in forums, bbs, weblogs, etc., - that people "just don't believe that kind of stuff belongs in a database." Why? When I hear statements like that, it makes me think there is no sound, technical reason for or against the scheme, but rather somebody's personal bias.

  8. #8
    Fine Tuned silver trophy KC's Avatar
    Join Date
    Sep 2001
    Posts
    2,291
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Moving to the Databases forum.

    Hope you find the answers you are looking for, xhat.
    Former Design Your Site Team Leader

  9. #9
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by xhat
    Yeah, mac, I was hoping, too! I keep reading - in forums, bbs, weblogs, etc., - that people "just don't believe that kind of stuff belongs in a database." Why? When I hear statements like that, it makes me think there is no sound, technical reason for or against the scheme, but rather somebody's personal bias.
    The reasons against doing so are (in a nutshell):

    1) It means that your database quickly becomes huge in size, which may impact backing up procedures, and will probably increase the charge for the use of the database server (usually calculated on diskspace and network bandwidth)

    2) This increased DB size can only result in more work for the database server and thus slower performance (it's not going to be QUICKER, is it?)

    3) These large volumes of binary data must be transferred between the database server and web server (further reducing performance since the network bandwidth will be a bottleneck, AND it will cost more money - see point 1)

    4) The binary data will need to be held in memory by the web server in order to stream it to the client, increasing the strain on web server resources. This obviously becomes particularly significant when the number of simultaneous accesses is high, the images are large in size, and/or multiple images are returned in each query.


    There are of course one or two advantages of using BLOBs over separate files on the web server, but they pale into insignificance in the face of that lot above!
    MarcusJT
    - former ASP web developer / former SPF "ASP Guru"
    - *very* old blog with some useful ASP code

    - Please think, Google, and search these forums before posting!

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    don't forget caching

    browsers that have a static url to an image don't request it again

    images stored in a database using a dynamic url are requested every time
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That's also true, thanks Rudy.

    However, I should also point out that assuming the alternative approach is to store the image filenames in the DB instead and serve them up from disk via a script (e.g. http://yourserver.com/image.asp?id=15), your point and my point no.4 also apply.
    MarcusJT
    - former ASP web developer / former SPF "ASP Guru"
    - *very* old blog with some useful ASP code

    - Please think, Google, and search these forums before posting!

  12. #12
    SitePoint Enthusiast xhat's Avatar
    Join Date
    Jun 2004
    Location
    USA
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you both for posting. I'd like to address the points you made and have you respond, if that's ok. I'm doing so not to be argumentative, but rather to learn by probing. You see, I know just enough to be dangerous, but not enough to be competent. Always a volativle mix!

    <q>1) It means that your database quickly becomes huge in size, which may impact backing up procedures, and the charge for the use of the database server (usually done on diskspace and bandwidth)
    </q>

    If I control the size of the images stored, and break them up across tables, isn't that being cost and space efficient? For example, if a user posts a 3MB picture, I can manipulate it with PHP and set a target size of, oh, say, 40k. (Or so I think). I will have to trade off quality to achieve this, but it should be possible to optimize the user's picture and store that "original" and toss what was uploaded. Then, a thumb can be generated from the new "original" at, say, 10% of the size, or 4k, and stored as well. That way, you control table bloat. And everything can be stored in a tinyBLOB so you don't have a lot of wasted space. Finally, limit each table to no more than, say, 100 images, and things should run fast and smoothly. (God, isn't the world great when your understanding of it is simplistic?)

    <q>3) These large volumes of binary data must be transferred between the database server and web server (further reducing performance since the network bandwidth will be a bottleneck, AND it will cost more money - see point 1)
    </q>

    If the image sizes are small and optimized for the web, is there a difference between the web server serving the image from the file system vs. serving it from the database server? Either way, it ultimately has to be served.

    <q>4) The binary data will need to be held in memory by the web server in order to stream it to the client, increasing the strain on web server resources. This obviously becomes particularly significant when the number of simultaneous accesses is high, the images are large in size, and/or multiple images are returned in each query.
    </q>

    Optimized images should be no more taxing on the server than any other type of gallery page using the file system to store images, no?

    Rudy, I hadn't considered the caching side of the equation. Thanks. However, I ran a test page using 20 img tags, each of which called a php script that grabbed the image out of the database and served it back. It was very fast (no numbers, just observation), but, of course, there are a lot of caveats to that. The page was JUST the image tags, nothing else, so that contributed to performance. Also, I thought that perhaps the images had been cached, but, Rudy, your comment makes me think otherwise. Which is a good thing!

    I'm still reading and learning a lot. Clearly, this is a big question in development, or Microsoft and Oracle and scads of others all over the web wouldn't be posting tuts and articles about the subject. Clearly, something is there, but the whole subject seems to be a subjective one: some folks are agin it, no matter what, and others say it's no problem.

    Not sure where I'm going to land.

    Thanks for the posts.

  13. #13
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by xhat
    If I control the size of the images stored, and break them up across tables, isn't that being cost and space efficient? For example, if a user posts a 3MB picture, I can manipulate it with PHP and set a target size of, oh, say, 40k. (Or so I think). I will have to trade off quality to achieve this, but it should be possible to optimize the user's picture and store that "original" and toss what was uploaded. Then, a thumb can be generated from the new "original" at, say, 10% of the size, or 4k, and stored as well. That way, you control table bloat. And everything can be stored in a tinyBLOB so you don't have a lot of wasted space. Finally, limit each table to no more than, say, 100 images, and things should run fast and smoothly. (God, isn't the world great when your understanding of it is simplistic?)
    Multiple tables? Throw normalisation out of the window? That sounds like a truly *AWFUL* idea!!!

    Resizing the images is sensible of course, and thumbnail generation good too, but they have nothing to do with the fundamental issue at hand.

    Increasing the size of the database = more work for the database. It won't be less, it's won't stay the same, so it must be more! *How much* more depends on 101 other factors, of course, the most significant probably being your choice of DB engine.

    Quote Originally Posted by xhat
    If the image sizes are small and optimized for the web, is there a difference between the web server serving the image from the file system vs. serving it from the database server? Either way, it ultimately has to be served.
    Yes, it still has to be served, but retrieving it over the network from the DB server to the web server consumes network bandwidth which you will have to pay for. And then you still have to pay for the usual internet bandwidth of transmitting it to the client. So you pay twice (if not more, since DB bandwidth is usually more expensive than internet bandwidth)! If the web server serves it straight up from disk, then just the normal internet bandwidth cost is incurred.

    Quote Originally Posted by xhat
    Optimized images should be no more taxing on the server than any other type of gallery page using the file system to store images, no?
    The optimization of the images is neither here nor there. If you perform a query which retrieves multiple image records (complete with binary data) but only uses the data of one record (i.e. to stream it to the client), that is clearly going to consume more web server resources than if you retrieved the same number of records but only the filename was stored, and then only the desired file was loaded from disk and streamed.

    Quote Originally Posted by xhat
    However, I ran a test page using 20 img tags, each of which called a php script that grabbed the image out of the database and served it back. It was very fast (no numbers, just observation), but, of course, there are a lot of caveats to that.
    That's one of the least scientific tests I've heard of!! ("fast" compared to what, for example?!)

    Quote Originally Posted by xhat
    The page was JUST the image tags, nothing else, so that contributed to performance.
    (words fail me - I don't know where to start commenting on that one)

    Quote Originally Posted by xhat
    Also, I thought that perhaps the images had been cached
    Thinking about it, in this particular case (i.e. of static images) caching may be a moot point. If you store the date and time that the image was stored in the database and serve it up each time as part of the HTTP response header (e.g. Last-Modified: Fri, 18 Aug 2000 13:14:58 GMT) then the browser should regard subsequent requests for the same file as identical and therefore load the image from cache instead of re-downloading.
    MarcusJT
    - former ASP web developer / former SPF "ASP Guru"
    - *very* old blog with some useful ASP code

    - Please think, Google, and search these forums before posting!


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
  •