Painfully slow database after upgrade to 1.9 from 1.8

Painfully slow database after upgrade to 1.9 from 1.8

i le Ian Ralph -
Number of replies: 14

We've recently upgraded our instance of Moodle from 1.8 to 1.9 (Build 20080423).

A very simple database with 8 text fields and only 12 records takes 12.4 seconds to move between one record and the next one in Single View. 

In a slightly larger database of about 50 fields and 120 records, the time is closer to 17s.

The "view" performance of these databases before the upgrade was as fast as browing the module list on this site....

All other aspects and modules of our instances of Moodle are working as fast as they did before the upgrade

Has anyone found a similar problem? Can you suggest how I might troubleshoot the issue to determine the cause of the problem?

Thanks
Ian

Average of ratings: -
In reply to Ian Ralph

Re: Painfully slow database after upgrade to 1.9 from 1.8

i le Joann Poock -

I just upgraded to 1.9 this morning and spent the morning flying through numerous features.  When I went to see what the upgrade had done to the database module, everything came to a screeching halt. 

My database was also relatively small with no more than a dozen fields and perhaps a dozen records.  Once I make any attempt to access the database module, the entire server seems to lock up until I rebootfa'anoanoa 

Average of ratings: -
In reply to Joann Poock

Re: Painfully slow database after upgrade to 1.9 from 1.8

i le Ian Ralph -

Hi Joann,

Thanks for posting your experience. It's "good" to know that I'm not the only one with this problem!mixed

I have done some more investigating and this is what I've found (I think I'll have to post this in another forum so it is read by someone who can fix bugs):

First my servers details: SuSe Linux 10.1 running MySQL v5.0.26, php v5.1.2

What I did: I opened one of the simple databases we have created using the database module. I then went to Single View and attempted to move from one record to the next.

What I saw: on the server the process "mysql-max" hogs the processor and it peaks at 100% for the duration of any activity related to the database created using the Database module. It may take up to 30 seconds before processor usage by mysql-max returns to <1%. No other activity in Moodle has the same effect - for example opening a survey shows mysql-max taking 1% of the processor's capacity.

Joann can you give me the details of your server's configuration so that I can add that to my bug post. I suspect if you waited long enough you would find that the server would return to normal operation when the query completes.

I've hidden the database and the databases that have been created using the database module until the problem is resolved.

Ian

Average of ratings: -
In reply to Ian Ralph

Re: Painfully slow database after upgrade to 1.9 from 1.8

i le Joann Poock -

Ian,

We're running Moodle on Windows Server 2003 running MySQL v4.1.22, and php v4.4.0.  I'm not well-versed in MySQL or phpfa'anoanoa

I had only begun experimenting with the database module in the past 6-8 months on our previous version of Moodle (1.83).  After discovering the problem yesterday, I went back and deleted 6 practice databases from the server, but I have 2 remaining.  One is an important one that I'd still like to be able to recover, but after waiting again this morning more than 30 minutes for it to open, I finally gave up and have left it hidden for now.  I also have another practice database that I'd like to delete but I can't seem to delete it or the course it's in (and it's the only thing in the course!).  The database I'd like to recover and use is one I created for our district employees to log technology service requests, and we were piloting its use this spring.

Thanks for logging the issue!

Joann

Average of ratings: -
In reply to Joann Poock

Re: Painfully slow database after upgrade to 1.9 from 1.8

i le Joann Poock -

Hi again,

I don't know if you've gotten anywhere with resolving your database problem, but I experimented with developing a new clean database in our new Moodle 1.9 environment.  All went well until I finished creating the fields and went to view/enter a record.  Then I received a Template Error message and everything locked up again. 

I tried deleting and then re-installing the database module this morning, but still haven't had any luck with resolving the problem.  I seem to be dead in the water in terms of the database module at this pointfa'anoanoa

Joann

Average of ratings: -
In reply to Joann Poock

Re: Painfully slow database module activity after upgrade to 1.9 from 1.8

i le Helen Foster -
Picture of Core developers Picture of Documentation writers Picture of Moodle HQ Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers Picture of Translators
Hi Ian and Joann,

I've not come across the problem you describe, however I recommend you report it in the tracker in order to assist developers in investigating the cause. Please post the tracker issue number in this discussion for others to watch / vote for / add comments to it.
Average of ratings: -
In reply to Helen Foster

Re: Painfully slow database module activity after upgrade to 1.9 from 1.8

i le Ian Ralph -

Thanks Helen,

As suggested I've posted the problem to the tracker: http://tracker.moodle.org/browse/MDL-14911

Average of ratings: -
In reply to Ian Ralph

Re: Painfully slow database module activity after upgrade to 1.9 from 1.8

i le Joann Poock -

I finally deleted the database module completely (again!) and re-installed it.  Everything seems to be functioning well now.  Unfortunately, I haven't been able to restore my original database from a back-up copy, but I've re-created it and am starting over.  Until I deleted everything, I couldn't even create anything new ... kept getting template error messages ... so I can only assume that something had gotten corrupted in the original installs.

Joann

Average of ratings: -
In reply to Ian Ralph

Re: Painfully slow database module activity after upgrade to 1.9 from 1.8

i le Just H -
Hi folks

Just hit the same problem - really slow (5-10 mins) navigating a couple of databases (full details in the tracker).

Anyone come up with a resolution other than total uninstall and a reinstall of the database module?

Regards
H
Average of ratings: -
In reply to Just H

Re: Painfully slow database module activity after upgrade to 1.9 from 1.8

i le James Tuttle -

We're suffering from this issue as well, and I've documented my findings in the tracker.  So I tried another solution.

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?

Average of ratings: -
In reply to James Tuttle

Re: Painfully slow database module activity after upgrade to 1.9 from 1.8

i le Lewis Carr -
I also have this problem. The CPU maxes out when a search is run against a large database. Often taking 10 or more minutes to return to normal. If several users are searching the whole server will lock up. I downgraded to the 1.8 db module but then I lost the search functionality completely (the tab disppeared and Moodle just listed all entries on 1 page). Not sure what to do.
We have no missing indices.
Average of ratings: -
In reply to Ian Ralph

Re: Painfully slow database after upgrade to 1.9 from 1.8

i le Tomasz Muras -
Picture of Core developers Picture of Plugin developers Picture of Plugins guardians Picture of Translators
We had the same problem and we have tracked it to missing indices in MySQL database.
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.
Average of ratings: Useful (1)
In reply to Tomasz Muras

Re: Painfully slow database after upgrade to 1.9 from 1.8

i le James Tuttle -

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!

Average of ratings: -
In reply to Tomasz Muras

Re: Painfully slow database after upgrade to 1.9 from 1.8

i le Greg Wathke -
Thomasz,
Thank you for posting this information! Your solution fixed the problem for us also.
Average of ratings: -