Speeding up Moodle - reindexing entire database would help?

Speeding up Moodle - reindexing entire database would help?

by Jerry Lau -
Number of replies: 6

Hello folks.

On RedHat 7.1 64-bit- Moodle 3.4.x, PhP 7.1 and MySQL 5.7.x community edition.

The app server has about 24 gbs and db has about 32 gbs.

Some users say that moodle is excruciating slow (I don't see it) and because we are running cron every 30 mins, could that be a factor.

It almost always seem to be during quiz time. Would re-indexing the entire database or just quiz tables, help to improve performance?

I noticed that we have 2 missing indexes and 66 foreign key violations. By fixing those, would it:

  1. Compromise the records of existing content or data in a course and cause the course to not work any more?
  2. Would it speed up the performance?
  3. How do I reindex and fix these foreign key violations?

The SQL slow log seems to be more cron oriented processes...

Thank you!


Average of ratings: -
In reply to Jerry Lau

Re: Speeding up Moodle - reindexing entire database would help?

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

Two things....

What, if anything, have you done to tune your database? For a database of this size you're going to have to put some effort into optimising its use of memory and such like.

There's no advantage in running cron every 30 minutes. It's designed to run every minute. Moodle takes care of the correct scheduling. 

I doubt reindexing would make any difference. How are you running quizzes? A class full of people undertaking quizzes at the same time (for example) can impose a substantial load. 

Really, we don't have enough information to give you a particularly informed reply.

Average of ratings: -
In reply to Howard Miller

Re: Speeding up Moodle - reindexing entire database would help?

by Jerry Lau -

The quizzes seem to be the issue

Since Sept 12th (or maybe earlier) a minimum of nine instructors have reported specifically on the Moodle quiz slowdown issue.

If they each have 20 students in their room during a quiz, that is 180 students affected by this. “Affected” could mean either frustrated and worried because their quiz completed *very* slowly (minutes per question, instead of seconds), or some of them are not able to complete at all due to a session time-out.

Here are my .cnf settings.. The DB Server has 64 GB or ram and CPU still hits 70%

# this is read by the standalone daemon and embedded servers
[server]


[client]
default-character-set = utf8mb4
max_allowed_packet=3G


[mysql]
default-character-set = utf8mb4

[mysqld]
datadir=/ourdb/ourmysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/opt/rh/rh-mysql57/log/mysql/mysqld.log
pid-file=/var/run/rh-mysql57-mysqld/mysqld.pid

slow_query_log=1
slow_query_log_file=/var/opt/rh/rh-mysql57/log/mysql57-slow.log
long_query_time=1

innodb_file_format = Barracuda
innodb_file_per_table = 1
innodb_large_prefix

character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
skip-character-set-client-handshake

innodb_buffer_pool_size=51G
max_allowed_packet=3G
innodb_log_buffer_size=32M
innodb_flush_log_at_trx_commit=0
innodb_flush_method=O_DIRECT
innodb_buffer_pool_instances=8
innodb_thread_concurrency=8
innodb_buffer_pool_dump_pct=75
innodb_adaptive_hash_index_parts=16
innodb_checksum_algorithm=crc32
innodb_page_cleaners=8
innodb_log_file_size=6G

# this is only for embedded server
[embedded]





Average of ratings: -
In reply to Howard Miller

Re: Speeding up Moodle - reindexing entire database would help?

by Jerry Lau -

I thought we had to call the cron via php script if we want to run it via cron job? Are you suggesting that we don't need to run it like:

 /our/php /path/cron.php

Average of ratings: -
In reply to Jerry Lau

Re: Speeding up Moodle - reindexing entire database would help?

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

Yes - that's the one. It's recommended to run it every minute though. Not every 30 minutes. 

Next, I would read - https://dba.stackexchange.com/questions/19164/what-to-set-innodb-buffer-pool-and-why/19181#19181 ; and things like it. I'm a bit sceptical about your db settings. Although I'm in no position to be certain. 

Have you got slow query logs set up?

Average of ratings: -
In reply to Howard Miller

Re: Speeding up Moodle - reindexing entire database would help?

by Jerry Lau -

yes I got slow query log and its mostly cron related queries.

why does running it every minute be better than every 30 mins? I thought it may load the system more?

Average of ratings: -
In reply to Jerry Lau

Re: Speeding up Moodle - reindexing entire database would help?

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

As I said in my first post, Moodle has a built in Scheduler. The only thing that always runs is the Scheduler but it gives Moodle full control over these processes. 

Average of ratings: -