Announcement

Collapse
No announcement yet.

Attachments in dB or file system, is there really a performance difference?

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Loco.M
    replied
    I always move the attachments to the file system, also profile pics, sig pics and custom avatars.

    Leave a comment:


  • Numbus
    replied
    Sorry if I bump this thread but I figured I reply with my own experiences.

    I run a forum with 1.4M+ attachments, daily about 2,000 to 10,000 attachments are added (depends entirely on the weather and day of the week). However with a Attachment storage amount of 928GB it is impossible to load the attachments through the Database, even though I would much prefer it this way. Due to the significantly easier management of the attachments and checking for duplicates and of course integrating the attachments into other pages.

    Now vbulletin has changed a lot over when I first used it back in v2.0 but back then having a DB of 6GB of attachments already slowed down the forum to horrible speeds. When using the 3.6x and later 3.8x I found that after 10 to 20GB of attachments in the Database the forum will slow down significantly.

    So yes I think putting attachments in a database is very handy and could perform very well if you have smaller size attachments and the size of the attachments in a whole don't take too much space but after a while, you really want to consider putting them on file storage as your Database will refuse to even open the main page.

    Stats of the forum are about 600 to 800 people online, 1.4M+ attachments, 32GB of Ram, Dual Quad Core and 4x 1TB HDD's.

    Leave a comment:


  • setishock
    replied
    1. Keeping the images separate from the database in the event of a DB crash you still have your images. If they're in the DB, they're gone as well.
    2. Reduces DB bloat. Your DB can be backed up / restored a lot quicker.
    3. Images in the file system can be backed up separately. Although you can't just put them back in the folder they came out of, at least you have them.
    Washing them through irfanview in a bulk conversion gives you the images back in a non(in-house)VB usable format.

    I have over 4000 LARGE images in my anime forum. The performance difference is minimal. But having suffered through 2 fatal crashes with the images in the DB, I find that having the images outside the DB a lot safer.
    I can ftp in and nab the image folder then cpanel in and nab the database. I'm a lot more comfortable with my site set up like this.

    Leave a comment:


  • anthonyparsons
    replied
    There is a pretty simple answer, though you have kind off gone away from VB software though... when referring to banks with signature images. Nobody is saying pulling images from a DB is bad in general, just bad with VB if a heavily imaged site. How a bank sets up to pull images from a DB is totally different than comparing the principle with VB software. Databases can be very effective tools, and if all you are doing with the one DB is pull signature images, then that would seem to be a pretty effective means IMHO. But when you are talking about forum software, that is pulling postbit data and has multiple users at once all doing different things, ie. pushing and pulling all different types off data from the one DB... that is a little different than say a bank DB just to store signature images and nothing else.

    If you ran a large image site using VB, then you could remove the image tables and put them within a slave and draw them uniquely, which would then provide back the performance as you are no longer accessing one DB, but now two!

    Leave a comment:


  • janaf
    replied
    Ok, thanks for your input guys.

    It seems there is no straight and simple awnser. I have seen a couple of sites with millons of images that rerport better performance with dB storage. One was a bank that had several million scanned signatures from customers. Another was a web site that reported they ran into problems with file system storage when the number of files exceeded a million. They got better performance by moving them back to the dB. The explanation was that the OS FS was running low on file handles / pointers / resources. I guess it all comes down to which resource is the bottleneck. A dB usually want o store as much as possible in RAM so the advise on not storing large blob in dB make good sense.

    The sites I have been working with have had a few thousand images and attachments only and I have never seen any performance downside with dB storage. With larger sites I guess it would have to be evaluated on a case to case basis. Possibly a system with file system system storage is less sensitive to resource bottlenecks than a dB stored system. But until I see problems I will stick to database storage.

    Leave a comment:


  • anthonyparsons
    replied
    I think Wayne kind off really hit it actually, being the option depending on the outcome. I know off some forums that are extremely image heavy, ie. cartoon forums, sketching, drawing, photography, etc... these type forums do not run well when storing images in the database vs. filesystem, because you begin to talk about images that are 5 - 10Mb in size, and extracting them from the database in a loaded environment shuts down access to others under load. Just trying to get the pages loaded can be a headache when you have 500 users online all pulling massive images from the database. This is where the file system comes into its own.

    Quite honestly, tests I have done has seen little to marginal change if albums / images are rarely used, ie. very text chatty type forums. Honestly your better to just leave the minority of images in the DB under such cases. For image intensive sites, download type forums, absolutely disagree with anyone who says leave them in the database.... your going to kill your site if its busy by doing so.

    Leave a comment:


  • Videx
    replied
    Many hosts now limit db users. And site backups are hard enough to do monthly, so why would you want pics in the db and have to download them all daily? Unless you run a tiny forum, put them in the filesystem.

    Leave a comment:


  • ShyGuy82
    replied
    I can tell you from a practical stand point. I used to store files in DB, it was painfully slow. My site has about 12-15K unique users with about 120-150K page views and lots of downloads. It's basically impossible to have the attachments stored in DB with this kind of load. No need for benchmarks etc....As soon as I moved my attachments to the filesystem, the site started working normal again.

    Leave a comment:


  • janaf
    replied
    Originally posted by leo.prasanth View Post
    1) What are the Pros and Cons of using the two storage options - Database and Filesystem
    2) Going through couple of threads, I see that the database size increases significantly (or gets bloated) if attachments are stored in the database. Is this true ?
    1) I can think of a few, not related to vB
    • pro dB:
      • easy to manage by SQL scripting
      • easy to back up via database manager or scripting
      • less stress on OS
      • easier to check versions, duplicates, unused / unlinked attchments
      • easier to fix if things go wrong?
      • simpler rights management for web application
    • pro FS:
      • easy to manage manually, by OS scripting, by FTP, file manager or similar
      • easy to back up via file system
      • less stress on app (mySQL)
      • file system management is more intuitive
      • easier to fix if things go wrong?
    2) If you add 1GB of attachments, the dB will grow by 1GB and you will recover about 1GB from the file system, so net, no difference in space needed, not taking minimum block sizes in cosideration. If you are on a hosted environment you can run dB maintenance via ACP but not for the file system.

    3) Please post in another thread

    I think the performance would be dependent on what type of site you have. If you have a large number of files that are accessed more or less randomly, (bad for caching) or if you have a low number of files that are accessed all the time (good for caching). But what I can see from my limited tests, is that the performace difference is very small or none. Definitly smaller that I had expected from all that has been said about the performance advantages of disk storage.

    Leave a comment:


  • Wayne Luke
    replied
    Slow drives, overburdened shared hosting and small attachments will probably have better results In the database.

    Fast drives, VPS/Dedicated, files over 1 MB will probably be better in the file system.

    I say probably because you would need to run thousands of page loads to notice significant differences in most cases.

    Really the options are there to allow users to use the best environment for their situation. IF you're on VPS or Dedicated then in the long run you will be better off storing all uploads in the filesystem. If your on a shared host with any company besides 1 & 1 then you're better off in the database for the time being. 1 & 1 users should always use the filesystem even if performance is worse because of their abnormal 100 Megabyte database size restrictions.

    Leave a comment:


  • leo.prasanth
    replied
    Lynne,

    It would be helpful if you can help us with the below points:

    1) What are the Pros and Cons of using the two storage options - Database and Filesystem.
    2) Going through couple of threads, I see that the database size increases significantly (or gets bloated) if attachments are stored in the database. Is this true ?
    3) When I take "Manage attachments" to upload a new attachment, I see that some "question marks" are seein instead of attachments, I rebuild the cache, it worked, but again after after a couple of days, I'm seeing questions marks. I'm storing the images in the database.

    Answers to these questions can help a lot of people who have just setup a VB forum, to decide on the option that is better in the long run.

    - Thanks
    Leo

    Leave a comment:


  • janaf
    replied
    Lynne: this is precisely why I ask. I would not expect owners of large sites in operation to do this. I and probably others would like to know when setting up their sites, before they grow large and busy.

    The work time for moving files between dB and file system is marginal but I would not want to do it on a large site that is working. If it ain't broken don't fix it. What if things do go wrong.

    The default vBulletin setting is storage in the dB. IF there is no performance downside, I would keep it that way for backup, maintenance and scripting reasons. Others may of course still prefer file storage.

    If there are there no other comparisons done, I will do some more tests with different test tools and get back with whatever results I get.

    Leave a comment:


  • Lynne
    replied
    It's kind of difficult for most of us to test. What site owner that has a lot of attachments is going to go through the steps to put the attachments back into the database just to test this out? My site is running fine with them in the filesystem and I really have no reason to go move them back into the database just for a test.

    Leave a comment:


  • janaf
    replied
    Originally posted by MRGTB View Post
    There should be a difference.
    That's what everybody keeps saying but my question is if it is really so.

    The simulation program I use, Jmeter, is designed specifically for load stress simulations and can simulates loads from a number of different users at the same time. I was using 10 users doing an average up to an average of 20 page downloads per second which is not that low, equivalent to around 70.000 pages per hour. I could of course set it to 100 user each doing a call every few seconds and see if it makes a difference. Will do that tomorrow. I could also set it up on 4-5 machines doing the same thing, but it is still a simulation.

    A deacde or more ago, image BLOBS where often stored as raw bitmaps internally i databases and converted on inport or export Very inefficient. But now they are usually stored in their native JPEG, tiff or similar format. Much more efficient. There has been a lot of development in database performance.

    So again, I wonder if others have really tested or if thisis more of an old truth that keeps being repeated, but is not really valid any more? Any published results, test of any kind?

    Leave a comment:


  • MRGTB
    replied
    There should be a difference. Storing attachments in the database would have an effect on performance, compared to storing them in the file system (folder). Plus it will bloat the size of your database much faster as well.

    You need to think in terms of mass people calling attachments from the database, not just yourself. What would be the case if lets say, you had 100 people all calling attachments from the database at the same time?

    Leave a comment:

widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
Working...
X