Moodle 1.9 to 2.2 Db performance issue

Moodle 1.9 to 2.2 Db performance issue

by Aswin Satish -
Number of replies: 5

Hi

We had migrated from moodle1.9 to moodle 2.2 in our university.Recently we had Moodle hanging and creating server loads on peak usage days especillay during assignment upload deadline dates.Further investigation could find that select query was getting locked when fetching data from mdl_log (select * from mdl_log where time > ' ' and course =' ' AND module='course' AND (action='add mod' OR action = 'update mod' OR action ='delete mod') ORDER BY id ASC;) and taking more time to fetch data because of large data and INNODB engine.Temporarily we fixed the issue by converting mdl_log table to MYISAM for which the reads are much faster.

We have seperate LMS and DB servers with quad core processores and 8 GB memory.Mysql optimizations have been done as per the moodle recommendations.

Is there anything else we can do resolve this slowness and reduce the server load  in moodle when it is INNODB.Any help would be appreciated

 

Thank you

Aswin

Average of ratings: -
In reply to Aswin Satish

Re: Moodle 1.9 to 2.2 Db performance issue

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

How big is your mdl_log table? This is something of a known problem when the table gets huge. Do you really need it all? You can speed things up by only keeping a few months worth of logs (there's an admin setting to do this automagically).

You could also check the schema for (at least) that table. Are you missing any indexes that should be there? Failing that I might consider logging it as a bug.

Average of ratings: -
In reply to Howard Miller

Re: Moodle 1.9 to 2.2 Db performance issue

by Aswin Satish -

Hi

We keep a moodle instance for about 4-5 months and create a new one after that.We require to log the activites and is not possible to flush the logs.

Currently the size of mdl_log is around 1 GB and has 6523609 rows.Checking the schema of that table.I found that it is not inedexed.I am not sure how to do this.It would be nice If you could guide me in this.

I am using apache 2 as webserver and Mysql 5.1.3 as database server

 

Aswin

Average of ratings: -
In reply to Aswin Satish

Re: Moodle 1.9 to 2.2 Db performance issue

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

I'm a bit confused. You said you upgraded from 1.9 but now you say you regularly create a new instance. Which is it?

Basically, to check the schema, you create a completely clean, new version of moodle with the *same* Moodle code as your production site and check that the schemas match (or not). I use a tool called http://schemasync.org/.

Or.. there's a more manual method described here: http://schemasync.org/

Average of ratings: -
In reply to Howard Miller

Re: Moodle 1.9 to 2.2 Db performance issue

by Aswin Satish -

Hi

I dont updgrade the moodle regularly but creates the moodle with same version and files  with a new databases.I have checked using the tool you suggested,the indexes seems to be present in both newly created instance and the intsance which we use.Are there any ohter steps to be folllowed to fix up this slowness due to the select query in mdl_log.

Thank You

Aswin

Average of ratings: -
In reply to Aswin Satish

Re: Moodle 1.9 to 2.2 Db performance issue

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

I suggest submitting it as a bug. The chances are that the code in this area requires some optimisation.

Average of ratings: -