Painfully slow database after upgrade to 1.9 from 1.8

Painfully slow database after upgrade to 1.9 from 1.8

door Ian Ralph -
Aantal antwoorden: 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

Gemiddelde van de beoordelingen:  -
Als antwoord op Ian Ralph

Re: Painfully slow database after upgrade to 1.9 from 1.8

door 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 rebootdroevig 

Gemiddelde van de beoordelingen:  -
Als antwoord op Joann Poock

Re: Painfully slow database after upgrade to 1.9 from 1.8

door 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

Gemiddelde van de beoordelingen:  -
Als antwoord op Ian Ralph

Re: Painfully slow database after upgrade to 1.9 from 1.8

door 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 phpdroevig

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

Gemiddelde van de beoordelingen:  -
Als antwoord op Joann Poock

Re: Painfully slow database after upgrade to 1.9 from 1.8

door 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 pointdroevig

Joann

Gemiddelde van de beoordelingen:  -
Als antwoord op Joann Poock

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

door Helen Foster -
Foto van Core developers Foto van Documentation writers Foto van Moodle HQ Foto van Particularly helpful Moodlers Foto van Plugin developers Foto van Testers Foto van 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.
Gemiddelde van de beoordelingen:  -
Als antwoord op Helen Foster

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

door Ian Ralph -

Thanks Helen,

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

Gemiddelde van de beoordelingen:  -
Als antwoord op Ian Ralph

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

door 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

Gemiddelde van de beoordelingen:  -
Als antwoord op Ian Ralph

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

door 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
Gemiddelde van de beoordelingen:  -
Als antwoord op Just H

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

door 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?

Gemiddelde van de beoordelingen:  -
Als antwoord op James Tuttle

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

door 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.
Gemiddelde van de beoordelingen:  -
Als antwoord op Ian Ralph

Re: Painfully slow database after upgrade to 1.9 from 1.8

door Tomasz Muras -
Foto van Core developers Foto van Plugin developers Foto van Plugins guardians Foto van 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.
Gemiddelde van de beoordelingen: Useful (1)
Als antwoord op Tomasz Muras

Re: Painfully slow database after upgrade to 1.9 from 1.8

door 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!

Gemiddelde van de beoordelingen:  -
Als antwoord op Tomasz Muras

Re: Painfully slow database after upgrade to 1.9 from 1.8

door Greg Wathke -
Thomasz,
Thank you for posting this information! Your solution fixed the problem for us also.
Gemiddelde van de beoordelingen:  -