Reading last few days worth of planet MySQL and commenting on some entries as you can see. The post by Oli catches my attention. There is also PDF with more details available
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 🙂
Interesting stuff.
Curious if anyone has tried any recent versions of clustering products like Continuents “uni/cluster for MySQL Enterprise” lately? I toyed with it a while ago when they were called emic and its performance wasn’t quite there, at least for our data.
Interesting….i run a data warehouse based on mysql so i can make a replication schema (storage issues)…i use 1 mysql box to write and two to read…using flush tables in the ETL process……..but always wanted to implement the active-active schema..do you have further info about it?
thanks
With replication if you write to the different set of tables it well may work.
This post is about shared storage which is very different thing.
well..not..i have a LVM GFS cluster that shares de db partition…but i use that schema…2 readers, 1writer…the writer flush the tables on the readers…