Web Server + Database selection/preferences (Apache, MySQL?MariaDB, Nginx, PostgreSQL)

Web Server + Database selection/preferences (Apache, MySQL?MariaDB, Nginx, PostgreSQL)

by Jerry Lau -
Number of replies: 6

Hello folks,

We have decided to go ahead with putting our database outside of our moodle server to increase performance a bit.

This gives us some thought as to whether we should also reconsider our choice of web server and database.

We are currently on Apache 2.4 (16 DB Ram) + MySQL 5.5

What are your preferences and suggestions? We'd like to see if we can handle up to about 100 users concurrently. These will all be VMWare O/S.

Apache + MySQL/MariaDB?

Apache + PostgreSQL?

Nginx + MySQL/MariaDB?

Nginx + PostgreSQL?

We don't have any experience withNginx nor with PostgreSQL but we have time to learn and perhaps adopt these new combinations.

What are your thoughts? Any recommendations?

Thank you.








Average of ratings: -
In reply to Jerry Lau

Re: Web Server + Database selection/preferences (Apache, MySQL?MariaDB, Nginx, PostgreSQL)

by Ken Task -
Picture of Particularly helpful Moodlers

Since you have VMWare virtjualization, one could setup several sandboxes with whateever combo you'd like to test ... beginning with the Operating system ... not mentioned but very key to many things Moodle.   Have preference for CentOS but many others run Ubuntu ... just make sure it's long term support version.   Think you'll see more Moodle docs written for Debian or Ubuntu ... not much for CentOS (not complaining).

That kinda relates to acquiring the latest and greatest PHP ... Ubuntu had as a goal at one time to replace Windows - so one could get out on that edge towards 'bleeding' ... uhhhh stable is better (IHMO)!

Apache vs nginx vs other ...

Apache itself can be run as fastcgi or as a mod.   Think, in my research, have read countless blogs etc. that compared Apache run as fastcgi vs mod and while there is a performance gain with fastcgi (php-fmp) I didn't see anything that said 'by far'.  

Judging the number you've provided (100 concurrent users) don't think the web service, whatever you choose would be pushed much at all.  Besides that concurrent thang pertains to DB more than apache (web service), me thinks.

Web server drive partitioning ... think I'd make sure that one had at least 1TB data partition for Moodle data + backups ... to begin.  Filedir could large in a hurry if doing automated backups and some/any courses are teaching anything of a digital nature.

Now-a-days ... the DB is more important ... here goes 2 cent opinion ... MariaDB is a 'drop in replacement for MySQL (so it claims) ... and it might be doing great things to improve but I don't think there is anything in Moodle code that takes advantage of any improvements to the MySQL base it began with.   Config of MariaDB a little different ... can't say I like it any more than MySQL.  Was helping someone with MariaDB issue (drive ran out of space) and what was reported for help was actually MySQL help - hmmmmm.

The DB server drive doesn't have to be partitioned ... DB (MariaDB, Mysql maybe postgres too) will use /var/lib on just about any Linux distro I think.  The *very last thing* one would want to happen is to have the drive run out of room on the DB server (helped a 'customer' one time that had allowed that to occur ... only saving grace was an sql dump of the primary DB's about a week before).

Since you mentioned VMWare .. investigate attaching a data volume in/on/with whatever OS you choose.

Also, networking ... would think the Web server and the DB server needs to be as few hops away as possible from one another.  Web server on a 192.x with DB server on a 10.x has to run through some switch somewhere.   While switches are seldom the bottle neck, they an develop issues of their own which would affect transfer between web service and DB.

That's my 2 cents! ;)

'spirit of sharing', Ken



Average of ratings: -
In reply to Ken Task

Re: Web Server + Database selection/preferences (Apache, MySQL?MariaDB, Nginx, PostgreSQL)

by Jerry Lau -

Thank you Ken.

I take it that once my database server is up, I just have to adjust the config.php to point to the "host" where the db is stored.


And create the user account and password on the database server so that the moodle server can remotely connect to this database server correct?

For example:

On Database server:

GRANT .... ON developmentmoodledb.* TO remoteuser@’mydevdb.server.com' IDENTIFIED BY ‘secret_password’;




$CFG->dbtype    = 'mysqli';

$CFG->dbhost    = 'mydevdb.server.com';

$CFG->dbname    = 'developmentmoodledb';

$CFG->dbuser    = 'thedatabaseuser';

...


Am I correct?





Average of ratings: -
In reply to Jerry Lau

Re: Web Server + Database selection/preferences (Apache, MySQL?MariaDB, Nginx, PostgreSQL)

by Ken Task -
Picture of Particularly helpful Moodlers

That's about it ... cept think I'd test first .... IF one has command line access to both servers, that is.

On the DB server:

Same grant for server/user etc. but change the FQDN of the web server to it's IP address.

Then turn off networking in the DB config ... that allows access by IP and won't be doing a DNS check each and every query the web server makes.   If I re-call correctly that line is 'skip_networking' in the MySQL cnf file (might want to Google that).   Any change there requires restart of DB service.

On the web server - use the mysql client and test connectivity:

mysql -h [IP address] - u [username] p[password] [ENTER]

Connected?  Great .. now the next test ... query some tables .. the users and the larger log tables.

At the mysql> prompt on the connected client of the web server:

select * from mdl_user; [ENTER]

That will display all users with all columns ... what you are interested in is the seconds it takes to make that query.

Another query

select * from mdl_logstore_standard_log; [ENTER]

That table is one of the largest and think that's the one queried when in Moodle Admin clicking logs.

Again, lots of data ... pay attention to seconds.

You could do the same test on the DB server itself.   Should see a slight different in how long the queries took - slightly longer query coming from web server.   Faster when using DB server client.

IF it's significantly different then you might want to think this.   Could be your need for speed could be solved by tuning the MySQL server on the Web server.

Seem OK?   Well then make the changes to the config file of Moodle

On the config.php file ... think I'd copy the lines about DB, paste them back in, and comment out those lines that applied to the localhost (DB sever on the Web host) leaving the dedicated DB server info active.   For reference or for quicking switching back if it doesn't work out for ya.   When you edit config.php file there is no need to restart any service ... just hit the site.

Obviously, IF successful and you decide it's better on the dedicated DB server, shut down the MySQL on the Web server.   *think I'd leave the DB there, just in case*.

That could free up some memory on the web server allowing you to launch more apaches on boot up ... (don't think that was the problem, with just 100 concurrent users, BTW).

Also strongly suggest (if you can) installing and using MySQLTuner for testing and tweaking the DB server.  Right out of the shrink wrap MySQL DB server using the default cnf might not be enough.   Also, MySQL provides some sample cnf files ... might want to take a gander at those - they are off in /usr/share/ some place (on CentOS/RHEL systems), IF I re-call correctly.

'spirit of sharing', Ken




Average of ratings: -
In reply to Ken Task

Re: Web Server + Database selection/preferences (Apache, MySQL?MariaDB, Nginx, PostgreSQL)

by Jerry Lau -

Thanks Ken. Yes those are some of the steps I would use to test connectivity between them.


Happy New Year everyone!

Average of ratings: -
In reply to Jerry Lau

Re: Web Server + Database selection/preferences (Apache, MySQL?MariaDB, Nginx, PostgreSQL)

by Usman Asar -
Picture of Plugin developers Picture of Testers

even 4GB ram combined with DB on same server can serve 100 concurrent, moodle prefers MySQL/MariaDB (both interchangeable) so consider any of the two in preference.

Where web Server is concerned nginx is only faster in static page delivery when compared to Apache so if you are skilled at apache already better enhance skills in PHP accelerators

Average of ratings: -