Moodle 2.04 MySQL in UNIX generates too many queries and hosting service is suspended! HELP

Moodle 2.04 MySQL in UNIX generates too many queries and hosting service is suspended! HELP

by Alexander Holsteinson -
Number of replies: 19

My Unix hosting service sent me the message below and has suspended due to my Moodle 2.04 MySQL DB  is generating too many queries    I only have 450 users registered   Can anyone indicate what could be causing this and the possible solutions?

Most common queries for holstei_*:

Time: 98% (14822 sec) 

Amount: 96% (141 queries) 

Rows Examined/Sent: 0 / 141 

Avg. Query Exec/Lock Time: 105 / 0 sec.

Used databases: [holstei_moodle]

Query example: use holstei_moodle; SELECT GET_LOCK('holstei_moodle-mdl_-session-151662',120); 

Database holstei_moodle has been chmoded to 0.

Average of ratings: -
In reply to Alexander Holsteinson

Re: Moodle 2.04 MySQL in UNIX generates too many queries and hosting service is suspended! HELP

by Alexander Holsteinson -

What actions can I do to solve this situation with so many queries generated by my Moodle MySQL database?   I usually dont have ever more than 30 users online at the same time

Is this a shared UNIX server capacity or a MySQL or Moodle problem?

All forum messages are sent by email to users

Average of ratings: -
In reply to Alexander Holsteinson

Re: Moodle 2.04 MySQL in UNIX generates too many queries and hosting service is suspended! HELP

by Ken Task -
Picture of Particularly helpful Moodlers

It's definitely shared host issue.  Check the settings for sessions ... in previous posting looked like the host was complaining about a sessions entry into the DB.  One can set sessions to write to a file as opposed to using the DB.

'spirit of sharing', Ken

Average of ratings: Useful (1)
In reply to Ken Task

Re: Moodle 2.04 MySQL in UNIX generates too many queries and hosting service is suspended! HELP

by Alexander Holsteinson -

Thanks Ken   any undesirable effects associated to session to write to a file instead of using the DB?

What else can I fix to solve this 

moodle SELECT GET_LOCK('-mdl_-session-37733',120);

Average of ratings: -
In reply to Alexander Holsteinson

Re: Moodle 2.04 MySQL in UNIX generates too many queries and hosting service is suspended! HELP

by Alexander Holsteinson -

Is there any Moodle Block that measures the hosting server load?

Average of ratings: -
In reply to Alexander Holsteinson

Re: Moodle 2.04 MySQL in UNIX generates too many queries and hosting service is suspended! HELP

by Alex Walker -
  • Turn database-based sessions off and use file-based sessions.
  • Find a better host. Easyjet-style 'pack in as many people as we can for as cheap as possible' hosts like HostGator, BlueHost, DreamHost are notorious for this. The rule of thumb is that if it says 'unlimited' for space, storage etc, it has this business model.
Average of ratings: Useful (2)
In reply to Alex Walker

Re: Moodle 2.04 MySQL in UNIX generates too many queries and hosting service is suspended! HELP

by Sam Mudle -

Find a better host. Easyjet-style 'pack in as many people as we can for as cheap as possible' hosts like HostGator, BlueHost, DreamHost are notorious for this. The rule of thumb is that if it says 'unlimited' for space, storage etc, it has this business model.

Yeah, I use HostGator's cheapo shared hosting for Moodle 2.1.1 for about 30 simultaneous users and it seems to work fine.  For $8.00 a month I can't complain.  I had to dump JustHost because they didn't support PHP 5.3.2. 

The tough part for us cheapies is that there is really no way to know just how many users you can support on these shared plans until you try.  Of course, you can complain to HostGator and they will happily upsell a dedicated server. 

However, if you have 400 paying clients for your Moodle course... You can afford to upgrade to a dedicated server.

Average of ratings: Useful (1)
In reply to Sam Mudle

Re: Moodle 2.04 MySQL in UNIX generates too many queries and hosting service is suspended! HELP

by Alex Walker -

I guess if you work for a small college that doesn't have the in-house resources, using an external host is what you have to do.

You could consider using a VPS for hosting Moodle. The benefit is that you have a certain processing power and RAM, and apart from that, you're in complete control. You can have whatever version of PHP you need, send as many emails as you want and hammer MySQL as much as your server can handle. The downside is that you're in complete control. Securing a server and setting it up right is your responsibility.

I guess I'm lucky to work for a college that has a dedicated network team and a small team of developers. My Moodle is running on 96GB of RAM. Can't remember how many processor cores, it's either 12, 18 or 24. Of course, we don't use all that power except when some particularly intensive scripts are running, during final submission week or when students want their exam results through Moodle.

Average of ratings: Useful (1)
In reply to Alex Walker

Re: Moodle 2.04 MySQL in UNIX generates too many queries and hosting service is suspended! HELP

by Alexander Holsteinson -

Should I upgrade to 2.0.8 or 2.2.2 to fix this problem?

Average of ratings: -
In reply to Sam Mudle

Re: Moodle 2.04 MySQL in UNIX generates too many queries and hosting service is suspended! HELP

by Alexander Holsteinson -

Amen Jeremy   I am paying my own shared hosting from my pocket since the public university I teach doesnt support or has the funds for VPS or inhouse server so from US$10/month is ok but to go out and pay US$200 for a basic VPS is an expensive teaching aid    

