|
Joined: Jul 2006
Posts: 5
stranger
|
stranger
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: 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
Former Developer
|
Former Developer
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,566 Likes: 78
|
Joined: Dec 2003
Posts: 6,566 Likes: 78 |
Can't even view the site url right now 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,566 Likes: 78
|
Joined: Dec 2003
Posts: 6,566 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. 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,566 Likes: 78
|
Joined: Dec 2003
Posts: 6,566 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,301 Likes: 116
|
Joined: Jun 2006
Posts: 16,301 Likes: 116 |
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...
|
|
|
|
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
Former Developer
|
Former Developer
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.
|
|
|
|
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?
|
|
|
|
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=20815Have 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.
|
|
|
|
Joined: Jun 2006
Posts: 16,301 Likes: 116
|
Joined: Jun 2006
Posts: 16,301 Likes: 116 |
And regarding MySQL and the webserver on the same host; that's actually how most environments are...
|
|
|
|
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.
|
|
|
|
Joined: Jul 2006
Posts: 5
stranger
|
stranger
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,566 Likes: 78
|
Joined: Dec 2003
Posts: 6,566 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
Former Developer
|
Former Developer
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: 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,566 Likes: 78
|
Joined: Dec 2003
Posts: 6,566 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,566 Likes: 78
|
Joined: Dec 2003
Posts: 6,566 Likes: 78 |
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
Former Developer
|
Former Developer
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
Former Developer
|
Former Developer
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?
|
|
|
|
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
|
|
|
|
Joined: Dec 2009
Posts: 1
stranger
|
stranger
Joined: Dec 2009
Posts: 1 |
Everything is fine for me here. Please check your configurations guys
|
|
|
|
Joined: Apr 2007
Posts: 3,940 Likes: 1
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..
|
|
|
2 members (ArsivTR, Morgan),
593
guests, and
136
robots. |
Key:
Admin,
Global Mod,
Mod
|
|
|
|