Home | Community | Message Board
You are not signed in.
Sign In New Account
Forum Index Search Posts Trusted Vendors Highlights Galleries FAQ User List Chat Store Random Growery

MagicBag Grow Bags
This site includes paid links. Please support our sponsors.

Feedback and Administration > Website Announcements and Feature Feedback
Threaded Mode Threaded  Previous topic Previous  View all topics Index  Next topic Next 

Welcome to the Shroomery Message Board! You are experiencing a small sample of what the site has to offer. Please login or register to post messages and view our exclusive members-only content. You'll gain access to additional forums, file attachments, board customizations, encrypted private messages, and much more!

Jump to first unread post Pages: 1
Fix connection limits
    #616161 -

I keep getting error messages because the forum has too many open connections to the database.  I am tired of this, so I am offering the following to help fix the problem:

From what I can tell, you are running Apache version 1.3.19 with PHP version 4.0.4pl1 and Microsoft Frontpage Extensions 4.0.4.3 (ick!).  You are using UUBThreads 5.5 with MySQL (unknown version).

1) Look at MaxClients in httpd.conf for apache (/usr/local/apache/conf is typical location).  There should probably be at least one available MySQL connection for every possible client plus a few extra MySQL connections available for grins.

2) Look at max_connections in mysql using either "mysqld --help" or "mysqladmin variables".  (mysqld is usually in /usr/local/mysql/libexec and mysqladmin is usually in /usr/local/mysql/bin).  This number should be larger than the MaxClients from the above.
To change this value, start mysqld with -O max_connections=###.  You will need to add this to the appropriate line in the correct /etc/rc file that starts up mysql to survive a reboot.

3) Look at mysql.max_persistent in php.ini (usually in /usr/local/lib/).  This only matters if you have persistent connections enabled in ubbthreads.  To check ubbthreads, look in config.inc.php for $config['persistent'].  Assuming persistent connections are enabled, set this number lower than the total MaxClients.

4) Add a loop with a sleep(1) around the connect() call in mysql.inc.php of ubbthreads.  Here is some untested code showing what I mean: 


if ($this->dbh == 0) 

{

  for($i=0; $i<10; $i++)

  {

    if ($config[persistent] == "off") 

    {

      if (($this->dbh=mysql_connect($config['dbserver'],$config['dbuser'],$config['dbpass']))>0)

        break;

    }

    else 

    {

      if (($this->dbh = mysql_pconnect($config['dbserver'],$config['dbuser'],$config['dbpass']))>0)

        break;

    }

    sleep(1);

  }

}


5) Restart apache and mysql if you made any config changes.


Hope this helps... now if only I can get this posted... :smile:


--------------------
Just another spore in the wind.

Extras: Filter Print Post Top
Re: Fix connection limits [Re: Seuss]
    #616284 -

The server can't handle more connections.  Just wait for the new server at the start of next month.

Thanks for trying though :smile:


--------------------
Your friendly neighborhood loon

Extras: Filter Print Post Top
Re: Fix connection limits [Re: 3DSHROOM]
    #616296 -

The problem isn't with the number of connections the server can handle, but rather a mismatch in maximum allowed connections defined via config files between mysql and apache. MySQL defaults to 100 maximum connections while apache defaults to 150 maximum connections. This leaves a gap where up to 50 apache connections can fail to access the database. The problem will not go away, even when you install your new server, unless you get the configs to match correctly.

Either lower the maxclients in apache to something like 90 (from 150), or raise the max_connections in mysql to something like 160 (from 100) so that there are more mysql connections than apache client connections available.

The code snippet I provided should fit right into the existing UUBThreads code. If you look at mysql.inc.php in the connect() method you will see exactly where it goes. The snippet will help somewhat with the problem, but the real fix is by changing the config variables.


--------------------
Just another spore in the wind.

Extras: Filter Print Post Top
Re: Fix connection limits [Re: Seuss]
    #616418 -

If you raise the MySQL limit, it will slow the site down even more so that isn't an option. If I was a visitor I would personally like to still be able to access the site (even if it does have mysql errors) than to get a page not found when the apache limit is hit... Also, there are MANY pages we host which do not use MySQL so the apache limit HAS to be higher.


--------------------
Your friendly neighborhood loon

Extras: Filter Print Post Top
Re: Fix connection limits [Re: 3DSHROOM]
    #616447 -

Hmmm.....  in that case the code snippet I provided would probably be the best solution.  Basically it tries to get a connection to mysql... if that fails it sleeps for 1 second and tries again.  It will do this up to 10 times before giving up and defaulting to the current behaviour.

> Also, there are MANY pages we host which do not use MySQL so the apache limit HAS to be higher.

I disagree.  Just because a httpd process CAN connect to MySQL doesn't mean that it WILL.  Granted, if all the people that might have been looking at non-mysql pages all the sudden start looking at mysql pages the system load will spike.  I still think (as an admin) that a load spike is better than an unavailable resource.

In the normal mode of operation, all of those extra available mysql connections will not be in use (persistent connections are off?) because people will be looking at the "many pages" that do not use mysql, so you aren't loosing anything.

