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 2 of 4 FirstFirst 1234 LastLast
Results 26 to 50 of 87
  1. #26
    SitePoint Guru
    Join Date
    Feb 2004
    Location
    Oregon
    Posts
    686
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by techmonkey
    From Berber's blog:

    This is what you need to be careful with. A database table can only be 2GB in size.
    who told you that? the limit is the size of the hard drive considering mysql will go into the terabytes.
    success is not by chance, it is by choice.

  2. #27
    Non-Member
    Join Date
    Jan 2003
    Posts
    866
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Sahajin
    who told you that? the limit is the size of the hard drive considering mysql will go into the terabytes.
    The maximum size of a file in a Linux file system is 2 GB.

  3. #28
    SitePoint Guru
    Join Date
    Feb 2004
    Location
    Oregon
    Posts
    686
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I find that hard to believe. that is a big limitation for a great OS. any proof? why would mysql be made for a OS that has limits to 2G?
    success is not by chance, it is by choice.

  4. #29
    SitePoint Wizard bronze trophy
    Join Date
    Apr 2003
    Posts
    4,095
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by techmonkey
    The maximum size of a file in a Linux file system is 2 GB.
    In Windows it's four gigabytes, and I believe SQL Server supports all of that.

  5. #30
    Non-Member
    Join Date
    Jan 2003
    Posts
    866
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    A couple. If you want to read more try Google.

    http://www.linuxgazette.com/issue42/tag/18.html
    https://listman.redhat.com/archives/.../msg00065.html

    Newer Linux versions will change this limitation but for now I think most of us are stuck with 2GB. It's really not a limitation that you run into often. In my case it's only a problem with my Weblogs and the attachment.myd Mysql file that is generated by Vbulletin. Most programs don't lump together lots of binary data into a single file - hence the problem with storing images in a db.

  6. #31
    SitePoint Guru
    Join Date
    Feb 2004
    Location
    Oregon
    Posts
    686
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    did you not read that? you are reading something back in linux 2.3 days.

    > Can someone tell me exactly what the maximum file size and
    > maximum file system size is for the ext2/3 implementations distributed
    > with Red Hat Linux 7.3?

    file size: about 4 TB. Filesystem size: 1 TB (limited by the block
    device layer, but that should be increased in 2.5.)
    and the windows one is way wrong. we have shape files (graphic files) that are 20gig. and windows holds them jsut fine.
    success is not by chance, it is by choice.

  7. #32
    Non-Member
    Join Date
    Jan 2003
    Posts
    866
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Sahajin
    did you not read that? you are reading something back in linux 2.3 days.
    Thats Kernal Version - not distribution numbers from companies like Red Hat. You need to do some research.

  8. #33
    SitePoint Guru
    Join Date
    Feb 2004
    Location
    Oregon
    Posts
    686
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    read agiain

    with Red Hat Linux 7.3?

    handles 1TB files most linux host are on at least 9
    success is not by chance, it is by choice.

  9. #34
    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)
    Linux kernel 2.4 did away with the 2GB filelimit. If you are on 2.2 then yes, you will have problems, but if you are on 2.2 then you have other problems as well.

  10. #35
    SitePoint Guru
    Join Date
    May 2003
    Location
    virginia
    Posts
    988
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I just did this for a website. It works great and actually got it from the PHP Anthology book. If HarryF is doing it, I figure it's probably "OK", at the least. It made coding much easier, because I use DB_DataObjects and didn't have to code logic for file/permission related issues (using the file system). Not that it would be that much *more* work.

    I personally think looking at a database as a place only to store "text" is too limiting. Databases crash and burn just like a complete hard drive can... so do your self a favor and backup!

    Matt

  11. #36
    ********* wombat firepages's Avatar
    Join Date
    Jul 2000
    Location
    Perth Australia
    Posts
    1,717
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    errr back on topic

    the only advantage of storing data in the DB is that you keep all your data in one place ,security and caching are non-issues , there are no other advantages.

    The major disadvantage is access time which is slower than from a filesystem under any circumstances , move your DB to its own server and it gets even worse.

    I can't understand how anyone can subscribe to the theory , especially you Matt your image data argument has merit , but still no reason to store the image itself in the DB , its not as though you can query the image metadata in the binary field to extract this info at runtime (or is it ? happy to be shown how if possible)

    Hey images are becoming databases themselves , and as backwards as it seems I am grabbing EXIF data from digital images and storing them in a DB for fast searching (no point recursing a directory tree to get image data innit), but still use the filesystem to store the actual images to keep the DB as lean and mean as it possibly could be.

  12. #37
    SitePoint Guru
    Join Date
    May 2003
    Location
    virginia
    Posts
    988
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmm, well I guess maybe I did it out of lazyness? Using DB_DataObject might have done that. But seriously no problems with it so far.

    I felt like it was much easier to implement than the file system, where you have to worry about permissions, directory structure, and/or a good file naming scheme. How do you handle depth when you try to organize images in a particular way... Create directories on the fly if needed? I guess. In the DB, organization is NOT an issue.

    If it's a performance thing, I guess that doesn't bother me. Maybe it should? If I feel good about the code I write, it's clean, simple and sturdy, then I'd trade that over a little performance hit any day. What I should do is just write some classes to handle the dirty work involved in the file system method!

    Matt

  13. #38
    ********* wombat firepages's Avatar
    Join Date
    Jul 2000
    Location
    Perth Australia
    Posts
    1,717
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hey if it aint broke then don't fix it

    I create directories on the fly in similar situations, but normally a ridgid file structure is already in place for the logic of your application.

    To be honest I never keep links to images in my database either (unless enforced e.g. externally hosted images) as I find that your filesystem normally ends up relational to your database structure , e.g. there is already enough data in the database to let you know where logically an image should exist.

    a user avatar may logically be expected to be found in
    /images/users/$user_id.jpg
    or
    /images/users/$user_id/avatar.jpg etc

    so no need to bother storing that in the database either , and far more complex relationships can be created as you can imagine , complex but still logical & therefore easy to utilise.

    I agree that file permissions are to be fair a pain at first , but thats a one-time issue , any database induced lag affects every single page view.

    What I should do is just write some classes to handle the dirty work involved in the file system method!
    exactly what I do , I have functions for creating directory stuctures for new users/whatevers and filewriting functions that remove chmod hell from my everyday life

  14. #39
    SitePoint Enthusiast
    Join Date
    Feb 2004
    Location
    France
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by firepages
    the only advantage of storing data in the DB is that you keep all your data in one place ,security and caching are non-issues , there are no other advantages.
    You forgot ACID. The only way to garantee 100% consistency of your datas is to use transactions and integrity constraints, and that means having all the datas in the dabase, wrapped with a nice begin/commit. This is not possible if you store images on the filesystem.

  15. #40
    SitePoint Enthusiast
    Join Date
    Feb 2004
    Location
    France
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by MattR
    Linux kernel 2.4 did away with the 2GB filelimit. If you are on 2.2 then yes, you will have problems, but if you are on 2.2 then you have other problems as well.
    Anyway as far as MySQL is concerned, you can use Innodb and span the datas on several files, which breaks any operating-system limits. I think there's also a "raw disk" mode where it will skip the filesystem and write directly to a designated hard-drive.

  16. #41
    ********* 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 Betcour
    You forgot ACID
    I don't see how images are relevant to ACIDity except in some unusual circumstances , images are normally decorative , though there are rarer instances where they are functional , and rarer still where they could affect transactional & business logic.

    So I don't see that as even close to valid in all but exceptional circumstances.

  17. #42
    SitePoint Enthusiast
    Join Date
    Feb 2004
    Location
    France
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by firepages
    I don't see how images are relevant to ACIDity except in some unusual circumstances , images are normally decorative , though there are rarer instances where they are functional , and rarer still where they could affect transactional & business logic.

    So I don't see that as even close to valid in all but exceptional circumstances.
    Well your images are usually linked to some other elements in your database (ie : user ID picture <-> user profile, product picture <-> product specs, etc.). If you don't want to end up with a "lost" picture (ie : user picture without a profile) or a broken record (ie : user profile which says there's a picture, but somehow the picture is missing or is the picture of someone else), you need some form of ACID (a foreign key and/or a transaction to wrap updates).

    And even if your pictures are purely decorative, you might want to have your datas in a consistent state anyway.

  18. #43
    SitePoint Member
    Join Date
    Dec 2003
    Location
    Dopamine
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The 2GB limit has very little to do with the kernel. What it has to do with is the filesystem used whether it's ext2, reiserfs, xfs, etc it's true that some filesystems have a limit on what size can be opened at once ( reiserfs ) but in reality whos going to open a 2GB table? If thats happening then you should already be using a server fs like xfs or jfs .

  19. #44
    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)
    Quote Originally Posted by firepages
    caching are non-issues , there are no other advantages.
    Please tell me how this is so.

  20. #45
    ********* 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 Betcour
    ie : user picture without a profile) or a broken record (ie : user profile which says there's a picture, but somehow the picture is missing or is the picture of someone else
    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?

    + how do you know the picture stored in the DB is not corrupt , wrong picture ? , you can't do a checksum and say 'ah ok thats Joes mugshot' , you can of course store the checksum of the image in the DB and check that the stored file matches it (still no proof its Joe of course)

    I can imagine scenarios where images could be considered transactional but by far they will be the exception rather than the rule , perhaps you consider otherwise but I think that will make you wrong

    Quote Originally Posted by MattR
    Please tell me how this is so.
    'caching' or the 'no other advantages' ?

    again there is a performance issue and there are not enough (if any) advantages to counter that, blaming a badly configured server is bypassing the issue , a properly configured server will increase the performance of all routines that it runs be they wise or otherwise.

  21. #46
    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)
    Quote Originally Posted by firepages
    do you intend on duplicating that image in the DB as well?
    Depends on how you model it, but it would most likely be a many-to-many relationship.

    Quote Originally Posted by firepages
    how do you know the picture stored in the DB is not corrupt
    A proper DBMS does not corrupt data, but if it does, it has significant recovery techniques which are not availible (or require activation) in a filesystem.

    Quote Originally Posted by firepages
    (still no proof its Joe of course)
    This problem exists regardless of where you store the image.

    I can imagine scenarios where images could be considered transactional but by far they will be the exception rather than the rule , perhaps you consider otherwise but I think that will make you wrong

    Quote Originally Posted by firepages
    'caching'
    ... and there are not enough (if any) advantages to counter that, blaming a badly configured server is bypassing the issue , a properly configured server will increase the performance of all routines that it runs be they wise or otherwise.
    Although that is mildly incoherent I'll try and simplify:
    What would lead you to believe that DBMS cache management provides no benefit over an OS-controlled filesystem

  22. #47
    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)
    p.s. all of this is academic. Why are you storing and serving static data anyway? It just wastes your bandwidth:
    www.akamai.com

  23. #48
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by MattR
    p.s. all of this is academic. Why are you storing and serving static data anyway? It just wastes your bandwidth:
    www.akamai.com
    academic or not, it's fun to watch the big guns in action

    WTF is the akami link for? who are those guys? it's pretty hard to sift through the corporate-speak bullsh1t on that site and figure out what they do

    are you saying we should host our databases with them? do they store pictures in the database instead of the file server?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  24. #49
    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)
    Are you being intentionally obtuse?

    It's not hard to find:
    http://www.akamai.com/en/html/services/edgesuite.html

    Akamai has been around for a while, I'm surprised you've never heard of it. Haven't you seen the links on web sites that look like http://a298.g.akamai.net/7/298/5382/...o/bar/img3.gif ?

  25. #50
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    intentionally obtuse? me??? you jest

    look up obtuse in the dictionary, and here's what it says:
    To reach a wider audience cost-effectively and better serve global customers, enterprises are using the Internet to move more information and business processes, more intelligently, to more people and places than ever before. To support advanced e-business, your infrastructure needs to be robust and scalable, to handle rich content and applications - and provide for variable traffic and flash crowds. It must be intelligent, to enable personalized and dynamic content; agile, to detect and react to network and Internet conditions; secure, to protect content and control access. And available, 24/7, to all users - customers, suppliers and partners - regardless of their location.
    i wish i could run that through google and translate it into english

    the only thing that made any sense in that paragraph is "flash crowds" which is when teenagers use their cellphones to all meet at a certain coffee shop at a certain time
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •