UBB.threads PHP Forum Software Community Forums UBB.threads 7 Version 7 Support 7.5 Experts? - Getting Database Error Messages

Our forums (www.PianoWorld.com/forum) have been acting up the last couple weeks. Seems to be even worse over the last couple days.

Members/visitors are encountering this message:

UBB Message
We encountered a problem. The reason reported was
Database error only visible to forum administrators

Please click back to return to the previous page.

Or, it's taking unusually long to load a page.
There is even a thread running about the issues:
http://www.pianoworld.com/forum/ubbthreads.php/topics/1237272

We are running on multiple Sun Machines with tons of ram.
The techs at our hosting service have allocated lots of memory , processing power, and SQL connections to the forums.

I'm not technical enough to know where to look or what might be causing the issues.

If anyone can help, I'd appreciate it.
I'm even willing to pay for some expertise. The forums are an important part of my site.

Well, your best bet is to turn on MySQL error logging and asking users to write down that time (and their timezone) that an error occoured, then you can look at the error log and see what the error is at that time.

The message is displayed as to not show users queries/data/passwords that may be bad to display as plain text...

Sounds like your not able to connect to the database.

Thanks, I just enabled the sql error logging.

Of course that presumes I'll have any idea what it means if it logs any errors.

Not nessessarily, there are plenty of reasons why an error would be triggered, such as not sending the forum id variable with request; there are plenty of reasons that the errors popup; just search around and you can find quite a few ...

Well, you tell us what the logs say, we'll tell you how to fix it .

So I turned on error logging (and tested the directory).

People are still getting the message...

"UBB Message
We encountered a problem. The reason reported was
Database error only visible to forum administrators

Please click back to return to the previous page."

Either that, or they get really sloooow loading pages.

Nothing has showed up in the error log directory (except the test file).

Arggh.

In the meantime, I've had the admin at our hosting service checking things on his end. He has some technical questions that may help, he is going to join the threads forum.
His name is Ken Chase, from heavycomputing.ca. Good guy, knows his stuff.
I would appreciate whatever you folks could do to help him (and me) out.

Hi, Im the admin of the system in question that pianoworld is referring to.

During a system backup, there's a fair bit of file IO going on, and of course the forums are still up and running as usual. If there's alot of activity (say a few forum searches in the large forum system), things can get very heavily loaded and slow for short periods of time. Sometimes however, it seems to stop completely.

I watched this happen today, and so stopped the backup, checked for anything else on the server using IO and killed it, and the system was completely idle. The main website (outside UBB) was peppy, and mysql on the cmd line for even complex queries was very fast. However the forums were still not working, generating that error.

Only after stopping and restarting apache and mysql did things recover. (Im thinking stopping mysql didnt do anything, it was apache). My guess is there's a connection pool to mysql that UBB uses and it got filled with a number of requests that wedged while the server was busy for a short period, but then all other queries piled up behind.

The most curious thing is that this has been happening off and on for the last few weeks, but the system eventually recovers. Im wondering how it does, and when - what's it waiting for when the system is already idle for several minutes before it recovers? I dont see any crontabs installed that would do some regular 'cleanup' operation... So what's causing it to unwedge, and why doesn't it happen faster once load dissipates?

(Obviously this connection pooling is my personal theory with no evidence, just have seen it with other applications in the past - wondering what I should do/run/look at to verify that's what's happening.)

Thanks.

-math

Well, if it's not writing errors to the disk, you could set the "$showerror = 0;" line in /libs/mysql.inc.php to "$showerror = 1;" and it'll just dump the error to the user's browser... But again, at times, it's possible that it could contain data that you otherwise wouldn't want people to see.

Thanks Gizmo, but I'm a little hesitant to open things up that way.

Does anyone know the answers to math's questions above?

It probably would be best to open a support ticket with UBB.
You have logging turned on so if you see that generic database error it should be creating error logs. You can test it by opening control panel>Database tools -SQL COMMAND and submit a bogus query. It will create a error log entry. So I don't understand why you don't have any logs.
As far as mathx stated there are requirements for the connection pool. Can't find it now but I think 50 is the minimum suggested here.

Error logs are working now... now we wait

What was wrong with the logs Allen. He said the test.file was created.

Thanks Ruben and Allen,
The test file was created, but I presumed I'd find the error log in that directory, not realizing there was a link in the control panel to logs / MySQL errors. Duh

Now that I'm looking in the right place, getting lots of messages like this... (which Allen said I'd see because of something he was doing). Keeping an eye on it now.

Thu, Jul 30 2009 14:31:28 -0400
65.55.211.115 Script: - Line: Unable to connect to the database! - Too many connections

