Suggestion on "Sorry, SMF was unable to connect to the database ...." error

[BravoV]

Got this forum's error message :

Connection Problems
Sorry, SMF was unable to connect to the database. This may be caused by the server being busy. Please try again later.

Usually I ignored them and happened only once in a while, but I've been getting this lot more lately compared to few months ago.

Bandwidth problem ? If it is, just a suggestion, not sure if SMF can do that, for non registered users or guest account, block the access to the post's attachment especially photos, we got tons here in this forum, hopefully this at least will decrease quite amount of bandwidth consumption.

Also search function, its a cpu & disk intensive process, make it a timed limit between searches for regged users only, again, if this is doable in SMF, the search feature is well known to be abused alot by users or even by a search bot. 

[baljemmett]

Bandwidth problem ? If it is, just a suggestion, not sure if SMF can do that, for non registered users or guest account, block the access to the post's attachment especially photos, we got tons here in this forum, hopefully this at least will decrease quite amount of bandwidth consumption.

I believe this is already the case - although I don't often view the forum when I'm not logged in, I do remember before signing up that attachments simply didn't show up at all (which was slightly confusing when people would refer to 'as you see in the photo', etc.)

I don't think the 'cannot connect to database' is a bandwidth issue though; it's likely to be either too many concurrent users exhausting the connection pool, or perhaps just high CPU usage causing timeouts?

[BravoV]

Bandwidth problem ? If it is, just a suggestion, not sure if SMF can do that, for non registered users or guest account, block the access to the post's attachment especially photos, we got tons here in this forum, hopefully this at least will decrease quite amount of bandwidth consumption.

I believe this is already the case - although I don't often view the forum when I'm not logged in, I do remember before signing up that attachments simply didn't show up at all (which was slightly confusing when people would refer to 'as you see in the photo', etc.)

I don't think the 'cannot connect to database' is a bandwidth issue though; it's likely to be either too many concurrent users exhausting the connection pool, or perhaps just high CPU usage causing timeouts?

Nope, just checked without logged in, still can see those attached pictures.

[justanothercanuck]

I don't think the 'cannot connect to database' is a bandwidth issue though; it's likely to be either too many concurrent users exhausting the connection pool, or perhaps just high CPU usage causing timeouts?

Could also be SQL running out of memory and restarting itself.

[EEVblog]

Pretty sure it's nothing to do with bandwidth. The forum (and blog) is hosted on it's own dedicated server, which is deliberately overkill in order to keep the forum as fast as possible. The bandwidth requirements are consistent and well within the capability of the server and it's backbone.

Dave.

[BravoV]

How bout cpu & disk utilization chart ? Any abnormal activities or overloaded recently ?

[EEVblog]

And some MySQL server stats for those who know about such things.

Dave.

[BravoV]

No idea on that sql thingy, Dave, hopefully experts will jump in if there are serious issues by looking that charts & logs.

[kolonelkadat]

post your my.cnf (/etc/my.cnf), and your server stats (ram cpu) maybe there is some optimization that can be done.

If im reading the graph right(green is used memory, orange is free memory), you have a fair amount of ram to throw at caching.

a switch to InnoDB is almost definitely in order. I would wager that OP's error is due to MyISAM's table level lock. (only 1 insert/update can be done on a table at any given time). InnoDB does need more ram for caching and whatnot, but it looks like you've plenty to spare.

[EEVblog]

a switch to InnoDB is almost definitely in order. I would wager that OP's error is due to MyISAM's table level lock. (only 1 insert/update can be done on a table at any given time). InnoDB does need more ram for caching and whatnot, but it looks like you've plenty to spare.

I have no idea what InnoDB is, but if it's not already part of my standard WHM/cPanel config then I'm not going near it 

There is nothing wrong with the forum, so nothing to fix really. If there were more database connection errors then more people would be complaining. I see a connection error maybe once a month, and I'm one of the heaviest forum users on the other side of the planet from the server.

Dave.

[grumpydoc]

There is nothing wrong with the forum, so nothing to fix really. If there were more database connection errors then more people would be complaining. I see a connection error maybe once a month, and I'm one of the heaviest forum users on the other side of the planet from the server.

FWIW I see it anything from once or twice a day to once or twice a week, usually clears if I come back in 5 mins or so.

[justanothercanuck]

I wouldn't worry about the SQL performance error messages, most of those are caused by how web forums are designed.

