-1

I have a sharing service, and I have 100,000+ files on one NTFS directory on Windows 2008 server.

The question is, about performance, is it better to add all the files to mysql blob?

Aminadav Glickshtein
  • 23,232
  • 12
  • 77
  • 117
  • That really depends on how these files are being used -- Are they constantly being written/read? By different users? – armani Feb 04 '13 at 20:29
  • 5
    no. files in a database are generally a stupid/bad idea. there's very few usage cases that call for this. if access time is getting to be a concern (e.g. all 100k files in a SINGLE directory), you could try splitting the load across multiple subdirectories, e.g. based on the first char of the file's name. But as with everything, you'll have to benchmark each solution and figure out which one is "cheapest". – Marc B Feb 04 '13 at 20:29
  • The size of the files can also have a massive impact. You probably do not want to store large files in the database. – ESG Feb 04 '13 at 20:30
  • This is image files, 200K-9MB each. – Aminadav Glickshtein Feb 04 '13 at 20:31
  • 2
    With that many files on a single server, you are likely to have IO problems if you get enough traffic. This is the case with a database too since a blob would be stored on disk. You will want to look at a distributed storage system (Something like amazon s3 or NAS if you are set on using physical hosting) – datasage Feb 04 '13 at 20:32
  • So what is the popular solution for using more than one server? – Aminadav Glickshtein Feb 04 '13 at 20:33

1 Answers1

1

Storing files on filesystem vs database is discussed in detail here. Storing that many files in one directory is definitely a bad idea. You might run into performance issues.

A better idea is to create subdirectories under that directory. They can be organized by:

  • Year/Month
  • First x characters of filename
  • First x characters of MD5 hash of filename
Community
  • 1
  • 1
Salman A
  • 262,204
  • 82
  • 430
  • 521
  • Do you know about any solution for creating the directoies and moving the files? I can do it for new files feature, but maybe there is a ready source code for that... – Aminadav Glickshtein Feb 04 '13 at 20:43
  • You can write a vbscript/jscript or powershell script for this. – Salman A Feb 04 '13 at 20:50
  • I assume you have some kinda *getPath(filename)* function in PHP, you should write a small PHP-CLI script, which calls it, e.g. *getpath.php myfile* echoes the path. So you can use *mkdir -p `getpath filename`* to create and *mv filename `getpath filename`/filename* to do the rest. – ern0 Feb 04 '13 at 21:17
  • 1
    Highy recommend the MD5 option, it will give you the best distribution. I found that the using the first 8 characters (split in groups of 2, e.g.: 11/22/33/44/) gave us the best performance – Evert Feb 04 '13 at 22:14