Just upgraded from 7.4.1 to 7.5.3

Now our board has slowed WAY DOWN and I'm seeing hundreds of max_user_connections error messages in the log.

I saw some earlier threads on this but they related to a custom Island. Are there any issues, or tips that I need to take into account as a result of the upgrade?

The host (Webintellects) just came back with this response:

Hmm, not real sure what we be causing that. The upgrade from 7.4.1 really didn't add much in the way of new heavy features at all.

Can you post some info like what the max_user_connections is and how many users you have online when you get the error?

Wow.. I've been getting emails (hate mail :-) since this morning... ouch.

At 9 am today it behaved good for a while. (Suspect they reset the server after my trouble ticket posting to them.)

Then within an hour later with only 5 or 6 uses showing got this from my database guy:

I'm getting errors now when I try to access the DBs:

*Warning*: mysql_connect() [function.mysql-connect <http://vcca.org/members/admin/members/function.mysql-connect>]: Can't create a new thread (errno 12); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug in *E:\inetpub\vhosts\vcca.org\httpdocs\members\Connections\mx_shop.php* on line *9*

*Fatal error*: Can't create a new thread (errno 12); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug in *E:\inetpub\vhosts\vcca.org\httpdocs\members\Connections\mx_shop.php* on line *9
*

I can't even log in right now...

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.

Can't even view the site url right now
That error should be viewable from the control panel.
Or ftp to your error logs folder

If you never enabled logs in the ubb control panel.
There is another way to view the errors to get something other than the generic message.
Here

Thanks... I'll need to FTP in since I can't log in to the Control Panel...

Yes, I know about the generic message flag, but have forgotten about it. Maybe it WOULD be helpful to let the users see the "real" message.

ah... just realized why I never enacted this before.

Yep, You don't want to edit the file and leave it on.
But in a pinch it may be the only way to find the real error.

Not really, if the error contained say a 3rd party location sql server if you where being DoSed; or say if a password would potentially be dumped to the browser...

Yep... got it... as luck has it, I was just able to log in and use the CP.

Here is what I'm seeing:

Beginning at 09:28 this morning this is the error message that filled my log file:
Script: - Line: Unable to connect to the database! - Can't create a new thread (errno 12); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug

Then from 11:46 to 13:13 it was this:
Script: - Line: Unable to connect to the database! - User vccachat already has more than 'max_user_connections' active connections

Then from 13:14 to 13:43 it went back to this:
Script: - Line: Unable to connect to the database! - Can't create a new thread (errno 12); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug

Then from 13:55 to 14:18
Script: - Line: Unable to connect to the database! - Lost connection to MySQL server at 'reading initial communication packet', system error: 0
=====================
Does this look like it could be caused by increaed users on the forum??? That's what the HOST is telling me... and of course, they want me to upgrade to a VPS for more $$$ per month.

Well, if you've seen a substantial increase in users, then potentially that could be the problem. Not from just a couple more users however. And the upgrade from 7.4 to 7.5 definitely doesn't add any more database connections.

Are you running the forum stats box on your site, and if so has your max online record been set recently?

errno 12 comes from a couple of things.

Primarily, there is a bug in earlier versions of MySQL 5.1 and some 5.0.x versions. http://bugs.mysql.com/bug.php?id=20815 You might want to upgrade your MySQL version. 5.0.x versions previous to 5.0.48 also exhibit this behavior.

The next possibility is that you've reached the limit of a 32 bit OS and can't allocate more memory but that would take millions of posts, not thousands.

Thanks. Every little bit helps.

1) No our Max Online hasn't changed since 03/31/08.
2) We finally got back on line by turning OFF the FastCGI function.
3) An SA at the hosting company finally admitted that he upped the PageFile setting.

Currently set at:
max_user_connections set to 100
max_connections=300 set to 300

After four days of relative calm, some users still experience some slowdowns and error messages, but most are doing okay.

I'm thinking about moving to a VPS. Does anyone have experience with hosting UBB and the MySQL db on the same server??

"I'm thinking about moving to a VPS. Does anyone have experience with hosting UBB and the MySQL db on the same server??"

This one is. As is UBBDev. They are not vps though.

Did you check on the MySQL server version number?

PHP Version 5.2.6

We had another 20 minute SLOW DOWN today... I got dozens of emails from people letting me know. I just sent the entire log file to the host... but they don't seem to know what to do.

I'm now looking at webintellects.com VPS with Linux.

But I'm wondering if having UBB and MySQL on the same server is a good idea or not.

Bill, I'm not trying to give you a hard time, but 3 times you've seemed to miss the one thing above all that causes this error. http://bugs.mysql.com/bug.php?id=20815

Have you checked your MySQL server version yet against the versions known to have the bug that most commonly gives the errno 12 error? You answered with PHP version but is not MySQL server version.

Please check that as it is the most likely cause.

And regarding MySQL and the webserver on the same host; that's actually how most environments are...

David, I love you man. Give me a hard time. ?!?!

Sorry, I was too distracted to stay focused. Tonight for the first time in 3 years, my host has finally sent me the list of the server settings.

Here's what I just pulled for the MySQL info:
MySQL client version: 5.0.22
and
Server version: 5.0.45-community-nt


Here is a copy of the current mysql configuration:

