RE: When to store files in DB vs. File system
Hi. Everyone is going to tell you that you must store it in the database. The reason are that in different OS's, there is a limit to how many files you can store in each directory. (windows i think is 1). I was faced with this same problem under a time constraint, so never took the time to figure out how to store the file directly into the database. So i went the a directory storing route. What I did was Create a directory root\1\1a\1aa\file.extension like this. Set a limit to the amount of files in this directory and when that limit was reached incremented the directory name to root\1\1a\1ab etc etc. And then just stored the directory and filename in the database and retrieved it like that. I'm pretty sure that this is not the correct way to do this sort of thing, but it does work. I will be watching this mail to find out how to do it properly. -Original Message- From: Andy Timm [mailto:[EMAIL PROTECTED]] Sent: Monday, December 03, 2001 9:53 AM To: [EMAIL PROTECTED] Subject: When to store files in DB vs. File system Hello, I am developing a webapp where users need to store files (200k). I am wondering if these should be stored in the (mysql) database, or in the file system. I am concerned about the number of files I could get in the database (possibility for 20,000+ users, so 100,000 files). On the other hand, I plan on replicating my database across multiple servers. Do I then also replicate the download of each file, or run a batch that copies them periodically? I would greatly appreciate advice from anyone who has dealt with these design issues. Thank you, Andrew Timm __ Do You Yahoo!? Buy the perfect holiday gifts at Yahoo! Shopping. http://shopping.yahoo.com -- To unsubscribe, e-mail: mailto:[EMAIL PROTECTED] For additional commands, e-mail: mailto:[EMAIL PROTECTED] -- To unsubscribe, e-mail: mailto:[EMAIL PROTECTED] For additional commands, e-mail: mailto:[EMAIL PROTECTED]
Re: When to store files in DB vs. File system
I've seen a lot of implementations do this sort of thing, especially when archive emails and the like. I've also heard that people find many OS's start to slow down with even 1000 files in a directory, so I believe many such systems use that as an increment /1/000 - /1/999. A general piece of good advice is that databases excel at storing itty-bitty pieces of information, and files systems are good for storing a larger blocks. If you decouple the actual files from the rest of the database, you also have the opportunity to centralize that stored on a machine optimized for that purpose. If you need something to index those files, I'd have a look at Lucene ;-) http://jakarta.apache.org/lucene -- Ted Husted, Husted dot Com, Fairport NY USA. -- Custom Software ~ Technical Services. -- Tel +1 716 737-3463 -- http://www.husted.com/struts/ Darryl Nortje wrote: Hi. Everyone is going to tell you that you must store it in the database. The reason are that in different OS's, there is a limit to how many files you can store in each directory. (windows i think is 1). I was faced with this same problem under a time constraint, so never took the time to figure out how to store the file directly into the database. So i went the a directory storing route. What I did was Create a directory root\1\1a\1aa\file.extension like this. Set a limit to the amount of files in this directory and when that limit was reached incremented the directory name to root\1\1a\1ab etc etc. And then just stored the directory and filename in the database and retrieved it like that. I'm pretty sure that this is not the correct way to do this sort of thing, but it does work. I will be watching this mail to find out how to do it properly. -Original Message- From: Andy Timm [mailto:[EMAIL PROTECTED]] Sent: Monday, December 03, 2001 9:53 AM To: [EMAIL PROTECTED] Subject: When to store files in DB vs. File system Hello, I am developing a webapp where users need to store files (200k). I am wondering if these should be stored in the (mysql) database, or in the file system. I am concerned about the number of files I could get in the database (possibility for 20,000+ users, so 100,000 files). On the other hand, I plan on replicating my database across multiple servers. Do I then also replicate the download of each file, or run a batch that copies them periodically? I would greatly appreciate advice from anyone who has dealt with these design issues. Thank you, Andrew Timm __ Do You Yahoo!? Buy the perfect holiday gifts at Yahoo! Shopping. http://shopping.yahoo.com -- To unsubscribe, e-mail: mailto:[EMAIL PROTECTED] For additional commands, e-mail: mailto:[EMAIL PROTECTED] -- To unsubscribe, e-mail: mailto:[EMAIL PROTECTED] For additional commands, e-mail: mailto:[EMAIL PROTECTED] -- To unsubscribe, e-mail: mailto:[EMAIL PROTECTED] For additional commands, e-mail: mailto:[EMAIL PROTECTED]
RE: When to store files in DB vs. File system
I would recommend using the file system, and storing the location in the db: - the file system is faster - storing files in the DB tends to generate enormous database physical files, so back ups become an issue - integrating with a freetext search engine becomes harder (you may need this down the track) We have not seen issues with the number of files in a directory, although we also partition files across directories to minimize the number of files and avoid OS limitations (a simple way to do this is to use \MM\DD as the directory structure). vendor You may also want to check out: http://www.karora.com/DocConn.htm /vendor Gavin Terrill CTO, Karora Technologies Inc. http://www.karora.com -Original Message- From: Ted Husted [mailto:[EMAIL PROTECTED]] Sent: Monday, December 03, 2001 6:48 AM To: Struts Users Mailing List Subject: Re: When to store files in DB vs. File system I've seen a lot of implementations do this sort of thing, especially when archive emails and the like. I've also heard that people find many OS's start to slow down with even 1000 files in a directory, so I believe many such systems use that as an increment /1/000 - /1/999. A general piece of good advice is that databases excel at storing itty-bitty pieces of information, and files systems are good for storing a larger blocks. If you decouple the actual files from the rest of the database, you also have the opportunity to centralize that stored on a machine optimized for that purpose. If you need something to index those files, I'd have a look at Lucene ;-) http://jakarta.apache.org/lucene -- Ted Husted, Husted dot Com, Fairport NY USA. -- Custom Software ~ Technical Services. -- Tel +1 716 737-3463 -- http://www.husted.com/struts/ Darryl Nortje wrote: Hi. Everyone is going to tell you that you must store it in the database. The reason are that in different OS's, there is a limit to how many files you can store in each directory. (windows i think is 1). I was faced with this same problem under a time constraint, so never took the time to figure out how to store the file directly into the database. So i went the a directory storing route. What I did was Create a directory root\1\1a\1aa\file.extension like this. Set a limit to the amount of files in this directory and when that limit was reached incremented the directory name to root\1\1a\1ab etc etc. And then just stored the directory and filename in the database and retrieved it like that. I'm pretty sure that this is not the correct way to do this sort of thing, but it does work. I will be watching this mail to find out how to do it properly. -Original Message- From: Andy Timm [mailto:[EMAIL PROTECTED]] Sent: Monday, December 03, 2001 9:53 AM To: [EMAIL PROTECTED] Subject: When to store files in DB vs. File system Hello, I am developing a webapp where users need to store files (200k). I am wondering if these should be stored in the (mysql) database, or in the file system. I am concerned about the number of files I could get in the database (possibility for 20,000+ users, so 100,000 files). On the other hand, I plan on replicating my database across multiple servers. Do I then also replicate the download of each file, or run a batch that copies them periodically? I would greatly appreciate advice from anyone who has dealt with these design issues. Thank you, Andrew Timm __ Do You Yahoo!? Buy the perfect holiday gifts at Yahoo! Shopping. http://shopping.yahoo.com -- To unsubscribe, e-mail: mailto:[EMAIL PROTECTED] For additional commands, e-mail: mailto:[EMAIL PROTECTED] -- To unsubscribe, e-mail: mailto:[EMAIL PROTECTED] For additional commands, e-mail: mailto:[EMAIL PROTECTED] -- To unsubscribe, e-mail: mailto:[EMAIL PROTECTED] For additional commands, e-mail: mailto:[EMAIL PROTECTED] -- To unsubscribe, e-mail: mailto:[EMAIL PROTECTED] For additional commands, e-mail: mailto:[EMAIL PROTECTED]
RE: When to store files in DB vs. File system
Another issue to consider when evaluating file system versus database is synchronizing your database backup with the file system backup if you are keeping file state information in the database. I've managed a couple of document systems where the documents are in a file system and the state of the doc is in the database. It was difficult to do any type of incremental backup that kept the database and the documents in synch. We ended up do full backups everytime just to be safe. Jim Berg Sr. Software Specialist PSCI [EMAIL PROTECTED] Gavin Terrill [EMAIL PROTECTED] To: Struts Users Mailing List 03-Dec-2001 11:03 Please respond tocc: Struts Users MailingSubject: RE: When to store files in DB vs. File system List [EMAIL PROTECTED] pache.org I would recommend using the file system, and storing the location in the db: - the file system is faster - storing files in the DB tends to generate enormous database physical files, so back ups become an issue - integrating with a freetext search engine becomes harder (you may need this down the track) We have not seen issues with the number of files in a directory, although we also partition files across directories to minimize the number of files and avoid OS limitations (a simple way to do this is to use \MM\DD as the directory structure). vendor You may also want to check out: http://www.karora.com/DocConn.htm /vendor Gavin Terrill CTO, Karora Technologies Inc. http://www.karora.com -Original Message- From: Ted Husted [mailto:[EMAIL PROTECTED]] Sent: Monday, December 03, 2001 6:48 AM To: Struts Users Mailing List Subject: Re: When to store files in DB vs. File system I've seen a lot of implementations do this sort of thing, especially when archive emails and the like. I've also heard that people find many OS's start to slow down with even 1000 files in a directory, so I believe many such systems use that as an increment /1/000 - /1/999. A general piece of good advice is that databases excel at storing itty-bitty pieces of information, and files systems are good for storing a larger blocks. If you decouple the actual files from the rest of the database, you also have the opportunity to centralize that stored on a machine optimized for that purpose. If you need something to index those files, I'd have a look at Lucene ;-) http://jakarta.apache.org/lucene -- Ted Husted, Husted dot Com, Fairport NY USA. -- Custom Software ~ Technical Services. -- Tel +1 716 737-3463 -- http://www.husted.com/struts/ Darryl Nortje wrote: Hi. Everyone is going to tell you that you must store it in the database. The reason are that in different OS's, there is a limit to how many files you can store in each directory. (windows i think is 1). I was faced with this same problem under a time constraint, so never took the time to figure out how to store the file directly into the database. So i went the a directory storing route. What I did was Create a directory root\1\1a\1aa\file.extension like this. Set a limit to the amount of files in this directory and when that limit was reached incremented the directory name to root\1\1a\1ab etc etc. And then just stored the directory and filename in the database and retrieved it like that. I'm pretty sure that this is not the correct way to do this sort of thing, but it does work. I will be watching this mail to find out how to do it properly. -Original Message- From: Andy Timm [mailto:[EMAIL PROTECTED]] Sent: Monday, December 03, 2001 9:53 AM To: [EMAIL PROTECTED] Subject: When to store files in DB vs. File