Hi everyone,
We are currently running moodle with multiple front-end webservers (serving out of a shared storage SAN) and a networked MySQL server on the back-end. The load on our MySQL server is getting a little high* during peak usage (multiple classes in labs taking tests simultaneously), and we're planning on aggressive growth over the next few years. We've got some money ear-marked for upgrades this year, but given the coming budget shortfalls in the state of California, we're not expecting to have the fiscal resources for upgrade over the next few years.
We're planning on rolling out a MySQL cluster to replace our current single-server database**. We'll be rolling this out in 3-4 months, but wisdom (obtained through last minute panic, admittedly) suggests we should begin our planning now. I'm attaching a graphic to show the current proposal (since a picture is worth a thousand words, it should explain things better than I can). We're planning on using MySQL 5.1, as it should be finalized by then. The blue lines indicate a 1GB ethernet network, and the green lines denote our 1GB Fibre Channel SAN links.
Does anyone else have experience upgrading to a MySQL cluster, and can you offer any advice on either: a) things to do, or just as importantly, b) things to avoid?
Thanks in advance!
- Kevin Metcalf
* Our scraping of the CPU usage over time shows we are above 80% utilization during peak times. This usage spans 5-10 consecutive minutes, which tends to make me nervous.
** Technically, we have a master-slave replicator setup, but we only use the slave for backups (you don't have to take the master down), and for "emergency promotion" should the master ever fail.
In reply to kevin metcalf
Re: Advice requested for running Moodle 1.6 w MySQL Cluster
by Taylor Judd -
Hi Kevin,
I don't have much experience in clustering but at San Francisco State we were running into similar performance problems. I also just attended the mysql conference up here in santa clara and as great as clustering is, it's not the recommendation in most cases. The takeaway I got is that unless your database is running thousands of queries per second than vertical scaling is likely a better solution.
Can I ask briefly, are you on myISAM or InnoDB? Have you done much performance tunning or are you out of the box? How big are your largest tables, likely quiz and log releated? What portion of your CPU load is spent on disk I/O?
The solution we came to at SFSU was to switch from myisam to INNODB and throw a whole bunch of RAM at the buffer_pool. By installing enough RAM to cover the whole database, minimum 15 gigs in our case, we made it so every table could exist in memory. Now writes occur to disk only for ACID compliance. There were a lot of other factors and issues to consider and I'm trying to write up something longer for this forum or another, but I'd be happy to talk to you about them.
In any case,
I'd recommend reading up here:
http://www.mysqlperformanceblog.com/
and here:
http://develooper.com/talks/
I don't have much experience in clustering but at San Francisco State we were running into similar performance problems. I also just attended the mysql conference up here in santa clara and as great as clustering is, it's not the recommendation in most cases. The takeaway I got is that unless your database is running thousands of queries per second than vertical scaling is likely a better solution.
Can I ask briefly, are you on myISAM or InnoDB? Have you done much performance tunning or are you out of the box? How big are your largest tables, likely quiz and log releated? What portion of your CPU load is spent on disk I/O?
The solution we came to at SFSU was to switch from myisam to INNODB and throw a whole bunch of RAM at the buffer_pool. By installing enough RAM to cover the whole database, minimum 15 gigs in our case, we made it so every table could exist in memory. Now writes occur to disk only for ACID compliance. There were a lot of other factors and issues to consider and I'm trying to write up something longer for this forum or another, but I'd be happy to talk to you about them.
In any case,
I'd recommend reading up here:
http://www.mysqlperformanceblog.com/
and here:
http://develooper.com/talks/
In reply to Taylor Judd
Re: Advice requested for running Moodle 1.6 w MySQL Cluster
by Thomas Haines -
before you go out and buy your cluster hardware, it might be worth checking this out:
http://tracker.moodle.org/browse/MDL-9718
it looks like moodle doesn't play well with the ndb engine
http://tracker.moodle.org/browse/MDL-9718
it looks like moodle doesn't play well with the ndb engine
In reply to Taylor Judd
Re: Advice requested for running Moodle 1.6 w MySQL Cluster
by Dan Poltawski -
Just to add to that list of talks, I found some really useful stuff in many of the presentations and notes from mysql conference, available on their site.
In reply to Taylor Judd
Re: Advice requested for running Moodle 1.6 w MySQL Cluster
by Christopher Medina -
Taylor,
I know this is quite an old post, and this is probably a shot in the dark; however, I seem to be struggling to find some relevant info on how to proceed with a project that I am working on. I don't know if you still check the Moodle forums or what.
Essentially, my organization would like to move to Moodle 2.0 from our currently existing mixed 1.8 - 1.9 farm. We have a very disorganized and poorly pieced together environment (from what I can tell), and was essentially looking for guidance on how to proceed. I was looking MySQL clustering, etc. to attempt to establish a MySQL HA solution for our Moodle farm; however, from what I have read this is not the way to go with Moodle.
My understanding is that SFSU is ran like a well-oiled machine, so if you could assist with some manner of guidance in any way shape or form it would be much appreciated.
Thank you,
P.S.
If you would like to you may email me at my personal email address:
christopher.oasisgames@gmail.com
In reply to kevin metcalf
Re: Advice requested for running Moodle 1.6 w MySQL Cluster
by Sojiro Sato -
I'll throw in a few of my cents and recommend not clustering but using memcached instead. http://www.danga.com/memcached/
It might take a bit of developer time but it would work much better.
It might take a bit of developer time but it would work much better.
In reply to kevin metcalf
Re: Advice requested for running Moodle 1.6 w MySQL Cluster
by Taylor Judd -
Hey Kevin,
You're post got me thinking more about mysql cluster and testing it out myself. It's a fun tool and I think you could see a good setup with moodle on it. But I'm still scared of some of the customizations and performance bottlenecks that appear when switching to this type of system. Anyway there is a good whitepaper from mysql about cluster and evaluating when/where to use it and how to do a basic setup. I'd recommend taking a look at it if you haven't already. I found it very informative:
http://www.mysql.com/why-mysql/white-papers/mysql_cluster_eval_guide.php
You're post got me thinking more about mysql cluster and testing it out myself. It's a fun tool and I think you could see a good setup with moodle on it. But I'm still scared of some of the customizations and performance bottlenecks that appear when switching to this type of system. Anyway there is a good whitepaper from mysql about cluster and evaluating when/where to use it and how to do a basic setup. I'd recommend taking a look at it if you haven't already. I found it very informative:
http://www.mysql.com/why-mysql/white-papers/mysql_cluster_eval_guide.php
In reply to kevin metcalf
Re: Advice requested for running Moodle 1.6 w MySQL Cluster
by Rahim Virani -
If I can get a workstation that is powerfull enough to do VMWare for three vmware servers I will test again, looks like there is alot of interest in this but I haven't had time to do R&D on it since Moodle 1.6, our instance has grown from 1500 to 15000 so we are running into some issues as well.
Has anyone else tried NDB clustering with Moodle 1.9?
Has anyone else tried NDB clustering with Moodle 1.9?