Thu, Jul 30 2009 14:31:30 -0400
129.49.176.44 Script: - Line: Unable to connect to the database! - Too many connections

Thu, Jul 30 2009 14:31:31 -0400
84.250.214.112 Script: - Line: Unable to connect to the database! - Too many connections

Thu, Jul 30 2009 14:31:31 -0400
98.174.219.202 Script: - Line: Unable to connect to the database! - Too many connections

Thu, Jul 30 2009 14:31:35 -0400
72.30.142.244 Script: - Line: Unable to connect to the database! - Too many connections

Thu, Jul 30 2009 14:31:35 -0400
24.131.130.193 Script: - Line: Unable to connect to the database! - Too many connections

Thu, Jul 30 2009 14:31:38 -0400
140.163.254.27 Script: - Line: Unable to connect to the database! - Too many connections

Thu, Jul 30 2009 14:31:39 -0400
142.46.193.2 Script: - Line: Unable to connect to the database! - Too many connections

Thu, Jul 30 2009 14:31:39 -0400
206.53.153.153 Script: - Line: Unable to connect to the database! - Too many connections

Thu, Jul 30 2009 14:31:40 -0400
205.188.116.135 Script: - Line: Unable to connect to the database! - Too many connections

Thu, Jul 30 2009 14:31:43 -0400
207.5.251.246 Script: - Line: Unable to connect to the database! - Too many connections

Thu, Jul 30 2009 14:31:43 -0400
84.250.214.112 Script: - Line: Unable to connect to the database! - Too many

Sounds like MATHX is on the right track might want to check into how many concurrent connections you have and how many it is limited to.

Here is a post with the same problem.

I was optimizing some tables during the "unable to connect" messages. From now on the messages should be legit.

Okay Allen if you are working on it . Then we all need to stay out of it. Did not know he solicited your help. Guess I just need to pay attention to the details in a post. LOL

Odd that the problem just developed over the past few weeks.

Wondering if we reached some critical mass that strains the system? I know Mathx alloted more connections at one point to the forums (forgive me if my terminology isn't right).

We have over 1 million posts, and over 43,000 registered members, but at any given time the "guests" out number the logged in about 3 to 1. The average over a 60 minute period is about 600-700 visitors (although obviously not concurrent).

Anyway, thanks everyone for your input.
Yes I did ask Allen to take a look, as my areas of expertise are pianos and Internet marketing, not technology :-)

By all means, if you think you have a solution, please don't hesitate to chime in.

No problem. It is just that if Allen is working on it. There should not be others trying to suggest something at the same time. It just adds to the confusion. Especially if more than one person is making changes and or suggestions.
Allen will let you know what your next step should be.

Now we wait for an error

I knew that's what was happening.

There is another list of them this morning.

The Forums keep functioning anyway, so it must be that with enough people accessing the forums at one time, it runs out of connections?

Sat, Aug 01 2009 07:38:47 -0400
24.186.32.192 Script: - Line: Unable to connect to the database! - Too many connections

Sat, Aug 01 2009 07:38:47 -0400
76.235.133.221 Script: - Line: Unable to connect to the database! - Too many connections

Sat, Aug 01 2009 07:38:49 -0400
71.57.238.41 Script: - Line: Unable to connect to the database! - Too many connections

Sat, Aug 01 2009 07:38:52 -0400
66.235.112.5 Script: - Line: Unable to connect to the database! - Too many connections

Sat, Aug 01 2009 07:38:56 -0400
72.30.142.244 Script: - Line: Unable to connect to the database! - Too many connections

Sat, Aug 01 2009 07:39:02 -0400
65.55.106.196 Script: - Line: Unable to connect to the database! - Too many connections

Sat, Aug 01 2009 07:39:05 -0400
65.55.106.183 Script: - Line: Unable to connect to the database! - Too many connections

Sat, Aug 01 2009 07:39:05 -0400
174.142.104.57 Script: - Line: Unable to connect to the database! - Too many connections

Sat, Aug 01 2009 07:39:06 -0400
71.57.238.41 Script: - Line: Unable to connect to the database! - Too many connections

Sat, Aug 01 2009 07:39:08 -0400
124.182.142.182 Script: - Line: Unable to connect to the database! - Too many connections

Sat, Aug 01 2009 07:39:14 -0400
65.55.106.168 Script: - Line: Unable to connect to the database! - Too many connections

Sat, Aug 01 2009 07:39:14 -0400
72.30.142.244 Script: - Line: Unable to connect to the database! - Too many connections

Sat, Aug 01 2009 07:39:18 -0400
124.107.217.181 Script: - Line: Unable to connect to the database! - Too many connections

