SitePoint Sponsor

User Tag List

View Poll Results: Do you store images in your sql database?

Voters
71. You may not vote on this poll
  • Yes, always

    1 1.41%
  • Sometimes

    11 15.49%
  • No, never

    48 67.61%
  • I would, but I don't know how

    11 15.49%
  • what ist a mysql database?

    0 0%
Page 3 of 4 FirstFirst 1234 LastLast
Results 51 to 75 of 87
  1. #51
    Wanna-be Apple nut silver trophy M. Johansson's Avatar
    Join Date
    Sep 2000
    Location
    Halmstad, Sweden
    Posts
    7,400
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I hate akamais copywriting. It's a prime example of a violation of the #1 in Jakob Nielsens latest top 10 usability mistakes.

    Anyway, Akamai is a distributed infrastructure solution. Basically, if you need to serve enourmous amounts of content really, really, really fast to all users in all countries, they are the ones you turn to. I believe it's them that Apple uses to stream their live webcasts to 40000 users simultaneously.
    Mattias Johansson
    Short, Swedish, Web Developer

    Buttons and Dog Tags with your custom design:
    FatStatement.com

  2. #52
    SitePoint Enthusiast
    Join Date
    Feb 2004
    Location
    France
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by firepages
    Some users wont have uploaded a picture , its not a transactional requirement as it is not a required field (in that particular case) , so you have a default 'no image' image , do you intend on duplicating that image in the DB as well?
    No - that's what the NULL value is for. It says that the field is empty and hold no data. Or if images are stored in a separate table, you'll just have a 1-n or n-n relationship with foreign keys (which, in the case of 1-n relationship, can be set to NULL to indicate the lack of image.

    + how do you know the picture stored in the DB is not corrupt , wrong picture ?
    With transactions, if an image upload or an image change fails halfway, the whole update is cancelled. That's the beauty of ACIDity : it's either done right, or not at all. If you mix'n match storage on database and on file, you won't be able to make sure that what is in the database match what is on the filesystem (and vice-versa).

    And if the plug is pulled during a write operation, your datas in the database will NOT be corrupted (there's a transaction log that garantee consistency at restart, and broken transactions are rolled-back automatically to their original state). With files, the write operation might be stopped halfway and your file is corrupt.

  3. #53
    SitePoint Wizard samsm's Avatar
    Join Date
    Nov 2001
    Location
    Atlanta, GA, USA
    Posts
    5,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, now I'm getting confused, so perhaps someone can clear this up for me.

    Can someone order these from most to least processor-intensive and provide brief reasons?

    1. Image data read from a filesystem through a script.
    2. Image data read from database through a script.
    3. Image data read directly from file.

    If those descriptions leave room for ambiguity, feel free to provide more info. For each of these, assume that the fastest approach possible fitting the description is used.

    I'm not going after which is best of provides the most or best functionality, just trying to get a sense of where people actually stand, one issue at a time.
    Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?

  4. #54
    SitePoint Enthusiast
    Join Date
    Feb 2004
    Location
    France
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by samsm
    Can someone order these from most to least processor-intensive and provide brief reasons?

    1. Image data read from a filesystem through a script.
    2. Image data read from database through a script.
    3. Image data read directly from file.
    Obviously 3, 1 and 2. But it's all pretty meaningless once you start caching your files with a script and/or mod_rewrite.

  5. #55
    SitePoint Addict StephenBauer's Avatar
    Join Date
    Apr 2004
    Location
    USA
    Posts
    263
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Some actual DBAs out there care to comment?

    This thread question really has multiple answers depending on many things - the amount of non-image data, the amount of image data, the number of other DB instances or DBs (depending on the technology), the capability of the servers, etc.

    Generally, it is not a good idea to store images in a database. Many of the DBMS servers out there are underpowered for true DBMS use. Typically they lack the appropriate number of IO channels and drives (in most businesses that are not of very large size). In hosting environments, some even stick the DBMS on the web server to keep things simpler to configure network wise (kind of a 1 on 1 mentality) knowing that those concerned with real performance use dedicated servers or host their own servers.

    What does that have to do with it? If you are storing images (and thus lots more data), you are burdening the IO subsystem of the DBMS server. This is usually the critical failing point in DBMS performance. This problem is only compounded if there are many instances or databases. Not only are your queries having to return your important data in a timely manner, but now they have to churn out your images.

    One can argue: why not add another channel and some drives for the images? Yes, you can, but typically that extra channel and drive array could be better used for other non-image purposes (i.e. move various high-use database objects to the added channel instead of adding the images into the mix). There are very high-end database scenarios that have used 15, 17, 21, etc. IO channel solutions. It just goes to say that most DBMS servers are typically underconfigured due to cost restrictions (except for those with big pocket books and budgets). It is easier (and often cheaper) to throw another PC/server (that was already purchased and just laying around) into the setup to store the images on then to add a high-end IO channel card and drive array.

    As far as backups, yes it can make it easier but it also makes backups longer. This is especially true if there are a lot of images, many instances or databases, and/or constant use of the system throughout the day (i.e. internet sites accessing DBMS servers as opposed to business DBMS servers which typically have windows of downtime for maintenance and backups). If a DBMS server starts managing several Gig of image data, your backups could literally take hours (and forget about the restoral time). Your site could get by without the images for a bit. If your DBMS without images restored quickly, then your site could be functional sooner and the images restored from a seperate backup while the users continue to use the system.

    As for ACIDity, the person not seeing importance in this is correct. They asked how do you guarantee the image is not already corrupt...by this they meant when it is presented for storage into the DBMS. In a properly developed system, you can ensure transactions and integrity without storing images in the DBMS (i.e. store the file at the filesystem level and use a checksum and such - but even then you don't know if the image was corrupt before you got it without proper non-DBMS routines in place for checking image layout - as well as gathering your width, height, etc.). Also, you can use transactions at a scope outside the DBMS (by this I do not mean the DBMS's implementation of transactions...that is only part of the picture). Check out .NET's transaction abilities. There are other similar concepts in Java, middle-ware packages, etc.

    What should you do? Well, ideally you should store your non-image data in a suitable DBMS system (or should I say RDBMS system) that is running on a properly configured server (and preferably void of any other application's/site's data - although for many of you with smaller sites a shared DBMS server probably makes sense). Then store your image data either on another properly configured server at the filesystem level or even at the DBMS level (although that is overkill if just storing the images) so that leaves us with the filesystem again. If money permits, utilization of edge-of-network caching servers (either your own [squid/ISA/network applicance/etc.] or for more benefit someone elses like Akamai) will give additional performance. By all means, whatever the solution, make the images cacheable with a decent cache retention time. Simple, isn't it!? Yeah right!

    That is the "ideal" solution. This is not to say other solutions (including storing in a DBMS) exist that will be suitable for certain situations (size, frequency of quering and types of queries, number of apps/sites sharing DBMS server, etc.).

    It all depends on the implementation needed (size, performance needed), the environment to be used (shared/dedicated, properly/improperly configured), and of course the money behind the scenes (dedicated costs/hosting costs, hardware costs). For most hosting environments, it probably doesn't matter (unless you are on a dedicated server) as you are usually hosted along with numerous others already violating appropriate methodologies and best practices.

    One point to ponder: most porn sites (aka lots of images) use the filesystem for a reason.

    Sorry about all the parenthesis - it is late and I am a little sick from allergies (cold).
    Last edited by StephenBauer; May 26, 2004 at 11:28.

  6. #56
    SitePoint Wizard silver trophy KLB's Avatar
    Join Date
    Nov 2003
    Location
    Maine USA
    Posts
    3,781
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    StephenBauer, Very good first post on databases and images

    Welcome to Sitepoint.
    Ken Barbalace: EnvironmentalChemistry.com (Blog, Careers)
    InternetSAR.org
    Volunteers Assist Search and Rescue via Internet
    My Firefox Theme: Classic Compact
    Based onFirefox's default theme but uses much less window space

  7. #57
    ********* wombat firepages's Avatar
    Join Date
    Jul 2000
    Location
    Perth Australia
    Posts
    1,717
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Obviously 3, 1 and 2. But it's all pretty meaningless once you start caching your files with a script and/or mod_rewrite.
    all the same its still 3 , 1 , 2 , or 'non the less, its still a frog'

    StephenBauer
    +1

  8. #58
    ☆★☆★ silver trophy vgarcia's Avatar
    Join Date
    Jan 2002
    Location
    in transition
    Posts
    21,235
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by M. Johansson
    I hate akamais copywriting. It's a prime example of a violation of the #1 in Jakob Nielsens latest top 10 usability mistakes.
    Quote Originally Posted by akamai's site
    As more and more business processes move to the Web, it's increasingly critical that enterprises provide always-available Web operations - no matter what the conditions may be at the site of the origin infrastructure, or on the public Internet. Hardware failures, software failures, attacks, natural disasters, misconfigurations, and even planned maintenance, create downtime that brings e-business to a temporary halt. And downtime for any reason can lead to frustrated users, damaged brand, or even the loss of critical information and revenue. How can you keep your e-business running effectively using the infrastructure you have today?
    Ugh. Their copy really sucks, it seems like they opened a Marketing book and grabbed every other word from the glossary.

  9. #59
    Wanna-be Apple nut silver trophy M. Johansson's Avatar
    Join Date
    Sep 2000
    Location
    Halmstad, Sweden
    Posts
    7,400
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by vgarcia
    Ugh. Their copy really sucks, it seems like they opened a Marketing book and grabbed every other word from the glossary.
    They completely rock, though. All Akamai sites are always extremly responsive.
    Mattias Johansson
    Short, Swedish, Web Developer

    Buttons and Dog Tags with your custom design:
    FatStatement.com

  10. #60
    ☆★☆★ silver trophy vgarcia's Avatar
    Join Date
    Jan 2002
    Location
    in transition
    Posts
    21,235
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by M. Johansson
    They completely rock, though. All Akamai sites are always extremly responsive.
    Definitely...it just makes me wonder who has more pull when it comes to their own site .

  11. #61
    SitePoint Wizard samsm's Avatar
    Join Date
    Nov 2001
    Location
    Atlanta, GA, USA
    Posts
    5,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In my opinion, this thread is confusing enough on topic and Akamai is a good topic on it's own.
    Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?

  12. #62
    SitePoint Enthusiast robsynnott's Avatar
    Join Date
    Mar 2004
    Location
    Ireland
    Posts
    54
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by platinum
    I beleive it's because the date of image creation will appear different everytime?
    If you send the correct headers, you should be able to tell the browser not to bother checking for new ones...
    http://ads.synnottsoftware.com
    Free Banner Text and Popup Exchange
    http://www.synnottsoftware.com/adsenseanalysis
    AdSense CSV Report analysis tool

  13. #63
    Phil fillup07's Avatar
    Join Date
    May 2002
    Location
    Jacksonville, FL
    Posts
    1,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is pulling and showing an image in a database slower than a direct path (or a direct path from a text db field)?

    If so, how much slower (if this was done a lot of times)...?

  14. #64
    ********* wombat firepages's Avatar
    Join Date
    Jul 2000
    Location
    Perth Australia
    Posts
    1,717
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by fillup07
    Is pulling and showing an image in a database slower than a direct path (or a direct path from a text db field)?
    Yes most circumstances , each and every time.

    & I can not understand why some here (with it must be said far more database knowledge than me) continue to avoid the simple 'yes in most circumstances' ???

    its obviously causing confusion as the same question keeps popping up in the same thread.

    are there circumstances when its a good/useful idea to use the DB for storing images ? , yes , again its the exception rather than the rule & performance issues are not one of these reasons.

  15. #65
    The knight who said ni! RockyShark's Avatar
    Join Date
    Apr 2003
    Location
    Rockhampton, Australia
    Posts
    699
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I guess the general opinion here is "usually no" - but I do have a couple of smaller sites where it might save a bit of work.

    For example: used car yard website. Maybe 30 vehicles on the lot. Maybe 2 or 3 photos of each tops. Say, max 90 photos. Store them in the database and it saves having to arrange a directory structure, setup permissions etc. Question... how do you write an image to a database? And when you retrieve it, how do you then display it as an image? Can anyone point me to a tutorial on the subject?

  16. #66
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    save a bit of work? i think once you do it, you will see that it doesn't save you a whole heck of a lot at all

    arrange a directory structure? why? why not in the same directory as the html?

    arrange permissions? why? isn't the directory where the html is stored already accessible to the internet?

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

  17. #67
    The knight who said ni! RockyShark's Avatar
    Join Date
    Apr 2003
    Location
    Rockhampton, Australia
    Posts
    699
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    save a bit of work? i think once you do it, you will see that it doesn't save you a whole heck of a lot at all

    arrange a directory structure? why? why not in the same directory as the html?

    arrange permissions? why? isn't the directory where the html is stored already accessible to the internet?

    Yeah - it is, but I'd need to adjust permissions on the directory where the images are stored in order for the bloke who runs the car yard to be able to update them himself. That's kinda why I'm asking the question - I've never done it (stored images in a database, that is. I've always only stored their path and used file uploads via forms and then used copy() and unlink())

  18. #68
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have never said that storing images in a DBMS is the “one true way”. However, if the only gripe is that “performance is bad” then show me some empirical evidence that show a substantial performance hit in a typical application.



    You won’t find one.



    As they say – “Premature optimization is the root of all evil.” If your DBMS is not running into I/O problems than adding more data to it will not be a huge deal. If you’re I/O bound then you already have a problem that you need to address.



    But, some reasons why performance will not differ significantly:

    With a DBMS I have much finer grained control over placement of data on disk and in memory. I can define a memory cache for, say, the image table and then bind it to that table which ensures that the images will not be paged to disk. This can be accomplished with gobs of memory on the web server, too, although you are never guaranteed that they will not be paged out by the OS.



    Via clustered indexes and device management I can specifically define where data will be physically stored so I can optimize their placement on disk.



    I can also increase the page size for the image table to improve read performance. If I know about the data I have I can optimize how much data is read in a single I/O operation. This is hard, if not impossible, to do with OS files.



    But, as I said, images are usually static, “dumb” data. Akamai (and their competitors) will cache your images and other static data for you so you don’t have to waste CPU and network bandwidth serving to the unwashed masses – you can devote all of that to, supposedly, the thing that people come for on your site: the content.

  19. #69
    SitePoint Addict StephenBauer's Avatar
    Join Date
    Apr 2004
    Location
    USA
    Posts
    263
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, you can build your DBMS server's IO subsystem out to accomodate just about any use but unless you are a corporation with a lot of money, this may not be doable.

    From a perspective of users on this website (from what I can see) many people here use hosted solutions or use their own server(s) and are not a large corporation. I would guess that some here do not have proper capital to put forth for a 4+ IO channel DBMS server with a set of drives for each channel. If their DB(s) are small, then this really would not matter but some do have large DB(s) or many DBs. By all means benchmark, tune, and if need be upgrade if you can.

    I have consulted for many companies (including hosting companies) and see the same underpowered DBMS servers all the time (usually either lacking adequate memory/IO subsystem and/or an oversaturated DBMS server). I tend to not see this as much at very large companies but it does happen. I think the hosting companies assume that is an okay practice since most of its clients will have small DBs being hosted or know that performance concerned users will want their own dedicated setup. If the hosting companies do optimize DBMS data file placement, it is usually only to split up transaction logs from the data (with no distinction for indices, image/binary data, temp storage, etc). This holds true for many normal (i.e. non-hosting) companies too. **It is at this point that IO performace issues almost always exist on a said configured system with a reasonable amount of data.** Unless the performance problems can be remedied by tuning or upgrades, then you surely will not want to add image data to the mix.

    This may sound like a negative perspective on DBMS in corporate america but it is what I have personally seen. Maybe I have just been "unlucky" if you will, so do with it as you will.

    When I come into a company there is usually a performance issue they are concerned with. These issues are typically frequent periodic spikes that are too frequent for their own comfort level. I then benchmark and profile like crazy. I try to optimize or help the employees optimize their queries/stored procs and their data placement. Unfortunately, it usually comes down to some hardware acquisition because either a non-DBA designed the DBMS server(s), not enough forethought was placed in data needs, or there was a budget concern and they tried to scrape by.

    I never said that images in a DBMS server will always cause a performance issue, but given the fact that a typical DBMS server configuration quite frequently already has a performance issue, then adding images to the DB will only compound this.

    My comments were based on these observations (or even trends if you will) to be a guideline for best practice in a typical environment. I think they hold water even if for just the backup reasons I stated.

    My "ideal solution" paragraph is just that, an ideal solution that lends itself to the idea of being able to build out an IO subsystem and optimize data placement. I personally prefer to use IO channels or even subsets of drives for splitting out the data/indices even further than to waste (in my opinion) them on storing image files (but then again the DBs I deal with are typically Gigs in size).

    Another thing that is typical for these companies (and many of the users on this board probably) is that they have an old PC/server laying around that would make an excellent image server. Since it is already purchased, it is definately a lot cheaper than adding another IO controller and a drive or two.

    I would say if you are using a hosted solution, ask questions about how they have things setup (if you cannot glean that from the hostnames you use to update your site/database and domain lookup tools), how data is split up, how many people tend to share that setup, and if they know, how much data currently resides on that setup (as well as their "cutoff" level for when they decide to not add users to that setup).

    If you are hosting your own setup, I do think it is wise to think ahead and be aware of best practices and ideal solutions to guide your thought even though it may not come into play as much for you right now (or even ever if your needs are small or limited). I agree with premature optimization and over-optimization and their evils in consuming resources, but knowing best practices and their ramifications will help you make logical decisions. For instance, if you are already experiencing IO bottlenecks, your budget is fixed, and your queries are sound, then you know adding images is probably a bad idea.

    Then consider your own personal data needs (including non-image data size, image data size, performance expectations, etc.) and how they play into a hosted or an in-house solution and their configurations, limitations, etc.

    Side note: Yes, most businesses go SCSI or better so you can save money by using higher end IDE for your smaller solutions but I have had three instances where IDE RAID controllers (two different manufacturers) corrupted their configurations frequently. I have not had this happen in a SCSI setup yet. I do know of one IDE RAID setup that has been very stable (so go figure). My point here is that SCSI, again in my opinion, is the way to go but then you are looking at more expenditure.
    Last edited by StephenBauer; May 26, 2004 at 11:26.

  20. #70
    Phil fillup07's Avatar
    Join Date
    May 2002
    Location
    Jacksonville, FL
    Posts
    1,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Store them in the database and it saves having to arrange a directory structure, setup permissions etc.
    Directory structure? Just one folder within the web.
    Permissions? Yes... you have to allow your script to upload pics to the directory. But that is like 5 clicks or a 2 line email to your host. Definately not much work in comparison to setting up what's required to store the image in the db.
    Yeah - it is, but I'd need to adjust permissions on the directory where the images are stored in order for the bloke who runs the car yard to be able to update them himself.
    Right, but that is a quick permissions change and is little work in comparison.
    how do you write an image to a database? And when you retrieve it, how do you then display it as an image? Can anyone point me to a tutorial on the subject?
    I don't have a tutorial link handy, but basically you store the binary data in the DB in a BLOB db field type (there might be others, but that is what I know).
    To display the picture, you use this:
    <img src="mypage.php?id=12345">
    Where mypage.php (or can be done in ASP) pulls the binary data from the database and displays it with the content type of the image (JPG, GIF, etc).


    Search around on here and on google, I'm sure you'll come up with a tutorial.

  21. #71
    Phil fillup07's Avatar
    Join Date
    May 2002
    Location
    Jacksonville, FL
    Posts
    1,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I would still like some hard facts regarding the performance difference of displaying an image from a DB versus pulling a simple text file path and showing it from the file system.

  22. #72
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you are a big time site then it makes sense to shove images off to a provider like Akamai

    If you are a small time site then it usually does not matter where you place the images because the performance hit, if any, is not noticeable

    If you are on a shared DBMS server then you are most likely also on a shared web server, so no matter where you place the images you are not getting dedicated I/O bandwidth – so it is impossible to say which would provide performance benefits or if one is significantly overutilized (causing performance issues)

    Also, it seems as if most shared web hosters also do not allow web write access which is required for image uploads to work, so that's why vB does in-database

    And as you said, most performance issues have to do with database design (or lack thereof) or bad queries.

  23. #73
    SitePoint Addict StephenBauer's Avatar
    Join Date
    Apr 2004
    Location
    USA
    Posts
    263
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Mattr, I think that is a great re-cap and summary of advice for this thread! Hopefully with all our rantings, there is a why behind all this for the less experienced in database workings.

    fillup07...Mattr answers your question in his first two paragraphs of his last response...

    Quote Originally Posted by MattR
    If you are a big time site then it makes sense to shove images off to a provider like Akamai

    If you are a small time site then it usually does not matter where you place the images because the performance hit, if any, is not noticeable

    If you are on a shared DBMS server then you are most likely also on a shared web server, so no matter where you place the images you are not getting dedicated I/O bandwidth – so it is impossible to say which would provide performance benefits or if one is significantly overutilized (causing performance issues)

    Also, it seems as if most shared web hosters also do not allow web write access which is required for image uploads to work, so that's why vB does in-database

    And as you said, most performance issues have to do with database design (or lack thereof) or bad queries.
    Last edited by StephenBauer; May 26, 2004 at 11:25.

  24. #74
    ********* wombat firepages's Avatar
    Join Date
    Jul 2000
    Location
    Perth Australia
    Posts
    1,717
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    considering perhaps 75% of this forums users utilise shared hosting with perhaps 200+ domains running on each (all powered off the one SQL database in most circumstances)...

    well If you still think

    "it usually does not matter where you place the images because the performance hit, if any, is not noticeable
    "

    is good advice ... then what can one say but `I give up`

  25. #75


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
  •