|
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: Problem:
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 Had a look at "my.cnf" that Martin mentioned, contents of that file: [mysqld] 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 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: Live site: I really need to figure out exactly what all that means 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 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: 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 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 PhysicalDisk Processor 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: 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. |
||||||||||||||||||||||||||||||||||||||||||||||||||||||
Bug
Open
Major
Database Module causes mysql-max process to hog CPU after upgrade from 1.8 to 1.9
 |
|
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?