MySQL Setup Question

MySQL Setup Question

by Zbigniew Fiedorowicz -
Number of replies: 2
Hi Martin and Eloy,

This question is unrelated to Moodle, but is a general question about MySQL. The developers of the WeBWorK web-based mathematics homework system have recently developed some enhancements. In particular, they now use MySQL instead of GDBM as their database.  In their setup they use a single MySQL database for every course they create. Thus their MySQL database model is many databases (potentially hundreds) with a few tables per database (currently 8 per database), as opposed to Moodle, which uses one database with lots of tables in the database.

Now I see evident drawbacks in the WeBWorK model of MySQL use. For instance course creation or deletion requires access by a MySQL superuser who can damage non-WeBWorK related databases (e.g. Moodle) on the MySQL server. However the WeBWorK developers seem to think that there are some efficiency benefits to doing things their way. In particular, they think their model allows more database connections than the alternative.

What are your thoughts on this? (Any other MySQL experts are welcome to chime in.)

It's not difficult to modify the WeBWorK code to use the one database many tables approach.

Zig 
Average of ratings: -
In reply to Zbigniew Fiedorowicz

Re: MySQL Setup Question

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
At a glance is seems like a strange design decision (although I know nothing of WebWork). I cannot possibly see any efficiency benefits in this model - the number of connections to MySql has nothing whatever to do with the number of databases, and yes, I agree, it sounds like an administrative and security nightmare.

I have worked with MySql a *lot* over the years, and while I don't think of myself has an expert, in a previous life I used it in projects that would frighten most Moodle administrators (tens of thousands of users - tables in the gigabytes). I have lost a lot of sleep over table optimisation and wouldn't have dreamed of doing it that way smile
Average of ratings: -
In reply to Zbigniew Fiedorowicz

Re: MySQL Setup Question

by Martin Dougiamas -
Picture of Core developers Picture of Documentation writers Picture of Moodle HQ Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers
If they have come from GDBM then I can imagine why they went for multiple databases, but this was probably more of a visualisation issue than a technical one.

As Howard said the number of connections is not a factor ... in fact their way will probably require keeping many more connections open to MySQL at a given time.

Requiring rights to create/delete databases is enough of a problem in itself.
Average of ratings: -