Optimizing connections to the database?

Optimizing connections to the database?

by Peter Ruthven-Stuart -
Number of replies: 21
Picture of Plugin developers
Last week I posted a message about the appearance of the following error message when students tried to access my moodle site:
Error: Database connection failed.
It is possible that the database is overloaded or otherwise not running properly.
The site administrator should also check that the database details have been correctly specified in config.php

Click here to see the original posting.

The site went back online after a few hours, but yesterday when a class of 44 students tried to access the site they kept on getting the same error message.

I have checked config.php and persistent connections are off ($CFG->dbpersist = false;). I contacted my hosting company (Bluehost), and was told that there is a limit of 10 simultaneous database connections. I was assured that this was quite enough and anyway it couldn't be increased. He then went on to say that I "should optimize the connection to the database", and that this could probably done by making some changes to the config.php file.

So, is it correct that a limit of 10 simultaneous database connections is enough and indeed normal? Furthermore, can the problems my students are having accessing my site be solved by 'optimizing the connection to the database? If so, how can this be done?

Any suggestions will be greatly appreciated.

Moodle version: moodle 1.4.3 + (2004083131)
MySQL 4.0.24-standard

Average of ratings: -
In reply to Peter Ruthven-Stuart

Re: Optimizing connections to the database?

by Martin Dougiamas -
Picture of Core developers Picture of Documentation writers Picture of Moodle HQ Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers
Each page generated uses only one database connection, no matter what work is done to generate it.

Have you tried $CFG->dbpersist = true; ?
Average of ratings: -
In reply to Martin Dougiamas

Re: Optimizing connections to the database?

by Peter Ruthven-Stuart -
Picture of Plugin developers
Martin, thanks for the reply. I have switched to

$CFG->dbpersist = true;

as you suggest. Also, it occurs to me that the database overload problem could be due to auto-linking to rather large student generated glossaries; about 700 entries in two glossaries, one in each of my two online courses. I have posted a message in the Glossary forum to see if anyone has any suggestions.

I let you know how things work out after my next class.
Average of ratings: -
In reply to Peter Ruthven-Stuart

Re: Optimizing connections to the database?

by Martin Dougiamas -
Picture of Core developers Picture of Documentation writers Picture of Moodle HQ Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers
I wasn't suggesting it, I was just asking if you'd tried it. The success of that setting seems to depend on the environment (on my servers I keep it off because it makes things worse).

The glossaries have nothing to do with the database connections directly ... Each page generated uses only one database connection, no matter what work is done to generate it.

