> With the NAS - what's to say that MySQL's retrieval and > network protocol > is not more efficient than whatever is running on your NAS boxes?
Well, currently we work like so: Client <-> Webserver/Application Server <-> Database The database returns file names to the application/webserver (yes, we're not fully three-tiered) which returns to the client, and the client must then generate more calls for images: Client <-> Webserver <-> Image Storage So I have 1 database call and several addtl HTTP calls for each page call. At our stage, the bandwith from the NAS to the webservers are not a limiting factor. If we put everything into the database, then I have each call for an image go to the database (unless there is a better method?), so I have several database calls and several HTTP calls for each primary page. Granted, this is simplified, but it seems like more overhead. What's the "best practice" method to retrieve images stored in a database for webserving? Joshua Thomas Network Operations Engineer PowerOne Media, Inc. tel: 518-687-6143 [EMAIL PROTECTED] --- In theory there is no difference between theory and practice. In practice there is. - Yogi Berra --- > Conversely, MySQL's current 16 MB per transfer limitation may > very well > not allow it to act in this role at all. > > Ah, the wonders of open discussion! > > Best regards, > > Chris > > On Sat, 2003-12-13 at 23:33, Joshua Thomas wrote: > > > you could very well do that, and frankly that is how alot > of websites > > > work. > > > > Yep, including one I run. That site has to generate <img> > and <a href> links > > for visitors, and it seems far easier to return > "/pics/imagefoo.jpg" then > > the image itself and decide how to embed that into the page. > > > > > But storing the actual binary file, weither it is > > > a .jpg or .tar, allows your application/website/whatever to be > > > independent of your files location on the server. > > > > Is this really such a problem? When I migrated the > above-mentioned website, > > I had some minor path issues which resolved with symbolic links. > > > > If your path for images changes, make a symlink to simulate > where it used to > > be; or use an UPDATE statement to change the path prefix on > the images in > > the database. If you really want to be clever, use a > single-table row for > > the prefix (/home/foo/myimages) and another table for the > actually names > > (foo.jpg) and just update the prefix when location changes. > > > > > > > This way, when you > > > select a specific row, you will have the binary... > > > regardless of path > > > locations. It also makes data organization cleaner as well. > > > If you decide > > > you don't need a binary anymore and delete it from the > > > database, it will > > > be gone. > > > > A cron job which does a SELECT imagename FROM imagetable, > then compares to > > the directory, and removes the non-exisiting images, would > also work. Or you > > could write a database trigger to call out and delete the > image when the row > > was deleted. > > > > I do admit it's less nice than being able to just drop the > image from the > > table, but you also lose the ability to manipulate the > content on the > > filesystem level. For example, in your .tar example, how > are you going to > > view the contents of one of those .tar files? You'll have > to SELECT it out > > of the database, write it somewhere, then view it; if you > want to update it, > > you have to SELECT it out, edit it, and UPDATE the table... > > > > > where as just storing the path will not, which can > > > get pretty > > > ugly. I hope that helps.... > > > > Works like a charm for us. The downsides I see to storing > in the database > > are: > > > > * More overhead reading and writing from the database > > * Much much larger database sizes. For me it's easy to > backup and restore my > > database. If my 2GB+ of images were IN the database, well, > that would be a > > different story. > > * I can schedule backups of the database, and filesystem content, at > > different times; this reduces the impact if something goes > wrong during a > > backup. > > > > Of course, I do see the advantages of embedding the images, > tar files, etc > > into the database; I just think it has enough issues of > it's own that I > > would caution against doing it. > > > > Here's another idea to chew on: My full-time employer runs > over a thousand > > sites for newspapers across the country. We have image > names embedded into > > the database, not the data. Why? Well, we store images on > NAS (Network > > Attached Storage) devices. We have several of them, and use > custom scripts > > to keep the content on them the same. When we have a > failure on one, we > > simply update a piece of code which defines which server to > read from. > > > > Now, if we put those images into the database, we'd have a > few issue: > > > > * Our database size would grow far, far beyond the current > size, which is > > over 60GB. > > > > * Restores to the database would be much slower, and in our > case, if we have > > a failure, we'd rather get everything up fast and then fix > images then wait > > longer and get both. Time == money to our customers. > > > > * Instead of returning the filename to the application > software (ASP and CF > > in our case), you'd return the whole image, and that's > going to be a huge > > drain on server CPU. We'd need something like 2x the CPU > power we have now, > > with many more high-speed disks. (and 15K RPM isn't cheap!) > > > > * Replication from the publisher to the subscribers would > be much more > > intensive and require more bandwith and CPU power, again driving our > > hardware needs up. You could argue that we'd loose the need > for the NASes. I > > haven't done a comparison on how the pricing would work. > > > > > > That's my .02$ and a then some. > > > > Joshua Thomas > > > > > > > > > > dan > > > > > > Joshua Thomas wrote: > > > > > > >Can I ask why? > > > > > > > >Why not define a char(50) (or whatever size) with the > > > relative or complete > > > >path to the .tar file? Storing it in your database would > > > create huge row > > > >sizes. > > > > > > > >Joshua Thomas > > > >Network Operations Engineer > > > >PowerOne Media, Inc. > > > >tel: 518-687-6143 > > > >[EMAIL PROTECTED] > > > > > > > >--- > > > >In theory there is no difference between theory and > > > practice. In practice > > > >there is. > > > >- Yogi Berra > > > >--- > > > > > > > > > > > > > > > > > > > > > > > >>-----Original Message----- > > > >>From: [EMAIL PROTECTED] > > [mailto:[EMAIL PROTECTED] > > >>Sent: Friday, December 12, 2003 3:55 PM > > >>To: [EMAIL PROTECTED] > > >>Subject: storing .tar files in mysql > > >> > > >> > > >>Hi all, > > >> I am new to mysql and I was wondering if someone could point > > >>me in the > > >>right direction on how to store .tar and .tar.gz (bzip2) > > >>files inside a > > >>mysql database. I have googled to try and find some help > > >>there but most > > >>of the hits come back with binary image files. I have gone > > >>thru the mysql > > >>tutorial and I can create the database and tables, but I > can't seem to > > >>insert the .tar file properly...Any pointers would be > appreicated... > > >> > > >>Thanks, > > >>Jake > > >> > > >>-- > > >>MySQL General Mailing List > > >>For list archives: http://lists.mysql.com/mysql > > >>To unsubscribe: > > >> > > >> > > >http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > > > > > >
