Previous Thread
Next Thread
Print Thread
Hop To
#229959 10/15/2009 10:01 AM
Joined: Jul 2006
Posts: 5
C
stranger
stranger
C Offline
Joined: Jul 2006
Posts: 5
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:
Quote
Error message means that you have exceeded limit on amount of connections to your database, you need to check your application and make sure that open connections are closed properly, because, unfortunately we can't increase max_user_connections value on the server, since it can cause server overload.

Joined: Jun 2006
Posts: 9,242
Likes: 1
R
Former Developer
Former Developer
R Offline
Joined: Jun 2006
Posts: 9,242
Likes: 1
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?

Joined: Oct 2006
Posts: 358
enthusiast
enthusiast
Joined: Oct 2006
Posts: 358
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
*


--BIll B
Joined: Oct 2006
Posts: 358
enthusiast
enthusiast
Joined: Oct 2006
Posts: 358
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.



--BIll B
Joined: Dec 2003
Posts: 6,560
Likes: 78
Joined: Dec 2003
Posts: 6,560
Likes: 78
Can't even view the site url right now
Quote
We encountered a problem. The reason reported was

Database error only visible to forum administrators

Please click back to return to the previous page.
That error should be viewable from the control panel.
Or ftp to your error logs folder


Blue Man Group
There is no such thing as stupid questions. Just stupid answers
Joined: Dec 2003
Posts: 6,560
Likes: 78
Joined: Dec 2003
Posts: 6,560
Likes: 78
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


Blue Man Group
There is no such thing as stupid questions. Just stupid answers
Joined: Oct 2006
Posts: 358
enthusiast
enthusiast
Joined: Oct 2006
Posts: 358
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.


--BIll B
Joined: Oct 2006
Posts: 358
enthusiast
enthusiast
Joined: Oct 2006
Posts: 358
ah... just realized why I never enacted this before.

Quote
You'll want to change this back when you're finished however, as it could identify db names and other data your users don't really need...


--BIll B
Joined: Dec 2003
Posts: 6,560
Likes: 78
Joined: Dec 2003
Posts: 6,560
Likes: 78
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.


Blue Man Group
There is no such thing as stupid questions. Just stupid answers
Joined: Jun 2006
Posts: 16,292
Likes: 116
UBB.threads Developer
UBB.threads Developer
Joined: Jun 2006
Posts: 16,292
Likes: 116
Originally Posted by Bill B
Maybe it WOULD be helpful to let the users see the "real" message.
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...


I am a Web Development Contractor, I do not work for UBBCentral. I have provided free User to User Support since the beginning of these support forums.
Do you need Forum Install or Upgrade Services?
Forums: A Gardeners Forum, Scouters World
UBB.threads: UBBWiki, UBB Styles, UBB.Sitemaps
Longtime Supporter & Resident Post-A-Holic
VNC Web Services: Code Modifications, Upgrades, Styling, Coding Services, Disaster Recovery, and more!
Joined: Oct 2006
Posts: 358
enthusiast
enthusiast
Joined: Oct 2006
Posts: 358
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.


--BIll B
Joined: Jun 2006
Posts: 9,242
Likes: 1
R
Former Developer
Former Developer
R Offline
Joined: Jun 2006
Posts: 9,242
Likes: 1
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?

Joined: Jul 2006
Posts: 2,143
Pooh-Bah
Pooh-Bah
Joined: Jul 2006
Posts: 2,143
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.


This thread for sale. Click here! [Linked Image from navaho.infopop.cc]
Joined: Oct 2006
Posts: 358
enthusiast
enthusiast
Joined: Oct 2006
Posts: 358
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??


--BIll B
Joined: Jul 2006
Posts: 2,143
Pooh-Bah
Pooh-Bah
Joined: Jul 2006
Posts: 2,143
"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?


This thread for sale. Click here! [Linked Image from navaho.infopop.cc]
Joined: Oct 2006
Posts: 358
enthusiast
enthusiast
Joined: Oct 2006
Posts: 358
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 B
Joined: Jul 2006
Posts: 2,143
Pooh-Bah
Pooh-Bah
Joined: Jul 2006
Posts: 2,143
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.


This thread for sale. Click here! [Linked Image from navaho.infopop.cc]
Joined: Jun 2006
Posts: 16,292
Likes: 116
UBB.threads Developer
UBB.threads Developer
Joined: Jun 2006
Posts: 16,292
Likes: 116
And regarding MySQL and the webserver on the same host; that's actually how most environments are...


I am a Web Development Contractor, I do not work for UBBCentral. I have provided free User to User Support since the beginning of these support forums.
Do you need Forum Install or Upgrade Services?
Forums: A Gardeners Forum, Scouters World
UBB.threads: UBBWiki, UBB Styles, UBB.Sitemaps
Longtime Supporter & Resident Post-A-Holic
VNC Web Services: Code Modifications, Upgrades, Styling, Coding Services, Disaster Recovery, and more!
Joined: Oct 2006
Posts: 358
enthusiast
enthusiast
Joined: Oct 2006
Posts: 358
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]


