We have Moodle 3.0.5 but I also installed the latest 3.1 on our test server and get the same results. It takes 22 seconds after clicking "turn editing on" in the gradebook to see the full grader report. One teacher said when he uploads grades it takes 10 minutes.
This following example is an example from a class with 40 students and 189 grade items for each student.
What do I need to ask my IT department to look at? I have shown them the page on Performance. They say we have plenty of RAM. I had them add the OPCACHE configuration but saw no increase in performance (apparently it wasn't there before?) https://docs.moodle.org/30/en/OPcache
I never had this complaint until we upgraded to Moodle 3.0 from 2.8, but who knows what else has happened with the server setup.
What do you experts see looking at this report?
I have another class with 56 students and and maybe 144 grade items that is slower.
Well... it's doing *something*...
Can you give us a bit more information about the configuration of your system? What is the page load time like on other pages? Is your cache set up properly (my favourite... trying to cache to NFS filespace or similar)?
What monitoring do they have in place (if they don't then they should) - what is the system doing during these periods? Make sure you check the database for slow queries. Is the database properly tuned (has it been tuned at all)?
I asked the server administrator the following and below that is his reply. Since I am not the server person, I don't really know what to ask except what I read from the performance page. The response from them is that it's Moodle causing the problem. I asked if he could tell if we were running out of RAM and he said he only saw swapping happen once in awhile. A regular course page loads in .3 seconds. I've attached a screenshot of that below.
When he responded that we didn't have a cache on our system, I had him put in the code found below, although I don't know how to tell if the code is actually working or not. We've had Moodle for 7 years now.
My question to server admin:
Can you tell me what we have for the following on our system? Disk Space, Processor, Memory
4 core Intel Xeon CPU @ 2.67GHZ
4GB HD with 841Mb free
I don't believe we are running any caching
moodledata directory is a virtual disk drive connected to the cluster that is using iscsi to the san
we don't have a php accelerator
memory limit in php.ini is currently set to 512M
Server people always say "Moodle is the problem" when they don't know how to optimise the system for Moodle It's a very big PHP application and requires a bit of effort and knowledge to get working well. You can't just install PHP and a database, throw Moodle at it and expect it to perform properly.
4GB HD.... well, I hope that it is typo. It's incredibly small.
4GB of RAM isn't a lot. It doesn't leave you a great deal of headroom. You dodged the question about monitoring - that tells you if you are running out of some resource at times. It's not "nice to have", it's important so I'm going to say it again! You also don't say what your operating system is (if this is Windows then 4GB is definitely not much RAM).
Caching... Go to Site administration > Plugins > Caching > Test performance. What are the results? If you've done nothing about caching then the file cache numbers really matter to you. ISCSI could *easily* be a big problem here.
You should have a PHP accelerator - it's for free. Make sure you are running a supported version of PHP. Preferably 5.6. Enable OpCache.
What database are you using? Has it been tuned? What web server are you using? Has it been tuned? It's unlikely either (on any platform) will be optimal for Moodle straight out of the box. Typically the web server will try to use all your RAM and the database server won't use enough.
Having said that, none of this is definitely your problem but we have to get all this stuff out of the way first.
You are asking all the right questions! I don't know even what to ask the Server admin, so this is VERY helpful. The numbers I gave you are the numbers he gave me, so I'm assuming they are correct. Below is more information and I've attached what I did after running the Cache Test Performance test. Of course I don't understand anything it means.
Is there a certain monitoring program you would recommend they have? All I know is when I went to his office to ask, he typed some command to see the RAM results and disk swapping results and said he was watching it all afternoon. Even on disk space, they say the SANS is getting low on space, but I don't know the questions to ask them.... I'm pretty sure the SANS holds the moodle server as well as the Moodle data. The database I think is on another server that is set aside for all the database activities that the college uses (moodle, zabbix, drupal, isticket, moodle test, and other misc.).
We have also had multiple network crashes this semester and last when taking Moodle quizzes. All I hear from them about that is that "It looks like the student is still connected to the internet, but not being able to get through to the network". Not sure if that is related also somehow.
What are the recommended requirements for each (moodle server, moodle data, and moodle database - RAM, Storage, etc). ? We are saving 5 years of data, we have about 1,000 students in the school total each year.
Like you can see from the attached picture, we don't have Barracuda file format for our database yet.
Maybe I need to take the Performance Recommendation website and go through it step by step with them to make sure everything is being looked at!
Thanks for your input. (need to tell them about "Typically the web server will try to use all your RAM and the database server won't use enough.")
PHP Version 5.5.27 System Linux moodle.ucollege.edu 3.10.0-514.6.1.el7.x86_64 #1 SMP Wed Jan 18 13:06:36 UTC 2017 x86_64
Apache Version Apache/2.4.6 (CentOS) OpenSSL/1.0.1e-fips PHP/5.5.27
- Your file cache numbers look pretty good so it probably isn't that.
- You *are* using OpCache but the settings aren't quite those that are recommended (see OPcache). That wouldn't be my first thought either though. OpCache generally doesn't make much difference to page load times but it does keep the server load down. Nevertheless, it would be worth getting it right
So we are back to "what is the server doing when it's taking minutes to load a page?". Again, I would be inclined to run (something like) 'munin' on the server and look for any peaks at those times. You can sit next to your server admin and ask them to monitor the system while you are waiting for these tricky pages too.
Getting more complex, you can identify the log running process and run 'strace' to see what it is doing (see, for example, https://derickrethans.nl/what-is-php-doing.html).
Also, make sure slow query logging is enabled on the database server.
It might also be worth running with Debugging up full for a while. Check for strange messages and (in particular) check the Web server / PHP error log.
You are really just trying stuff hoping to spot that smoking gun.
Pardon intrusion ... there are many possible bottlenecks, but since you've mentioned ...
"It looks like the student is still connected to the internet, but not being able to get through to the network"
Student connection to the internet where? At the college? From home? Student Commons?
'get through to the network' also indicates routing or firewall(s) [boundary/server] issues.
Basically, networking comes before application (Moodle being the application).
As far as what to look at ... think one more suggestion is in order ... the DB server sounds like it's dedicated ... ie, not located on the same server as the moodle code. And I see it's MariaDB a dropin replacement for MySQL which RH and then follows CentOS 7's default for a DB. Am assuming that an appropriately configured my.cnf file exist. One of the reasons for slowness is the ability of the DB server to respond to request for data (that still could be a networking issue but one also should check config of DB server). Thus would highly recommend to your server folks the running MySQLTuner and, in the case of backend dedicated DB server, running tuner from both the DB server itself *and* from the web server serving Moodle code (remember, networking comes before application).
In addition ... ask the server admin for a screen shot or text clip from the output of top ... that's a command line tool that is one of the troubleshooting tools that exist on CentOS 7 boxen. SWAP space is something that server admins attempt to avoid ... but with 4Gig memory and a heavy hit Moodle with the numbers the college might be talking about, think that would be hard to achieve. Not saying this is always true, but find that 16Gig memory on a server that is both serving Moodle code AND the DB to be a 'sweet spot'. Moodle is resource hungry and will continue to be so when it comes to memory, more is obviously better than not enough ... even if it's more than one might need. IF the DB server or the front end Web servers are 'guest' OS's under a VM setup, it's easy to increase memory for those.
'spirit of sharing', Ken
Thanks to both of you for your input!
I took a look at the resources when they ran the top command, and everything looks good. The only thing that they saw happen was that the io top command for the sql server stayed at 3 meg for the entire 38 seconds that it took to delete a grade item for 50 student and 200 grade items.
I don't know what to ask the server admin regarding your question "what is the database doing"? What can I specifically ask him? Thanks!
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
Thank you Ken. My understanding is the the DB server is used for ALL of the applications at the college. Moodle, The college website, the college portal (PowerCampus), and probably other things. This is what I see he says is taking up disk space on the SQL server (moodle, zabbix, drupal, isticket and others).
I will ask him your other questions! Thanks so much.
Nothing to add... however, I think we both strongly suspect a database issue (having more or less excluded all the other usual culprits).
The thing is that databases rarely work well "out of the box". Some tuning is required. Even some fairly naive optimisation using off the shelf scripts is a whole lot better than nothing.
Depending on the spec and setup of the database it may or may not be optimistic running everything on the same database.
Personally, I would prefer to see Moodle running its own database as it is database "heavy" but it's probably wrong to generalise. It would certainly make it easier to diagnose problems.
Part 1: He ran the mysql tuner and the result is below and made the changes that it recommended. But the next morning I tested the production site (on Moodle 3.0) and the test site (on Moodle 3.1), and the both took the 36 seconds to delete a grade item for all students with 2738/8539 read/writes.
Part 2: Then he talked about moving the database for the test site on it's own server (remember, apparently all of the databases for the college are on one server). I didn't do any testing, but the notes he wrote were "I loaded the live database into the moodlet database that is hosted on mariadb (live database server)." and it took only 9 seconds with 1777/739 read/writes. The next note was "changed the database drive to mariadb instead of mysqli and results are the same (9 seconds).
The only difference I see is that the server check for the production site says the database mysql (5.5.52-MariaDB) and the test server says mariadb (5.5.52-MariaDB).
I just don't understand why he says it isn't a server problem and that we need to update moodle to 3.1, when after he ran Part 1, both had very similar times. I don't know what he did in part two to make the test server run faster. Unless I didn't test it after Part 1, but I was sure I had.
Any more suggestions?! Thanks for everything!
***Recommended InnoDB Buffer Pool Size based on all InnoDB Data and Indexes with an additional 60%
*** --> This currently equals 36. Used for buffer size below <--
SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
(SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
FROM information_schema.tables WHERE engine='InnoDB') A;
***changed to be made:
***Shutdown MariaDB and change memory to be dynamically allocated.
***Min Memory must match the innodb_buffer_pool_size + 2GB = 38GB
***Max Memory will be 45GB for potential growth
***make it so the OS doesn't swap much
echo 1 > /proc/sys/vm/swappiness
***add to /etc/my.cnf.d/uc.cnf
#added 2/21/17 per mysqltuner script recommendations
query_cache_size = 8M
join_buffer_size = 128.0K
thread_cache_size = 4
table_open_cache = 400
performance_schema = OFF
innodb_buffer_pool_instances = 8
#recommended to not use OS caching
#recommended by sql query
#SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
#(SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
#FROM information_schema.tables WHERE engine='InnoDB') A;
innodb_buffer_pool_size = 36G
#Other settings that might need to be tuned per
***remove from /etc/my.cnf and /etc/my.cnf.d/server.cnf
SHOW VARIABLES LIKE 'query_cache_size';
SHOW VARIABLES LIKE 'innodb%'
***This will give you how many actual GB of memory is in use by InnoDB Data in the InnoDB Buffer Pool at this moment.
SELECT (PagesData*PageSize)/POWER(1024,3) DataGB FROM
(SELECT variable_value PagesData
WHERE variable_name='Innodb_buffer_pool_pages_data') A,
(SELECT variable_value PageSize
WHERE variable_name='Innodb_page_size') B;
Well ... am not a credentialed DB admin ... but ...
not sure one can get a 3.0 and a 3.1 to behave exactly like one another.
So there's one DB server for everything, huh? Just how much memory does that server have?
"and the both took the 36 seconds to delete a grade item for all students with 2738/8539 read/writes"
both exactly the same?
The first is MariaDB ... so is the second!!!
DB Admin said
"I loaded the live database into the moodlet database that is hosted on mariadb (live database server)." and it took only 9 seconds with 1777/739 read/writes"
Dunno what the heck that means!!! ... so he loaded the DB off the main DB server, put them onto another DB server and the results were above.
Hmmmm ... would think it rather obvious, but I'll guess I'll say it ... 1 server running multiple DB ... vs another server running only one DB. If both servers have same specs I would say the other server running one db should be faster.
innodb_buffer_pool_instances = 8
innodb_buffer_pool_size = 36G
I've read that if buffer pool size is 36G then buffer pool instances need to be 1 per Gig
The link provided in notes
.. if one goes to the page ... one sees the first line says there is an update to the post
On the new page:
And a lot has in fact changed! In this post I am going to highlight most of the InnoDB parameters critical for InnoDB – specifically from a performance perspective. I’m a support engineer and I can tell you that Percona Support gets many questions related to the right sizing of basic InnoDB parameters.
So hopefully this post will help others with similar questions and issues.
happens sometimes ... a page used 7 years ago to tweak performance etc. gets updated ... as has DB's .... might be a good idea to read the new page.
Will say this ... things seem a little confused ...
'spirit of sharing', Ken
Thank you, Ken and Howard, for responding and giving great suggestions! You were correct, the times were not exactly the same, but similar. What I was missing was that I needed to check the # of read/writes for each. I will also let our server guy know of the updates!