MySQL/InnoDB is a relational database management system which works best when a significant part of its data is on memory for fast retrieval. Traditionally, it has been said that you should keep only small data (integers, floats, smallish pieces of text) on MySQL and use the filesystem, or a dedicated blob/file storage for extreme cases. The reasons are several:
- Metadata and small data can be effectively indexed on a MySQL a table and accessed and searched quickly, but huge amount of text or binary data cannot (even fulltext indexes are limited in capabilities, and other string search engines are recommended)
- For a long time, small updates on large blobs meant that the whole field was updated and duplicated, even if a single bit was updated
- Large blobs tend to be stored off-row, losing some of the advantages of in-row storage
- In general, metadata tends to require very fast access (e.g. search a particular row among millions) while retrieving a large file is usually more lenient on latency
- MySQL/InnoDB caching system is not optimized for large IO/operations, making it potentially less efficient than the OS caching system. You want probably to save your buffer pool (MySQL application cache) for in-memory metadata retrieval, and don't cache or cache only once on the OS large files in a more simplistic way (different access patterns)
- Normally, MySQL query results are handled in-memory on the application, which is fast and nice for KB or results, but it would be highly inefficient for MB of files, which if they were stored separately of the filesystem or a dedicated blob storage, it would not affect the application's memory
- MySQL/InnoDB in general is optimized for small transactions, and you may find latency problems on IO operations and replication for very large transactions. That is even more true for distributed and clustered topologies. The need for ACID properties on files is usually non-existent of very low (files are normally added to the storage and have very low consistency needs)
- Backing up MySQL gets more and more difficult the more data you have due to consistency requirement. Backing up a directory of files is usually trivial.
For all those reasons and some more I may be forgetting, it is a common advice to store on the database only a reference of the files (e.g. name, path, hash) and store the actual files separately. You can break the rules, no answer is absolute (e.g. you will not hit a scalability problem with 10MB of small files), but that is the reason why filesystem/hadoop/ceph/S3/etc. are chosen and why files are cached/dealt with on a separate pipeline, to allow for future scalability.
Regarding some advantages:
- probably it will be a simpler setup (less technologies, less code, and less logic to handle distributed files- as that is all handled transparently by the db)
- It will be simpler to maintain consistency between data and metadata, as a single agent will be used to control both; not only for the application, also for things like backups
- Even if MySQL may not be the best technology, it may be better to keep it there than on the filesystem if you are handling millions or billions of small files, e.g. on a single directory