These session handling affect how Moodle runs instead of database?

Average of ratings: -
In reply to Alexander Holsteinson

Re: Moodle 2.04 MySQL in UNIX generates too many queries and hosting service is suspended! HELP

by Bret Miller -

DreamHost offers pretty low-cost hosting and offers Moodle on their one-click installs (though I've always installed it myself). As such, it should run ok on their shared hosting. We pay the extra $40/mo for VPS, though if you actually had 30 students online at the same time, it might require more resources that what we run at.

Average of ratings: Useful (1)
In reply to Bret Miller

Re: Moodle 2.04 MySQL in UNIX generates too many queries and hosting service is suspended! HELP

by Alexander Holsteinson -

Thanks Bret   what VPS plan you use ?

Average of ratings: -
In reply to Alex Walker

Re: Moodle 2.04 MySQL in UNIX generates too many queries and hosting service is suspended! HELP

by Alexander Holsteinson -

Definetly it is a sales pitch I felt for from Host Excellence "Ultimate" hosting plan that it was supposed to be unlimited bla bla bla.    I guess their unlimited is 30 students accessing the Moodle app, which is very different to real unlimited!

The problem with VPS for non IT technically teachers like me is that I can not fix this routine error in the database used by Moodle:

moodle SELECT GET_LOCK('-mdl_-session-37733',120);

Nor do I know if this is a specific 2.0.x Moodle bug since I never ran into it in version 1.9.x

Can anyone suggest a great VPS for Moodle that it has been really tested?

Average of ratings: -
In reply to Alexander Holsteinson

Re: Moodle 2.04 MySQL in UNIX generates too many queries and hosting service is suspended! HELP

by Ken Task -
Picture of Particularly helpful Moodlers

Set MDL not to use DB for sessions.

Drop all tables in mdl_sessions
That won't affect Moodle.

There is a directory for sessions in moodledata folder
Removing all files in there will also have no affect, but it would cause logged on users to login again.

https://dev.mysql.com/doc/refman/5.5/en/miscellaneous-functions.html

Above talks about sessions and locks.

Wish a knew (first hand) of a good VPS provider, but don't sorry.  As long as you know the questions to ask, however, for whatever VPS is offered then you'll know ... that question, of course, is any limitation on the number of queries made to the DB on the VPS.

Best of luck.

'spirit of sharing', Ken

Average of ratings: Useful (2)
In reply to Ken Task

Re: Moodle 2.04 MySQL in UNIX generates too many queries and hosting service is suspended! HELP

by Alexander Holsteinson -

Thanks so much ken for your assistance with this pressing issue.   

Is there any plugin for Moodle that would monitor the number of queries made to the DB that you know?      If not, it would be a great idea if a Moodle developer creates one so that we end users can know for sure if the hosting companies are reducing the numbers to force users to upgrade to VPS?

Average of ratings: -
In reply to Alexander Holsteinson

Re: Moodle 2.04 MySQL in UNIX generates too many queries and hosting service is suspended! HELP

by Ken Task -
Picture of Particularly helpful Moodlers

Un-aware of any plugin that would do that ... but think about it ... that would just add to the hits to the DB, wouldn't it? sad  [danged if you don't, danged if you don't].  BUT ... what of hosting provider tools?

That might be another item in the criteria of selecting a good hosting provider - a dashboard/stats kinda thing that shows usage of MySQL.

'spirit of sharing', Ken

Average of ratings: Useful (2)
In reply to Ken Task

Re: Moodle 2.04 MySQL in UNIX generates too many queries and hosting service is suspended! HELP

by Alexander Holsteinson -

Indeed you are right about more load on the DB but I dont know how else we end users can verify if we are really overloading the DB or if the hosting provider is shortchanging us...   

The dashboard showing the MySQL usage should be mandatory for all hosting providers though  (or MOODLE should build it into the application)

Average of ratings: -
In reply to Alexander Holsteinson

Re: Moodle 2.04 MySQL in UNIX generates too many queries and hosting service is suspended! HELP

by Visvanath Ratnaweera -
Picture of Particularly helpful Moodlers Picture of Translators
Hi

Moodle is not just another LAMP application. It is highly interactive and feature rich - as a result resource hungry. Problems like yours, due to limited resources of cheap hosting, are a common topic in the "Hardware and performance" forum http://moodle.org/mod/forum/view.php?id=596.
Average of ratings: Useful (1)
In reply to Visvanath Ratnaweera

Re: Moodle 2.04 MySQL in UNIX generates too many queries and hosting service is suspended! HELP

by Alexander Holsteinson -

Thanks Visvanath for your suggestion    what do you think about Ken's idea of a built block or dashboard app within Moodle to monitor the MySQL DB server usage?    

I think that it would be a great feature to have for Moodle and end users (we are getting shortchanged all the time with alledged going over the server quota by the hosting providers without any proof to counter them)

Average of ratings: -
In reply to Ken Task

Re: Moodle 2.04 MySQL in UNIX generates too many queries and hosting service is suspended! HELP

by Alexander Holsteinson -

Well Ken  I set Moodle not to use DB for sessions so lets see how long will run ok before the HOST EXCELLENCE shared hosting provider comes back again with ToS violation    thanks amigo!

Average of ratings: -