MySQL MyISAM Active Active Clustering – looking for trouble ?Peter Zaitsev
Oli is saying you can use MySQL with Active Active Clustering and MyISAM tables if you follow certain rules like enabling external locking, disabling query cache and delayed key writes etc. This is as far as many articles on this point go, and in theory this is what should work. In practice however you should think about what exactly are you looking for with this setup.
Performance ? This configuration adds effectively global table locks besides extra overhead you get from external locking. Plus key cache have to be invalidated with remote writes which makes even less efficient. So this configuration would unlikely be well performing compared to other setups, ie replication based. If you can’t use replication because it is async and your load loads CPU more than IO subsystem it may sense configure multiple MySQL servers this way.
High availability ? This is where the biggest gotcha is waiting for you. If other MySQL node crashes it leaves table in potentially corrupted state which you need to check and repair before you can safely use it, which takes long time for large tables. Furthermore as I remember MySQL simply was not repairing corrupted tables in this configuration even if myisam_repair is set.
Space Saving ? As I mentioned replication can be better idea but may be the problem is your data is so large you can’t afford multiple copies ? Well in this case you’re risking more in terms of downtime while repairing large tables. Disk space is cheap these days so it is rarely the problem.
Stability ? But the main problem probably comes from the fact barely anyone is using this setup and there is no QA being run with it (at least was not running) which means you can’t be sure all new features are really being compatible with such setup. As Oli describes even query cache which was implemented many years ago in 4.0 was implemented without looking at this feature, so what are you expecting from newer stuff. The other stuff which Oli mentions is table cache which does not seems to be properly controlled. Manually doing FLUSH TABLES does not really works as it does not allow to prevent race condition.
Of course sometimes your Boss simply heard Active Active clustering is cool and you should just set up that one with MySQL is yet another argument to use it 🙂