Slow loading time for searching courses

Slow loading time for searching courses

by Vijay N -
Number of replies: 3

Hi,

Moodle 1.9

When running the moodle search facility, it take ages to come up with results and slows down the website as a whole: 

www.moodle-site/admin/report/search/index.php

 

Please can some advice on how to increase this issue. If it is a server related issue, then this website is running on a Linux virtual server hosted by Rackspace service provider. Hope this helps/

 

i look forward to any advice on this please?

Thanks

V

(Edited by Mary Cooch - to remove link on request original submission Thursday, 16 May 2013, 12:37 PM)

 

Average of ratings: -
In reply to Vijay N

Re: Slow loading time for searching courses

by Vijay N -

The reply I got was the following (although I don't think this a solution)

SELECT course.fullname, course.summary, course.id FROM mdl_course AS course LEFT JOIN mdl_assignment AS assignment ON course.id = assignment.course LEFT JOIN mdl_data AS data ON course.id = data.course LEFT JOIN mdl_choice AS choice ON course.id = choice.course LEFT JOIN mdl_facetoface AS facetoface ON course.id = facetoface.course LEFT JOIN mdl_feedback AS feedback ON course.id = feedback.course LEFT JOIN mdl_forum AS forum ON course.id = forum.course LEFT JOIN mdl_glossary AS glossary ON course.id = glossary.course LEFT JOIN mdl_label AS label ON course.id = label.course LEFT JOIN mdl_quiz AS quiz ON course.id = quiz.course LEFT JOIN mdl_resource AS resource ON course.id = resource.course LEFT JOIN mdl_scorm AS scorm ON course.id = scorm.course LEFT JOIN mdl_survey AS survey ON course.id = survey.course LEFT JOIN mdl_wiki AS wiki ON course.id = wiki.course WHERE ((course.category > '-1' AND course.id != '1' AND course.id > '-1') AND (course.fullname LIKE '%%' OR course.summary LIKE '%%' OR assignment.name LIKE '%%' OR assignment.description LIKE '%%' OR data.name LIKE '%%' OR data.intro LIKE '%%' OR choice.name LIKE '%%' OR choice.text LIKE '%%' OR facetoface.name LIKE '%%' OR feedback.name LIKE '%%' OR feedback.summary LIKE '%%' OR forum.name LIKE '%%' OR forum.intro LIKE '%%' OR glossary.name LIKE '%%' OR glossary.intro LIKE '%%' OR label.content LIKE '%%' OR quiz.name LIKE '%%' OR quiz.intro LIKE '%%' OR resource.name LIKE '%%' OR scorm.name LIKE '%%' OR scorm.summary LIKE '%%' OR survey.name LIKE '%%' OR survey.intro LIKE '%%' OR wiki.name LIKE '%%' OR wiki.summary LIKE '%%'))


This query can run for several minutes and spends most of the time in one of the following statues.

- Sending data
- Writing to net


Running an EXPLAIN on this query does not suggest that it would actually spend so long executing but I suspect that the LIKE '%%@ clauses in this query is contributing to the problem and maybe the query can be written more optimally.
Average of ratings: -
In reply to Vijay N

Re: Slow loading time for searching courses

by Vijay N - 
Further to the above,

I have made the changes in /etc/my.cnf:  (Redhat Linux platform)

[root@356807-trn1 ~]# egrep "^innodb-buffer|^innodb-log-file" /etc/my.cnf
innodb-buffer-pool-size         = 2G
innodb-log-file-size           = 100M

Restarted MySQL to enable these changes.  But could not see how this made any improvement to the speed.
Average of ratings: -
In reply to Vijay N

Re: Slow loading time for searching courses

by Vijay N - 
I have been monitoring the server and MySQL DB behavior, 
queries running for the first time are taking a long time to complete and they are pretty fast once they are in cache.

First of all the 'search' query itself needs to be improved secondly we need some server side caching such as memcached, I can look at MySQL query cache settings too but I doubt that it will help alot as the resultant search result size is almost 1.3Mb and the search term will differ resulting in a different result set every time.

Also to take complete advantage of memcahced you will have to tweak your code (i.e. put objects in cache and read from there first).

While investigating this I noticed that running an Explain on the query returns results fairly quickly however actual query can take upto a minute, I have tried various methods to investigate this further (including stracing the thread serving the request) but I was unable to find a reasonable answer to this therefore I will now request our DBA team to advice us on that.
Average of ratings: -