2

This is a design related question.

I have a java web application(connected to MySQL database) running on tomcat. It runs on both linux and windows machines separately(as of now). I should store files which come as input, whose size varies from 500 KB to 100 MB (max). The use of these files is to parse them and get required data. I can use the 'blob' in database schema to store files, access them to generate the required output and store them in MySQL db. Else, I can use file system to store the files in the windows or linux filesystem and use them to extract the data, store it in MySQL db. In any month I will be saving close to 200 files of varying length. I am unable to decide which implementation to choose?

The following are some pros and cons which I came up comparing filesystem vs database.

File-System:

  1. automated indexing of files by OS.
  2. easier to access files.
  3. MySQL database that I use for other tables doesn't store huge amounts of data (200 files * 50 MB average = 10GB data).
  4. Varying lengths of the files doesn't matter much to the DB( the typical blob sizes for MySQL is, TINYBLOB: 255 bytes BLOB: 65,535 bytes (64 KB) MEDIUMBLOB: 16,777,215 bytes (16 MB) LONGBLOB: 4 GB So if my file size is >16MB I should store it as a LONGBLOB. which means for the size range I should be using LONGBLOB to store 500 KB file or a 100 MB file.

MySQL:

  1. Faster access.
  2. Single place for all the data. Data extracted from files and files are stored in one location.
  3. ..
Aspirant9
  • 163
  • 1
  • 13
  • you should go with 1st approach as in any month there may be increase in no of request as well as its easy for you to take backup from filesystem and as you already mention mysql limit – Key_coder Jun 23 '15 at 04:51
  • 1
    possible duplicate of [File Storage for Web Applications: Filesystem vs DB vs NoSQL engines](http://stackoverflow.com/questions/2890452/file-storage-for-web-applications-filesystem-vs-db-vs-nosql-engines) – Tim Biegeleisen Jun 23 '15 at 04:51
  • You may encounter a packet size limit when trying to `INSERT` or `SELECT` 100MB into/from a `LONGBLOB`. – Rick James Jun 23 '15 at 05:40
  • Essentially the only difference between the `BLOBs` is how many extra bytes (1 to 4) are allocated to hold the length. – Rick James Jun 23 '15 at 05:41

1 Answers1

0

Read https://dba.stackexchange.com/questions/2445/files-in-the-database-or-not

This is a good article too: http://www.revsys.com/blog/2012/may/01/three-things-you-should-never-put-your-database/

I think it depends on how much read/write activity you have. I'm personally in favor of keeping these files in the database specially if you have a fast database like MongoDB. You put a limitation of 100MG so it wouldn't get that large either.

Community
  • 1
  • 1
Peyman
  • 3,059
  • 1
  • 33
  • 68