On Sun, Jun 6, 2010 at 8:24 PM, Phpster <phpster@xxxxxxxxx> wrote: > > > On Jun 6, 2010, at 5:51 PM, Paul M Foster <paulf@xxxxxxxxxxxxxxxxx> wrote: > > On Sun, Jun 06, 2010 at 09:55:20PM +0100, Ashley Sheridan wrote: >> >> <snip> >> >> >>> Having said that, it's not generally a good idea to store images as >>> blobs in my experience. It can create slowdowns in database access, and >>> increases the amount of space the db requires by a lot! What most people >>> do I've found is to store the image as a file and store the path and >>> filename to it in the db. However, if the location is going to change >>> often then it might be best to store it as a blob like you're doing. >>> >>> >> +1 >> >> Storing blobs in an RDBMS is an abuse of the technology, IMO. RDBMSes >> were not originally designed to store this type of information, and as >> Ash pointed out, it still can create problems. His assessment of the >> preferred method for storing this type of information is spot on. Store >> them in a file and then store the file location in your database. >> >> I don't know, but this might be use case for the new generation of NoSQL >> databases. I have very little knowledge of how they store data, but I >> would assume they're more accustomed to storing blobs and whole >> documents. And if not, then someone should come up with one which works >> this way. It would be relatively simple to create a key-locaton table >> (using dbm or somesuch) and have all the actual "data" stored in files. >> >> Paul >> >> -- >> Paul M. Foster >> >> -- >> PHP General Mailing List (http://www.php.net/) >> To unsubscribe, visit: http://www.php.net/unsub.php >> >> > In my experience, most RDBMs fail at blobs and internally they still store > it as a file with a pointer. It's also worthwhile noting that in all cases, > after a certain size in the db is reached, performance is impacted. With > mysql, it seems to be about 12gb and with SQL server it's around the 15gb > size. > > I had to write a script that would pull those images and store them on the > file system and when run, it reduced the size of the db to about 1 gb in all > instances, if not smaller. This dramatic change reduces back up time and > costs, making the clients way happier since the performace also picked up. > > As mentioned, it's just not worth storing in the db. > > Bastien > > Sent from my iPod > > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > Hi all, Indeed, I believe that the balance typically shifts in favor of storing meta information about the image in the database, leaving the web server to handle storing the actual image. However, I've also worked on apps where the balance appeared to shift towards storing the image in the DB. For example, we were running ASP.Net, had 1 load balancer, 3 web servers, and 1 super-duper DB server (the site is subject to very large spikes, and generally high traffic.) The client used my custom CMS to administer content, including images (not for the site templates, but rather for the items they were specifically managing on their site.) I could have chosen to store the images on one of the web servers, but that posed issues in terms of maintenance and also the webservers were not as reliable as the DB (per SLA and the equipment used, as the DB server had internal redundancies built-in, and those who've worked with IIS are likely familiar with the "It's not working, let's just reboot" methodology for servers.) I could have duplicated the images across all of the servers. However, keeping things synched, when coupled with the security requirements, proved to add a fair amount of complexity (not that it was impossible, but troublesome given the budget, timeline, and the fact I wasn't the sa.) I could have set up another server as a media server, but that wasn't logistically feasible at the time (there are other options I've not mentioned, too.) I decided to examine the performance of an architecture where we stored the images in the DB, as this app was unique in a few ways. First, each image was limited to less-than 45KB (pretty small.) Second, the DB is relatively small, containing thousands of items that they manage (old records are frequently purged.) After running several benchmarks, I realized that the website performed very well using this architecture (although I found several other issues in terms of performance, not that they were because of my [cough] code ;) I sometimes hear people lament the speed of PHP. As Rasmus Lerdorf points out in his presentation "Simple is Hard" ( http://talks.php.net/show/froscon08), PHP is rarely the bottleneck. When I ask those who take issue with PHP's speed what they're basing it on, I find few back it up with meaningful numbers, or if they do, they're pointing to the comparison of PHP to some other language while running a for loop, which is interesting if you're writing a for-looper app. Rarely have these naysayers benchmarked PHP in the greater ecosystem of an application (and rarely have they come to grips with the technological grandeur of what flickr and facebook accomplish every second) to realize that it's not the bottleneck. All this to say, there are ideas and schemes that generally prove true, and I believe that it is generally a better approach to avoid storing images in a DB. However, I hope we still take the opportunity to make sure the numbers back up the generalized approach in the specific case at hand, and we're addressing the true bottlenecks in our application. I wouldn't have pursued the architecture above under normal circumstances, but the constraints forced me to try something new, and contrary to me expectations, performance was no issue at all. Morals of the story: Huge bottlenecks can hide in tiny bits. Don't judge a bottleneck by it's cover(-age online ;) It ain't a bottleneck until it's a bottleneck. [OK, OK, I'll stop!] Adam -- Nephtali: PHP web framework that functions beautifully http://nephtaliproject.com
