failover and load balancing

[Fajar A. Nugraha]

On Wed, Apr 22, 2009 at 8:43 PM, Uwe Kastens <kiste at kiste.org> wrote:
>>> The problem with master master for
>>> mysql is, that you have to resync each time you are dropping a table, a
>>> view etc.pp.

No you don't.
When setup correctly, all SQL statement on one node will be executed
on the other node as well. That includes DDL like creating/dropping
table, or adding/removing users.

An exception is if you EXPLICITLY don't replicate changes to mysql
schema. In that case what you say might be true.

>>
>> It depends on what you are doing. If you want to read out you user database
>> for authentication you are right. But If you want to write accouting you have
>> a lot of writes. I have seen up to 300 writes/sec for a small national
>> provider.

If you have enough memory then with Innodb engine on MySQL you can
easily serve all reads from Innodb buffer pool (a.k.a. memory cache).
That way only writes will be disk-bound. My db currently handles over
100k reads/s, mostly served from buufer pool.
That way I only need to scale the disk enough to handle writes
(currently  around several hundred writes/s)

> I would prefer to have some fallback solution to write data to a flat
> file if the database is offline (which should be a question of minutes
> or an hour)  and import it later on.

which is what buffered-sql does for acct.

Regards,

Fajar