8

Although this question has been appear in past previous post, but different scenario and different consideration decide which one is the best.

I need to implement a system whereby it can handle 200GB - 400GB size of images yearly(approximately < 1mb per image). It is P&C images which only allowed for authorised personal to access and VIEW only. I am planning to use an application based of system to INSERT to MYSQL database and using PHP web based application for VIEW only.

I am thinking to use FILESYSTEM because it is easy to do backup & restore on the images and no need to worry on the size of the MYSQL database.

I am using MySQL + Apache + PHP running in Windows Server.

Your advice and input is very much appreciated.

Thank you.

Regards, Desmond

Chris
  • 91
  • 1
  • 2
  • 4
  • Don't think anyone will recommend putting 400GB worth of images a year in a database. File System is the way to go mostly. – Hanky Panky Oct 30 '13 at 07:36
  • This sounds identical to many other questions asked and answered in the past about storing images in database vs. storing on filesystem. How is this question different? – Bill Karwin Oct 30 '13 at 07:41

2 Answers2

12

Also worth reading: Best Practice in File Storage while Building Applications - Database (Blob Storage) Vs File System

BLOB Storage as the Best Solution

  • For better scalability. Although file systems are designed to handle a large number of objects of varying sizes, say files and folders, actually they are not optimized for a huge number (tens of millions) of small files. Database systems are optimized for such scenarios.

  • For better availability. Database servers have availability features that extend beyond those provided by the file system. Database replication is a set of solutions that allow you to copy, distribute, and potentially modify data in a distributed environment whereas Log shipping provides a way of keeping a stand-by copy of a database in case the primary system fails.

  • For central repository of data with controlled growth. DBA has the privilege to control and monitor the growth of database and split the database as and when needed.

  • For full-text index and search operations. You can index and search certain types of data stored in BLOB columns. When a database designer decides that a table will contain a BLOB column and the column will participate in a full-text index, the designer must create, in the same table, a separate character-based data column that will hold the file extension of the file in the corresponding BLOB field. During the full-text indexing operation, the full-text service looks at the extensions listed in the character-based column (.txt, .doc, .xls, etc.), applies the corresponding filter to interpret the binary data, and extracts the textual information needed for indexing and querying.

File System Storage as the Best Solution

  • For the application in which the images will be used requires streaming performance, such as real-time video playback.
  • For applications such as Microsoft PhotoDraw® or Adobe PhotoShop, which only know how to access files.
  • If you want to use some specific feature in the NTFS file system such as Remote Storage.
Cléssio Mendes
  • 996
  • 1
  • 9
  • 25
9

objects smaller than 256K are best stored in a database while objects larger than 1M are best stored in the filesystem. Between 256K and 1M, the read:write ratio and rate of object overwrite or replacement are important factors.

souce: http://research.microsoft.com/apps/pubs/default.aspx?id=64525

Edit: It is MS SQL, so MAYBE same as Mysql :)

Eathen Nutt
  • 1,433
  • 5
  • 19
  • 27
  • the size of the image will be between 500K - 800K. Mostly only Write and Read. No need to overwrite the images. – Chris Oct 30 '13 at 07:56
  • 1
    It is up to you, with blob, images are easier to organize, delete etc, but as most people use file system, when you have problems, there will be more people to help you. – Eathen Nutt Oct 30 '13 at 08:31
  • am concern security issue on open up the folder for http access thru file systems. – Chris Oct 30 '13 at 08:59
  • people can get your images from file system in different ways, ftp, http, but for database, the only way I know is from select syntax. so maybe mysql is secuer, but it is a pain to backup blob – Eathen Nutt Oct 30 '13 at 09:50
  • I built a test website before, when there are more than 10 images extracted from database, sometimes, one or two of them failed to display, this confirmed that get images from database is slower. – Eathen Nutt Oct 30 '13 at 10:09
  • yeah...backup and restore blob is pain. speed for me still ok...i think i will choose if i want secure or i want easier backup/restore... really hard to choose – Chris Nov 01 '13 at 05:09
  • Go for filesystem, you will be happy with it, faster, more compatible, easy backup, less problems – Eathen Nutt Nov 01 '13 at 07:29
  • @Chris an old issue I know, but most web servers have the option to disallow folder browsing. If you enable disallow folder browsing, security issues should be pretty similar. Additionally, you can always store files away from the web server and have a script fetch them as and when required. Give files a temporary guiid name and make sure user is logged in if security is a real issue - e.g. for confidential documents. – dewd Jul 03 '15 at 11:38