Hi All
We are looking at setting up a MySQL V5.x cluster. We want to use 3 machines for this(1 Controller and 2 data servers). We have ± 15 Gb of data split over 42 databases. The total data grows by 100 - 200 mb per day and this will increase in the near future. We have 60 - 80 users connecting at the same time and running 300 - 800 qps depending on the day(Thursday and Friday avg. 750 qps) and transferring 1.5 mb - 2mb data per second.
What server hardware should we look at to begin with? I read somewhare that MySQL clustering needs alot of memory, is this true?
MySQL Cluster in 5.0 requires all data and indexes to be stored in memory. This means if you want to have 2 copies of data you need at least 3x amount of memory. So if you have 10GB of data to store you need 30GB, - 2 nodes by 16GB for example.
With MySQL 5.1 (currently in beta) some data can be stored on the disk.
Also make sure to evaluate cluster performance before you take a move. There are many surprising moving from Innodb for example, such as no instant deadlock detection, slow joins (often many times slower) etc.
Also make sure to evaluate cluster performance before you take a move. There are many surprising moving from Innodb for example, such as no instant deadlock detection, slow joins (often many times slower) etc.
Can you ellaborate on this please.
What would you suggest if you had to give a solution for this “client”? Performance and redundancy is the main problems.
Honestly I think replication is often better than MySQL Cluster in its current state for replication and HA. You also can use active-passive clustering (shared storage or DRBD) in very high avaiability requirements. There is also continuent solutions (commercial) which might be good.
MySQL Cluster is good for predictable applications which use very simple queries, very short transactions, have small data size and paramount availability. Most frequently telecom applications.
Note if you’re serious about availability you rather need two clusters which are being replicated as it is possible for software to fail and bring all cluster down.