Error establishing a database connection

[jbk]

Hey there

I recently noticed* that there may be a misconfiguration between the number of concurrent connections that your Apache frontend accepts and the number of connections that the backend database will allow.

Since this can be a slight nuisance to some** I would like to suggest something that may remedy the situation - along with a description of why the problem may exist in the first place.

The problem:

When a user connects to the Apache webserver Apache will launch the forum software through an interpreter running through a "Multi-Processing Module" and the forum software will in turn create a connection to the database backend. By default Apache will allow 250 such connects and will queue up any further connections and treat them later (see http://httpd.apache.org/docs/2.....maxclients ).
Once a user has established a connection to Apache they can keep the connection for a little while (HTTP 1.1 keepalive) to further speed up loading of other page content.

Since we are talking about Wordpress here I'm going to assume that MySQL is the database server used by the backend. When Wordpress connects to MySQL it either creates a new connection on every request (default) or reuses a set of persistant connections. MySQL only allows a limited number of client programs (like Wordpress and the forum) to connect at the same time - 151 seems to be the golden number here. Once that many clients have connected, any further attempts to connect will be met by an immediate rejection by MySQL (see http://dev.mysql.com/doc/refma.....tions.html ).

In other words: A default Apache+MySQL+PHP+Wordpress setup would start emitting immediate Database Connection Rejected errors once more than 151 concurrent users visit the site. The frontend would be able to quickly serve the error messages and thus would appear to be fairly quick even under load. This is exactly the situation observed for this website - and please feel free to delete my post if this is in fact an incorrect analysis of your problem.

Suggested Solutions and other Optimizations:

1) Match backend connections with frontend connections:
Either increase the number of connections that MySQL can handle concurrently or decrease the number of connections that Apache will put into the MPM concurrently in such a way that there is always enough headroom for the database to serve the requests without dropping users to an error page. Specifically this is done by adjusting the following configuration directives:
Apache config file (typically /etc/apache2/httpd.conf or /etc/apache2/modules.d/00_mpm.conf) -> MaxClients
MySQL config file (typically /etc/mysql/my.cnf) -> mysqld section
set-variable = max_connections=<xyz>
The result is that all page loads will be slightly slower but rather than sometimes dumping people to an error message all pages should be served properly.

2) Try switching to persistant connections if you aren't already using them. (mysql_pconnect instead of mysql_connect in wp-db.php, you must have done [1] first, though!)

