16MB? you mean the max packet per query limit? If your storing data in huge/large blob then you are making a big mistake in my opinion and taking a huge performance hit... I've got files over 1GB in size in mysql now.. they went in and out at almost filesystem speed...
On Sun, 14 Dec 2003, Chris Nolan wrote: > Forgot something in my other reply. > > 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? > > 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] > > > > > > > > > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
