Re: [PHP] Uploading Files Should I use MySQL or Server for storage?
On Sun, May 20, 2007 10:43 pm, Robert Cummings wrote: > On Sun, 2007-05-20 at 20:35 -0500, Greg Donald wrote: >> On 5/20/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: >> > I am in the process of adding a part to my website which would >> include >> > pictures, pdf files, txt files, and excel files. The files sizes >> > could be anywhere on average of 100k to 2mb. Do you think I >> should be >> > uploading the files to a MySQL database or to my server? >> >> >> http://www.zend.com/zend/trick/tricks-sept-2001.php?id=342 >> >> [snip] >> cuts performance by approximately a third >> [/snip] > > Sure, if you use database file storage in the naive way described in > the > document. But I'm quite certain a database stored binary file > dispensed > to multiple servers that keep a locally cached copy for subsequent > requests beats NFS retrieval hands down. Sure, you could do the same > caching with the NFS file but then the solution is quite likely just > as > good as the database storage solution. So the 1/3 performance penalty > is > for the naive solution. Or you could put all the images on a multi-million dollar content-distribution-network of image servers... There are so many ways to skin this cat and make up a "benchmark" to prove whatever you want to prove. So just do whatever makes sense to you for your application at the tim you do it, and accept the consequences, either way, down the road. -- Some people have a "gift" link here. Know what I want? I want you to buy a CD from some indie artist. http://cdbaby.com/browse/from/lynch Yeah, I get a buck. So? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Uploading Files Should I use MySQL or Server for storage?
On Sun, May 20, 2007 8:16 pm, [EMAIL PROTECTED] wrote: > I am in the process of adding a part to my website which would include > pictures, pdf files, txt files, and excel files. The files sizes > could be anywhere on average of 100k to 2mb. Do you think I should be > uploading the files to a MySQL database or to my server? > > I have head that there are pros and cons to both, but have never > really received a definitive answer that helps much. I appreciate all > your opinions on the pros and cons of both. We've pretty much beat this horse to death around here... So I'll try to give just one sample of each: DB PRO: Deleting image data can be simpler. DB CON: Difficult to scale out the DB when it has so much blob data; easy to set up "image server" to segment architecture when it's just a bunch of files to move and a line of PHP code to change the base URL. There are enough fanatics on both sides of this coin... I'm in the "put it in the file system" camp. -- Some people have a "gift" link here. Know what I want? I want you to buy a CD from some indie artist. http://cdbaby.com/browse/from/lynch Yeah, I get a buck. So? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Uploading Files Should I use MySQL or Server for storage?
On Sunday 20 May 2007, Robert Cummings wrote: > On Sun, 2007-05-20 at 23:11 -0500, Larry Garfield wrote: > > A well-optimized and load balanced database-based setup will beat a badly > > configured file system setup, sure. But will it beat a well-optimized > > and load balanced file system setup? I would be very surprised. > > > > Really, it comes down to this, assuming you know what you're doing either > > way. Using a database and PHP access script will add overhead to the > > process. Period. There's extra script execution time, database > > connection time, database read time, and pass-through time. Plus memory > > overhead on all of those, and coding/debugging time and effort. What you > > get in return is more places to programmatically control and log things; > > access-controls for whether or not a user is authorized to see a file, > > potentially more detailed access logs than you can get from simple apache > > logs, etc. > > > > Sometimes that trade-off will be worth it for whatever it is you're > > doing. Most of the time, it probably won't be. Decide based on what it > > is you're doing. > > Yep, I never said database was necessarily superior, only that anyone > with any skill whatsoever isn't going to be hammered by a 1/3 > performance penalty for using the database. As you said... it all > depends on what you're doing. As for all the penalties you mentioned > above, you may be incurring these anyways if you have any kind of > logical control of the files since you probably have to hit the database > for file access permissions, or meta information, etc, etc. On the request to generate the link, yes. But in the browser if it's given, say, an image URL, it has to make a new HTTP request back to the server to get whatever that URL is. If that URL is a PHP script that returns an image out of a database it will be slower than if it's a URL to a file sitting on disk. That's where the performance loss is. Whether or not that's a worthwhile trade-off is a case-by-case question. -- Larry Garfield AIM: LOLG42 [EMAIL PROTECTED] ICQ: 6817012 "If nature has made any one thing less susceptible than all others of exclusive property, it is the action of the thinking power called an idea, which an individual may exclusively possess as long as he keeps it to himself; but the moment it is divulged, it forces itself into the possession of every one, and the receiver cannot dispossess himself of it." -- Thomas Jefferson -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Uploading Files Should I use MySQL or Server for storage?
On Sun, 2007-05-20 at 23:11 -0500, Larry Garfield wrote: > A well-optimized and load balanced database-based setup will beat a badly > configured file system setup, sure. But will it beat a well-optimized and > load balanced file system setup? I would be very surprised. > > Really, it comes down to this, assuming you know what you're doing either > way. > Using a database and PHP access script will add overhead to the process. > Period. There's extra script execution time, database connection time, > database read time, and pass-through time. Plus memory overhead on all of > those, and coding/debugging time and effort. What you get in return is more > places to programmatically control and log things; access-controls for > whether or not a user is authorized to see a file, potentially more detailed > access logs than you can get from simple apache logs, etc. > > Sometimes that trade-off will be worth it for whatever it is you're doing. > Most of the time, it probably won't be. Decide based on what it is you're > doing. Yep, I never said database was necessarily superior, only that anyone with any skill whatsoever isn't going to be hammered by a 1/3 performance penalty for using the database. As you said... it all depends on what you're doing. As for all the penalties you mentioned above, you may be incurring these anyways if you have any kind of logical control of the files since you probably have to hit the database for file access permissions, or meta information, etc, etc. Cheers, Rob. -- .. | InterJinn Application Framework - http://www.interjinn.com | :: | An application and templating framework for PHP. Boasting | | a powerful, scalable system for accessing system services | | such as forms, properties, sessions, and caches. InterJinn | | also provides an extremely flexible architecture for | | creating re-usable components quickly and easily. | `' -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Uploading Files Should I use MySQL or Server for storage?
A well-optimized and load balanced database-based setup will beat a badly configured file system setup, sure. But will it beat a well-optimized and load balanced file system setup? I would be very surprised. Really, it comes down to this, assuming you know what you're doing either way. Using a database and PHP access script will add overhead to the process. Period. There's extra script execution time, database connection time, database read time, and pass-through time. Plus memory overhead on all of those, and coding/debugging time and effort. What you get in return is more places to programmatically control and log things; access-controls for whether or not a user is authorized to see a file, potentially more detailed access logs than you can get from simple apache logs, etc. Sometimes that trade-off will be worth it for whatever it is you're doing. Most of the time, it probably won't be. Decide based on what it is you're doing. On Sunday 20 May 2007, Robert Cummings wrote: > On Sun, 2007-05-20 at 20:35 -0500, Greg Donald wrote: > > On 5/20/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > > I am in the process of adding a part to my website which would include > > > pictures, pdf files, txt files, and excel files. The files sizes > > > could be anywhere on average of 100k to 2mb. Do you think I should be > > > uploading the files to a MySQL database or to my server? > > > > http://www.zend.com/zend/trick/tricks-sept-2001.php?id=342 > > > > [snip] > > cuts performance by approximately a third > > [/snip] > > Sure, if you use database file storage in the naive way described in the > document. But I'm quite certain a database stored binary file dispensed > to multiple servers that keep a locally cached copy for subsequent > requests beats NFS retrieval hands down. Sure, you could do the same > caching with the NFS file but then the solution is quite likely just as > good as the database storage solution. So the 1/3 performance penalty is > for the naive solution. > > Cheers, > Rob. > -- > .. > > | InterJinn Application Framework - http://www.interjinn.com | > | > :: > : > | An application and templating framework for PHP. Boasting | > | a powerful, scalable system for accessing system services | > | such as forms, properties, sessions, and caches. InterJinn | > | also provides an extremely flexible architecture for | > | creating re-usable components quickly and easily. | > > `' -- Larry Garfield AIM: LOLG42 [EMAIL PROTECTED] ICQ: 6817012 "If nature has made any one thing less susceptible than all others of exclusive property, it is the action of the thinking power called an idea, which an individual may exclusively possess as long as he keeps it to himself; but the moment it is divulged, it forces itself into the possession of every one, and the receiver cannot dispossess himself of it." -- Thomas Jefferson -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Uploading Files Should I use MySQL or Server for storage?
On Sun, 2007-05-20 at 20:35 -0500, Greg Donald wrote: > On 5/20/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > I am in the process of adding a part to my website which would include > > pictures, pdf files, txt files, and excel files. The files sizes > > could be anywhere on average of 100k to 2mb. Do you think I should be > > uploading the files to a MySQL database or to my server? > > > http://www.zend.com/zend/trick/tricks-sept-2001.php?id=342 > > [snip] > cuts performance by approximately a third > [/snip] Sure, if you use database file storage in the naive way described in the document. But I'm quite certain a database stored binary file dispensed to multiple servers that keep a locally cached copy for subsequent requests beats NFS retrieval hands down. Sure, you could do the same caching with the NFS file but then the solution is quite likely just as good as the database storage solution. So the 1/3 performance penalty is for the naive solution. Cheers, Rob. -- .. | InterJinn Application Framework - http://www.interjinn.com | :: | An application and templating framework for PHP. Boasting | | a powerful, scalable system for accessing system services | | such as forms, properties, sessions, and caches. InterJinn | | also provides an extremely flexible architecture for | | creating re-usable components quickly and easily. | `' -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Uploading Files Should I use MySQL or Server for storage?
On 5/20/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: I am in the process of adding a part to my website which would include pictures, pdf files, txt files, and excel files. The files sizes could be anywhere on average of 100k to 2mb. Do you think I should be uploading the files to a MySQL database or to my server? http://www.zend.com/zend/trick/tricks-sept-2001.php?id=342 [snip] cuts performance by approximately a third [/snip] -- Greg Donald http://destiney.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Uploading Files Should I use MySQL or Server for storage?
Thanks Rich. The files are not going to be downloaded all that often and the overall traffic is relatively low. However, as with probably everyone I am hoping and expecting big increases in traffic. I currently run most of my site off LAMP. My main concerns as you mentioned is using memory/CPU bandwidth. There will probably be far more files stored than accessed. Hope this helps everyone else with their opinions. On 5/20/07, Richard Davey <[EMAIL PROTECTED]> wrote: Hi benc11, Monday, May 21, 2007, 2:16:19 AM, you wrote: > I am in the process of adding a part to my website which would include > pictures, pdf files, txt files, and excel files. The files sizes > could be anywhere on average of 100k to 2mb. Do you think I should be > uploading the files to a MySQL database or to my server? > I have head that there are pros and cons to both, but have never > really received a definitive answer that helps much. I appreciate all > your opinions on the pros and cons of both. This isn't a 'one size fits all' question. The pros and cons are specific only to your site. How many uploads are you going to be dealing with, at what frequency, at what growth rate? Are they going to be massively downloaded too? There generally are far less 'pros' for storing binary files in MySQL than you'd think. The only real benefit imho is that they are then filesystem / platform agnostic. There are plenty of 'cons' however. Just think of the server overhead involved in your PHP script talking to MySQL, MySQL sending back the entire file to PHP (using memory / cpu bandwidth), then you've got to blast that file out to the end user. Repeat this X however much traffic you get and you're performing pointless exercises over and over when the web server could just serve the file directly. Only you can answer your question really. Cheers, Rich -- Zend Certified Engineer http://www.corephp.co.uk "Never trust a computer you can't throw out of a window" -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- ** The content of this e-mail message and any attachments are confidential and may be legally privileged, intended solely for the addressee. If you are not the intended recipient, be advised that any use, dissemination, distribution, or copying of this e-mail is strictly prohibited. If you receive this message in error, please notify the sender immediately by reply email and destroy the message and its attachments. * -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Uploading Files Should I use MySQL or Server for storage?
Hi benc11, Monday, May 21, 2007, 2:16:19 AM, you wrote: > I am in the process of adding a part to my website which would include > pictures, pdf files, txt files, and excel files. The files sizes > could be anywhere on average of 100k to 2mb. Do you think I should be > uploading the files to a MySQL database or to my server? > I have head that there are pros and cons to both, but have never > really received a definitive answer that helps much. I appreciate all > your opinions on the pros and cons of both. This isn't a 'one size fits all' question. The pros and cons are specific only to your site. How many uploads are you going to be dealing with, at what frequency, at what growth rate? Are they going to be massively downloaded too? There generally are far less 'pros' for storing binary files in MySQL than you'd think. The only real benefit imho is that they are then filesystem / platform agnostic. There are plenty of 'cons' however. Just think of the server overhead involved in your PHP script talking to MySQL, MySQL sending back the entire file to PHP (using memory / cpu bandwidth), then you've got to blast that file out to the end user. Repeat this X however much traffic you get and you're performing pointless exercises over and over when the web server could just serve the file directly. Only you can answer your question really. Cheers, Rich -- Zend Certified Engineer http://www.corephp.co.uk "Never trust a computer you can't throw out of a window" -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
