I don't think you will see performance problems as your table fills up with images, because the number of images stored will be relatively low, and your database will not be trying to index the binary data in the blobs.

If you had a normal 3Gb table where the cells contained text, numbers or foreign keys, then there might easily be 100 million rows, the database would be maintaining indexes and foreign key relationships on all those rows, which would be a lot of work. In your case your 3Gb table probably only contains 150 thousand rows, which is fairly small, so not much work to index.

I think the total size of the data will only become a problem when it becomes an issue for the underlying file system that your database uses to store it's data, so if it fills the disc to the point that it becomes heavily fragmented or suchlike then there will be a problem, but you would have had those problems if you where storing the files on disc anyway.

Having said that, I don't think you should be storing the image files in your database, because by doing so you will make the database a lot larger which makes it harder to backup, restore or run in a cluster. The problem is that it is generally hard to incrementally backup a database without deep knowledge of it's schema, so most backup will be full backups. On the other hand it is easy to incrementally backup a directory full of files, so I think your best strategy long term would be to keep the binary data out of the database, and so keep it small, separately store your images on the file-system, and develop a backup procedure that backs up both the database and the file-system for the images. Seeing as everyone needs to backup file-systems it should be very easy to find a suitable tool to backup that part.

Contrary to what dHarry said, I don't think a huge number of files in one directory will hurt file system performance, as modern Linux file-systems use modern data structures to store the list of files in a directory and can easily cope with thousands of files per directory, however you should probably split things up anyway for your own sanity, because while the file system will cope fine with 100_000 files in a directory, ls, or worse a GUI file browser will not cope so well.

In reply to Re: Storing/Retrieving images as blobs by chrestomanci
in thread Storing/Retrieving images as blobs by Anonymous Monk

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.