I ran query caching and stored files in innodb engine with mysql and never had this issue. It was quite snappy.
- Sean Thayne On Sep 22, 2009, at 9:32 PM, "Joel Daynes" <[email protected]> wrote: > One other thing to consider with keeping files in the database is (at > least in the MySQL world), the effect of keeping these potentially > large > files in the query cache and innodb buffer pool. For example, the > default > size of the InnoDB buffer pool (the slice of RAM where InnoDB holds > data > and indexes for faster access) on many MySQL installs is 8MB. If you > are > storing large files in the database and haven't increased the pool > size, > you can quickly chew up that buffer pool, requiring other queries to > go to > disk because your files are clogging up the pool. A similar situation > would be encountered with the query cache--you could quickly consume > your > whole query cache with large files, sending more small queries to > disk, > and hurting performance. > > In my opinion, it's almost always best to use tools for the tasks > they're > designed and optimized for--filesystem for files, databases for > data, etc. > There may be some corner cases where you'd be better off with files > in the > database, but I'm having a hard time coming up with one. > > Joel > > >> Hey, >> >> For that project, I ended up just keeping them in a directory. I did >> do a large media project in the past thou that stored 20mb< files >> in a >> db and kept larger files in a plain directory structure... They were >> both access via the same php script that would figure where the file >> was stored, both ways performed very similar. >> >> The key argument against this practice was the slow or corrupt >> backups. Also some db servers (postgres) have been known to easily >> become corupt with 10mg> files. >> >> >> - Sean Thayne >> >> On Sep 22, 2009, at 5:40 PM, Bob kane <[email protected]> >> wrote: >> >>> Yeah I saw the thread a couple weeks ago on this list that was >>> debating >>> using a database to store files. I've always wanted to do it but >>> from what >>> people have said it seems that it's bad to use it for large files >>> (some of >>> mine will be over 100 megs). In the end of that thread was a post >>> that was >>> heavily against this practice but was written in 2001. Other people >>> said >>> that replication could be slow or doing backups. However I'm using >>> the ZFS >>> filesystem which i'm learning but seems very cool so far and it does >>> replication very well. it's also a pretty beefy machine with 24 gigs >>> of ram >>> and 8 cores. I'm still hesitant though with the talk of mysqldump >>> will fail >>> on large data sets. In my case i'd take security over performance >>> but not >>> security over data loss. I'll probably be going with the directory >>> approach >>> but If Sean Thayne* *is reading from that original thread, what >>> approach did >>> you end up taking for your large files? If you did store them in the >>> DB, how >>> was the performance? Thanks if you see this. >>> >>> >>> On Tue, Sep 22, 2009 at 4:05 PM, thin <[email protected]> wrote: >>> >>>> As already mentioned, there are approx 1,000 ways of tackling this >>>> issue. One that has not been mentioned yet [and will certainly be >>>> received with varying opinions] is to store the files in a DB. That >>>> would make the work of securing them nearly effortless and could >>>> easily be extended to also allow of dynamic password generation and >>>> various user levels/permissions, etc - all with very little coding! >>>> >>>> good times with files in databases. provide special password, and >>>> i'll >>>> give you a file all right!!! :) >>>> >>>> >>>> >>>> On Tue, Sep 22, 2009 at 4:44 PM, Bob kane <[email protected]> >>>> wrote: >>>>> Thanks Will and everyone else, i'm gonna tinker around and see >>>>> what i >>>> come >>>>> up with. >>>>> >>>>> On Tue, Sep 22, 2009 at 3:40 PM, William Attwood >>>>> <[email protected]> >>>> wrote: >>>>> >>>>>> Sort of - basically, Yes. With Apache, you can modify access >>>>>> rights. >>>>>> However, the PHP script does not need the folder to be in the >>>> DocumentRoot >>>>>> to access the files and feed them. >>>>>> >>>>>> Example: >>>>>> >>>>>> DocumentRoot = /var/www/site/httpdocs >>>>>> FileDirectory = /var/www/site/files >>>>>> >>>>>> In your PHP, you'll tell it to go to /var/www/site/files/ >>>>>> filename.ext >>>> and >>>>>> feed that, using whichever method as covered, here. >>>>>> >>>>>> PHP can feed files outside of the DocumentRoot. >>>>>> >>>>>> -Will >>>>>> >>>>>> On Tue, Sep 22, 2009 at 4:39 PM, Bob kane >>>>>> <[email protected] >>>>> wrote: >>>>>> >>>>>>> Yes but the folder i'm referencing in the php script has to be >>>>>>> outside >>>>>>> the >>>>>>> documentroot else someone can visit the files with a direct link >>>>>>> right? >>>>>>> >>>>>>> >>>>>>> On Tue, Sep 22, 2009 at 3:29 PM, William Attwood <[email protected] >>>>>>>> >>>>>>> wrote: >>>>>>> >>>>>>>> Hey Bob-- >>>>>>>> You don't need to map to a folder outside of the >>>>>>>> DocumentRoot. If >>>>>>> you >>>>>>>> use PHP (server side execution) you reference that folder in >>>>>>>> your PHP >>>>>>>> script, and as Beau and Mac stated, just stream the data to the >>>>>>>> end >>>>>>> user. >>>>>>>> This keeps everything secure. >>>>>>>> >>>>>>>> --Will >>>>>>>> >>>>>>>> >>>>>>>> On Tue, Sep 22, 2009 at 4:23 PM, Bob kane <[email protected] >>>>>>>>> >>>>>>> wrote: >>>>>>>> >>>>>>>>> ---------- Forwarded message ---------- >>>>>>>>> From: Bob kane <[email protected]> >>>>>>>>> Date: Tue, Sep 22, 2009 at 3:23 PM >>>>>>>>> Subject: Re: [UPHPU] Secure PHP file serving. >>>>>>>>> To: Beau Scott <[email protected]> >>>>>>>>> >>>>>>>>> >>>>>>>>> Thanks for the responses guys. Yeah it would not be okay for >>>>>>>>> someone >>>> to >>>>>>>>> retrive the file if they know the link. I've just stumbled >>>>>>>>> upon >>>>>>> somthing >>>>>>>>> called open_basedir in php.ini that (i think) you can use this >>>>>>> directive >>>>>>>>> to >>>>>>>>> map directories that are allowed to load files outside the web >>>>>>>>> root. >>>>>>>>> Anyone >>>>>>>>> ever use this? I'm almost on my way though with these answers, >>>>>>>>> much >>>>>>>>> appriciated. >>>>>>>>> >>>>>>>>> >>>>>>>>> On Tue, Sep 22, 2009 at 3:14 PM, Beau Scott <[email protected] >>>>>>>>>> >>>>>>> wrote: >>>>>>>>> >>>>>>>>>> readfile() is the easier approach to this if you're not >>>>>>>>>> concerned >>>>>>> with >>>>>>>>>> utilizing HTTP Content-Range headers ( >>>>>>>>>> http://us.php.net/manual/en/function.readfile.php). It writes >>>>>>>>>> the >>>>>>>>> content >>>>>>>>>> of >>>>>>>>>> the file directly to the output buffer, avoiding the memory >>>>>>>>>> limits >>>>>>>>>> altogether. >>>>>>>>>> >>>>>>>>>> However, if you're going to be serving even remotely large >>>>>>>>>> files, >>>>>>> You'll >>>>>>>>>> want to take a look at using Content-Range. This will allow >>>> clients >>>>>>> to >>>>>>>>>> break >>>>>>>>>> downloads up into manageable sizes and/or resume downloads. >>>>>>>>>> The >>>>>>> browser >>>>>>>>>> will >>>>>>>>>> cache the downloads in chunks, remembering which portions >>>>>>>>>> have >>>> been >>>>>>>>>> downloaded. It will then fill in the pieces as it needs by >>>> providing >>>>>>>>> your >>>>>>>>>> script with a content byte range. E-Tag headers will also be >>>> highly >>>>>>>>>> beneficial for you here. (there's an example of how to handle >>>> E-Tag >>>>>>>>> headers >>>>>>>>>> in the readfile() user notes.) >>>>>>>>>> >>>>>>>>>> Here's a high-level example of byte serving: >>>>>>>>>> http://www.coneural.org/florian/papers/04_byteserving.php and >>>>>>> there's >>>>>>>>>> another example in the readfile() user comments as well >>>>>>>>>> (search >>>> for >>>>>>>>>> SmartReadFile) >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> Beau >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> On Tue, Sep 22, 2009 at 3:56 PM, Mac Newbold <[email protected] >>>>>>>>>>> >>>>>>>>> wrote: >>>>>>>>>> >>>>>>>>>>> Today at 2:40pm, Andrew kain said: >>>>>>>>>>> >>>>>>>>>>>> Hello list, I am looking for the best way to serve secure >>>>>>> sensitive >>>>>>>>>> files >>>>>>>>>>>> uploaded to a PHP server. I only want authenticated users >>>>>>>>>>>> to >>>> be >>>>>>> able >>>>>>>>> to >>>>>>>>>>> view >>>>>>>>>>>> these files (jpg, pdf, etc). Usually anyone can view files >>>>>>> uploaded >>>>>>>>> to >>>>>>>>>>> any >>>>>>>>>>>> directory. I'm guessing the best way would be to upload the >>>> files >>>>>>>>>> outside >>>>>>>>>>> of >>>>>>>>>>>> the web root that way they are not directly accessable from >>>> the >>>>>>> web >>>>>>>>>>> server. >>>>>>>>>>>> My question is, what would be the next step? To >>>>>>>>>>>> authenticate >>>> the >>>>>>>>>> session >>>>>>>>>>> and >>>>>>>>>>>> mod re-write to direct the user to the secured area? Can >>>> anyone >>>>>>> with >>>>>>>>>>>> any experience with this please give some pointers? thank >>>>>>>>>>>> you >>>>>>> much >>>>>>>>> in >>>>>>>>>>>> advance. >>>>>>>>>>> >>>>>>>>>>> There are a variety of options, as William Attwood >>>>>>>>>>> explained. >>>> One >>>>>>>>>>> difference is whether it is okay for someone to be able to >>>> retrieve >>>>>>>>> the >>>>>>>>>>> file by knowing its link. One level of security is to turn >>>>>>>>>>> off >>>>>>>>> directory >>>>>>>>>>> indexing (if necessary) and use PHP to ask for a password >>>>>>>>>>> before >>>>>>>>> showing >>>>>>>>>>> them the links to the files. But once they have the links, >>>> they'd >>>>>>> be >>>>>>>>> able >>>>>>>>>>> to request them directly. >>>>>>>>>>> >>>>>>>>>>> The path you mention above, of putting the files outside >>>> webroot, >>>>>>> is a >>>>>>>>>>> stronger solution. As you mentioned, you authenticate the >>>> session >>>>>>>>> first, >>>>>>>>>>> but I don't think you can rewrite them to the secured area >>>> because >>>>>>> it >>>>>>>>> is >>>>>>>>>>> outside the webroot. Generally the way you'd do this is by >>>> having >>>>>>> the >>>>>>>>> PHP >>>>>>>>>>> script hand the file back to the user rather than having >>>>>>>>>>> Apache >>>> do >>>>>>> it >>>>>>>>>>> directly. >>>>>>>>>>> >>>>>>>>>>> The PHP script to hand off the file is pretty simple. >>>>>>>>>>> Usually >>>> you'd >>>>>>>>> set >>>>>>>>>> it >>>>>>>>>>> up to check permissions and display an error or a login form >>>>>>>>>>> if >>>>>>> they >>>>>>>>>> don't >>>>>>>>>>> have access. If they pass the access checks, you use header >>>>>>>>>>> () to >>>>>>> set >>>>>>>>> your >>>>>>>>>>> HTTP response headers (controlling things like saving vs >>>>>>>>>>> opening >>>>>>> the >>>>>>>>>> file, >>>>>>>>>>> content type, caching controls, etc.), then you pass the >>>> contents >>>>>>> of >>>>>>>>> the >>>>>>>>>>> file back. One way is fpassthru() but it turns out to be a >>>> memory >>>>>>> hog, >>>>>>>>>>> reading the whole file into memory before sending it out, >>>>>>>>>>> which >>>>>>> means >>>>>>>>> you >>>>>>>>>>> often run into the 8MB default memory limit. Another way >>>>>>>>>>> is to >>>> do >>>>>>> it >>>>>>>>>> chunk >>>>>>>>>>> by chunk like so: >>>>>>>>>>> >>>>>>>>>>> $fp = fopen($path,'r'); >>>>>>>>>>> while (!feof($fp)) { >>>>>>>>>>> $data = fread($fp,1048576); >>>>>>>>>>> echo $data; >>>>>>>>>>> } >>>>>>>>>>> fclose($fp); >>>>>>>>>>> >>>>>>>>>>> As you can see, that one reads 1MB at a time. >>>>>>>>>>> >>>>>>>>>>> Thanks, >>>>>>>>>>> Mac >>>>>>>>>>> >>>>>>>>>>> -- >>>>>>>>>>> Mac Newbold Code Greene, LLC >>>>>>>>>>> CTO/Chief Technical Officer 44 Exchange Place >>>>>>>>>>> Office: 801-582-0148 Salt Lake City, UT 84111 >>>>>>>>>>> Cell: 801-694-6334 www.codegreene.com >>>>>>>>>>> >>>>>>>>>>> _______________________________________________ >>>>>>>>>>> >>>>>>>>>>> UPHPU mailing list >>>>>>>>>>> [email protected] >>>>>>>>>>> http://uphpu.org/mailman/listinfo/uphpu >>>>>>>>>>> IRC: #uphpu on irc.freenode.net >>>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> -- >>>>>>>>>> Beau D. Scott >>>>>>>>>> Software Engineer >>>>>>>>>> >>>>>>>>>> _______________________________________________ >>>>>>>>>> >>>>>>>>>> UPHPU mailing list >>>>>>>>>> [email protected] >>>>>>>>>> http://uphpu.org/mailman/listinfo/uphpu >>>>>>>>>> IRC: #uphpu on irc.freenode.net >>>>>>>>>> >>>>>>>>> >>>>>>>>> _______________________________________________ >>>>>>>>> >>>>>>>>> UPHPU mailing list >>>>>>>>> [email protected] >>>>>>>>> http://uphpu.org/mailman/listinfo/uphpu >>>>>>>>> IRC: #uphpu on irc.freenode.net >>>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> -- >>>>>>>> Take care, >>>>>>>> William Attwood >>>>>>>> Idea Extraordinaire >>>>>>>> [email protected] >>>>>>>> >>>>>>>> Jonathan Swift< >>>>>>> http://www.brainyquote.com/quotes/authors/j/ >>>>>>> jonathan_swift.html> - >>>> "May >>>>>>> you live every day of your life." >>>>>>>> >>>>>>> >>>>>>> _______________________________________________ >>>>>>> >>>>>>> UPHPU mailing list >>>>>>> [email protected] >>>>>>> http://uphpu.org/mailman/listinfo/uphpu >>>>>>> IRC: #uphpu on irc.freenode.net >>>>>>> >>>>>> >>>>>> >>>>>> >>>>>> -- >>>>>> Take care, >>>>>> William Attwood >>>>>> Idea Extraordinaire >>>>>> [email protected] >>>>>> >>>>>> Joan Crawford< >>>> http://www.brainyquote.com/quotes/authors/j/joan_crawford.html> - >>>> "I, Joan >>>> Crawford, I believe in the dollar. Everything I earn, I spend." >>>>>> >>>>> >>>>> _______________________________________________ >>>>> >>>>> UPHPU mailing list >>>>> [email protected] >>>>> http://uphpu.org/mailman/listinfo/uphpu >>>>> IRC: #uphpu on irc.freenode.net >>>>> >>>> >>>> _______________________________________________ >>>> >>>> UPHPU mailing list >>>> [email protected] >>>> http://uphpu.org/mailman/listinfo/uphpu >>>> IRC: #uphpu on irc.freenode.net >>>> >>> >>> _______________________________________________ >>> >>> UPHPU mailing list >>> [email protected] >>> http://uphpu.org/mailman/listinfo/uphpu >>> IRC: #uphpu on irc.freenode.net >> >> _______________________________________________ >> >> UPHPU mailing list >> [email protected] >> http://uphpu.org/mailman/listinfo/uphpu >> IRC: #uphpu on irc.freenode.net >> > > > > _______________________________________________ > > UPHPU mailing list > [email protected] > http://uphpu.org/mailman/listinfo/uphpu > IRC: #uphpu on irc.freenode.net _______________________________________________ UPHPU mailing list [email protected] http://uphpu.org/mailman/listinfo/uphpu IRC: #uphpu on irc.freenode.net
