Error Connecting to SMF Database while Optimizing

Started by The Latinist, December 16, 2014, 09:25:31 AM

Previous topic - Next topic

The Latinist

Whenever the scheduled database optimization is occurring, my users (and I) are unable to view our forum.  For the first part of the optimization process the pages simply will not load, and after a few minutes we start getting the following error:

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

I can replicate the issue by simply running the optimization manually.

My forum is running on a KnownHost VPS with plenty of RAM.  Server load during the optimization process never exceeds about 1.6, whereas we have access to 8+ virtual cores.  mysqld tops out at about 101.5% CPU usage.  There is no indication that the server is oversold, and the site is otherwise quite responsive.  What could be causing this issue?

margarett

Well, if MySQL drops completely (it does, since SMF can't connect to the database) we need to assume that it should be a server issue. Can you host help you to diagnose it?

What are your forum stats? (users, topics/messages, users online?)
Se forem conduzir, não bebam. Se forem beber... CHAMEM-ME!!!! :D

QuoteOver 90% of all computer problems can be traced back to the interface between the keyboard and the chair

LiroyvH

Sounds like your tables are getting locked, try switching to innoDB for engine; bet you're using myISAM now.
((U + C + I)x(10 − S)) / 20xAx1 / (1 − sin(F / 10))
President/CEO of Simple Machines - Server Manager
Please do not PM for support - anything else is usually OK.

The Latinist

#3
Quote from: margarett on December 16, 2014, 01:07:28 PM
Well, if MySQL drops completely (it does, since SMF can't connect to the database) we need to assume that it should be a server issue. Can you host help you to diagnose it?

What are your forum stats? (users, topics/messages, users online?)

1207654 Posts, 40094 Topics, 11325 Members.

We generally see about 200 active members per day, 20000 page views and about 400 new posts.

Quote from: CoreISP on December 16, 2014, 01:13:48 PMSounds like your tables are getting locked, try switching to innoDB for engine; bet you're using myISAM now.

I will look into this.

ETA: here's our status.php: sguforums.us/status.php [nofollow]?

margarett

Yeah, 1.2 million posts will always be intensive on the server.
You should be able to apply for our "Big Forum Operator" group that, after approval, should give you access to an exclusive board for big forum owners ;)
(that will not fix your issue, though)
Se forem conduzir, não bebam. Se forem beber... CHAMEM-ME!!!! :D

QuoteOver 90% of all computer problems can be traced back to the interface between the keyboard and the chair

The Latinist

So after talking with the other admins on my site we're starting to plan to switch our mySQL engine over to innoDB.  I'm working on the checklist for the project, and hoped I could get a bit of advice:

Reading through some other threads it appears that people do not recommend converting all tables to innoDB (at the very least most say you should keep smf_messages and smf_personal_messages using myISAM).  Is http://wiki.simplemachines.org/smf/Performance_enhancements_part_2 a good guide to which tables to convert to innoDB?  Or should we convert all but the two mentioned above? We also use the Like Posts mod; I assume we should convert those tables to innoDB, too?


Night09

Whatever you decide to do make sure you back it all up first! ;)

The Latinist

Quote from: Night09 on December 17, 2014, 03:47:50 PMWhatever you decide to do make sure you back it all up first! ;)

Oh, yes.  We learned that lesson.  Just before I came aboard the admin team in November we had a server issue that resulted in the loss of our forum and all backups on our server.  Only then was it discovered that the most recent offline backup they had was from 2012. Yes, I know.  Needless to say, we've since implemented multiple redundant backups, both local and cloud.

LiroyvH

Beware that in order to properly use innoDB, you should properly tune the server first, don't leave the settings on mySQL's defaults...
It'll get you in trouble.
((U + C + I)x(10 − S)) / 20xAx1 / (1 − sin(F / 10))
President/CEO of Simple Machines - Server Manager
Please do not PM for support - anything else is usually OK.

The Latinist

Made the conversion five days ago with great results.  No more freezing during optimization, and we've not had any issues with the "Sorry, SMF was unable to connect to the database" error.  The server could use a little bit more tuning, I think, but I've got it into pretty good shape.  Thanks for your advice!

My next big project is probably going to be switching over to Sphinx for search engine...

LiroyvH

Great to hear that, enjoy :)
Sphinx is a good idea. Might not hurt to equip your server with memcached either if you don't have that installed yet.
((U + C + I)x(10 − S)) / 20xAx1 / (1 − sin(F / 10))
President/CEO of Simple Machines - Server Manager
Please do not PM for support - anything else is usually OK.

Advertisement: