Databases: Large 50000 installation architecture

Databases: Large 50000 installation architecture

by Ronan McG -
Number of replies: 7

Hi all,

I work for an Australian uuniversity, who are considering moodle 2.0 next year. I am one of the dba's involved in the architecting of the solution and am considering Mysql cluster as an option. I've googled quite a bit for mysql *cluster* / moodle information but its quite sparse. I'm not interested in a postgres / mysql / other DB discussion, I'd like to focus on mysql cluster as the topic.

I've modded the schema to use ndb as the engine and am now hitting ndb's 8k row limit, because of all the varchar(255) columns. I'm actively looking for a mod / workaround and I'd like to know, from someone in -the-know ( dev / previous moodle/cluster experience ) how it could be done.

Or are there any *MAJOR* no's for moodle with mysql cluster?

I'm aware of

http://tracker.moodle.org/browse/MDL-9718

but thee isn't enough information in there to stop the hacker in me trying to get it to work / figuring out *WHY* it wont work.

FWIW - our current arch is prob similar to the following

 

2*front end apache 8*core 16G

Sun 7410 NAS appliance for the shared storage.

4x 8*core 128G Mysql DBs ( cluster 2xndb + 2x (mysql + mgmt ) )

or 2x 8* 128G mysql DB ( either master-master or mast-slave HA-replication )

 

Thanks in advance.

Average of ratings: -
In reply to Ronan McG

Re: Databases: Large 50000 installation architecture

by Deleted user -

Ronan,

I don't do MySQL or MySQL clusters, but as far as I know is that a NDB cluster needs to have all data + indexes in memory to operate. This works very well if you do a lot of reads on the DB. However, if for some reason your cluster goes down hard, you loose all un-commited data, just remember that.

From the IRC channel I got this answer:

ries_: Is it possible (with a patch?) to grow beyond MySQL ndb row limit of 8K?
MontgoDB: ries_: yes but it requires a tweak to the source.bronb: no specific 'arbitrator', no specific 'president'ries_: MontgoDB: is there a patch available for that?
MontgoDB: ./storage/ndb/include/kernel/ndb_limits.h
MontgoDB: #define MAX_TUPLE_SIZE_IN_WORDS 2013
MontgoDB: and
MontgoDB: ./storage/ndb/include/ndbapi/ndbapi_limits.h
MontgoDB: #define NDB_MAX_TUPLE_SIZE_IN_WORDS 2013
MontgoDB: ries_: no path it's just these two lines.
ries_: MontgoDB: awsome, thanks
MontgoDB: s/path/patch
MontgoDB: the unit is 4byte wordsMontgoDB: 2013 * 4 = 8052 byte row length
MontgoDB: ries_: I can't guarantee you won't run into some weird edge case either.

I would still be open to any solution then just to fixate on 'I want NDB', read the pros and cons carefully and seeif that applies well to your situation.

Good luck!

Ries

Later I also found : http://planet.mysql.com/entry/?id=24906

Average of ratings: -
In reply to Deleted user

Re: Databases: Large 50000 installation architecture

by Ronan McG -

Rise,

Thanks for that. To make it clear I'm not fixating on Cluster as the solution. My plan A is for a Single node configuration, with linux-HA/ DRDB taking over for failover in the beginning. I just want ( in the limited time I have ) to investigate what is stopping Cluster from being a viable solution to the problem.

I'd much rather mod the application / scehma rather than tweak the limits above, although I will probably investigate both. I have a 1.9 instance running on a test cluster currently, ( i had to reduce a few varchars on some tables to (180) instead of (255). Are there any pre-written / community scripts used to load data into moodle for load / failover testing that I may use, or do i need to write some of my own?

 

Thanks again

 

R

Average of ratings: -
In reply to Ronan McG

Re: Databases: Large 50000 installation architecture

by Jonathan Harker -

I would not use MySQL NDB, and I also wouldn't use the word "architect" incorrectly smile

I hear that you aren't interested in a PostgreSQL discussion. In my experience both MySQL and PostgreSQL will work fine if you throw a sufficiently hefty server at them. The difference being that PostgreSQL is so far infallible in fail situations, whereas with MySQL I am having to periodically un-corrupt tables, deal with lost data, etc. This is essentially because no matter how you dress it up, MySQL does not guarantee disk writes.

If you have a dedicated database box with PostgreSQL running on it, with a sufficiently fast disk array and plenty of RAM, it should be fine. If you want fail-over, I suggest you investigate something like two identical boxes linked with 10G ethernet, and use heartbeat and drbd for the data partition. This should also work with MySQL, but use at your own (or your University's) risk.

Average of ratings: -
In reply to Jonathan Harker

Re: Databases: Large 50000 installation architecture

by Ronan McG -

Just came out of a management bingo meeting before writing the OP, shame on me for being corrupted so easily.. :S

The reason I said I didn't want to entertain a Mysql vs PostgreSQL was and is that I would like to focus on Cluster discussion. That is still the case. I dont want this thread to be highjacked into *another* MvP discussion.

As I mentioned above, the proposed solution is Mysql with drdb/linux-ha, that is unless cluster proves itself  ( to mine and the  univeristy's  standards ) stable / capable / resilient and performant enough to warrant its use in this scenario. This is why I'm currently doing the pre pre pre checks smile and trying to gather as much community thoughts as possible to help with the decision.

Thanks

R

Average of ratings: -
In reply to Ronan McG

Re: Databases: Large 50000 installation architecture

by Ted Celestin -

Hi i've been testing moodle with MYSQL NDB cluster.

and there are serious problems when u try to create the entire moodle database into a mysql cluster.

some keys are not recognized and if u succeed moodle performance is not that great and u will need a LOT  of ram.

Average of ratings: -
In reply to Ted Celestin

Re: Databases: Large 50000 installation architecture

by Tomasz Muras -
Picture of Core developers Picture of Plugin developers Picture of Plugins guardians Picture of Translators

That is correct Ted.

To do that you would need to change the types of several columns in core Moodle database to make them NDB-friendly.

Tomasz Muras
Enovation Solutions

Average of ratings: -
In reply to Tomasz Muras

Re: Databases: Large 50000 installation architecture

by Ted Celestin -

Anybody have a dump of example of moodle database modified for ndb cluster ??

Average of ratings: -