3) Try enabling and increasing the MySQL query cache size. This will allow reads from the database to be greatly (!) sped up when using Wordpress:
query-cache-size=<xyz> (see http://dev.mysql.com/doc/refma.....cache.html This can be done without doing [1] or [2])

After-word:
Please ignore my general ignorance about your particular setup. I'm afraid info has been really sparse.
I may be completely wrong in my assumptions, in which case I really hope that you understand that this post was meant as a helping hand for the organization and its participants; not a rant in any way***.

Best regards,

Me

ps. I apologize in advance for spelling and grammatical errors, my native language is not English.

* An understatement - this has been going on for several days now, it is blowing peoples' heads up!

** It is blowing my head up!

*** Although the thing is still really blowing my head up!

[abishur]

An interesting theory, but how does that fit in with the fact that there are 587 guests online (not counting all the registered users)?

Also just to clarify, when you say you noticed a misconfiguration, are you in fact seeing the Apache configuration for this site, or are you talking about just the default configuration?

The issue at hand here is that the site itself (and not just the forum) is getting a *tremendous* amount of traffic!  The good news is that since they finally are making some money they can afford a more robust server *and* we're getting an upgrade to a different forum software (though I'm sure someone will be disappointed because it's not their favorite one )

[spamel]

LMAO!

[jbk]

how does that fit in with the fact that there are 587 guests online (not counting all the registered users)?


I believe that simple-press only tracks logins/logouts for registered users. Timed out sessions for logged in users as well as guests are not tracked like that and instead it uses a 20 min interval for that.

The number of concurrent connections to Apache is just that - how many connections can concurrently be open at any given time. With just 250 connections you can serve thousands of users per second if the backend is fast enough (as seen with the static content during launch).

Also just to clarify, when you say you noticed a misconfiguration, are you in fact seeing the Apache configuration for this site, or are you talking about just the default configuration?
No, I don't think that the Apache configuration file has been posted online.

[abishur]

jbk said:



Also just to clarify, when you say you noticed a misconfiguration, are you in fact seeing the Apache configuration for this site, or are you talking about just the default configuration?


No, I don't think that the Apache configuration file has been posted online.



That was kinda my point, but I was being nice about it   The fact is that the RPF has done a lot of optimizing to the site, but as I said, the issue at hand here is that the site itself (and not just the forum) is getting a *tremendous* amount of traffic and not as simple of a matter of some site tweak

I mean as you said, the server can host thousands of connections per second, but looking at the forum list we getting awfully close to a thousand people online just in the forum.  The amount of traffic the site is getting right now is astounding!  Frankly, I'm impressed that the site is staying up at all right now.

[jbk]

That was kinda my point, but I was being nice about it


You guys (and gals) are always being nice


The fact is that the RPF has done a lot of optimizing to the site


No doubt about it. The Apache frontend (on Sphinx I believe?) is really fast at picking up connections under the current circumstances.

[timn]

"a thousand people online" - in a couple of weeks when the RPi devices drop through letter boxes there"ll be ten thousand people online

[Lynbarn]

Abishur said:

... The good news is that since they finally are making some money they can afford a more robust server *and* we're getting an upgrade to a different forum software (though I'm sure someone will be disappointed because it's not their favorite one )
The new forum, using the phpbb platform, is being developed as we speak. This is intended to resolve many of the issues, including, we all most sincerely hope, the database connection error, but will no doubt introduce new challenges to overcome.

Please be patient for a short time longer. (If you think it's frustrating posting as a member, just think what the admins are having to deal with!

[SN]

I am so pleased you're moving to phpBB – the current WordPress forum is just weird. I'm sure you had your reasons but its a bit like the Betamax  vs VHS choice

[liz]

A quick update here: we're upgrading the database server and moving stuff first, before we make any forum software changes. ETA on the server is (hallelujah) Wednesday/Thursday this week. I want to spend some time fiddling with that and seeing how this forum behaves with more room to breathe before making further changes, but my intention is to get everything migrated to phpBB (which will cost us some consultancy fees) once that's done.

[steviewevie]

Fantastic ! Thanks to all those involved in getting a new phpBB server up and running, I can't wait !

[zag]

Hurray! The only reason I setup my forum was that I didn't like the simple press forum interface over here. PHPBB is so much more efficient than any other forum. It is hard to upgrade though if you make mods and is basically not developed these days. It does work though.

Let me know if you need any help with the phpbb side of things. Its quite easy to create  a bridge and single login to wordpress as well.

Oh and one more thing, you really also need to think about how you moderate the forum. Admins seem to be "locking" threads to avoid discussion of certain topics. That isn't the right way to run a forum really, it just annoys users. Its far better to have a forum for unwanted topics or broken rules to be moved to.

[scep]

Hi zag,

I understand your concerns - some of us are fairly experienced mods   - but the current structure makes it difficult to moderate efficiently with the ammont of traffic we are getting, so whilst some of the locks etc might seem like a sledgehammer to crack a nut, they are necessary at the moment. Once the new forum is up we can deal with a lot of this stuff  more subtly.

[cnxsoft]

Simply installing W3 Total Cache plugin would greatly reduce the number of connections to the database and possibly remove the need to upgrade the server.

[Joe Schmoe]

Just an FYI...

I've heard that some people have lost posts (that they had spent considerable time and effort composing) because of the "Eeadc" problem.  I wish to point out two things from my own experience, that may be helpful:

1) This happens very rarely.  I get the Eeadc message almost all the time when reading, but have only gotten it 2 or 3 times when posting.  Obviously, it is much more traumatic when posting, since you think you've lost your work (and, of course, forgot to follow the Frequently Given Advice of doing a clipboard copy of your text before attempting to post).  Note also that frequently you will get it after posting, but it happens sufficiently late in the process that you can determine that the error was in the re-painting, not in the posting.  I.e., your post did, in fact, go through.  This observation probably accounts for some of the multiple posts we've seen (where the user didn't check to see that it had, in fact, gone through).

2) In the event that it does happen - i.e., the post did not go through - I have found that you really haven't lost.  All one has to do is hit reload, and it should re-attempt the posting - and just keep hitting reload until it goes through.  One is kind of "flying blind" in this process (since the text has vanished from the screen), but I do think this method works.  As they say in the HHGtG, the watchword is "Don't Panic!"

HTH...

[grumpyoldgit]

If you press the back button on your browser and then the reply button in the topic, you will find all your text is still there. You do need to do the maths problem again as until you re-enter the numbers the Post New Reply button is greyed.

[Lob0426]

On large posts I always copy the text before I post, just in case the error hits. If backing out does not find my text, then just paste it back and try again.

[Max]

Might be better to switch to different webserver software like Lighttpd or nginx that is more suitable for handling large number of connections, if you expect the number of visitors to stay high.

Lighttpd executes PHP scripts as fast as possible by caching the script output in memory. This frees the database connection for the next user, as soon as script execution is complete.

Apache blocks script execution if the website visitor has a slow Internet connection.

Database connection may not be released until the visitor has finished downloading the page.

[jamesh]

New server and forum destined to arrive this week, so hopefully the problem will disappear.

[jbk]

Liz:
A quick update here: we're upgrading the database server and moving stuff first, before we make any forum software changes. ETA on the server is (hallelujah) Wednesday/Thursday this week. I want to spend some time fiddling with that and seeing how this forum behaves with more room to breathe before making further changes, but my intention is to get everything migrated to phpBB (which will cost us some consultancy fees) once that's done.


That is absolutely great news, Liz! Thanks for sharing!


Simply installing W3 Total Cache plugin would greatly reduce the number of connections to the database


Sounds interesting, but does it actually work with the simple-press and phpBB plugins? I'm starting to suspect that it may only do minification on those, not caching. But sure, for site content it sounds cool. Do you have any experiences with it?


Apache blocks script execution if the website visitor has a slow Internet connection.

Database connection may not be released until the visitor has finished downloading the page.


A good guess would be that they are running on a suExec'ed virtualhost with PHP in a buffered fastcgi setup (rather than running modPHP directly). If this is indeed the case then script blocking is not much of an issue because the output simply sits in the output buffer until it is sent. The PHP instances continue to serve other requests meanwhile, just like with Lighttpd.

[ArborealSeer]

the OP may well be right.. you'd be amazed what i've pushed my centos based VPS to do.. things that people claim need machines many times the power.

however mysql database optimisation is a black art and it'd really need several days changing + reviewing to really nail it down.. and with how fast rpi is changing.. it could all be out of date quickly

he's also missed one trick – php cacheing (like apc)

btw i'd forget about phpbb, and look into smf (simple machines forum) designed from the ground up to outperform phpbb, and its open source, and free with a really good support community. (it grew out of a cgi thing called YaBB)

btw they also have importers to take in other forum software

[olivernz]

Sorry but it's amazing how the world sticks to solving a software issue by throwing more hardware at it.

The first post is right. Change the number of connections of the MySQL DB up or the concurrent sessions of Apache down (which I wouldn't suggest). One thing I can guarantee you though is, that a faster server will do absolutely nothing if the config isn't changed.

So do yourself a favor and check those configs. It's a 5min thing and a one line change.

[jamesh]

And you know this hasn't been done already how?

Problem is sheer amount of traffic. 1000's people online at any one time is what causing the problem. Sure, there maybe some tweaks to settings, but there always comes a point where you need faster hardware - even if the database allows access, without the extra speed the whole system slows to a crawl.

[nick.mccloud]

Please send advice to Sainsbury's, I'm trying to do my shopping (see image)

[Joe Schmoe]

Grumpyoldgit said:      


If you press the back button on your browser and then the reply button in the topic, you will find all your text is still there. You do need to do the maths problem again as until you re-enter the numbers the Post New Reply button is greyed. 



FWIW, the "back button" trick doesn't work on my browser.  'Twas the first thing I tried, of course, but it doesn't bring back my text.