----- Original Message ----- > From: "Rick James" <rja...@yahoo-inc.com>
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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
