Can a MyISAM DB hold /everything/?

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=gcdmg-mysql-2@m.gmane.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 wrote:
>
>
> Hi folks --
>
> I am new to MySQL and just laying out what I hope will be just one db tha=
t 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. Th=
at 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-langua=
ge descriptions. These descriptions might be as long as 5,000 characters ea=
ch.
>
> I believe I have the choice of:
>
> 1. Storing everything -- photos and descriptions included -- in the datab=
ase; 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 par=
t 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 o=
vercome 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=3Dlists@o=
lindata.com
>
>



--=20
Walter Heck

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

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=gcdmg-mysql-2@m.gmane.org

Re: Can a MyISAM DB hold /everything/?

--001485f8592c3da306046aecba53
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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.

--001485f8592c3da306046aecba53--

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 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=gcdmg-mysql-2@m.gmane.org

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 --- pj@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=gcdmg-mysql-2@m.gmane.org

RE: Can a MyISAM DB hold /everything/?

>-----Original Message-----
>From: mugisha moses [mailto:mossplix@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
>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=jschwartz@the-
>infoshop.com





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.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 > > 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=gcdmg-mysql-2@m.gmane.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=gcdmg-mysql-2@m.gmane.org