However, autolinking (especially such large glossaries) causes a lot of work for the server (especially in 1.4 series, it's faster in 1.5), slowing down each individual page generation, which means that jobs on the server take longer to execute, which means reseources like database connections are tied up for longer, and so you can hit limits sooner.

The up shot is if you're paying for cheap servers you get smaller resources, and can do less with it. It's as simple as that.

Luckily Moodle is configurable and you can turn off the fancy features to make it run even in budget web spaces. If I were you I'd switch off the filtering for a start.
Average of ratings: -
In reply to Peter Ruthven-Stuart

Re: Optimizing connections to the database?

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
I think 10 connections is marginal - I suspect you will have this problem regardless of the state of $CFG->dbpersist. In fact as connections remain open and are not immediately closed after each page is generated I think you may find that it makes the problem worse. The fact is that Moodle is a complex piece of software and it takes a significant amount to time to generate each page. The result is that if the server is busy, many db connections will be required to service all the users requesting simultaneous pages. There's a lot of factors here, most of which, unfortunately, you can't do anything about sad

PS. I doubt the auto linking has anything to do with excepting that it will increase the general load on the system - newer versions of Moodle have got steadily more efficient in most areas - significantly so in some.
Average of ratings: -
In reply to Howard Miller

Re: Optimizing connections to the database?

by Genner Cerna -
Yes your right connection are not being free, By adding the code: mysql_close(); after running sql statements or query will free resources and free connections. As of now moodle uses many connections for every students and not being free or re-use the said connections. To close idle persistent connections, set a low wait_timeout in MySQL

Note: not all queries are applicable in adding the said function.

Maybe Martin, can check on this or maybe other developers.
Average of ratings: -
In reply to Genner Cerna

Re: Optimizing connections to the database?

by Martín Langhoff -

By adding the code: mysql_close();

No, this is just wrong, and will break your Moodle install.

Peter, generic webhosting companies are great value for money if you don't have many simultaneous users. With 44 simultaneous users you won't be happy with a standard discount hosting company. Even if you manage to work around the limits to simultaneous database connections, the server load will be high, and your webhosting provider will be unhappy (and likely to shut you account down).

I'd suggest that you consider running your own server, or getting in touch with the Moodle Partners listed in Moodle.com.

Average of ratings: -
In reply to Martín Langhoff

Re: Optimizing connections to the database?

by Karen Cheale -
Hi, Martin and Peter

I am running my own server and having the same problem.  Are you suggesting that we should go through the moodle code closing database connections?


Average of ratings: -
In reply to Karen Cheale

Re: Optimizing connections to the database?

by Karen Cheale -
Having read the previous posts more carefully, I see that you're not.  Sorry
Average of ratings: -
In reply to Karen Cheale

Re: Optimizing connections to the database?

by Martín Langhoff -

Are you suggesting that we should go through the moodle code closing database connections?

My previous post says "No." so I guess I am not suggesting anything of the kind wink

Rather the opposite, it seems.

Average of ratings: -
In reply to Peter Ruthven-Stuart

Re: Optimizing connections to the database?

by Marc Dastous -

Hello all,

While I am running a reseller program, with shared hosting, I am allotted 35 simultaneous connections and still have run into this problem recently. 

So I have a couple of questions:

  1. What does changing the $CFG->dbpersist = false; to TRUE actually accomplish?
  2. What would be the minimum number of connections for a large scale operation (a school of 1100 students)?  Not all of which are simultaneous users obviously.

I love Moodle and continue to promote it in the Southeastern US.  I would really like to solve this issue if possible.

Marc

Average of ratings: -
In reply to Marc Dastous

Re: Optimizing connections to the database?

by Art Lader -

I would really like to solve this issue if possible.

Hi, Marc,

I think we must solve this problem if Moodle is going to be viable. Or am I missing something?

-- Art

Average of ratings: -
In reply to Art Lader

Re: Optimizing connections to the database?

by Martín Langhoff -

Or am I missing something?

I didn't know Moodle wasn't viable. I thought it was installed thousands of times across the world in small and large installations. And that it had lower server requirements (and better scalability) than commercial alternatives.

Perhaps I got it all wrong wink

Show me a web app that (a) uses a database, (b) is fully featured like moodle and (c) handles 40 simultaneous users on a cheap shared hosting. And then I'll consider Moodle flawed.

In the meantime, let's do something useful: head over to servers and performance and help us document how to better configure and tweak things for better performance. Help us find particular real bottlenecks. Moodle is getting better, much better.

But 40 users in a shared hosting with a 10 database connection cap is like an four-elephants-in-a-tiny-car joke.

Average of ratings: -
In reply to Martín Langhoff

Re: Optimizing connections to the database?

by Art Lader -

I didn't know Moodle wasn't viable. I thought it was installed thousands of times across the world in small and large installations. And that it had lower server requirements (and better scalability) than commercial alternatives.

Ouch! That smarts! Well, I had that coming. I will choose my words more carefully next time. blush

-- Art

Average of ratings: -
In reply to Art Lader

Re: Optimizing connections to the database?

by Martin Dougiamas -
Picture of Core developers Picture of Documentation writers Picture of Moodle HQ Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers
Moodle is perfectly viable, even in budget spaces.

However, Moodle is a big program with a lot of features. You can't expect to run it for a lot of users with all advanced features turned on inside a budget web space. There are good reasons why system administration is a career.

I like Martin's elephant analogy smile
Average of ratings: -
In reply to Marc Dastous

Re: Optimizing connections to the database?

by John Rodgers -
I have operated moodle for about a year now on a shared host with php in cgi mode for a relatively small school.  I have seen it handle a reasonably large group simultaneously hit the server for a fairly large pages (graphic intensive) and I have seen small groups bog it down with really small loads.  I have even received the message with almost noone using it (generally required a mysql restart, sorry I can't look at raw logs). 

I have come to believe it is fairly exclusively related to the shared hosting environment.
Average of ratings: -
In reply to John Rodgers

Re: Optimizing connections to the database?

by Genner Cerna -
We had our own server (clone server), we had 8,000 and growing student currently. About 250 is logging simutaneous but this numbers is too much to handle by our server. I do restart MySQL and Apache very often to free connections and free resources.

The maximum Moodle can handle simutaneous log is about 25-30 students with 1gb ram. But then, Moodle is very cool software.

Hope will be able to find way to solve this problems.
Average of ratings: -
In reply to Genner Cerna

Re: Optimizing connections to the database?

by Michael Penney -
We went to 3gb ram, that helped us improve our performance (though on our server the main performance hogs are not Moodle but Gallery--netpbm and Postnuke).

Is your server a dual processor? What kind of processor(s)? Do you have fast (SCSI) disk(s) and/or RAID array?

If boosting RAM and getting faster drives doesn't help, you could try putting the database on one server and getting a second server for php/apache.

With nice dual P4 servers going for ~$1200, that might well be cheaper than trying to learn how to tune your databasesmile.
Average of ratings: -
In reply to Michael Penney

Re: Optimizing connections to the database?

by Kam Weng Chai -

Hi there,

I also facing this problems - server slow when 25-30 concurrent connections.

My Server is P4 3.2GHz, 1GB RAM, and 36GB SCSI (10Krpm).

The server RAM usage is over 800MB. The student compaint slow.

I changed the $CFG->dbpersist =  true; and restarted the httpd and mysqld. The RAM immediate free up to 400MB. However, my concern is whenever there are 20-30 connections at the sametime, what will happen?

I planning to upgrade the RAM to 2GB! Only thing is after upgrade, how many concurrent connections it can handle? According to previous post for this forum - 1GB RAM can only support 30-40 users!

I hope that 2GB RAM can support at least 100 concurrent connectionsbig grin

It is possible? mixed Any guide on how to optimise the server?

PS: My University have arround 8000 students. 

Thanks a lot!

Chai 

Average of ratings: -
In reply to Peter Ruthven-Stuart

Re: Optimizing connections to the database?

by Joel Dickes -

I ran across this discussion recently. My wife has recently encountered the error mentioned above when doing quizes/tests in her class (math):

i.e. "Error: Database connection failed.
It is possible that the database is overloaded or otherwise not running properly.
The site administrator should also check that the database details have been correctly specified in config.php"

We are using iPower for our hosting package and I have been using them for years. Not so much because they are great but they have had decent prices and nice features. They have become less and less so over the years.

    From what I know, we are on a shared hosting package on a Debian Linux server running MySql 5.0.91-log and PHP 5.3.13, all running Moodle 1.9.10.

   I don't know what hardware they allocate to our package but know that they have the limitation of 10 concurrent connections/users to the MySql server on our particular package and I had to buy a transactions increase package to address the enormous amount of transactions being run, per hour, in a class of 20 - 40 students.

Everything had been working great for the past 1.5 - 2 years and then just this year we got hit with the error above. The odd thing is that nothing has changed (as far as I know) during this time (i.e. same version of Moodle, same version of PHP and MySql). They claim that there have been no changes to the resources allocated to my package. I have playing ping-pong with the level 1 tech support trying to see what changed but haven't gotten any useful information back.

I checked all the basic stuff (i.e. the config.php) and the dbpersist was already set to false. I checked all the permissions on the database and they seem good. It seems to cook along when browsing through it in MyPHPAdmin.

I see a lot of other people have encountered the same or similar error but it doesn't seem to apply in my case.

Moodle is an awesome product and I'm amazed that it is free! But sometimes, when there is a problem like this, it just leaves you banging your head.

If you have any recommendations, I would like to hear them.

Best Regards All.

Average of ratings: -
In reply to Joel Dickes

Re: Optimizing connections to the database?

by Billy Zwiener -

Joel, I'm not an expert like a lot on here but I was running a 1.9x install on a shared host as well up until a couple weeks ago when things weren't working great and I knew I had to upgrade eventually. My shared hosting that once ran Moodle 1.9x stopped upgrading it after 2.x as well. I wondered why for a long time and then after realizing the resources 2.x took it made complete sense that it simply wasn't possible to run 2.x properly on shared cheap hosting. I too have a small user base but I needed to get a VPS (virtual private host) to run the upgraded software properly. It costs $ BUT you're most likely just not going to be able to run it properly any other way. A lot recommend a dedicated server but for a small user base I feel a VPS is fine. (A VPS is cheaper btw). Anyway, something to prep yourself for. Moodle is still very cost efficient if you know how to run administer it and it still is the best solution for me. It just needs a powerul server to run all it's goodness.

Average of ratings: -
In reply to Joel Dickes

Re: Optimizing connections to the database?

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

This error comes only at certain times, like when a class is taking a quizz? That sounds very much like your Moodle requesting more resources than your shared hosting allow. Moodle is not some information web site, it is highly interactive and allow complex transactions like quizzes. There is a forum dedicated to resources, http://moodle.org/mod/forum/view.php?id=596. Go through the pointers in its introduction.

But, do you say the identical set up worked flawlessly last year, including classes of the same size taking the same quiz? That would be odd.

That said, between 1.9.10 and 1.9.19 there were huge performance improvements. Consider upgrading to 1.9.latesthttp://docs.moodle.org/19/en/Upgrading , but not to Moodle 2 yet.

P.S. Did you notice that the post you replied http://moodle.org/mod/forum/discuss.php?d=24372 is over seven years old? You should request the moderator to make your post a new discussion or even move the new discussion to the "Hardware and Performance" forum.
Average of ratings: -