Since I upgraded to Moodle 2.3.3. my Database Module works very slow.
The rest of Moodle works fast even on high load. But if one person opens the database module the mysqld on my Ubuntu 10.04 LTS LAMP server goes to 100% CPU load.
It takes serveral minutes before a small database of 20 records opens. If I click on "next" the same happens.
Once the data is displayed the mysqld goes to it normal load (<10%)
This results in the fact that all our database modules aren't workable, which is a big problem for us.
I already upgraded to 2.3.3+ to get the latest fixes, but that didn't make any difference. Also already optimized all my moodle databases, changed max_execution_time to 600 in my php.ini (https://moodle.org/mod/forum/discuss.php?d=197308) and followed the advice in http://docs.moodle.org/23/en/Database_activity_FAQ#Why_is_my_database_so_slow_to_show_each_page.3F
Nothing seems to work...
Does anyone know what to do?
See this recent entry in the tracker http://tracker.moodle.org/browse/MDL-35961
Try to apply the proposed modifications and see if it helps. Your database seems small (20 entries) but you may have a lot of users ?
It is does please report on the tracker to ask for backporting from Moodle 2.4 to previous versions (2.2, 2.3)
Cheers.
Today I updated my Moodle with the latest 2.3.3.+ version.
It seems like the code of mod/data/view.php was last modified on nov 9 2012 but the problem isn't solved.
So I took a look at the path http://tracker.moodle.org/browse/MDL-35961
The syntax of the path didn't really match my syntax of view.php, so I only modified the following:
on line 519:
$what = ' DISTINCT r.id, r.approved, r.timecreated, r.timemodified, r.userid, u.firstname, u.lastname';
$count = ' COUNT(DISTINCT c.recordid) ';
//$tables = '{data_content} c,{data_records} r, {data_content} cs, {user} u ';
$tables = '{data_content} c,{data_records} r, {user} u ';
$where = 'WHERE c.recordid = r.id
AND r.dataid = :dataid
AND r.userid = u.id ';
on line 568:
$what = ' DISTINCT r.id, r.approved, r.timecreated, r.timemodified, r.userid, u.firstname, u.lastname, ' . $sortcontentfull . ' AS sortorder ';
$count = ' COUNT(DISTINCT c.recordid) ';
// $tables = '{data_content} c, {data_records} r, {data_content} cs, {user} u ';
$tables = '{data_content} c, {data_records} r, {user} u ';
$where = 'WHERE c.recordid = r.id
AND r.dataid = :dataid
AND r.userid = u.id ';
Now my database is fast again.
As I am not a php nor a mysql programmer, I would realy like that someone verifies that the changes make are correct and sufficiant.
Looks quite good to me. These are the two places where the SQL should be modified ...
But you forgot to modify lines in the case you do a search in the database (i.e. remplace twice cs.content by c.content)
@@ -528,7 +529,8 @@ if ($showactivity) {
$advparams = array_merge($advparams, $val->params);
}
} else if ($search) {
- $searchselect = " AND (".$DB->sql_like('cs.content', ':search1', false)." OR ".$DB->sql_like('u.firstname', ':search2', false)." OR ".$DB->sql_like('u.lastname', ':search3', false)." ) ";
+ //$searchselect = " AND (".$DB->sql_like('cs.content', ':search1', false)." OR ".$DB->sql_like('u.firstname', ':search2', false)." OR ".$DB->sql_like('u.lastname', ':search3', false)." ) ";
+ $searchselect = " AND (".$DB->sql_like('c.content', ':search1', false)." OR ".$DB->sql_like('u.firstname', ':search2', false)." OR ".$DB->sql_like('u.lastname', ':search3', false)." ) ";
$params['search1'] = "%$search%";
$params['search2'] = "%$search%";
$params['search3'] = "%$search%";
and
@@ -574,7 +577,8 @@ if ($showactivity) {
$advparams = array_merge($advparams, $val->params);
}
} else if ($search) {
- $searchselect = " AND (".$DB->sql_like('cs.content', ':search1', false)." OR ".$DB->sql_like('u.firstname', ':search2', false)." OR ".$DB->sql_like('u.lastname', ':search3', false)." ) ";
+ //$searchselect = " AND (".$DB->sql_like('cs.content', ':search1', false)." OR ".$DB->sql_like('u.firstname', ':search2', false)." OR ".$DB->sql_like('u.lastname', ':search3', false)." ) ";
+ $searchselect = " AND (".$DB->sql_like('c.content', ':search1', false)." OR ".$DB->sql_like('u.firstname', ':search2', false)." OR ".$DB->sql_like('u.lastname', ':search3', false)." ) ";
$params['search1'] = "%$search%";
$params['search2'] = "%$search%";
$params['search3'] = "%$search%";
> Now my database is fast again.
Glad you did it. Please report of the tracker that Moodle <2.4 should be also corrected
Cheers
Thanks, Patrick
I didn't modify it the first time because the code line is different in 2.3.3.+ I looked for $showactivity and didn't find the syntax as was mentioned in your patch.
Now I searched for $searchselect, and I found it at line 555 and 606. I made the modifications, and my database still works fast, so I think it will be ok.
This really looks like a bug in view.php Will I have to modify it manual each time I upgrade my moodle? I really hope not!
Thanks again for your support and effort to help me. I surely wouldn't have found it at my own.
Thank you very much, that soved it!
But for other people who modify their code: In my 2.3.3+ installation I have another WHERE statement (also two times)
Just before the codeexample from Patrick there is the line:
} else if ($search) {
// $where .= ' AND cs.recordid = r.id ';
$where .= ' AND c.recordid = r.id ';
greetings from Berlin
Please note that related issue MDL-36668 has just been integrated into 2.3.3+ today.
Hello,
I'm having a very slow performance on a database activity with only 1 record but over 3.000+ fields (yes, for real).
I've manually applied the patch but there's been no improvement, loading still takes more than a minute.
This is happening on both MySQL and PostgreSQL with Moodle 2.1.3 under Linux.
Any ideas?
I'm having a very slow performance on a database activity with only 1 record but over 3.000+ fields (yes, for real).
I've manually applied the patch but there's been no improvement, loading still takes more than a minute.
This is happening on both MySQL and PostgreSQL with Moodle 2.1.3 under Linux.
Any ideas?
Did it perform better on any previous version the module or is it the first time you are trying 1 record with 3000+ fields?
What does the performance info Moodle generates suggests?
All right, I tried the patch on the production server and it worked (time decreased to a few seconds). For some reason on the local install wasn't working (prob I did something wrong).
Thanks for your time!
Thanks for your time!