Howard might add ... but I'll give 2 cents worth here ...
From what you've described, the DB is on a dedicated DB server, correct?
You mentioned running top there and seeing mysqld memory going to 3Meg and staying there until an operation from the code server was completed. Well, that's going to happen and should. Was swap space being used? How much of that swap space was taken up by the DB server? SWAP means disk IO ... input/output (reads/writes). SWAP isn't good for DB server performance. Have read that DB server (dediated) runs best when MySQL can use 80% of the memory resources of the server it resides upon. Best I've been able to get is 60% ... and that took some doing! :\
So ask DB server admin about SWAP space ... what's using it.
When the DB server is dedicated and not on the same server as the Moodle code, it means the web server (where the moodle code resides) has to query the DB server across the network. So networking is involved. Think I've seen many times that in this situation, the DB server config has 'networking' turned off ... for each query, MySQL/MariaDB will do a DNS check and compare what permissions (called Previleges in DB lingo) a host
(your web server) has in the 'mysql' table for the DB for Moodle. Doesn't sound like much, but if you have 2000 concurrent users doing something in Moodle and their clicks require a query of the DB, then that's at least 2000 clicks that did a DNS lookups. Those can slow down things.
Is there any automated or scheduled task on Moodle that would kick in and use resources? Autobackup of courses is one of those. If not scheduled for off peak time or Moodle still trying to finish the courses that should be backed up, that could cause slowness.
On the DB server itself ... has the server admin run MySQL tuner? When first setting up a Moodle, I'd advise running Tuner often at first ... at least once or twice a week. Tuner will, more than likely, have recommended tweaks for DB server config.
Is the DB server also serving other databases for other applications that might exist on other web servers?
Has the DB server admin turned on slow query logging to inspect the output?
Wouldn't leave that on, BTW
, as those logs get large in a hurry.
BTW, please don't post the entire slow query log. ;)
There's my 2 cents! ;)
Ok, Howard ... you're up! ;)
'spirit of sharing', Ken