MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Scott Hess Date:June 28 1999 5:40pm
Subject:Re: to much fragmentation
View as plain text  
Vivek Khera <khera@stripped> wrote:
> >>>>> "SH" == Scott Hess <scott@stripped> writes:
> SH> That (storing the blobs out to the filesystem) is the solution we're
> SH> currently pondering.  The main downside is that the database is being
> SH> as the central "memory" for a system that involves a couple
components, and
> SH> we'd _really_ rather not teach all components about the off-database
> SH> storage, if we can get away with it.
> One common technique is to keep blobs out of all tables except one.
> This table would consist of two fields: a unique ID and the blob
> data.  Then, like you normally would keep the blob on the file system
> and store a reference to it, you instead keep the blob data in that
> table and store a reference to it.
> This way you keep all your data in the blob and the only fragmentation
> you may have is in the blob table.  This really won't matter too much
> since the time to transfer the data over the network will be much
> larger than the time to seek the disk around those fragments.

I agree with your chain of reasoning - but we have found that the last
sentence does not hold, and without that last sentence, the entire thing
falls apart.  Assume a 100Mbit switched Ethernet between your MYSQL server
and clients...

What we've found to happen is that all of your clients are hitting the
database, and at some point it catastrophically degrades - performance
doesn't plateau (sp?), it degrades.  Our best simulations indicate that this
point is where the number of transactions per second to the disk drive
passes a certain point (this is as opposed to megabytes per second).  At
that point, more MYSQL statements are coming into the system than are being
retired, and if you don't take action to remedy the situation, things melt

The remedy that has worked well for us is to run isamchk -r (or OPTIMIZE
TABLE) to defragment the tables.  It makes all the difference in the world.


to much fragmentationDavid Johnson26 Jun
  • Re: to much fragmentationSasha Pachev27 Jun
  • Re: to much fragmentationScott Hess28 Jun
    • Re: to much fragmentationVivek Khera28 Jun
    • Re: to much fragmentationMichael Widenius29 Jun
      • Re: to much fragmentationEd Carp29 Jun
        • Re: to much fragmentationSasha Pachev29 Jun
        • Re: to much fragmentationMichael Widenius29 Jun
          • Authentication methodsRoger Smith29 Jun
            • Authentication methodsJani Tolonen1 Jul
        • Re: to much fragmentationScott Hess29 Jun
      • Re: to much fragmentationEd Carp29 Jun
        • Re: to much fragmentationMichael Widenius30 Jun
      • Re: to much fragmentationT├Ánu Samuel29 Jun
  • Re: to much fragmentationScott Hess28 Jun
Re: to much fragmentationFred Lindberg28 Jun