3 ms·

I think, that is a rather complicated question. You should also take into account how accessing other data is affected by large blobs that reside in the same da

I think, that is a rather complicated question. You should also take into account how accessing other data is affected by large blobs that reside in the same database. For example: you have a table with large data blobs and other tables with conventional relational data. The blobs will take a lot of the cache size away from the other data. Also by increasing the database size, you will have potential longer access times, at least when you use spinning hard drives. So, several things to think about (make cache bigger, how much grows the overall db size) that can change the database behavior. Of course those numbers can give some directions, but since only the blob data was taken into account, it can not give full information. I personally would most of the time prefer smaller db sizes, at least, on edge cases. Relational databases are great for structured data -- unstructured data is not their specialty.

cefstat 12y ago
About 9 years ago I had a small site where both page content and some large PDF files were stored in the same SQLite database. Nothing extravagant: about 20 pages and 10 PDF files, each PDF file averaging 2-3 MBs. The website was extremely and consistently slow: it would take several seconds to load any page. I eventually figured out that the problem was the binary blobs. After moving the PDF files from the database to the filesystem the load times went below 0.1s. Since then I have not put large binary blobs in a database but I wonder if I would get reasonable performance by putting them in a _separate_ database instead of mixing them with other small-size rows.
- emn13 12y ago
  That's weird, and should not have happened. I've done similar things with sqlite databases many times that size, and though the FS might have been even faster, sqlite was still more than fast enough (as in, sub-10ms responses the norm). I suspect there's some way in which your access pattern or usage was suboptimal. Perhaps you had big reads with small writes in a transaction, or your network streamed directly from db leaving connections open a long time, or... something, because there's no way this should have taken so long.

cefstat 12y ago

About 9 years ago I had a small site where both page content and some large PDF files were stored in the same SQLite database. Nothing extravagant: about 20 pages and 10 PDF files, each PDF file averaging 2-3 MBs. The website was extremely and consistently slow: it would take several seconds to load any page. I eventually figured out that the problem was the binary blobs. After moving the PDF files from the database to the filesystem the load times went below 0.1s. Since then I have not put large binary blobs in a database but I wonder if I would get reasonable performance by putting them in a _separate_ database instead of mixing them with other small-size rows.

emn13 12y ago
That's weird, and should not have happened. I've done similar things with sqlite databases many times that size, and though the FS might have been even faster, sqlite was still more than fast enough (as in, sub-10ms responses the norm). I suspect there's some way in which your access pattern or usage was suboptimal. Perhaps you had big reads with small writes in a transaction, or your network streamed directly from db leaving connections open a long time, or... something, because there's no way this should have taken so long.