Re: Can a MyISAM DB hold /everything/?
Scott Haneda wrote: Unless you have very good reason to store binary data like an image in your database, do not. It may work well for a time, but always be prepared that your system will grow. If it grows a good deal relative to your hardware, and users, and connections etc, you will always be in a race to throw more hardware at the problem. In a simplistic terms, an image is a file, files should be stored on a file system. You can do some very nice and simple things later on to distribute image loads. If you store the path to the image, so example, images/date/time.jpg, and then need to grow your serving needs, you could adjust your code in the http request to call images.example.com as the hostname. You could set images.example.com to return round robin DNS records. Now all you have to do is mirror or sync your images directory to any number of high performance http servers. That is just one example of how I have avoided dealing with binary file load in MySql, or any database for that matter. Even a reltively small image stored in a database can become a lot of data very fast. You will also find it can be inefficient to fetch that data and show it to the end user. At the very least, you have to request the data, read it, rebuild it into a form that can be displayed to a user, which is a lot of work, compared to no work at all if just calling it from the file system. Your question about which is faster, always will be on disk binary storage, with the exception of deletes. Deletes will happen in near the same time, though you do have to locate the record to delete. This probably still has no impact, since you will locate on an index. Hope that was helpful. On May 27, 2009, at 9:50 AM, Pete Wilson wrote: I am new to MySQL and just laying out what I hope will be just one db that holds all the info for a list of forged or machined metal parts. Let's say there are 10,000 such parts, each with a unique part number. That part number is the primary index. Each part has at least one, and maybe several, accompanying photos. And each part has at least one, and perhaps as many as five, human-language descriptions. These descriptions might be as long as 5,000 characters each. I believe I have the choice of: 1. Storing everything -- photos and descriptions included -- in the database; or 2. Holding some info in the database; and storing photos and descriptions in normal-type disk files apart from the database, with names based on part numbers. So my questions: 1. Which scheme is faster and less resource-hogging in reading, updating, adding, and deleting? 2. I understand there are problems in storing photos in the db. Can one overcome these problems somehow? Could you clarify/expand on this a bit - I am setting up a site where I expect to have a lot of images, both still and flvs, and a lot of recipes (including ingredients, procedures ad text : ;images). I am storing the images in /images directory. If the amount of images gets rather large, you are suggesting to store them on another server, right? Now, with a lot of recipes, I understand that I should be storing them also on another server; and perhaps using XML to store the recipes. Does that sound like I have understood your advice? TIA -- Hervé Kempf: Pour sauver la planète, sortez du capitalisme. - Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Can a MyISAM DB hold /everything/?
-Original Message- From: mugisha moses [mailto:mossp...@gmail.com] Sent: Thursday, May 28, 2009 12:11 AM To: Arthur Fuller Cc: mysql Subject: Re: Can a MyISAM DB hold /everything/? what if you had no issues of scaling, which would have less access time, file system or database? [JS] I don't know that there is a definitive answer, but I would also lean in the direction of storing a pointer rather than the image itself. = Either way, you have to retrieve the relevant row from the database so you incur that overhead. + If you store the image as a blob, retrieving the row should cache the blob in MySQL's buffers. - Since images are going to be both big and of vastly different sizes, if you put them in the table rows it probably makes MySQL work harder to manage space in its own files. - Since MySQL doesn't use raw partitions, retrieving the row goes through the file system anyways. + Using a flat file for the image also goes through the file system, but I suspect that most file systems are better at reading flat sequential files than they are at reading the random-access files of which a database is composed. (It's been a long time since I worked with *nix file system internals.) On Thu, May 28, 2009 at 1:54 AM, Arthur Fuller fuller.art...@gmail.com wrote: I second that emotion (don't store the images in the data file: just store the paths to said images). Why? Because in the event of an updated image it is more hassle than it is worth, to drop the old image and replace it with the new one; if what you store is a simple pointer to an image file, lots of this work goes away. A. -- skype name : mossplix twitter: @mugisha -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=jschwa...@the- infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Can a MyISAM DB hold /everything/?
The times I have heard, this is just a test, hack it together, or this will never see significant load are more than I care to count. Worse, the times that those statements ended up being false, and a rigged and hacked demo code base become production has taught me to treat all work as though it will become production. That small rant aside... Your question is hard to answer without knowing how you plan on displaying the results of the data that comes out of the database. If this is a case where you pull the data out and show it in a web browser, then without question, less access time would be to store the images in the file system. There is no access to if there is no request for the image out of the database. Even if you are not using a browser, I am having a hard time thinking of many cases in which storing an image in the database is a good idea. Then again, I am thinking jpg, png, gif etc. Could be by image you mean a highly structured bitmap pattern, or pure vector coordinates that render an image out to SVG. I do have one question. If you can make the statement that scaling will never be an issue, then you why does it matter? In a non performance driven environment, you should pick the most convenient method to manage, and not worry about performance or scaling, if you have that luxury. On May 27, 2009, at 9:10 PM, mugisha moses wrote: what if you had no issues of scaling, which would have less access time, file system or database? On Thu, May 28, 2009 at 1:54 AM, Arthur Fuller fuller.art...@gmail.com wrote: I second that emotion (don't store the images in the data file: just store the paths to said images). Why? Because in the event of an updated image it is more hassle than it is worth, to drop the old image and replace it with the new one; if what you store is a simple pointer to an image file, lots of this work goes away. -- Scott * If you contact me off list replace talklists@ with scott@ * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Can a MyISAM DB hold /everything/?
A bit of a confusing set of replies was in the previous thread... Doing my best to answer the issue at hand, please reference past posts if my reply is not clear... On May 28, 2009, at 6:04 AM, PJ wrote: Could you clarify/expand on this a bit - I am setting up a site where I expect to have a lot of images, both still and flvs, and a lot of recipes (including ingredients, procedures ad text : ;images). I would put some thought into how you store the recipes. You mention XML below. This will all depend on the structure of the recipes data. In a simple case, you have quantity and item for each recipe. A simple relationship of something like the recipe parent, where that holds the title, and maybe description and instructions, linked to a second table with quantity and item columns would work fine. That is also very rigid, and you never know where your data needs are going to deviate from that form. Put some thought into this. Get a good sampling of your recipe data to make sure you build this out in a way that is forward flexible. I am storing the images in /images directory. If the amount of images gets rather large, you are suggesting to store them on another server, right? I would first start be researching how your OS deals with large quantities of images in one single directory. Write a script to copy 1 image 100,000 times over into the same directory. Test how fast you can grab a random image, test how fast you can delete a random image. Different OS's will behave different under different amounts Even from a pure CLI management perspective, a simple `ls -la` will take some time to finish on 100,000 images. I generally do something along the lines of: images/$user-id/$year/$month/$day/$image-name-$random.ext Back to your question. Just because the amount of images gets large, does not mean you need to use multiple servers. It is only if the load of requests for those images gets more than the server can handle that you may want to look into distributing that load. Distributing image load over http, if that is what you are doing, is almost trivial. Starting with round robin DNS and rsync to more advanced load balancing, or looking into a CDN like what Amazon offers. There are solutions abound in this area. Now, with a lot of recipes, I understand that I should be storing them also on another server; and perhaps using XML to store the recipes. Does that sound like I have understood your advice? I am not so sure. Recipes are text only, take little space. A simple phpBB forum may have GB's of data in them, on a shared database server. You may want to have a second database for replication, as a hot backup, and then have other things in place for cold backups. This all depends on a lot of factors about your end plans which have not yet been shared. -- Scott * If you contact me off list replace talklists@ with scott@ * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Can a MyISAM DB hold /everything/?
Most commonly, you would store all information (including descriptions in teh database. The amount of data you describe is peanuts for MySQL. Then, you would probably store a path to an image in the database as well. You could then store the images on disk outside the database, or even think about using amazon S3 for that. Takes away your need for administering a file server :) Walter -- Walter Heck, Consultant @ Open Query (http://openquery.com) Affordable Training and ProActive Support for MySQL related technologies Follow our blog at http://openquery.com/blog/ OurDelta: free enhanced builds for MySQL @ http://ourdelta.org On Wed, May 27, 2009 at 10:50 AM, Pete Wilson pete...@yahoo.com wrote: Hi folks -- I am new to MySQL and just laying out what I hope will be just one db that holds all the info for a list of forged or machined metal parts. Let's say there are 10,000 such parts, each with a unique part number. That part number is the primary index. Each part has at least one, and maybe several, accompanying photos. And each part has at least one, and perhaps as many as five, human-language descriptions. These descriptions might be as long as 5,000 characters each. I believe I have the choice of: 1. Storing everything -- photos and descriptions included -- in the database; or 2. Holding some info in the database; and storing photos and descriptions in normal-type disk files apart from the database, with names based on part numbers. So my questions: 1. Which scheme is faster and less resource-hogging in reading, updating, adding, and deleting? 2. I understand there are problems in storing photos in the db. Can one overcome these problems somehow? Thanks! -- Pete -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=li...@olindata.com -- Walter Heck -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Can a MyISAM DB hold /everything/?
Unless you have very good reason to store binary data like an image in your database, do not. It may work well for a time, but always be prepared that your system will grow. If it grows a good deal relative to your hardware, and users, and connections etc, you will always be in a race to throw more hardware at the problem. In a simplistic terms, an image is a file, files should be stored on a file system. You can do some very nice and simple things later on to distribute image loads. If you store the path to the image, so example, images/ date/time.jpg, and then need to grow your serving needs, you could adjust your code in the http request to call images.example.com as the hostname. You could set images.example.com to return round robin DNS records. Now all you have to do is mirror or sync your images directory to any number of high performance http servers. That is just one example of how I have avoided dealing with binary file load in MySql, or any database for that matter. Even a reltively small image stored in a database can become a lot of data very fast. You will also find it can be inefficient to fetch that data and show it to the end user. At the very least, you have to request the data, read it, rebuild it into a form that can be displayed to a user, which is a lot of work, compared to no work at all if just calling it from the file system. Your question about which is faster, always will be on disk binary storage, with the exception of deletes. Deletes will happen in near the same time, though you do have to locate the record to delete. This probably still has no impact, since you will locate on an index. Hope that was helpful. On May 27, 2009, at 9:50 AM, Pete Wilson wrote: I am new to MySQL and just laying out what I hope will be just one db that holds all the info for a list of forged or machined metal parts. Let's say there are 10,000 such parts, each with a unique part number. That part number is the primary index. Each part has at least one, and maybe several, accompanying photos. And each part has at least one, and perhaps as many as five, human- language descriptions. These descriptions might be as long as 5,000 characters each. I believe I have the choice of: 1. Storing everything -- photos and descriptions included -- in the database; or 2. Holding some info in the database; and storing photos and descriptions in normal-type disk files apart from the database, with names based on part numbers. So my questions: 1. Which scheme is faster and less resource-hogging in reading, updating, adding, and deleting? 2. I understand there are problems in storing photos in the db. Can one overcome these problems somehow? -- Scott * If you contact me off list replace talklists@ with scott@ * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Can a MyISAM DB hold /everything/?
I second that emotion (don't store the images in the data file: just store the paths to said images). Why? Because in the event of an updated image it is more hassle than it is worth, to drop the old image and replace it with the new one; if what you store is a simple pointer to an image file, lots of this work goes away. A.
Re: Can a MyISAM DB hold /everything/?
what if you had no issues of scaling, which would have less access time, file system or database? On Thu, May 28, 2009 at 1:54 AM, Arthur Fuller fuller.art...@gmail.com wrote: I second that emotion (don't store the images in the data file: just store the paths to said images). Why? Because in the event of an updated image it is more hassle than it is worth, to drop the old image and replace it with the new one; if what you store is a simple pointer to an image file, lots of this work goes away. A. -- skype name : mossplix twitter: @mugisha -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
