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.
Posts
10 comments:
It's not an accident that Postgres partisans (and partisans of other RDBMSes) try to equate MyISAM==MySQL. It's because if you are trying to show how your "side" is superior, it's then much easier to compare against a straw man than legitimate strengths and weaknesses.
The list of flaws with MyISAM are both long and also well known, so if you want to write a political "hit piece" on MySQL it's cheap and easy to just copy and paste what you found posted on someone's blog 5 years ago, do some minor editing, and presto you've got some much-needed attention.
There are quite a few other inaccuracies. For example, the "Support for Triggers" is exactly opposite: MySQL supports ONLY row-level triggers, and that's actually quite a shame :-) I won't argue that MySQL's trigger implementation is great -- it's not great. But it's not great in just the opposite way that the whitepaper claims.
"the analysis below will be of particular interest to those who have little or no experience with MySQL in deployment" is 100% correct.
Those who do have lots of MySQL experience will know enough to laugh at it. Which is a good thing if the people seeing this do even minimal research to see how accurate the claims are: nothing like being caught lying to help to sell something else.
MySQL is very buggy lately with quality issues and also has trouble getting their 5.1 out the door in production form.
Domas, is that you? Are you unhappy that we made you upgrade from 4.0?
You need to clarify 'MySQL' as there are many versions. 4.0, 4.1 and 5.0 are rock solid. I depend on 5.0.37. It works great. 5.0.62 looks even better.
I don't care much about the delays in releasing 5.1 as 5.0 works for me.
Mark, I will revenge for this. No, it wasn't me ;-)
Why is anybody still talking about MyISAM or MySQL 4.x anymore? Nobody should be using 4.x anymore and MyISAM is only for really lowest common denominator or legacy apps at this point (or maybe some weird apps where you need file-based backups or something).
MySQL 4.0 is the release for which MySQL became famous, sort of like Oracle and release 7.3.
For what it is worth, MyISAM still gets marketing -- it is the only storage engine for which Online Backup does anything interesting.
You know why the comparisons always use MyISAM? Because MySQL apologists have for years used MyISAM performance benchmarks to show how much faster they were than the competition, even though MyISAM barely resembled an RDBMS at all other than using a SQL interface.
The MySQL guys have gotten better about it now, but you gotta give your destractors some time to adjust to it.
I think that back in the day the official docs claimed that a UPS, careful database application programming (no rollbacks allowed) and default column values were a substitute for transactions. But times have changed.
Post a Comment