Moodle

OK, that's odd.

You can see http://moodle.org/modules for an example of a perfectly fast Database activity with quite a few records so the reported problem is not endemic.

Something else is going on ... perhaps a tuning problem. Your system might be running out of memory during this operation and the operating system is paging.

Can you post your MySQL configuration (my.cnf) as well as information about your CPU and RAM?

Forum discussion: http://moodle.org/mod/forum/discuss.php?d=96966

Hi folks

Just found out I have the same problem on one of my sites. Started as Moodle 1.6, upgraded to 1.9 (did a CVS update last night).

Server specs:
8 x Intel Xeon E5310 processors (1.6GHz)
4GB
2 x 250GB harddrives
CENTOS Enterprise 4.5
PHP 5.1.6
MySQL 4.1.22
Apache 1.3.37

Problem:

• I've got three databases that where all set up within a day or two max.
• None of them are large (17, 30, and 4 entries)
• The database with 17 entries is fine, no problem at all so far.
• The other two are killing me.
• While investigating, it was averaging 5-7 minutes from clicking the database name to the list view showing (if it actually did, most of yesterday it tried for 5+ minutes then said "done" but no listings).
• Averaging 5-10 minutes trying to get to single view, if it showed up.
• When it was trying to load it was hitting the CPU at 100%

After hunting on the forums, found this tracker entry and followed Ian's advice re Top (no real idea what I was looking at though!). I did a screen capture of trying the three databases if it helps, it can be viewed at http://filexchange.cfals.info/emcp_database/emcp_database.html It quite clearly shows CPU at 100% on the dodgy ones, RAM only using about 2/3 max and the load difference between the good and the bad.

Had a look at "my.cnf" that Martin mentioned, contents of that file:

[mysqld]
set-variable = max_connections=500
safe-show-database

Have not hit this problem on any of my other installs (another 4, all followed the same upgrade path e.g. jumping from 1.6+ to 1.9+). Unfortunately, the site in question is one I am trying to get up and going for collaboration across various emergency service organisations.

The uptake has been reluctant and very slow (most of the entries in the databases are dummy entries to try and get them using it). It is not a high traffic site but I have the guy who's supposed to be running the project coming for a meeting next week - it was he who raised the issue of it being that slow it's unusable

Any ideas on where the problem lies would be much appreciated.

Regards
H

Sorry folks . . . just to clarify, by "did a CVS update last night" I meant that to show it was the latest version. The site's where all upgraded about a couple of weeks before 1.9 went gold.

I did reread before posting, my excuse is I worked through the night and kinda tired!

I would also want to see the database structure for the data tables in the new database just to make sure something strange didn't happen during the upgrade. Next I would want a look at the mysql logs to see what it is doing when it is maxed out. Peace - Anthony

I'll look into the areas that Anthony mentioned over the weekend (can't do much from work due to firewalls).

In the meantime, turned on "Performance info" on the live site and an old test site that I thought I had decommisioned (test site was a snapshot of the live before doing the upgrade).

The test site is still running 1.9 Beta 4 and has 23 less entries in one of the affected databases (27 as opposed to 30 on the live site). The performance results for clicking the database name are interesting.

Test site:
1.428711 secs
Included 54 files
ticks: 143 user: 27 sys: 5 cuser: 0 csys: 0
Load average: 0.48
Record cache hit/miss ratio : 0/0

Live site:
216.307581 secs
Included 66 files
ticks: 21631 user: 31 sys: 5 cuser: 0 csys: 0
Load average: 0.99
Record cache hit/miss ratio : 0/0

I really need to figure out exactly what all that means Thing that jumps out immediately is the discrepancy in "ticks" which I assume is the culprit?

Sorry (wish there was an edit button):

"23 less entries" = "3 less entries"

Hi folks

Sorry to be a pain but got the "project owner" coming tomorrow (from another organisation so a bit embarrasing).

Not having much luck tracking anything down, any pointers as to tracking down why the huge jump from 143 to 2163 ticks would be much appreciated.

Regards
H

I'm seeing the exact same issue here on our recently upgraded to 1.9 server. The server is a quad-core intel XServe running 10.5.x (have to verify-- i think it is on 10.5.4)

