Did anyone watch the Marcia, Marcia, Marcia episode of the Brady Bunch? Sometimes I think that MySQL is Marcia and someone else is Jan.
There is a claim about MyISAM versus InnoDB performance. It is wrong, but not that different from an opinion I hear too frequently within the MySQL community. Because of this, the focus of too much of the paper is on MyISAM rather than InnoDB.
However, acceptable performance is generally considered to be only available from MySQL’s default storage engine, MyISAM ...
A typo and an incorrect claim about lock granularity in MyISAM. Everyone knows that MyISAM uses table-level locks, not page or block-level locks.
MyISAM lasks this feature, and instead locks rows at the less-granular block level.
More details are needed to make this problem clear. Changes to catalog tables during DDL are not transactional because MyISAM doesn't do transactions. Also, crashes during some DDL operations can be a problem (InnoDB might drop a table but the delete from the catalog is not done, InnoDB might add a table but the add to the catalog is not done).
However, the MySQL catalog only operates on MyISAM. Because of this limitation, catalog corruption and administrative tasks are still problematic.
PostgreSQL does concurrent index builds. The index build runs concurrently with DML as write locks are not taken. It requires two table scans. This is great, but I am confused about the lack of write locks. My guess is that writes to the indexed table are prevented during the second table scan. MySQL doesn't support this. Long running DDL can be done in MySQL without downtime by performing the operation on a slave that is otherwise offline and then make the slave a primary after it has caught up on replication.
Is this true? Sometimes, I wish I were hacking on PostgreSQL rather than MySQL, but the lack of a storage engine API has kept me from doing that. Note that PostgreSQL didn't exist in the late 1980's, Postgres did. The PostgreSQL project converted an interesting academic project into an amazing RDBMS product. MySQL has supported multiple storage engines long before the pluggable storage engine API started.
PostgreSQL has supported multiple storage engines since the late 1980’s. It wasn’t until just recently that MySQL implemented similar functionality via their newly developed Pluggable Storage Engine API.
Partly true. One of the calls is used to get index cardinality stats and no query context is available for that. Index cardinality stats are used to determine join order. These stats also do not represent skew as a uniform distribution is assume. The other call is used to determine selectivity for predicates that can use an index. In this case the query context is provided as the storage engine is given the predicates. InnoDB estimates selectivity by walking the index from the root to leaf for the first and last rows to be scanned for the predicates. The results are amazing.
MySQL’s API presents only two optimizer-related function calls to storage engine developers. These calls, if implemented, can assist in costing plans for the query optimizer. However, because these calls do not present the context of the query to the storage engines, the engines themselves cannot accurately return a proper estimate.