Sat, Aug 01 2009 07:39:21 -0400
134.184.84.204 Script: - Line: Unable to connect
(it just keeps going like this).

This sounds like it is something your hosting provider needs to address and repair. Perhaps there is just to much going on in the shared environment your on with your hosting account.

Thanks JAISP, but we don't have a shared environment, we have multiple Sun machines of our own. Supposedly lots of power, and tons of ram, configured to run the forums in an optimal environment.

I have a feeling when there are 600-700 users on the forums at one time it's causing problems. I realize they aren't all concurrent, but apparently enough are. Or it's that combined with extensive searches.
Of course, I'm just guessing.
I'm hoping between Allen and Mathx they can figure out the cause and solution, as it's beyond my feeble tech knowledge.

I dont want to change too many things at once in mysql.cnf... I increased the max # of connections. But does that affect UBB's connection pool? Is the 'too many connections' an error that mysql is reporting to UBB, or is that UBB reporting on itself? If the connection pool system inside UBB is set to 100 it wont matter if mysql is set to 250 or more connections (which is what I have changed it to, up from 100).

UBB.threads has no connection pool setting, this is completely controlled via MySQL so whatever you set in there is what you're limit is going to be.

That mysql.cnf change should make a dramatic difference in the occurence of errors.

Figured out the problem.

When users search for stuff, they cause huge joins that take 2-3 minutes to run. The tables are all locked while this runs of course. So for 2-3 minutes (on this particular query, some could be faster, and some could be even slower, i just caught this one), nothing works. All the other queries just sit spinlocked on the db. This time 80 queries piled up in 2 minutes. With 250 max connections to the db, that could run out and generate the error.

Even with 5000 max connections tho, even if we never hit the max, the users see the site loading and not displaying anything however.

Slow queries/searches must die.

The tables involved in this are all indexed (except topic status, I added an index, didnt help):

SELECT p.POST_ID FROM ubbt_POSTS p LEFT JOIN ubbt_POSTS pp on p.POST_ID=pp.POST_ID, ubbt_TOPICS t WHERE p.POST_IS_APPROVED = '1' AND t.TOPIC_STATUS <> 'M' AND t.FORUM_ID IN ('1','2','3','4','5','6','7','9','10','11','12','15','17','18','19','20','21','22','23','25','26','27','29','30','31','32','33','35','39','41','42','43','44') AND p.TOPIC_ID = t.TOPIC_ID AND MATCH p.POST_SUBJECT AGAINST ('\\"nothing else matters\\"' IN BOOLEAN MODE) ORDER BY p.POST_POSTED_TIME DESC LIMIT 300;

Not sure what generated that query, but it took 2 minutes to run.

