Beefy Boxes and Bandwidth Generously Provided by pair Networks
laziness, impatience, and hubris
 
PerlMonks  

comment on

( #3333=superdoc: print w/replies, xml ) Need Help??
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.

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

In reply to Re: mysql cluster by gmax
in thread mysql cluster by InfiniteLoop

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":



  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.
  • Log In?
    Username:
    Password:

    What's my password?
    Create A New User
    Chatterbox?
    and the web crawler heard nothing...

    How do I use this? | Other CB clients
    Other Users?
    Others avoiding work at the Monastery: (8)
    As of 2021-01-21 15:49 GMT
    Sections?
    Information?
    Find Nodes?
    Leftovers?
      Voting Booth?
      Notices?