Home / server / Questions / 1034880
In Process
Michael restore Monica Cellio
Asked: 2020-09-23 03:31:33 +0800 CST

Storing BLOBs in mysql database

  • 772

There's a lot of opinion about storing BLOBs in a database and I wonder if there is any actual investigation or figures on the matter.

We have an application that stores images related to various entities such as user (their profile picture) and event (a photo of the event taking place). The application database table stores a file name such as 1234joesmith.png in a column called image and the our server-side code forms a complete URL from that to return to the front end, such https://ourapp.com/uploads/users/1234joesmith.png. (This is obviously a security risk and I will be dealing with that problem separately.)

This I am sure has been done to avoid placing BLOBs in the database and slowing-down access to it. However, it precludes an architecture where we have multiple server-side instances with a load-balancer or other redundancy middleware, all using the same database (which could then be an AWS database instance, for example).

My suspicion is that the advice to keep BLOBs out of the database is based on old technology and is obsolete. For example, a mysql table row is limited to a total of 4kb, but BLOBs and TEXTs do not count towards that total, except that each such column adds a fixed few bytes only. Clearly then the BLOBs and TEXTs are stored indirectly and not placed inline in the table row. Obviously therefore the designers of mysql have considered the matter of storing BLOBs and will have minimised any overhead, so that it is not significantly different from filesystem access (I suspect).

So my question: where are the figures, the actual figures? and not hand-wavy "I would have thought" speculations, which I can make as well as the next person.

performance mysql blob-storage

1 Answers

  1. For web pages, I suggest that the following answers the question:

    • Store images as files
    • In the database store the path to the image
    • Build the web page with <img src=...>
    • This lets HTTP and the network do the work, and do it somewhat in parallel. That is, it will probably speed up page loading and the user experience.

    To those who argue that the decoupling of the database entry and the image leads to broken links, etc., I say

    • It is a rare problem.
    • Store the image before INSERTing into the database. That way, the worst that might happen is an unreferenced image wasting disk space, not a "missing" picture.

    If you are not talking about images on a web page, then elaborate.

    As for some of your comments about MySQL:

    • The 4KB limit is bogus; ignore. Ditto for 64KB, etc. However, it is "hard" to store a BLOB bigger than 16MB.
    • You should use ENGINE=InnoDB
    • InnoDB has 4 "row_formats", each of which puts most or all of a big blob or text "off-record".
    • Fetching a image from a BLOB is messier (your code, MySQL code, etc) than using the img tag as discussed above. (In my opinion.)

    Hand-waving... Yes, this Answer contains some hand-waving. I counter it by saying that I have tried various implementations (including using base54 in the img tab) in multiple "products" with hundreds of web pages. In general, the responsiveness of the pages was "similar". I argue that this is usually the main criteria for judging this kind of operation (delivering web pages with images).

    I suggest that the main activity going on is copying a large blob from one place to another.

    Store in BLOB; use callback (`) (I use this technique if I want to modify the image as it is being delivered. Example: large image on disk, but web page needs only thumbnail.)

    • Web server launches a php child
    • App code (in php) connects to MySQL
    • BLOB is fetched into buffer_pool
    • php, with the aid of a suitable Header, declares that the output is an image
    • Send across the network
    • close MySQL connection and PHP child

    Base64 (I tried this for thumbnails)

    • Fetch from disk into buffer_pool
    • Copy to app (in php or whatever)
    • App converts to base 64
    • App builds img
    • Send across the network

    <img ...>

    • Your web server (already running) knows how to fetch the image file from disk
    • It sends it across the network.

    All of the above are subject to caching, at least from disk to RAM.

    • -1