Re: Example of Cluster SLOWER than InnoDB -- help?

[John Muehlhausen]

Heikki Tuuri wrote:
> Have you set
>
> innodb_flush_log_at_trx_commit=2
>
> in my.cnf? NDB only flushes its log once per
> second, which makes the comparison unfavorable for
> InnoDB if you let InnoDB to flush its log at every
> commit. A log flush to disk restricts the commit
> speed to about 100 commits per second.

I am using transactions to protect against system failure (hardware or software). That means that at commit I want the transaction to be "somewhere safe". In InnoDB that means a write to disk at every commit. In NDB that means synchronous replication to another node. I know that I can speed up InnoDB by allowing it to lose data in the event of a server crash, but that is not an option for this application. BTW, isn't it irrelevant how often NDB flushes? That isn't meant to be the safety net-- replication to other nodes is.

> Actually, if the application is an implementation
> of a queue, it will almost certainly perform worse
> with a multi-node cluster than in a single-node
> database. You get database cache ping-pong between
> nodes if you repeatedly update the same row in
> different nodes.

It is an implementation of a queue-- I'm trying to get out of buying a MOM product for transactional messaging (such as WebSphere MQ) since my use-case is simple, and it would be convenient to do other MySQL work in the same transaction as the messaging "unit of work" to avoid a two-phase commit.

I'm going to do some testing on a two-node cluster and see what happens.