7

I know and understand that there are performance hits in storing blob data in the database, but the blob portion of the data is going to be rarely retrieved/viewed, it is for smaller data (the vast majority under 256k with a max of 10mb), it is not going to be used by most customers, and the total rows is expected to be relatively low, very likely under half a million, if not less. Also some of the data is dynamic and can change for some users, as in it's not static images. In other words we're at the edge of whether or not it's worth it.

I keep reading that it's better to store in the file system but I can't find actual metrics that show the performance difference, just people repeating each other without any concrete proof or metrics. For us it may be worth the performance cost in exchange for being fully ACID as well as guaranteeing that all our backups are completely synched.

That being said does anyone know or have any real world metrics to show the performance difference between storing items as blobs vs in the file system. I'm trying to understand if the performance penalty is worth it or not rather than blindly following the general rule of thumb and after spending at least 2-3 hours and I've yet to be able to see anyone show any actual numbers. It's all just words with nothing concrete.

By the way this is a MySQL InnoDB table. The actual data table has a link to the blob data table, so the blob is not in the main able and is only retrieved when need be to avoid any I/O issues. In other words instead of the path to the data on the filesystem, it's an ID to another table with only blobs. How does that compare in terms of performance? Is it 25% worse? Is it 100%? Is it 200-500%? Is it 1000%?

If the cost is only 100%-200% it is probably worth it for us because again the data is rarely retrieved. So even if we had say 10,000 concurrent users, maybe only 50 users would be retrieving their blob data concurrently at best. Yes the data is specific to each user, it isn't images.

1

The main cost of handling the data is the I/O. You are doing approximately the same amount of I/O, whether it is in 4KB chunks in the OS (plus directory traversal) or 16KB chunks in InnoDB (plus indirect block lookup).

The filesystem and InnoDB are cached in radically different ways; this may factor into a difference -- depending on how cacheable the blogs are.

You say "rarely retrieved". So why does speed matter?

So, I doubt if it will be more than 25% difference. And I can't predict which will be faster.

As for space, again there are several differences, so it is hard to predict which would be tighter. In any case, the diff can't be more than about 2% for the size blobs you mentioned.

How compressible are the blobs? (Most image formats are already compressed; text is typically compressible 3:1.) If compressible, then do so in the client. (InnoDB's builtin optional compression is easier, but not as good.)

And, yes, having it in a "parallel table" (as you mentioned) is often better.

Another point -- If the blob is an image destined for a web page, it is more efficient to simply have it in a file and say <img srg=file-path>. If it is in a table BLOB, you have to do extra work to hand it off to the web page. Since I/O is the main difference, I might expect the img tag to be 2x faster.

  • Performance matters for me in that I don't want the whole database to come to a crawl if a blob is retrieved. Unfortunately some databases have extremely poor performance in regards to blobs. That being said do you have any blogs or websites you can point to that have run tests to confirm your assumption? I can't find anything with any concreteness... – Stephane Grenier Jul 8 '15 at 16:06
  • 1
    I have used BLOB for images, compressed text, and other things for various projects. Alternatively, I have also used urls to images. I did not find "extremely poor performance". One argument in favor of BLOB is that the "file" (table) containing the BLOB is already open, whereas the other approach needs to locate the file and open it. File opening, especially on Windows, can be slow. – Rick James Jul 8 '15 at 16:41
  • 1
    @StephaneGrenier - I added a point. – Rick James Oct 17 '18 at 22:08
0

The biggest issue what happens when BLOB stored in db - It queries like:

SELECT * FROM blob_table WHERE range

even if it after WHERE return just few rows, but server will operate with huge size of data.

Solution - split table for:

  • PK and most often searchable columns
  • table with BLOB and FK columns

or just properly handle all queries:

include in column list only really necessary column, request BLOB data after by second request with access by PK

by the way add second biggest issue when BLOB stored in db -

  • increase size of dump
  • and (really same reason) - increase time of operations like Optimize table
0

By the way this is a MySQL InnoDB table. The actual data table has a link to the blob data table, so the blob is not in the main able and is only retrieved when need be to avoid any I/O issues. In other words instead of the path to the data on the filesystem, it's an ID to another table with only blobs. How does that compare in terms of performance? Is it 25% worse? Is it 100%? Is it 200-500%? Is it 1000%?

  • From the programmer's perspective, it can be 100,000,000 percent worse. Or even a billion times that bad. Blobs don't return file handles. This is an upcoming feature of the BLOB Locator API. That means you have no ability to seek. PostgreSQL provides bytea (the equivalent of blob), and large_objects: the large_object type is close to the Locator API. The lack of actually being able to retrieve a file handle makes working with a server-side api, or building a front end a ton of fun! Imagine HTTP where every partial download request from the client, required a full BLOB retrieval from the server -- now this can be yours!

  • Not just can you not seek, but you can't receive the unbuffered blobs through the client library in C, from the docs

    The communication buffer must be large enough to contain a single SQL statement (for client-to-server traffic) and one row of returned data (for server-to-client traffic). Each session's communication buffer is dynamically enlarged to handle any query or row up to the maximum limit. For example, if you have BLOB values that contain up to 16MB of data, you must have a communication buffer limit of at least 16MB (in both server and client). The default maximum built into the client library is 1GB, but the default maximum in the server is 1MB. You can increase this by changing the value of the max_allowed_packet parameter at server startup. See Section 5.1.1, “Configuring the Server”.

    Also from the docs,

    max_allowed_packet You must increase this value if you are using large BLOB columns or long strings. It should be as big as the largest BLOB you want to use. The protocol limit for max_allowed_packet is 1GB. The value should be a multiple of 1024; nonmultiples are rounded down to the nearest multiple.

  • Also the server may apply general storage policies to the blob under the hood, that may include compression overhead.

  • You also say that the data is dynamic. You acknowledge it's ACID compliant. Do you understand when that blob changes you'll be rewriting the entire row? The very process of row-generation and writing to the heap the non-blob components is not free and should be viewed as overhead if all you need to do is update the blob.

So yeah, there is a lot of overhead and downsides. As a general practice, I never suggest this.

  • I do not see the advantage of a "BLOB locator" if the only use is to fetch an image, presumably for display purposes. – Rick James Oct 17 '18 at 22:01
  • There are lots of reasons why you may want to do this. Not least of which is for progressive/interlaced rendering. And even more reasons if you're modifying/updating the image, or rendering a specific channel etc. – Evan Carroll Oct 17 '18 at 22:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.