--BIll B
Joined: Jul 2006
Posts: 2,143
Pooh-Bah
Pooh-Bah
Joined: Jul 2006
Posts: 2,143
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.


This thread for sale. Click here! [Linked Image from navaho.infopop.cc]
Joined: Jul 2006
Posts: 5
C
stranger
stranger
C Offline
Joined: Jul 2006
Posts: 5
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.

Joined: Dec 2003
Posts: 6,560
Likes: 78
Joined: Dec 2003
Posts: 6,560
Likes: 78
Just a thought.
Did you happen to enable
"Use Persistent Connections"
in the ubb control panel?


Blue Man Group
There is no such thing as stupid questions. Just stupid answers
Joined: Jun 2006
Posts: 9,242
Likes: 1
R
Former Developer
Former Developer
R Offline
Joined: Jun 2006
Posts: 9,242
Likes: 1
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:

SQL Query
show processlist

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.

Joined: Dec 2003
Posts: 6,560
Likes: 78
Joined: Dec 2003
Posts: 6,560
Likes: 78
Well I am not sure how IIS deals with persistent connections.
Since the only value in IIS I can reference is concurrent connections.


Blue Man Group
There is no such thing as stupid questions. Just stupid answers
Joined: Oct 2006
Posts: 358
enthusiast
enthusiast
Joined: Oct 2006
Posts: 358
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.


--BIll B
Joined: Dec 2003
Posts: 6,560
Likes: 78
Joined: Dec 2003
Posts: 6,560
Likes: 78
Originally Posted by Bill B
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.


Blue Man Group
There is no such thing as stupid questions. Just stupid answers
Joined: Oct 2006
Posts: 358
enthusiast
enthusiast
Joined: Oct 2006
Posts: 358
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
------------------------------------------------------------




--BIll B
Joined: Jun 2006
Posts: 9,242
Likes: 1
R
Former Developer
Former Developer
R Offline
Joined: Jun 2006
Posts: 9,242
Likes: 1
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.

Joined: Oct 2006
Posts: 358
enthusiast
enthusiast
Joined: Oct 2006
Posts: 358
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.


--BIll B
Joined: Oct 2006
Posts: 358
enthusiast
enthusiast
Joined: Oct 2006
Posts: 358
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


--BIll B
Joined: Jun 2006
Posts: 9,242
Likes: 1
R
Former Developer
Former Developer
R Offline
Joined: Jun 2006
Posts: 9,242
Likes: 1
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.


Joined: Oct 2006
Posts: 358
enthusiast
enthusiast
Joined: Oct 2006
Posts: 358
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.


--BIll B
Joined: Jul 2006
Posts: 2,143
Pooh-Bah
Pooh-Bah
Joined: Jul 2006
Posts: 2,143
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?


This thread for sale. Click here! [Linked Image from navaho.infopop.cc]
Joined: Oct 2006
Posts: 358
enthusiast
enthusiast
Joined: Oct 2006
Posts: 358
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.


--BIll B
Joined: Jul 2006
Posts: 2,143
Pooh-Bah
Pooh-Bah
Joined: Jul 2006
Posts: 2,143
Oh good, then we can do some tuning on MySQL and get it to sing smile


This thread for sale. Click here! [Linked Image from navaho.infopop.cc]
Joined: Dec 2009
Posts: 1
C
stranger
stranger
C Offline
Joined: Dec 2009
Posts: 1
Everything is fine for me here. Please check your configurations guys wink

Joined: Apr 2007
Posts: 3,940
Likes: 1
SD Offline
Former Developer
Former Developer
Joined: Apr 2007
Posts: 3,940
Likes: 1
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..


Link Copied to Clipboard
ShoutChat
Comment Guidelines: Do post respectful and insightful comments. Don't flame, hate, spam.
Recent Topics
spam issues
by ECNet - 03/19/2024 11:45 PM
Looking for a forum
by azr - 03/15/2024 11:26 PM
Editing Links in Post
by Outdoorking - 03/15/2024 9:31 AM
Question on barkrowler and the like
by Mors - 02/29/2024 6:51 PM
Member Permissions Help
by domspeak - 02/27/2024 6:31 PM
Who's Online Now
3 members (rootman, Gizmo, Nightcrawler), 562 guests, and 186 robots.
Key: Admin, Global Mod, Mod
Random Gallery Image
Latest Gallery Images
Los Angeles
Los Angeles
by isaac, August 6
3D Creations
3D Creations
by JAISP, December 30
Artistic structures
Artistic structures
by isaac, August 29
Stones
Stones
by isaac, August 19
Powered by UBB.threads™ PHP Forum Software 8.0.0
(Preview build 20230217)