I am worried about the CPU and memory usage though.  Ideally you want as close to 0% as possible (even if that sounds like a waste).  If we're running at 97% cpu with 2 gigs of memory swapped out, then the server is totally underpowered. 

[Thor-Arne]

I am worried about the CPU and memory usage though.  Ideally you want as close to 0% as possible (even if that sounds like a waste).  If we're running at 97% cpu with 2 gigs of memory swapped out, then the server is totally underpowered. 

I agree.

Another thing to watch out for is time spent reading/writing from/to disk.
Optimizing the database might help a little on disk access.

That said, I haven't noticed any database issues.

[ModemHead]

I get those errors very often during east-coast US morning hours. Sometimes it's bad enough I have to just move on and come back later.  Most other times it's OK.

[robrenz]

Same here and It does seem to be more frequent lately.

[EEVblog]

I am worried about the CPU and memory usage though.  Ideally you want as close to 0% as possible (even if that sounds like a waste).  If we're running at 97% cpu with 2 gigs of memory swapped out, then the server is totally underpowered. 

Yes, but if it was really maxed out like that I'd expect poor performance all the time. But this is probably one of the fastest forums that I use. 

For those curious. I'm on the basic plan here:
http://www.hostgator.com/dedicated

Intel Xeon E3-1220LV2 2.3GHz
10 mbps Uplink
4 GB DDR3 Memory
500 GB RAID-1 Drives
10 TB Bandwidth
Up to 5 Dedicated IPs

I can go up to a bigger plan that gets me a faster processor and more bandwidth, but like I showed, I don't need that bandwidth.
They don't have any fancy hybrid server mix-n-match plans optimised for things like database access etc.

(and no, unless something is drastically wrong, I'm not changing host providers)

Dave.

[manicdoc]

As I just said in my email; you need to look at the whole 'chain' i.e. Apache <> Php <> MySQL - if any one of these is not correctly 'scaled' it will impact the other and create a backlog that will result in either dropping connections at Apache or MySQL level when your traffic peaks - either way the pages break or it goes slowmo..

Probably need to sit down with you next Thursday and have a look around, can probably sort you in 5 minutes.

[EEVblog]

Server status attached.
All seems ok.

Dave.

[TerminalJack505]

I've been getting this message lately.  The only time it's a problem is when you try to post something.  If you get it when you are posting then you will lose the text of your (profound and life-changing ) post.  I've been copying the text of my posts to the clipboard before hitting 'Post' or 'Preview' because of this.

So far as the error goes, it sounds like the maximum database connections may be getting exceeded.  I'd check the SQL logs and also peek at what the maximum is currently set to.

[BravoV]

Looks like I'm not alone experiencing this.

[justanothercanuck]

Server status attached.
All seems ok.

Dave.

That one seems okay.  A load average of 0.5 for a 8 core system is more than fine.  I'm not sure why the other ones said you were deep in swap.  Still doesn't explain the slowness and errors. 

[EEVblog]

I asked HostGator about the issue and they responded with:

> The error log for the stie
> http://eevblog.com/forum
>
> did not contain any information about recent database issues. The server and
> the database have both been
> up for a long time. The server has been up for over a year. The database 148
> days.
>
> The database is configured for 150 connections. The maximum which is allowed
> for any one database user
> is 25. If there are problems in the future, you may want to consider
> increasing the maximum of the per user
> connections. Since there are not many database users, have a single user be
> able to use more connections
> would not be unreasonable.
>
> I have installed the latest version of our system snapshot tools. These
> tools will collect more detailed information
> about the processing on the server, including information about the mysql
> usage. We can review this information
> later in order to learn more about the cause of the database connection
> issues.
>
> If you would like, we can go ahead and increase the maximum number of
> connections for the database.
> Alternatively, we can gather more information using the system snapshot tool
> to better determine the root
> cause of the problem.
>

So we'll increase the number of connections and see if that makes a difference.

Dave.

[EEVblog]

Another stats shot.

Dave.

[EEVblog]

I have enabled "compressed output" on the forum which is supposed to lower the bandwidth requirement.
I have enabled the "persistent connection" option which "Keeps the connection active to increase performance. If you aren't on a dedicated server, this may cause you problems with your host."
I have also enabled it to email me every time there is a database connection error.

Will see how it goes.

Dave.

[EEVblog]

Also, the current SMF forum "load average" is 1.34
This figure is very low, and the default recommended values are 2.5 to disable the search feature, and 40 to disable the forum completely.

So all pointers seem to show that the forum is not being overloaded in any way.

Dave.