Here's the environment details:
Name Information Report Status
moodle version 1.0 is required and you are running 1.9.2 OK
unicode must be installed and enabled OK
database mysql version 4.1.16 is required and you are running 5.0.45 OK
php version 4.3.0 is required and you are running 5.2.4 OK
php_extension iconv should be installed and enabled for best results OK
php_extension mbstring should be installed and enabled for best results OK
php_extension curl should be installed and enabled for best results OK
php_extension openssl should be installed and enabled for best results OK
php_extension tokenizer should be installed and enabled for best results OK
php_extension xmlrpc should be installed and enabled for best results OK
php_extension ctype should be installed and enabled for best results OK

What else can I post that might be helpful?

Hi All,

I am also experiencing this problem. As soon as anyone hits the database, the mysql process on one of our processors sits at around 25% until the database loads. It then drops back to normal.

Check
moodle version 1.0 is required and you are running 1.9.2 OK
unicode must be installed and enabled OK
database mysql version 4.1.16 is required and you are running 5.0.24 OK
php version 4.3.0 is required and you are running 5.2.0 OK
php_extension iconv should be installed and enabled for best results OK
php_extension mbstring should be installed and enabled for best results OK
php_extension tokenizer should be installed and enabled for best results OK
php_extension ctype should be installed and enabled for best results OK

I'm running this on a Solaris 10 box using Apache 2, PHP 5, and MySQL 5. Please let me know if I can be of any help.

Thanks,

Matthew

Could you please try the next weekly build (tomorrow)? it should be already fixed for some time I think.

Our server was running the latest weekly build as of yesterday and was still exhibiting the issue, so unless it has been fixed in the past week?, we are still seeing the problem.

At this time we have reverted our server back to the 1.8.6+ release.

hmm, the change I was talking about was done some weeks ago

We are seeing this behavior on only some of our databases. We have a database that we have been using for about 2 years, with hundreds of entries, that is not affected, however, new databases we have created since we upgraded from 1.8.3 to 1.9.2 are suffering from this issue.

We are running the Moodle build released just a few days ago on Windows Server 2003, Apache 2.2.9, PHP 5.2.6, and MySQL 5.0.51b. Our box is a Dell PowerEdge 2950 with two dual-core Intel Xeon processors running at 2.00 GHz, with 4 GB of RAM.

I have a database with 5 dummy entries in a relatively simple structure (less than 10 fields), and I put it to the Windows Performance Monitor to see what was happening. The main counters I used were

Memory

Page Faults /sec
Page Reads/sec
Page Writes/sec
Pages/sec

PhysicalDisk
Avg. Disk Queue Length
Current Disk Queue Length
Disk Reads/sec
Disk Writes/sec

Processor
% Processor Time (separate entry for each core)