> get a page not found when the apache limit is hit

Actually, you get a 5xx server temporarily unavailble, not a 4xx not found.

... just trying to help out :smile:


--------------------
Just another spore in the wind.

Extras: Filter Print Post Top
Re: Fix connection limits [Re: Seuss]
    #616750 -

"the system load will spike"

The current system load is already very ugly. I would hate to put even more load on it.
load average: 16.19, 10.04, 8.79
CPU states: 47.2% user, 89.4% system, 0.0% nice, 0.0% idle
Mem: 514296K av, 509472K used, 4824K free



I know you are trying to help, it is just a little late (with the new server coming).


--------------------
Your friendly neighborhood loon

Edited by 3DSHROOM (04/23/02 10:25 PM)

Extras: Filter Print Post Top
Re: Fix connection limits [Re: 3DSHROOM]
    #617212 -

3D, after the new server is up and everything, could you make another post with that same info for me? Sorry im a techie and like seeing that kinda info. Also what kinda hardware the new server gonna have in it?

Extras: Filter Print Post Top
Re: Fix connection limits [Re: 3DSHROOM]
    #617236 -

> load average: 16.19, 10.04, 8.79

OUCH!!! I was guessing you were down around 1.5 to 2.5 on the 15 minute average. Very ugly (even bolded) is an understatement.

Is that physical memory or virtual memory? If it is virtual, that would explain the high loads. The majority of your system time would be going into swapping and very little else would be getting done.

If the memory shown in pysical, then my guess is an IO bottleneck with the disks (most likely IDE instead of SCSI as a cause).

Either way, your new server should help.


--------------------
Just another spore in the wind.

Extras: Filter Print Post Top
Re: Fix connection limits [Re: Seuss]
    #617285 -

Yes, the current IDE drive really slows things down.

We have 512MB of physical memory in the current server.
Mem: 514296K av, 493460K used, 20836K free, 73044K shrd, 32644K buff
Swap: 130748K av, 27756K used, 102992K free 385808K cached

The new server specs:
2 x AMD MP 1800+
2 x 512 MB PC2100 Registered ECC DDR RAM
1 x 36GB 15,000 RPM SCSI Drive


--------------------
Your friendly neighborhood loon

Extras: Filter Print Post Top
Re: Fix connection limits [Re: 3DSHROOM]
    #618251 -

hey guys your server and my general use system are about the same except for the ECC, one of the CPU's, and 18G  i hope to change that fairly soon though :wink:

I just ditched my IDE hard drives last week, went with an adaptec 64-bit Ultra160 adaptor and 15,000RPM fujitsu Ultra160 HDD.  It was one of the best upgrades i could have made, im sorry i hadnt done it before.  I went with the 64-bit cause it will work 32 or 64 and im hoping that motherboards will start having 64-bit PCI busses, as the 64-bit CPUS get closer to release

Edited by windex (04/25/02 11:54 AM)

Extras: Filter Print Post Top
Re: Fix connection limits [Re: windex]
    #619396 -

If you were running solaris 2.8 on an ultrasparc you would be at 64 bit already... pretty cheap on ebay as well.  :smile:

(can you tell it is a slow day at work...)


--------------------
Just another spore in the wind.

Extras: Filter Print Post Top
Re: Fix connection limits [Re: Seuss]
    #620952 -

i have a sparc server 640mp sitting accross the room from me, i justt need to get some scsi cables for the disk array, and figure out why the network card is so flakey

Extras: Filter Print Post Top
Jump to top Pages: 1

Feedback and Administration > Website Announcements and Feature Feedback
Threaded Mode Threaded  Previous topic Previous  View all topics Index  Next topic Next 

Similar ThreadsPosterViewsRepliesLast post
* Lost connection to MySQL server during query when using... daussaulit 1,180 3 01/16/04 06:42 PM
by 3DSHROOM
* Lost connection to MySQL server during query Toricious 1,325 2 10/26/03 10:43 AM
by Toricious
* Database connection problems mm. 1,428 3 07/16/01 09:42 PM
by mm.
* Holy crap! Fuck MySQL!! Yarry 1,703 11 01/20/04 07:22 PM
by TinMan
* mysql upgrade
( 1 2 all )		 LinkA 3,290 22 10/07/07 04:27 PM
by ShroomieGirl
* this has got to be fixed Anonymous 1,123 5 12/10/03 03:35 PM
by wrestler_az
* MySql error: Colonel Kurtz Ph.D 577 0 03/13/07 11:25 AM
by Colonel Kurtz Ph.D
* Easy to get around max upload limit baraka 1,358 7 12/24/02 04:47 PM
by matts

Extra information
You cannot start new topics / You cannot reply to topics
HTML is disabled / BBCode is enabled
Moderator: Ythan, Thor, Seuss, geokills
2,116 topic views. 0 members, 8 guests and 30 web crawlers are browsing this forum.
[ Show Images Only | Sort by Score | Print Topic ]
Search this thread:

Copyright 1997-2026 Mind Media. Some rights reserved.

Generated in 0.033 seconds spending 0.006 seconds on 16 queries.