3

I'm in the process of designing a server responsible for serving files that are between 10MB and 50MB in size.
Initially we will run two instances of the server (lets call them fs1 and fs2), with future plans to switch to a micro-service architecture, where the server instances will grow or shrink depending on the load.

These two instances need to interact with a third server running a scheduler and a file management application, as well as a database (on another server) where some metadata will be saved for clients to use.

My initial thoughts where to use a rabbitmq to allow the fs1 and fs2 to communicate with each other and the management app. the process would work as follows:

  1. The management app uploads to fs1 server (could be either fs1 or fs2)
  2. fs1 notifies fs2 and the management app when upload is complete
  3. fs2 contacts fs1 and stores a copy of the file
  4. fs2 notifies the management app when upload is complete
  5. The management app saves metadata to the external database
  6. both fs1 and fs2 can now server the files when requested

This seems OK, if there are only two instances, but once you start adding more it doesn't work.
Our ops department are very much against the idea of using the database to store files. They are worried that it will slow down the system too much. I agree it might, which is why I want a separate database for the specific purpose of storing the files and metadata. I want to build something like the following:
system diagram

My thinking is that the upload service can manage uploading of files and saving of metadata to the database. When the scheduler schedules a new job, the upload service (badly named, I know, but I'm not making that image again :-) ) can notify the file server instances that they need to cache the required file(s) from the database, which they can access directly.
The file servers won't need to cache more than 5 or 6 files each at a time.
Also, in the diagram I missed that the file management service will receive download progress messages from both file servers.

So to my questions:

  1. Is this a reasonable way to store files of this size for serving?
  2. Is this the right way to be thinking when considering the move to microservices in the future?
  3. Are there advantages to storing the files on the file system of each fs instance instead of just caching?
  4. How can I convince our ops team that storing 50MB files in a database is the way to go? what are the pros and cons?
  5. Any other thoughts or comments appreciated.
  • I don't know why, but my gut feeling is that there is something very bad about this idea that I'm missing. – T. Sar Feb 9 '18 at 10:44
  • 1
    I'm not sure. I'm thinking a bit more about this, but every idea I had revolves around using a database with proper file-management techniques or just storing the filepaths in the database and the files somewhere else. I'm really not sure, but every time someone says "storing files in a relational database" my spine shivers and my skin gets all riled up, as if someone tried to summon chtullhu or worse - like parsing HTML with regex. – T. Sar Feb 9 '18 at 11:02
  • 2
    Because binary files have nothing to do with relational data. On the other hand, you didn't mention what to do with DB backups and recovery policies. IMO, files are better stored, managed and versioned in file systems. – Laiv Feb 9 '18 at 15:24
  • 1
    That's fair enough. – Laiv Feb 9 '18 at 20:03
  • 2
    Might this be a good time to remind you all that the correct answer (mine :D ) suggests the simple solution and continuing to collect data to make longer-term decisions with facts! ... You know, instead of spending time making important decisions based on fears, horror stories, and misty-eyed philosophies ... – svidgen Feb 9 '18 at 21:34
7

NO, don't store files in a relational database

Trust me, I've learned this the hard way. One problem with applications that deal with files, is as they evolve, the users always want to store more than the application was intended to handle.

I once created an application with a document storage component meant to store Word and Excel documents. The storage component was useful enough that eventually people started storing videos in it.

I mention this because, the performance implications will be higher than you expect; this leads me to my next point.

Even if a database can handle files fine (filestream type) scaling a DB is hard, it is always the hardest part to scale. Let the db concentrate on saving and retrieving data, that way you can put off scaling it as long as possible. If your DB is busy serving a large file, those are resources not being used to serve transaction and lookup requests; its bread and butter.

Server to Server synchronization does not scale well

Your system seems over-complicated to me, I would go with a simpler design. The problem with servers fs1 and fs2 talking to each other is, as you scale, the number of paths increases exponentially.

With two servers, each server only has to ask make one synch request, for a total of 2 paths. 3 severs, there are a total of 6. With 5 servers there are 20. synchRequests = (n-1)*(n); n = number of servers

I would simply have a dedicated DB server, and a dedicated File server that the FSn servers talk to to. If you need more complex synchronization behavior, add a dedicated Redis serve in the mix to serve as the single source of truth for non-persistent details.

The point is, don't have fs1 talking to fs2, or vice-versa, this will not scale.

Graph

                             [ fs1 ] [ fs2 ] [ fs3 ] [ ect ]
                                |       |       |       |
                                +-------+---+---+-------+
                                            |
                         +------------------+-------------------+
                         |                  |                   |
                      [ RDB ]           [ Redis ]           [ Files ] 

The best of both worlds?

You can head off most of the disadvantages of storing your files in a RDB, and still get most of the advantages by segregating a completely separate DB instance and storing only your files there. This is a viable option if you don;t want to setup and maintain a file server.

A quick word about microservices

I am not sure why you would want to go the microservices route. The original intent of microservices is to get around political problems, not technical problems. For example, the server admin refuses to open any ports other than 80.

  • 1
    "This went horribly wrong for me once" stories are no justification for something never bring the best-fit solution. I once decided to store my files into the filesystem and referred to them in the database and it went horribly wrong! (Ono!) ... True story. In fact, it went horribly wrong several times!!! The added complexity of running two user-data stores (file + db) with fundamentally different permissions, replication, and backup models led to lots of extra work -- and a handful of data consistency issues. ... Whose horror story wins? – svidgen Feb 9 '18 at 21:28
  • A clarification about microservices -- It may be true in your organization that microservices are politically-driven. But, this is not universal. Some organizations must use microservices due to complexity, performance, and availability requirements ... and so on. ... Not to mention the human management and dependency management nightmare. ... Still, that said. I wouldn't make a sweeping recommendation either way. Monoliths can be loads easier to manage ... until they're suddenly not. – svidgen Feb 9 '18 at 21:48
  • Thanks for your input. My solution above is a stopgap, there will only be two server instances running, no more. I totally agree that it doesn't scale well. That's why I was looking at the database, so they could access files directly. Once a file is saved to the database as message is pushed to the bus and the servers can cache the file if required. That might be straight away, or it might be when a particular job has started. – bot_bot Feb 12 '18 at 7:16
  • @svidgen please read Bob Martin's Clean Architecture chapter addressing micro-services. The problems you face with a large monolith are the result of a lack of proper boundaries, not the lack of micro-services. – TheCatWhisperer Feb 12 '18 at 15:50
  • 1
    This is going to be DB specific but in Oracle, here are a couple of real issues that come with LOBs (over 4K): table space is not freed when LOBs are deleted, you must re-org or drop the table which basically means going off-line. Connection specific server memory may be used, you are responsible for freeing it (surprise!). If you use too much, the server may become unavailable for other users. – JimmyJames Feb 12 '18 at 17:16
2

Obviously, you have some options but there are also trade-offs. To answer your questions:

Is this a reasonable way to store files of this size for serving?

The answer of that really depends on your database and the recommendations for that. For example SQL Server has good support for arbitrary sized files if you use the FILESTREAM option (or something that in turn makes use of it like FileTable). But you'll get a different answer with something like Sqlite that can run into problems if the overall database file gets too large.

If you are hosting in the cloud then the best bet is to make use of the blob store available by your provider. You can even use something like the JCloud library (from Apache) to abstract the actual cloud provider from the process of storing and retrieving files from the blob store. AWS calls it S3, but all providers have some sort of blob store that is geared for serving directly.

Is this the right way to be thinking when considering the move to microservices in the future?

Probably not. Think of microservices as complete and autonomous entities. Whether you use S3, a simple file store, or a dedicated database.

By creating something like a "Blob Store" microservice, you can use hashes to prevent duplicate files. If two people upload the same file by different names or paths, you could be reasonably sure there is only one copy of it in your blob store. You would store your file by the hash for the filename, and if necessary you could put metadata in a JSON file that is stored with the same filename but a .json extension. Or your metadata is looked up by hash.

Are there advantages to storing the files on the file system of each fs instance instead of just caching?

Not as much as using S3 which hides away the whole caching/storage distribution problem so you don't have to solve it.

However, the file system allows you to have greater control over how your files are stored, allowing for encryption and/or compression at the service level if you need it.

How can I convince our ops team that storing 50MB files in a database is the way to go? what are the pros and cons?

Depends on:

  1. How many files we are expecting
  2. How fast it grows
  3. Cost of indexing

The more records you are dealing with the higher your risk of bad locking events slowing down your system.

The main killer feature of a database is that backup and restore includes the file content as well as the metadata. It's great for disaster recovery, but beyond that the advantages start diminishing fast.

Using cloud storage basically mitigates most disaster recovery problems with a better support story than traditional databases. You'll still need the offsite archive for full disaster recovery, but that's the same requirement if you had a database.

A plain file-system is most disadvantaged here, but you can stand up Hadoop File System (HFS) and get a cloud blob store within your own network. So there are some options I don't think you've explored yet.

Any other thoughts or comments appreciated.

It sounds like you are moving more to a distributed cloud based solution, but still thinking in traditional development terms. I get it, I am still going through the learning curve with the job I'm in now. We opted for a Blob Store microservice that uses cloud provided storage. That allows us to prevent storing duplicate data, etc.

I'm also supporting a legacy app which had to change how it is storing files in the database so that we could handle larger files. Changing how files are stored in a database, complying with the DB manufacturer's recommendations is a lot more implications than you might think.

I think you will outgrow the file database sooner rather than later.

  • You are correct, we are moving to a more distributed solution, what I'm working on now is a stop gap to get the required functionality up and running for other teams that need it now, then next year we'll most likely be rewriting it to fit a micro-services architecture. This will be on our own servers for now, but maybe cloud in the future. – bot_bot Feb 12 '18 at 7:21
  • When I say may be on the cloud in the future, I mean after a rewrite – bot_bot Feb 12 '18 at 7:34
  • @bot_bot, you can stand up Hadoop servers for HFS support now, and that will take care of the distributed file system with redundancy. That vastly simplifies your design. – Berin Loritsch Feb 12 '18 at 13:14
1

Storing files in databases can be done, but all (relational) DBMS I am aware of are generally designed to store small pieces of data per entry. You can of course set up an additional DBMS system where only the files are served from (some performance tuning is really recommended then), but in the end Filesystems are already designed to store files, regardless of their size.

So I'm wondering if you have thought about using a distributed filesystem as I believe this would be much easier and more performant. There are many of those systems out there like

Ceph or

LizardFS

to name just a few.

You could then simple expose the distributed FileSystem to the AppServer where it can be treated as a local volume while providing as many storage capacity as needed (those distributed filesystems scale very well).

Hope this helps

  • 2
    Oracle, PostgreSQL, MySQL, MS SQL Server, DB2 and even SQLite would beg to disagree with the assertion in your first sentence. – Blrfl Feb 9 '18 at 18:30
  • @Blrfl You can take a pickup truck, lower it, turbocharge it, ect, and it will go fast, but it's still a pickup truck, not a sports care. If you wanted performance, you money would have been better spent on a sports car instead of modifying the truck. – TheCatWhisperer Feb 9 '18 at 18:41
  • 1
    @TheCatWhisperer The second sentence in your answer explains why you had difficulties: the requirements changed and the application and infrastructure didn't. That's like grabbing the keys to the compact car you usually use to deliver envelopes after being told that your next dozen trips are going to be for two pallets of bricks. I've done binary file storage on two of the RDBMSes mentioned in my comment; it worked just fine once everything (hardware included) was spec'd and tuned to do the job. – Blrfl Feb 9 '18 at 19:45
  • @Blrfl that is a good point. However, just not storing it in the RDB in the first place just makes things easier. – TheCatWhisperer Feb 9 '18 at 19:50
0

Sure, try it.

Most databases support text and blob types for precisely for the purpose of storing large blocks of "unstructured" data. So, most DBMS's have already made considerations for your use-case. But, leave this aspect of your application open to configuration.

Ideally, ensure that metrics are in place to monitor anything that might change negatively — both as a result of this and any other change in usage patterns. Things like CPU utilization, disk IO, network IO, latency, etc.

If you have data to show the impact of your changes and your application is open to configuration, you can have factual conversations about the impact of your decisions — and you can pivot quickly when you make a bad decision.

With data in hand, the opinions of a bunch of strangers on the internet is properly meaningless, and you and your colleagues can have rational, productive conversations.

  • It's pretty sad that the answer which suggests gathering actual data on the impact of doing this before making a decision is getting downvotes. – Blrfl Feb 9 '18 at 18:41
  • @Blrfl it not a matter of raw performance. A db is capable of serving files without any difficulty. The issue is, as we need to scale, the resources being used for file serving are not available for other purposes. – TheCatWhisperer Feb 9 '18 at 19:32
  • @TheCatWhisperer OP's requirements for scale are...? – Blrfl Feb 9 '18 at 20:04
  • 2
    @TheCatWhisperer Not only is a modern DBMS capable of serving files, as my answer notes, there are design considerations built right into the darn things for just that sort of purpose! ... I've advocated for trying it in the DB and measuring the impact. If there is no impact, there is no issue. If there is an impact, the demand for new infrastructure is supported by data -- rather than by vague fears and advice from strangers on the internet (running completely different applications in completely different environments and circumstances, mind you). – svidgen Feb 9 '18 at 20:44
  • The problem here is that even though many relational DBs have support for LOBs, that doesn't mean that it's worth using. Recently a colleague of mine discovered that Oracle will sometimes elect to start using memory on the server to manage blob interactions because of something something. The really fun part is that it doesn't tell you in any obvious way that it's doing this and once it starts, it's up to owner of the database connection to call a 'free' routine. Otherwise you can exhaust all the memory on the server. This is like telling someone to experiment with eating arsenic. – JimmyJames Feb 12 '18 at 17:09

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.