Hi,
As mentioned by other posts in this thread memory usage
is due to that in 4.1 and 5.0 MySQL Cluster implements
VARCHAR through fixed size using the maximum size. In 5.1
MySQL Cluster implements true VARCHAR support and this will
significantly bring down memory sizes if you use a lot of
VARCHAR fields.
Performance-wise one needs to remember that it isn't really
possible to compare MyISAM and MySQL Cluster. As an example
when an UPDATE takes place in MyISAM it is a fully local
event where one only updates things in the local MySQL Server.
Also MyISAM isn't transactional so no log records are
produced as part of the UPDATE. For MySQL Cluster an UPDATE
means that we do a fully distributed transaction consisting
of a prepare phase, a commit phase and a clean-up phase
(complete phase). MySQL Cluster is highly optimised so the
overhead of distributed transactions is still very low. We
have mady many benchmarks where the base throughput of a data
node is superior to comparable solutions. However latency of
queries in one thread will always benefit the local solution
where the entire SQL statement can be executed in the same
thread and this is especially true in non-loaded systems where
the single thread doesn't have to compete for mutexes and other
common resources.
MySQL Cluster actually behaves in the opposite manner. The overhead
of a statement decreases as more threads execute in parallel (up to
a certain point). This is due to that many threads share the same
communication channel, gets executed in the same context and thus
overhead for context switching decreases. If you on top of this also
starts adding MySQL Servers you see an even better throughput.
Finally a last note is that 5.0 has some performance optimisations
that improves performance of general queries where batching occurs.
Join queries might as mentioned in this thread require some tweaking
to get good performance. MySQL Cluster is very good for certain type
of queries (there are even a few variants where MySQL Cluster
outperforms MyISAM) but there are still performance issues for general
join queries. These are currently worked on and will be handled in
future MySQL versions.
So is clustering viable for production. Most deifinitely yes, I write
this forum post using my ISP's DNS and DHCP server that implements
an NDB Cluster solution, there are many other solutions out there with
many million users that use MySQL Cluster in production. So no doubt,
MySQL Cluster is viable for production even in very high load situations.
Does this mean MySQL Cluster is the hammer that fits all solutions.
Naturally not, we know of many improvements that can be made to
increase the footprint of where MySQL Cluster fits, 5.0 added a couple
of very important performance optimisations, 5.1 adds disk data for
non-indexed data, 5.1 also adds the aforementioned true varchar support.
5.2 and onwards will continue to add to this list of improvements.
Rgrds Mikael
John Kowal wrote:
> We have started to implement a sql cluster using 2
> dual proc xeons with 2gb ram a piece. Starting at
> sql5, we have had unbeleivably long load times for
> select statements, almost x5 and x6 more than on a
> normal ISAM format. After some tweaking, we have
> been unable to bring these lengths down to an
> acceptable level, and have seen a extremely high
> rate of load time accross our 2 boxes.
>
> We rolled back to 4 to make sure it wasn't an
> issue with 5, and still have yet to have
> satisfactory results. I was hoping anyone who
> might have some reccomendations on tweaks, other
> than things they have been listed in FAQ's would
> enlighten us. I am pretty close to saying Mysql is
> not production capable for high load clustering at
> this point, and at best is only light load
> capable.
Mikael Ronstrom
Senior Software Architect, MySQL AB
My blog:
http://mikaelronstrom.blogspot.com