http://qs321.pair.com?node_id=480635


in reply to mysql cluster

my own db access module, which base classes DBI and over-ride execute() for this

I advise you against doing this.

Even if you succeed in changing the host overriding the execute() method (perhaps you should aim at prepare()), you would undermine any transaction that could be under way.

The MySQL replication schema may be unconvenient for its division of write-on-master and read-from-slaves, but it's rather solid. In my experience, using it with transactional tables (InnoDB) guarantees replication of transactions without a glitch.

The solution you are proposing is filter-based, and it's similar to the one offered from some commercial clusters, (emic networks, for example), which lose their transaction capabilities in the bargain.

Remember that, when using transactions, you should direct to the master the whole transaction, including any reading instruction within it. If you don't, you may get completely wrong results.

Instead of filtering, you could create an application wrapper, to send all modifying statements and transactions to the master, and rotate the slaves who should receive a simple read statement.

I don't know of any (public) Perl modules that deal with this problem. I may only recommend reading Jeremy Zawodny's High Performance MySQL (there is a sample chapter about replication).

Update
If you are tempted to solve your problem by looking at MySQL Cluster, be aware that it's a completely different database system. Every node is at the same time master and slave, but you pay this luxury with a huge amount of RAM. According to the docs, in the current implementation, you need to have enough RAM to cover twice the size of your data, plus 20%. Therefore, if your data is 10 GB, your cluster must have 24 GB of RAM available (divided among all data nodes). There are plans of changing the in-memory architecture to something more resources-friendly, but don't hold your breath.

 _  _ _  _  
(_|| | |(_|><
 _|