When clicking on the database it takes about a minute for any entries to show up. In that minute, the processor activity takes turns between the cores hitting between 75-100% (though most of the activity stays on one of the cores), but there is almost no disk activity or memory paging at all, (referring to Martin's question above about paging). I can't figure out what the processor is doing all that time, but it doesn't appear to be waiting for memory or disk activity.

I was working on another Moodle database today, one that was not suffering from this issue. I had created all my fields & templates, and even added an entry. The database was behaving quite well until I decided to delete a text field & replace it with a URL field instead. I updated all my templates to reflect the change, but when I went to "View List" so that I could edit my one entry that I had already put in, I was presented with the long wait for entry display. Up until this point, the database had been behaving quite fast.

Now, however, could it be that the system is getting hung up because I have a database entry that refers to data in a field that no longer exists?

Alas, even after editing my entry & putting data into the correct field, my long wait persists. I thought maybe it was looking for that ghost data in the field I deleted.

I did some MySQL queries on the actual data that exists in the database, and the fields & entries I deleted are indeed gone...but ever since I deleted that database field & put a new one in, database operations have become very slow.

In a test environment, I pulled the database module out of the latest 1.8.6 download, and over-wrote the database module in my 1.9 install with the older code. I then modified the version.php so my Administration module wouldn't complain.

My databases immediately returned to their former speedy operation.

Anyone know of any problems I'd encounter in this scenario?

Hi James,

just to be 100% sure. Can you paste here the first line of your mod/data/view.php file ? Should be something like this:

<?php // $Id: view.php,v 1.70.2.23 2008/04/21 14:15:30 skodak Exp $

TIA and ciao

Here is the first line of my view.php file:

<?php // $Id: view.php,v 1.56.2.8 2008/03/31 06:18:11 nicolasconnault Exp $

Sorry James, but that's the version corresponding to Moodle 1.8. I wanted to know the 1.9 version you were using.

My apologies. Here is the version of the database module we were using prior to the "downgrade."

<?php // $Id: view.php,v 1.70.2.24 2008/07/18 07:49:27 moodler Exp $

We had the same problem and we have tracked it to the missing indices in MySQL database.

Yes! This has fixed our problem. Once I created the missing indices, I put the 1.9 database back in, and now it's even faster than the 1.8 version was.

I'm not sure how I ended up with these missing indices, but we've been upgrading this installation since 1.4, so it's been through a few rounds. Thanks for your solution. I hope it benefits many others as well!

Which indices did you find to be missing? I will test this out on our test server and report back.

Thank you for your work!

Oh, I went back to the forum discussion linked above to find your answer Tomasz, thank you.

The solution noted in the forum is:

As an admin go to:
Site Administration -> Miscellaneous -> XMLDB editor and click on [Check Indexes] to see if it's the same problem.
If yes - create the missing indices.

I have the same problem with Moodle 1.9. The CPU will max out when several searches are run at once. The only solution is to restart the server.
We run a Windows Apache MySQL config with 4 gig RAM. Other than this our Moodle installation is very very fast considering its large size.
We have no missing indices and I'm at a loss as what to do. Our install was upgraded to 1.9 from 1.8 and since then the problem has been apparent.

I can confirm this issue here using Moodle 1.9.1+ (Build: 20080625). When performing a very simple (one or two fields) search in a small database of about 50 entries, it works but there is a noticeable 4-5 second delay. However, filling more fields in the search form dramatically increases the time required for the query to complete. At worst, this causes MySQL to completely hang for an indefinite amount of time, rendering the Moodle site completely inaccessible.

I'm also seeing indefinite hangs, rendering the site inaccessible

Doug, Lewis and Jerome - Please make sure you are running the latest weekly version (1.9.4+) and then do the index check. If you are still having troubles let me know and I will do what I can to help out. If possible tail your mysql logs to see exactly which query is causing the problem. That will help us to track down what might be happening. Peace - Anthony

I can't upgrade our production server at the moment but I'll install a test site, replicate the issue and get back to you.
In the mean time I have solved the problem by making a small change to the code. I have replaced the select distinct with a select * statement, obviously I have lost pagination but results are returned instantly with no load on the CPU.

I just tested for this problem with version 20090629 and it's still happening. I'm attaching a text file containing an example query, where I'm doing a search using 9 fields in a database activity module with less than 100 entries. Launching the search produces this query, which in turn causes MySQL to top 100% cpu usage for an unreasonable amount of time.

We are also having this issue. I have upgraded Moodle and xampp to the latest versions. (xampp 1.7.2). I am running this on linux in VMware.

Once we have about 20 users log into Moodle our CPU spikes to 100%. The memory seems to be ok. The process that is causing the high load is httpd.

I am running out of ideas and soon we will have about 3000 users on the system. Which is a problem since 20 users are overloading it right now. Moodle was working just fine on verson 1.8 last year. I would downgrade if I was not worried about database issues.

Ryan-

do the index check and see what that gives you... totally cleared up the problem for us:

Administration ? Miscellaneous ? XMLDB editor - check indicies

Thank you for the reply.
I did run the check index and it found a problem with a log table. I fixed that a couple of weeks ago and still have this issue. I rechecked it today and it is showing no errors.

Recently upgraded to Moodle 1.9.6+ (Build: 20091112), in the hopes of seeing this bug resolved. It's not, and our Database module is virtually useless since we have to keep the search function disabled. The cause and symptom is exactly the same from when I previously commented this issue.

I'm willing to help test any kind of proposed solution for this.

Thanks for reporting this issue.

We have detected that this issue has been inactive for over a year has been recorded as affecting versions that are no longer supported.

If you believe that this issue is still relevant to current versions (2.1 and beyond), please comment on the issue. Issues left inactive for a further month will be closed.

Michael d;

lqjjLKA0p6

Michael - Reviewing the history here I believe it is safe to close this issue as fixed. As best I can tell it was an upgrade issue where an index was not created. There are steps listed in this issue to fix it. Hopefully no one is still suffering from this issue. Peace - Anthony

Closing as not a bug, thanks!