No, the machine is relatively fast, this is just a really large UBB forum system (~91,000 topics,

another query that gummed things up:

select t1.TOPIC_ID,t1.POST_ID,t2.USER_DISPLAY_NAME,t1.TOPIC_CREATED_TIME,t1.TOPIC_LAST_REPLY_TIME,t1.TOPIC_SUBJECT, t1.TOPIC_STATUS,t1.TOPIC_IS_APPROVED,t1.TOPIC_ICON,t1.TOPIC_VIEWS,t1.TOPIC_REPLIES,t1.TOPIC_TOTAL_RATES, t1.TOPIC_RATING,t3.USER_NAME_COLOR,t2.USER_MEMBERSHIP_LEVEL,t1.USER_ID,t1.TOPIC_IS_STICKY,t1.TOPIC_LAST_POSTER_ID, t1.TOPIC_LAST_POSTER_NAME,t1.TOPIC_LAST_POST_ID,t1.TOPIC_IS_EVENT,t1.TOPIC_HAS_FILE,t1.TOPIC_HAS_POLL,t1.TOPIC_POSTER_NAME,t1.TOPIC_THUMBNAIL,t4.POST_BODY,t3.USER_GROUP_IMAGES from ubbt_TOPICS as t1 left join ubbt_USERS as t2 on t1.USER_ID = t2.USER_ID left join ubbt_USER_PROFILE as t3 on t1.USER_ID = t3.USER_ID left join ubbt_POSTS as t4 on t1.POST_ID = t4.POST_ID where t1.FORUM_ID = 10 and t1.TOPIC_IS_STICKY = '0' AND t1.TOPIC_IS_APPROVED = '1' ORDER BY t2.USER_DISPLAY_NAME asc LIMIT 25;

Im not getting how ubb admin panel is telling me there's 500mb of data in the tables, and Ive got a InnoDB cache/buffer size setting in my.cnf of 2048Mb. You'd think there'd be enough space to mess around in and make things super fast.

What's the solution here? Throwing faster machinery at the problem? How about optimizing these queries or something?

Another solution is to run mysql in ramdisk only and replicate to actual disk. Or use a disk-backed ram disk system... seems all like a hack to avoid fixing the root problem tho.

oh, there's 1.25 million posts in the system :/

(perhaps a cleanse would also be a good idea... not sure how/what to do to do that).

The big one is the search and that's the first query that you posted. By default we use MySQL's fulltext indexing and by looking at your query it looks like it's searching all of your posts (there is no date limiter in there). So it will search through all 1.25 million posts. That's definitely going to cause some pretty big slowdowns. I'd make sure there is a reasonable range on the time that users can search as normally it should just be searching through a portion instead of all of them.

The second query is just grabbing the 25 newest topics for a particular forum. That should normally be a pretty fast query. That might be slow if you have a forum with a very large number of posts and it's set to display all of them, since even with a limit clause MySQL still has to search through all of them to find the 25 newest. You can usually speed that up by changing the default to only show like the last 6 months or year by default.

Devils Advocate Rick.
What good is it to keep the posts if you limit the date range to search by.
If they can't find them then you might as well delete them.
Just my 2 cents.
Or maybe you are working on this in v8. Hey I could have had a v8. LOL sounds like a commercial.

Much of the popularity (read: traffic) of our site is tied to the Piano Forums.
As are thousands of pages indexed in the search engines.

I'd hate to have to decide which posts to delete (not to mention, it would take an enormous chunk of my time to slog through all the posts).

I guess my lack of technical knowledge leaves me confused about why a search query would lock up the rest of the forums.

Isn't one search = to one connection?
Shouldn't it be able to more or less run in the background without bringing down the rest of the forums?

Lots of people come to Piano World to research information about pianos. It's natural for them to gravitate to the search function.

Well you don't have to delete them, Ricks suggestion is just limit the search to say 1 year or so.
My question is if you limit searches to 1 year then what good is the posts older than 1 year or so. You would have to click forever to find them. So might as well delete them or possibly move them to a archive forum. But you would still have the dilemma of finding them.

I hate scrolling to the right.

When it gets so large the fulltext is slowing things down the only thing you can do is really search in chunks. That's what the newer than / older than selections are for on the search page. Having a 1 year date range doesn't limit you to only last year's posts. You can search any daterange, ie 2004-2005. It's not the optimal solution, but as the size of the db grows, fulltext will slow down.

In simple terms it has to lock the table in order to do the query. When the table is locked then other queries on that table will stack up until it's done.

There are a few solutions that some sites will do such as having a slave database that is strictly just for searches. So when a search is done it's done on the slave and won't block other queries.

There are a few things we're going to try and do to help with search speed, but it's always a sticking point especially when it's done on all posts in the database.

Oh okay. I thought the date range setting limited the newer than/ less than search fields in the advanced search.
You do get a message above the fields showing the "Maximum date range is xxx"

Ok, got it, thanks.

Then in theory, would it be possible to rework the search interface to restrict searches to specific blocks?

I'm thinking of a series of Radio buttons:
* Past Month
* Past 6 Months
* 1 Year Ago (a 6 month search of Aug 2008 - Feb. 2009)
* 2 Years Ago (where it will search Aug. 2007 to Aug. 2008)
* 3 Years Ago (where it will only search Aug. 2006 - Aug. 2007)

I know, probably making it more complicated than it needs to be. It's just that there are still posts with some value going back years. I'd hate to cut people off from those.

By the same token, it's annoying a lot of our members/visitors when the forums lock up, and I'm sure it's scaring away some new potential members.

We already have some pretty decent hardware cranking, I keep hoping there is a coding/development solution lurking in here somewhere.

Search Engines, scrolling through dates manually

im sure the ordering of the joins has some effect too, ill have to readup again on my rusty mysql query optimizations (im mostly a postgres guy -- there is an index key on the posting date, so if that is restricted first before the rest of the joins occur in the query, then that should speed things up.

Of course Pianoworld's suggestions above of a sliding time range window for the searches is a good idea, but whatever is the biggest search in terms of # of posts included in the search range will be the slowest - so those 1 year ranges will be quite large.

there are 132k posts since jan 1 2009, and 336k posts jan 2008-2009. so we're looking at > 300k/year. so a 1 year search limit would be about 4 times fewer posts. if impact on the query is O(n^2) or larger, that'd speed things up, but i dont know how the query engine will react to that - depends on mysql internals for innodb that i am not familiar with - i spose we can only try.