List:General Discussion« Previous MessageNext Message »
From:Johan De Meersman Date:February 22 2013 7:23am
Subject:Re: MyISAM table size vs actual data, and performance
View as plain text  
----- Original Message -----
> From: "Rick James" <rjames@stripped>

Hey Rick,

Thanks for your thoughts.

> * Smells like some huge LONGTEXTs were INSERTed, then DELETEd.
>  Perhaps just a single one of nearly 500M.

I considered that, too; but I can see the on-disk size grow over a period of a few months
- it's not a sudden bump.


> * Yes, there is an impact on full table scans -- it has to step over
> the empty spots.  Or maybe not -- one big cow chip of 500MB would be
> easy to leap over.

Hmm, that is a point. I keep expecting a full tablescan to still use the PK, but this
isn't InnoDB. Still, it's peculiar then that it suddenly becomes slow - I would expect
the odds to tip in favour of an index scan as table_free grows, not the other way around.


> * OPTIMIZE TABLE is the primary way to recover the space.  It _may_
> be that space on the _end_ is automatically recovered.  If so, you
> might see the .MYD shrink even when OPTIMIZE is not run.

Yes, that's what I do, of course; but the free space should really be reallocated to
updates - escpecially because of the longtext, which means it's allowed to fragment. Are
there any tools available to analyze MyISAM datafiles? It'd be interesting to see how the
free space is really distributed.


> * LONGTEXT is almost never useful.  Do you really think there are
> thingies that big?  Consider changing it to MEDIUMTEXT -- that would
> truncate any biggies to 16MB.
> 
> * Smells like a key-value (EAV) schema design.  Such is destined to
> fail when trying to scale.  Yeah, you are probably stuck with
> Drupal.  Here are my comments and recommendations on EAV:
>  http://mysql.rjweb.org/doc.php/eav

Yeps, Drupal. I could probably truncate that field, yes; but that's just another
workaround, not a fix.

It's indeed a key/value scheme - every single page load that gets through the caches will
select-star that entire table. It's crap, but I have precious little influence on the CMS
decisionmaking.


> * Please try to find a way in your Email client to display STATUS
> without losing the spacing.

Heh, sorry. I've always preferred the wide layout, and tend to forget \G for mails.


> * When you switched to InnoDB, I hope you had innodb_file_per_table
> turned on.  That way, you can actually recoup the space when doing
> ALTER.  Otherwise, you will be stuck with a bloated ibdata1 file
> that you cannot easily shrink.

Default on all instances, of course.


> * In InnoDB, the LONGTEXT will usually be stored separately, thereby
> making a full table scan relatively efficient.

For now, they seem to be behaving. We'll see.


Thanks,
Johan


-- 
Unhappiness is discouraged and will be corrected with kitten pictures.
Thread
MyISAM table size vs actual data, and performanceJohan De Meersman15 Feb
  • RE: MyISAM table size vs actual data, and performanceRick James21 Feb
    • Re: MyISAM table size vs actual data, and performanceJohan De Meersman22 Feb