June 19, 2013

Post: Using MyISAM in production

June 17, 2006 By 36 Comments

… by MyISAM behavior (and default MySQL behavior due to this) – it will be corrupted by unproper shutdown, it will fail with partial… unnoticed with MyISAM storage engine. This hidden corruption may later cause crashes wrong query results and further data corruption. Partial updates. MyISAM is… but still watch out. Lack of row cache. MyISAM tables only have indexes cached in key_buffer while data is cached in…

Filed Under: Insight for DBAs Tagged With: ,

Post: MyISAM Scalability and Innodb, Falcon Benchmarks

October 12, 2007 By 10 Comments

…) unsigned NOT NULL, PRIMARY KEY (`id`), KEY `val` (`val`) ) ENGINE=MyISAM AUTO_INCREMENT=4097 Table t1 contains about 260,000 records… smart index structure for such data distribution but good enough for performance gotcha illustration purposes. The benchmark shows following results for MyISAM… to them. In this case however single index had most of the load. This is partially proved by oprofile data (case with…

Filed Under: Uncategorized Tagged With: , , , ,

Post: Should you move from MyISAM to Innodb ?

January 12, 2009 By 59 Comments

… get to deal with recovering tables on the crash or partially executed statements. Table locks is no more problem, hot backups… Innodb. Features The MyISAM features which forbid moving to Innodb are typically Full Text Search and RTREE indexes/GIS with Full Text being much more common. There are workarounds for both of them, including dedicated MyISAM slave…

Filed Under: Insight for DBAs Tagged With: , ,

Post: Why you should ignore MySQL's key cache hit ratio

February 28, 2010 By 28 Comments

…counter never worked! A partially valid use of Key_reads There is a partially valid reason to …large enough to hold your working set — the index blocks that are frequently used. How large is…s dedicated to MyISAM tables. Consider your mixture of storage engines (some InnoDB, some MyISAM, which is …

Filed Under: Insight for DBAs, MySQL Tagged With: , , ,

Post: MySQL Crash Recovery

July 30, 2006 By 30 Comments

… and master.info being corrupted. Not to mention MyISAM tables can contain partially completed statements as well as some of updates totally… might want to populate caches. For MyISAM key_cache this can be done by using LOAD INDEX INTO CACHE statement, for other… longer time on extreme load. Innodb statistics – Unlike MyISAM Innodb does not store index cardinality in tables, instead it computes them on…

Filed Under: Insight for DBAs Tagged With: , , ,

Post: InnoDB Full-text Search in MySQL 5.6 (part 1)

February 26, 2013 By 1 Comment

… very big fan of MyISAM; I would argue that in most situations, any possible advantages to using MyISAM are far outweighed by… InnoDB, but due to their use of MyISAM FTS, the idea of a complete or partial migration was, for one reason or… subject of adding and dropping FT indexes, it’s entirely possible to DROP multiple FT indexes with InnoDB in the same ALTER…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,

Post: Falcon Storage Engine Design Review

January 12, 2007 By 19 Comments

…do quite frequently with MySQL :) [+] Compact Indexes Compact Indexes are great. Huge indexes is where large portion of … engines, it certainly can be applied for MyISAM and Innodb. I remember there were plans … is missing so far. [-] No protection from partial page writes This means if single page …

Filed Under: Insight for DBAs Tagged With:

Comment: Falcon Storage Engine Design Review

January 12, 2007 By 19 Comments

… Innodb uses alternative approach with adaptive hash indexes. Two sizes: Right if you say max…so concurrency increases – on low concurrency even MyISAM with table level locks does just fine…. techniques however are needed to prevent from partial page writes breaking database. No uncommitted changes…

Filed Under: Insight for DBAs Tagged With:

Post: MySQL EXPLAIN limits and errors.

July 24, 2006 By 6 Comments

… ? Is it expected to rebuild index by sort of key_cache as it can for MyISAM. EXPLAIN may take long time In… passed to the next step – there could be some non-index based filtering applied. The other reason for difference is join… enough to perform sorting or use temporary table with partial result set (partial rows) and than compose full rows by reading data…

Filed Under: Insight for Developers Tagged With: ,

Post: My Innodb Feature wishes

May 4, 2006 By 30 Comments

… I’d like to see Packed indexes. In many cases then difference in Performace with MyISAM and Innodb is huge for read… the fact Innodb does not have prefix compression for its indexes, as MyISAM has which in certain cases makes them 10 times… 4 concurrent queries). The problem seems to be at least partially due to global mutexes in many places so it is…

Filed Under: Insight for Developers Tagged With:
« Older Posts