1

I have an application where I have audio files. I need to store them in a mysql table since we already have a mysql database available.

I saw that some people don't advise to do so. What are the advantages and inconvenients of storing the audio files in the table?

Does it affect the operations performed on the database?

2

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
| improve this answer | |
2

I have audio files ... I need to store them in a mysql table since we already have a mysql database available.

"Just because it's there" is not a Good Reason to use a database.

How do you want to store the data?

That's always based on how you're going to use it.

What are you going to do with these audio files?
I assume you're going to "present" them to the Users so that they can "play" them. You can't [easily] do that from "inside" a database. You can do it very, very easily if the files are lying around in a file system, just waiting to get read.

This is why people don't advocate putting files like this into databases. There's an overhead in getting them into the database, and another overhead in getting them out again (usually into a file) and you don't gain anything from their being inside the database. You can't query the "contents" of an audio file with SQL; you have to extract that metadata and store it separately (i.e. in separate columns).

| improve this answer | |
  • While I agree with your answer in general, actually "Just because it's there" can be a reason. For example, imagine op wants to store 1MB of blobs because they are only 100 midis, and has huge ACID requirements (yes, I know, unlikely :-D). Probably in other context, introducing more technologies may not be the most important reason to do something, but it can be a reason (simplifying workflow). For example, I use MySQL for disk-based cache on my infra- it is not a good technology for that, but I have other 200 mysql servers so it avoids an extra new stack for the low requirement I have. – jynus May 15 '19 at 18:20

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