List:General Discussion« Previous MessageNext Message »
From:DreamWerx Date:September 28 2004 5:37pm
Subject:Re: Images
View as plain text  
Most people make the mistake of using the biggest blob size to store
files.. That blob size is capable of storing just HUGE files..    What
we do is store files in 64K (medium blob) chunks..

So if the image was say 200K in size, the metadata for the image would
be 1 row in a table, and the image data would be 4 rows in the data
table.  3 full 64K rows + 1 partially used rows.

There is a good article/sample code here on the kind of technique we
started with:
http://php.dreamwerx.net/forums/viewtopic.php?t=6

Using chunked data, apache/php only needs to pull row by row(64k) and
deliver to the client, keeping the resultset size low = memory
overhead low.

The storage servers (mysql storage) I have tested on the LAN; them
storing and retreiving data from mysql (using FTP gateway) at rates of
4600K/sec.. which is I think the fastest speed my laptop network card
could deliver.

That's pretty fast..  Rare day when most internet users can talk to
servers at those speeds.



On Tue, 28 Sep 2004 09:19:47 -0700, Ed Lazor <ed.lazor@stripped> wrote:
> What do you mean "chunked"?
> 
> I figured it would be easier to track about 32,000 images in MySQL than in
> files, so I setup a test to see what the performance difference is and if
> storing in MySQL would actually work.
> 
> Everything is working and it's a lot easier to keep track of the images in
> MySQL.  I ran some performance tests using Apache's ab though and there's a
> huge performance difference - 38 times faster grabbing the file.
> 
> It could be my test system here at home.  I've asked my ISP to upgrade my
> production server to PHP5 so that I can run tests from there.
> 
> There could also be performance hits in the script that grabs the images
> from MySQL, because I tried using OOP.  I was told OOP would be a faster
> approach, but I'm pretty new to it and may not have done something
> correctly.  I'm going to create some non-OOP scripts to cross-reference the
> tests.
> 
> I only have one production server, so I won't be able to separate / dedicate
> servers.  Your mention of chunking sounds promising though.  Actually, I'd
> appreciate any ideas or recommendations you have.
> 
> Thanks,
> 
> Ed
> 
> 
> 
> 
> > -----Original Message-----
> > From: DreamWerx [mailto:dreamwerx@stripped]
> > Sent: Tuesday, September 28, 2004 8:54 AM
> > To: GH
> > Cc: Jigal van Hemert; mysql@stripped
> > Subject: Re: Images
> >
> > Separate/dedicated servers for web/database.   All the data was
> > chunked to allow faster streaming/lower overhead (large
> > images/files)..   If you have more specific questions I can answer
> > them..
> >
> >
> >
> > On Tue, 28 Sep 2004 11:49:27 -0400, GH <garyhotko@stripped> wrote:
> > > Is there anything special in your setup that you did to have such good
> > > performance?
> > >
> > >
> > >
> > >
> > > On Tue, 28 Sep 2004 11:47:26 -0400, DreamWerx <dreamwerx@stripped>
> > wrote:
> > > > It's an argument that can go on forever...  We have 10's of thousands
> > > > of images in mysql databases..   very fast/reliable.. easy to
> > > > replicate, stream, etc..
> > > >
> > > >
> > > >
> > > >
> > > > On Tue, 28 Sep 2004 08:42:13 +0200, Jigal van Hemert
> <jigal@stripped>
> > wrote:
> > > > > From: "GH" <GaryHotko@stripped>
> > > > >
> > > > > > I was wondering how to get images into and out of a Mysql
> database
> > > > > >  was told it was possible.
> > > > > >
> > > > > > I have  mysql 4.0.18
> > > > >
> > > > > The general opinion is that files should be stored in a file
> system
> > and not
> > > > > a database. There are circumstances that you might want to store
> > binary data
> > > > > in a database.
> > > > > Take a look at the BLOB column types
> > > > > (http://dev.mysql.com/doc/mysql/en/BLOB.html).
> > > > >
> > > > > Regards, Jigal.
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > --
> > > > > MySQL General Mailing List
> > > > > For list archives: http://lists.mysql.com/mysql
> > > > > To unsubscribe:
> > http://lists.mysql.com/mysql?unsub=1
> > > > >
> > > > >
> > > >
> > >
> > 
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
> 
> 
> 
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
> 
>
Thread
ImagesGH28 Sep
  • Re: ImagesEric Bergen28 Sep
  • Re: ImagesJigal van Hemert28 Sep
    • Re: ImagesDreamWerx28 Sep
      • Re: ImagesGH28 Sep
        • Re: ImagesDreamWerx28 Sep
          • RE: ImagesEd Lazor28 Sep
RE: Imagesjabbott28 Sep
  • RE: ImagesEd Lazor28 Sep
Re: ImagesEric Bergen28 Sep
  • RE: ImagesEd Lazor28 Sep
  • RE: ImagesEd Lazor28 Sep
Re: ImagesDreamWerx28 Sep
  • RE: ImagesEd Lazor28 Sep
  • Multiple Insert Statement?Eve Atley28 Sep
    • Re: Multiple Insert Statement?GH28 Sep
      • Re: Multiple Insert Statement?SGreen28 Sep
    • Re: Multiple Insert Statement?Paul DuBois28 Sep
      • RE: Multiple Insert Statement?Eve Atley28 Sep
        • RE: Multiple Insert Statement?mos28 Sep
        • Re: Multiple Insert Statement?Rhino28 Sep
        • Re: Multiple Insert Statement?Rhino29 Sep
  • RE: ImagesEd Lazor28 Sep
Re: ImagesDreamWerx28 Sep
  • Re: ImagesGH28 Sep
    • Re: ImagesDreamWerx28 Sep
Re: ImagesDreamWerx28 Sep
Re: ImagesGH28 Sep
Re: ImagesDreamWerx28 Sep