Using MySQL 5 cluster with Exponential

Monday 19 December 2005 4:24:19 am

As Exponential is used for more and more business critical applications, it's very important to have it installed in a failsafe an redundant environment. To achieve such an environment you need -beside other things- a redundant and failsafe database, like MySQL 5 cluster.

So this topic is dedicated to experiences with running Exponential. on a MySQL 5 cluster.

Any input is welcome, so let's share your information.

Daniel Beyer
_________________________________
YMC AG
Kreuzlingen, Switzerland
web: www.ymc.ch
____________________________________

Wednesday 21 December 2005 10:38:23 am

As far as I know about MySQL Cluster, it requires LOTS of RAM to run properly. And when I say lots, it's not 2 GB... on mysql.com, the suggest something like 16 GB... So maybe you have a bottleneck here ;-)

We are going to setup a big website using MySQL Cluster in a few days, I really hope it's going to work correctly. I'll post infos about that later one !

Thursday 22 December 2005 2:16:24 am

Hi,

I actually don't think it's the RAM. I configured the hole thing to work with the single GB on each machine without using the swap. But just to be sure it isn't the missing RAM we already ordered 4 other GB of RAM, so each machine wil have 3 GB next week.

Btw: MySQL Cluster 5.0 is know to have problems with joins, doing a full search on the table for each single join. Maybe that's the problem...

Below is the result of atop 1.14 during a Exponential page load:

PRC | sys 3050 ms | user 7980 ms | #thr     106 | #exits     0 | #zombie    0 |
CPU | sys     16% | user     52% | irq       6% | idle    123% | wait      2% |
cpu | sys      8% | user     28% | irq       5% | idle     57% | cpu000 w  1% |
cpu | sys      7% | user     24% | irq       1% | idle     66% | cpu001 w  1% |
MEM | tot  883.7M | free    8.5M | cache 184.8M | buff   42.4M | slab   28.4M |
SWP | tot    2.6G | free    2.6G | vmcom   1.3G | swin       0 | swout      0 |
DSK |         sda | busy      4% | read       0 | write     69 | avio    5 ms |

Daniel Beyer
_________________________________
YMC AG
Kreuzlingen, Switzerland
web: www.ymc.ch
____________________________________

Thursday 22 December 2005 3:45:37 am

Hi Gabriel.

Yes, joins are causing the traffic. That because every full search on the database is done across the cluster, which in this case need to exchange the db between the nodes.

Some might now think about turning only a single node on, which is exactly what came in my mind. If you do so, no mor heavy traffic is exchanged, but the speed won't increase much (it's about 5 seconds faster, which is the time the giga-net needs to transport the data). So it can be said, that in our setup the bottleneck can't be the network.

BTW.: The response time using the myisam engine on a clusters mysqld is just great. So the bottleneck isn't the link between the webserver and the cluster, either. But using myisam on a clusters mysqld won't give you any redundancy, as it is stored on a single machine.

Daniel Beyer
_________________________________
YMC AG
Kreuzlingen, Switzerland
web: www.ymc.ch
____________________________________

Thursday 22 December 2005 5:16:04 am

You're right, replication should be used for business critical sites today. But it lacks of redundancy and easy scalability, as - in common - you only have a single master. If the master goes down you probably won't lose data, but your Exponential isn't avalible anymore. And if your master becomes to slow for it's job, you need to upgrade the hardware for this single machine. In a cluster you just have to add an other machine.

Those facts are why we want to use a cluster, as it is the only realy redundant and easy-scaling solution.

Daniel Beyer
_________________________________
YMC AG
Kreuzlingen, Switzerland
web: www.ymc.ch
____________________________________

Thursday 22 December 2005 7:46:02 am

If you enable the use of a slave server in Exponential, every read-query will be send to it and every write-query will be send to the master. If you enable more than one slave in Exponential only one random slave is used. If your master goes down, Exponential won't answer anymore (someone correct me if this behavior changed recently). So simple master/slaves replication won't give redundancy.

There are setups for having multiple masters, meaning a slave is turned into a new master if the primary master goes down. But with replication you always have a single master (otherwise it would be a cluster). I personaly don't trust the master-switching thing, as there is always a risk sth. goes wrong with it. That could lead eg. to an inconsistent database, which might is much more worse than having a site down for a while.

I hope mysql is working on the join issue, as I think this is the current problem with the weak perfomance.

Daniel Beyer
_________________________________
YMC AG
Kreuzlingen, Switzerland
web: www.ymc.ch
____________________________________

Friday 23 December 2005 10:59:10 am

Hi Gabriel,

you're right again. Having the DB only in the memory is a problem. But this hopefully will change with 5.1. But I don't think it's a problem with the right hardware. Todays servers normaly make use of ECC memory, and so smaller mistakes in the memory can be handeled without any data corruption. The servers we plan to use for the cluster in future are from the "zeus"-type. And those machine are capable of memory minoring. This means memory is handel in a RAID-1 style: one hole bank falls out -> the other takes over. But beneath the redundancy in a single machine (RAID, Dual-Power, Dual-CPU, Dual-Anything), you can plug as many machines in the cluster as you want to. So the redundacy inside a single machine might isn't anymore the main issue. The only thing you have to worry about, is a complete powerfail of the hole cluster (no power, no more data in the RAM of any machine).

So personally I think the cluster is the right thing in future. But it seems like the hole cluster thing needs some more development (on mysql's side).

Thanks a lot for your input so far and have a merry Christmas!

Daniel Beyer
_________________________________
YMC AG
Kreuzlingen, Switzerland
web: www.ymc.ch
____________________________________