SitePoint Sponsor

User Tag List

Results 1 to 22 of 22
  1. #1
    SitePoint Addict molder101's Avatar
    Join Date
    Apr 2004
    Location
    dorothy
    Posts
    234
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    To Blob or Not To Blob?

    I am curious as to how other designers out there approach the use of blob for pictures.

    I just built an easy to use cms for a client that allows the addition of pictures, managing of a shopping cart etc.

    The thing I am running into is that when the database gets backed up it obviously does not include the new pictures she has added because they are just saved to a picture directory and only the relative location is stored in the database.

    Is this the preferred method of accomplishing this task? It seems to me that using the blob for pictures could put an unnecessary burden on the db and the server. (Also makes the database get rather large quickly.)

    However, it would be convenient to have all the pictures backed up when she backs up all the other databases.

    Any suggestions / recommendations?

    (**She is not on a cpanel host so I cant just backup the whole site at once**)

    Thanks,
    Mike
    Michael R. Sikora
    www.blue-eyedesign.com
    {redesigning web design}

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    all of the hard-core DB programmers i know strongly discourage storing files and pictures in a DB, specifically for the reasons you've suggested.

    so, here are the two methods of displaying images based on a database for comparison, asuming PHP, MySQL and Apache:

    1) user brings up a PHP page, which gets a URL from the database. the URL is a static link to a picture stored on the server which PHP uses in an img tag. the picture is served up by Apache.

    2) user brings up a static or PHP page with an IMG tage linking to a PHP page. this second page gets the BLOB from the MySQL server, which is in turn sent out by PHP, then Apache.

    with #1, you've used the strengths of each component: PHP, processing data; MySQL, quickly retrieving discrete data; Apache, serving static content quickly. each component transfers between eachother the smallest amount of data needed to get the job done.

    with #2, MySQL has to load the BLOB in memory to return it to the client, then PHP has to hold it in memory to turn it over to Apache which FINALLY transmits it to the client.

    and if the MySQL server is not on the same box as the Apache server, there's an added hit in both cases, but much more so in the case of BLOBs.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,245
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    longneck, great analysis, nice job

    we used to have a rep system here, you'd've definitely got some points for that one

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    i'm thinking of converting my "analysis" to a few test scripts using the 300 or so pictures i have at work for our security badges. care to take a wager on the performance ratio? the mysql server is local.

  5. #5
    SitePoint Evangelist
    Join Date
    Apr 2005
    Location
    Moscow, Russia
    Posts
    557
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by longneck
    1) user brings up a PHP page, which gets a URL from the database. the URL is a static link to a picture stored on the server which PHP uses in an img tag. the picture is served up by Apache.
    URL can be also a dynamic link and its parts may be based on PK values something like: http://site.com/usersession_ID/imagecategory_ID/image_PKID.jpg

  6. #6
    SitePoint Addict molder101's Avatar
    Join Date
    Apr 2004
    Location
    dorothy
    Posts
    234
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So how do I go about backing up all of my picture files?

    Do most clients just use cpanel to do a site backup or what?

    The key is I want to have backups of the pictures in the event the site gets hacked etc.

    Mike
    Michael R. Sikora
    www.blue-eyedesign.com
    {redesigning web design}

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,245
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    any FTP-capable software -- i use WS_FTP and UltraEdit (the latter for updating text files right on the server)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Addict molder101's Avatar
    Join Date
    Apr 2004
    Location
    dorothy
    Posts
    234
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I know that I can of course use any FTP but I am looking for an easier way to backup.

    I am trying to get some sort of automated process so the client has little to no involvement. We all know how "smart" most clients are, and how they always back up on time, etc.

    {Hopefully you noticed the sarcasm}

    Anyway, that is what I am looking for, a smarter dynamic way to backup the pictures (preferably without using cron) -- whether it be clicking on a link or whatever.

    Thanks for the help,
    Mike
    Michael R. Sikora
    www.blue-eyedesign.com
    {redesigning web design}

  9. #9
    Currently Occupied; Till Sunda Andrew-J2000's Avatar
    Join Date
    Aug 2001
    Location
    London
    Posts
    2,475
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmmm, I am stuck in this dilemma despite having warned Hosting & Operations of the problems with storing binary data in the database as have requested. Currently editors are allowed to upload upto 4mb of data (Too much). This is on an 8 CPU, 16GB dedicated server, however when the editors questioned why they couldn't upload 130mb advertisement video files, this came as a rather large shock when I put this forward to the Server team, obviously.

    We are going to have to come up with a solution for storing this large amount of data as part of phase 2, however we have two memcached servers that cache the streamed data from the db. Although I would really recommend against storing binary data in blobs...

    Anyone have any suggestions for storing that amount of data? Currently there is roughly 1500 files, however I expect this to increase exponentially when this goes to the markets.

  10. #10
    SitePoint Evangelist
    Join Date
    Apr 2005
    Location
    Moscow, Russia
    Posts
    557
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by molder101
    Anyway, that is what I am looking for, a smarter dynamic way to backup the pictures (preferably without using cron) -- whether it be clicking on a link or whatever.
    Hi, i'm not a website administrator, so i'm not familar with ready to use backup solutions, but you can implement such system by yourself using web UI + server-side script implementing such simple functionality as displaying archive owner/datetime and copying of image directory to user specified or predefined directory or in tar archive. How about Probably you don't have to re-invent the wheel

  11. #11
    chown linux:users\ /world Hartmann's Avatar
    Join Date
    Aug 2000
    Location
    Houston, TX, USA
    Posts
    6,455
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)
    If you're worried about backing it up you could write a cron job that goes through and puts the image folders into a zip file (there won't be much compression, especially if they're jpg's) and then you just need to download the zip.

    Personally I have a backup schedule where one day a month I just go through and back up every single client site and their database.

  12. #12
    SitePoint Addict molder101's Avatar
    Join Date
    Apr 2004
    Location
    dorothy
    Posts
    234
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Good point Hartmann to have a schedule like that.

    I think that I will either do a cron or quasi cron that zips up the photo directories as you mentioned. After that I will just have an alert in the cms that pictures are ready to be downloaded.

    That sounds good.

    Thanks,
    Mike
    Michael R. Sikora
    www.blue-eyedesign.com
    {redesigning web design}

  13. #13
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    ok, so i put together a test script with 347 photos totaling 54 mb with sizes ranging from 22 kb to 800 kb.

    i used one of two methods to return the image to the user:
    ->apache: path to picture is stored as a varchar and PHP's readfile() function to pass it to the client
    ->blob: picture is stored as a blob and echo'd to the client in PHP

    in addition, i used one of two methods to pick a random picture::
    ->php: PHP picks a random number 1-347 and the select statement looks like "select theblob from blobtable where id = $random_number"
    ->mysql: select statement looks like "select theblob from blobtable order by rand() limit 1"

    here are my results (100 or 1000 total attempts with 10 concurrent connections):
    ->apache/php was the fastest with a mean service time of 322 ms
    ->apache/mysql was only 10% slower with a mean service time of 354 ms
    ->blob/php was alomst as fast as apache/mysql (362 ms)
    ->blob/mysql was HORRIBLE. with 10 concurrent connections, mysql never (not even once) returned a result within 30 seconds. i had to drop the concurrent connections down to 2 before it would work with a mean service time of 17 seconds.

    this test was conducted on a 2.4Ghz P4 with 1 GB RAM running Windows XP, Apache 2 and MySQL 4.1
    Last edited by longneck; Sep 19, 2005 at 08:38.

  14. #14
    Currently Occupied; Till Sunda Andrew-J2000's Avatar
    Join Date
    Aug 2001
    Location
    London
    Posts
    2,475
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You should be able to get some better performance with the Zend Download Server.

    Download optimization -- The Zend Download Server. Because of the inefficient method with which Apache Web Servers process file downloads, many parallel requests for large files can slow -- or even halt -- the application’s ability to service additional users or requests. The Zend Download Server eliminates this problem by taking over the download process, freeing the server to continue handling other requests. The Zend Download Server completely eliminates server memory download limits, allowing sites to service more concurrent downloads without investing in additional hardware.
    PHP Code:
    if (function_exists("zend_send_file")) {
      
    zend_send_file($filename);

    Edit:


    I was not referring to streaming data from blobs out of the database. Also use MemCached servers....
    Last edited by Andrew-J2000; Jul 11, 2005 at 23:18.

  15. #15
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    sorry, Zend download server won't fix the blob/mysql combination. mysql is the bottleneck, not apache.

  16. #16
    SitePoint Addict molder101's Avatar
    Join Date
    Apr 2004
    Location
    dorothy
    Posts
    234
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So essentially your tests prove that larger sites are more than likely not blobbing their images.

    Regardless of the database, I would think that similar efficiency problems would exist and ultimately would hinder performance.

    It is very interesting that the concurrent connections is where performance is reduced.

    Conclusion: dont use blob for storing pictures, especially if multiple users are accessing the system at any given time.

    Thanks for your tests longneck,
    Mike
    Michael R. Sikora
    www.blue-eyedesign.com
    {redesigning web design}

  17. #17
    SitePoint Guru toasti's Avatar
    Join Date
    Feb 2004
    Location
    Grahamstown
    Posts
    634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I know that I can of course use any FTP but I am looking for an easier way to backup.
    this is prob very out of date, but if you are using linux you can us rsync to syncronise two folders on either the same host, or even a completely different host.

    if u google it there are some helpful resources...

  18. #18
    SitePoint Enthusiast
    Join Date
    Oct 2005
    Location
    Lyon, France
    Posts
    43
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Image files naming convention

    Quote Originally Posted by Tangerine_Dream View Post
    URL can be also a dynamic link and its parts may be based on PK values something like: http://site.com/usersession_ID/image...image_PKID.jpg
    I use a similar naming convention, based on the PK, something like http://site.com/images/PKID.jpg. But the original filenames are stored in the database, and used when saving a file for example so the user gets a real filename and not the PKID-weirdo

  19. #19
    SitePoint Enthusiast
    Join Date
    Oct 2005
    Location
    Lyon, France
    Posts
    43
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Backing up image files : Software

    Quote Originally Posted by molder101 View Post
    So how do I go about backing up all of my picture files?
    I use Cobian Backup and its scheduling feature. It also supports FTP and custom scripts, like PHP. When the images are stored as files it's really just a matter of copying files around, really basic.

  20. #20
    SitePoint Enthusiast
    Join Date
    Oct 2005
    Location
    Lyon, France
    Posts
    43
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Backing up image files : Protocols & Process

    Quote Originally Posted by molder101 View Post
    I know that I can of course use any FTP but I am looking for an easier way to backup.
    What's wrong with FTP ? The File Transfert Protocol is perfect for a backup job, it's meant to transfert binaries over a network. Of course they're other protocols (HTTP, P2P…) but FTP has a history so… you can trust it

    Quote Originally Posted by molder101 View Post
    I am trying to get some sort of automated process so the client has little to no involvement. We all know how "smart" most clients are, and how they always back up on time, etc.
    You said it, it's all about automating the process and it's crucial not to involve the client in the process. Backups, software upgrades… you can't trust them ! The solution is to separate the information system into layers. A client, a server. The client access the data from the server. The server is backed up, you don't have to access the client… specially considering he shutdowns its computer at the end of the day. But the server is supposed to be ready for backups. Note that the backup server and client server can be the same, it really depends of your infrastructure. It's a crucial to back up the data on a distant server for example.

    Quote Originally Posted by molder101 View Post
    Anyway, that is what I am looking for, a smarter dynamic way to backup the pictures (preferably without using cron) -- whether it be clicking on a link or whatever.
    FTP… now cron ? I don't get it… it works like magic ! How sysdamins could schedule jobs if cron didn't exist ?

    You set up a cron job and rest in peace… but don't forget to send a backup report to the sysadmin in case something goes wrong : server is not accessible, files couldn't be backed up, backup duration...

  21. #21
    SitePoint Enthusiast
    Join Date
    Oct 2005
    Location
    Lyon, France
    Posts
    43
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    AMP to manage and store images as BLOBs

    Quote Originally Posted by longneck View Post
    sorry, Zend download server won't fix the blob/mysql combination. mysql is the bottleneck, not apache.
    I'm not a MySQL guru but I think the problem could be solved by tweaking the server to optimize the Query Cache, specially how it manages BLOB types. For example if the same image is fetched multiple times, does it memory cache it or not ? Or does it just load it from the HD ?

    Your tests were interesting but I think it lacks some layering. You can't just use a basic server configuration to handle a large amount of data and make assumptions from it. The files are stored in a database instead of a filesystem, you have to bind the DBMS to the Web server properly, specially considering there's now PHP between Apache and MySQL. The problem is the same than with content management, logic (PHP) and presentation (HTML) separation. In that case you use a template system to avoid dynamically generating the same pages over and over. If not then PHP (process) and MySQL (fetch) would be the bottlenecks, like MySQL was in your tests.

    Of course the ideal solution would be MySQL to adapt itself to the context, how the application manages the data… but I'm not sure you can consider it as a real solution, at the production level. To sum things up, I think it's all about right configuring the server and setting up the proper modules/extensions to optimize the workflow. That's where the real challenge is.

    Maybe we should run your tests on a MySQL Enterprise powered database or something... it would also be great to run a test with PostgreSQL ! Considering my Relational Vs. Object DBMS to store images post it seems PG would do the job just smoothly. Because it knows a BLOB is evil when MySQL just handles it, by default, like any ordinary small-text-wannabe data

  22. #22
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    sure, you could accelerate the whole process, if you have wicked amounts of memory to throw at the problem. but you missed the point of my post: on a standard apache/mysql/php setup, storing images in a database is not as efficient as storing them in the file system.

    and yes, talking about a "basic configuration" is very relevant. 95% of the people who come here do not have the resources to get a dedicated mysql server with 16 gigs of RAM, which is what you'd need to effectively cache all that data.
    Considering my Relational Vs. Object DBMS to store images post it seems PG would do the job just smoothly. Because it knows a BLOB is evil when MySQL just handles it, by default, like any ordinary small-text-wannabe data
    ok mr. smarty pants, explain to me from a programmer's perspective how postgresql and mysql store BLOB's. i know, but i'm betting you don't.


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
  •