[MySQLD]
port=3306
basedir=E:\\Databases\\MySQL
datadir=E:\\Databases\\MySQL\\Data
default-character-set=latin1
default-storage-engine=INNODB
read_buffer_size=1M
read_rnd_buffer_size=256K
sort_buffer_size=256K
innodb_additional_mem_pool_size=2M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=1M
innodb_buffer_pool_size=16M
innodb_log_file_size=10M
innodb_thread_concurrency=8
max_connections=400
old_passwords=1
max_user_connections=100
thread_cache_size=4
thread_stack=196608
binlog_cache_size=32768
net_read_timeout=300
net_retry_count=10
net_write_timeout=300
thread_concurrency=10
wait_timeout=400
interactive_timeout=400
open_files_limit=0
[client]

5.0.45. In other words, 3 sub versions before the bug was fixed at 5.0.48. Your server version has the bug known to cause issue that you have. Encourage them to upgrade to something newer. Give them the link to to that bug if they need to understand why they need to upgrade.

Two weeks have gone by and we still get max_connections and timeout errors. Only have 20 or 25 users at any one time. The host increased connections and file size (for cache). And reduced timeout settings.

Yesterday they restarted IIS to get up back.. but 20 minutes later we were bogged down again.

Are you sure that there isn't any issue with 7.5.4 not closing out processes? Sorry, I had to ask.

Just a thought.
Did you happen to enable
"Use Persistent Connections"
in the ubb control panel?

There are only 2 ways a connection won't get closed.

A. If you have persistent connections turned on (even then it will only have 1 connection per apache process)

B. If there is some other deeper issue going on with php or mysql.

When a PHP script finishes, everything closes along with it. There is nothing you actually need to do to actually close the connection.

What you probably should do is once it's been running for awhile, go into your control panel and then run the following SQL command:



That will show you any processes (connections) that are currently open. That should give you an idea if there are lingering connections hanging around.

You should definitely run that query when you're getting the max_connections error and let us know what you find.

Well I am not sure how IIS deals with persistent connections.
Since the only value in IIS I can reference is concurrent connections.

Ruben -- thanks. I did NOT have persistent_connections turned on. So I just did that.. We'll watch to see what happens.

Rick -- Thanks too. I'll try out the "show processlist" query.

Well I meant it could be causing the problem! Oh Well.

Performance has been better since I turned on "persistent connections"... but I noticed about 25 error messages from an hour ago when the MySQL calls failed due to "maximum_connections" error.

I just went in and saw these three processes running. The first one looks odd to me -- with the comment about copying to a "temp table". Is that unusual? What do you think?

- - - - -SQL LOG - - - - - - - - - - - - - - -
35266 vccachat localhost:4598 vccachat_org_-_chat Query 39 Copying to tmp table SELECT p.POST_ID FROM ubbt_POSTS p LEFT JOIN ubbt_POSTS pp on p.POST_ID=pp.POST_ID, ubbt_TOP

35289 vccachat localhost:4636 vccachat_org_-_chat Query 0 show processlist

35291 vccachat localhost:4638 vccachat_org_-_chat Query 0 Sending data select t1.TOPIC_ID,t1.POST_ID,t2.USER_DISPLAY_NAME,t1.TOPIC_CREATED_TIME,t1.TOPIC_LAST_REPLY_TIME,t
------------------------------------------------------------

That' query is coming from your search. If the result set is larger than your mysql configuration allows then it will have to create a temp table for sorting. I'd check what your maximum search results and search range is set to in the control panel.

Okay, I'm starting to see something here.

There are only 4 or 5 people logged into my forum right now.
I saw the three processes as noted in the previous posting. Then 3 or 4 minutes later I went back in --- and there were 30 processes running. See Picture1 , Picture2 and Picture3 .

As soon as I copied them to a file (approx 1 mintue later) I looked and only had the processes in Picture4 running.

I had Search set to 6 years, 3 characters, 100 results.
I changed it to 5 years, 3 characters, 50 results.

My database is:

Totals=
ROWS: 380591, DATA: 153.1 MB, INDEXES: 68.55 MB

Ok, so what it looks like is connections waiting on your searches to finish, so they pile up behind that. You really don't have that much data, so it shouldn't be taking that long to search.

The odd thing is the search has been this way since 7.0, but you said you're just starting to see issues recently.

You might have answered this before, but are you on a shared server, virtual private, dedicated? You could probably see some improvements on this if MySQL was tuned properly.

I'd also suggest turning off searches for guest users if it isn't already. We will be making some major changes to the search for 8.0 to help, but from those screenshots, your searches are taking well over 10 minutes to complete, which isn't normal at all for the amount of data that you have.

We are on a shared server. But I am beginning the move to a virtual private this weekend. Will move the web pages now and save the db until next week.

I'll see where searches for guests is located.. but I don't think that applies to me. ALL of our users are required to register.

Shared server means the MySQL server can't be tuned for you, normally. VPS means it probably can be. You going Linux or Windows, Bill?

I'm moving FROM Windows to a Linux VPS. Decided to make as many changes as I can to get better performance.

As of a couple of minutes ago, all of my web pages and 5 MySQL databases are moved and running good in the new location.

I have two "virtual" links that I need to fix.

Then, if I don't find any other problems, I might try to get a copy of the Threads database moved over to the new location tomorrow. That will be the Big Test.

Oh good, then we can do some tuning on MySQL and get it to sing

Everything is fine for me here. Please check your configurations guys

Just watch out for the CASE sensitive differences between Doze and Nix regards DB table names..

that is a bugga boo at times..

Rick should really go all lower case for v8.0 and this will go away as an issue..