We would like to setup a MySQL cluster for Moodle 2.3.1+. May i ask if there is any brother here who has setup a MySQL cluster before? If so, may you share your experience with me? Thank you very much.
Your going to need a round-robin dns setup with load balancing. Then setup two MYSQL servers and set them to syncronize(master/slave) to each other.
Thats kind of a make-shift(pre-cloud) way of doing it.
Now-a-days you probably setup one vm (virtualized machine) in your own private Ubuntu OPENSTACK cloud. However you'll probably take a small hit on performance because it's not running on bare-metal. On the other hand, with the ease of just adding another node if you need more processing power, might make it easier to expand with less downtime.
Just my 2cents.
Those are probably a few ways I would attack the problem. There is probably going to be a fair amount of trial and error.
I have researched Moodle - MySQL cluster for quite some time and haven't found any clear answer.
Therefore i tested each solution:
1- Master-Slave replication solution
2- Master-Master replication solution
3- Mysql Cluster.
Each had its pros and cons.
Mysql Cluster works with database engine "NDB". Moodle works with INNODB.
I did mysqldump, altered all the tables engines to NDB. Database creation failed.
Seems Moodle only works with InnoDB.
Any advice on how to proceed?
I have tried master-master replication with moodle database. The database was replicated but it had problems when sharing the content or moodle data. How to solve the issue of content sharing?I used unison to synchronize /moodledata between those two machines but it shows error message " Can not find data record in database table context". Any advice how to solve this issue?
You may not be familiar with the moodle.org site policies, so please check the Post your question in one forum only section.
You have already posted in five different threads:
(the search is linked in the intro).
(also linked in the intro)
Also forgot to mention, no need for DNS Round Robin or anything, they have a Load Balancer specificly built for MySQL Galera, http://codership.com/products/galera-load-balancer
I have tried master-master replication with moodle database. The database was replicated but it had problems when sharing the content or moodle data. How to solve the issue of content sharing?I used unison to synchronize /moodledata between those two machines but it shows error message " Can not find data record in database table context" in one of the machine running moodle instance. Any advice how to solve this issue?
When coming with moodledata folder in clustered environments, so far we successfully implemented file systems such as OCFS2 and GlusterFS. You may want try one of those to see if it suits your needs. Please also note in clustered setups we use memcache servers for sessions and Moodle Universal Cache as well as local cache dirs and temp dirs on each front end.
my scenario is as follows.
I have three schools where i want to use moodle. I want these schools to use common database.The problem is network outage.during day time. I want students in these schools to use moodle during day time when there is network is disconnected. These students will acess the database locally during day time whereas at night i want these 3 databases to synchronize with each other. so that the next day when student uses the moodle it is updated with synchronized database, will it b possoble to achieve this with your solution?
thanks in advance for your valueable advice.
Thanks for that helpful post - and sorry to revive such an old discussion, but can you clarify a few things:
- Do you use Memcache for your session management AND the Moodle Universal Cache (MUC)?
- Are they different servers i.e. one machine for for the MUC Memcache and another machine for the session management Memcache?
- What do you mean by "as well as local cache dirs and temp dirs on each front end"?
- Do you use Memcache or Memcached?
- And if I may ask, why?
- for our clustered environments yes, we use memcached server for both sessions and MUC.
- sessions and MUC are not stored on the same memcached instance to avoid sessions cleanup when MUC is purged. We usually use two memcached instances running on the same server but listening on different ports, however you may want to add some more memcached servers for redundancy. Please note Moodle 2.8 introduced a "clustered" setting for memcached and memcache MUC stores, so you can now use memcached instances for MUC running directly on the front ends rather than on external servers.
- local file system is usually much faster than networked file system, so if your moodledata sits on a networked file system you can get some performance gain using tempdirs and local cachedirs stored in each front end node. More info: https://docs.moodle.org/27/en/Server_cluster#.24CFG-.3Etempdir. Please note currently tempdirs stored on each front end are plagued by a bug that may require disabling them, for example when uploading users or restoring courses: MDL-44874
About memcached and memcache PHP extensions, according to the tests we performed on our clustered setups we noticed a slightly better performance using memcached PHP extension, thus for clusters we decided to use memcached PHP extension for both MUC stores and sessions. However for single server setup running local memcached instances we are also using memcache PHP extension. Your mileage may vary, as usual it is always recommended to make as many tests as you can.
You should setup GlusterFS for moodledata, Unison is not fast enough. You also need to make sure, important, that you configure each Moodle Cache ( MUC ) into memcache/d.
You can also go the NFS route for moodledata, but the same goes for the MUC as above. If you disregard the MUC settings then your site will be slow.
If you are using multimaster in a active/active scenario I would move away from it, use Galera http://galeracluster.com/ . Version 3 of Galera added some really nice features and supports MySQL 5.6 GTID's, this is especially good for a situation like ours, using WAN replication.
On a side note, we use GlusterFS+Galera+Memcache/d for cross datacenter DR/Failover. No issues or complaints from our end-users so far.
I'm currently studying a very similar setup to the one you use, with a mariadb galera based cluster. During installation, the following warnings are triggered:
!! Unexpected id generated for the Guest account. Your database configuration or clustering setup may not be fully supported !!
!! Unexpected id generated for the Admin account. Your database configuration or clustering setup may not be fully supported !!
!! Nonconsecutive id generated for the Admin account. Your database configuration or clustering setup may not be fully supported. !!
This is expected given the default configurations on increments:
Do you think this default configuration will be a problem? Thanks a lot for your input.
Yes, that is because of the increments, somewhere in my posts I had noted that. I only came across that in a dev environment because it was a fresh install but didn't run into any issues. Our current setup was migrated from a standard MySQL install, database backed up then restored to the cluster. I really don't think it will cause any issues but even if you wanted to you could install Moodle into a standard MySQL database then dump it then restore it to the cluster, id for admin would be correct.
We have been running the same moodle core database since 1.6 so it has been moved around allot!
For your below question, I would have to setup another dev environment and run that test, at the time when I did have a dev setup Moodle did not have those features yet to build out test courses/users and such.
I'm still worried with the ability to make the app crash/return errors (difficult to reproduce the same behavior) while doing the test below, still trying to figure out why.
An additional question, for those of you who have experience in these clustered environments:
1) Visit admin/tool/generator/maketestcourse.php
2) Select an M size course
3) Make create course
4) Now select the course and delete it, but while deleting it force a sudden shutdown of one of the BD nodes. App crashes or returns errors in your case?