Site Links
Home
Features
Documentation
Pricing & Order
Members Area
Support Options
UBBDev.com
UBBWiki.com
Who's Online Now
6 registered members (Ruben, peter gariepy, Gizmo, Basil, jbergmann, Geoff), 54 guests, and 392 spiders.
Key: Admin, Global Mod, Mod
Member Spotlight
Gizmo
Gizmo
Portland, OR; USA
Posts: 16,830
Joined: June 2006
Show All Member Profiles 
Top Posters(30 Days)
isaac 53
Gizmo 40
Geoff 32
Morgan 30
ECNet 17
Ruben 12
Mark S 11
JAISP 10
Bert 8
Latest Photos
Chinese Buddhist temple.
My buddha beads.
Rendered Walls
Multi-Screen wallpaper
Stockholm Metro
Previous Thread
Next Thread
Print Thread
MaxConnection SQL errors #34276
05/20/03 12:16 PM
05/20/03 12:16 PM

A
Anonymous OP
Unregistered
Anonymous OP
Unregistered
A


Here's the deal...

Server: Single AMD 650 w/512mb ram, scsi drives on RackSpace.

UBBT 6.2 w/zlib enabled, max connections on the sql is set to 150... about 265k records right now.

Usually we have 80-120 people online, with peak being around 150. Connections are usually 30-35.

In the past month or so, we've been having max connection sql errors, and can't seem to duplicate the problem with a user-initiated db call.

Sometimes it happens when there are 100 people online, sometimes when there are only 50-80.

I can't seem to duplicate a db call that takes a really long time that would cuase the max connections to be reached. For example, I can search all posts for "the" during a peak time, without incident. I can also have someone else in the office run the same search at the same time... no problem.

Based on the error log, the ONLY thing that we've been able to get to cause the max connections error is to run an optimize table (set to run nightly).

Yesterday, we made the following adjustments, based on what I've read here, and some common sense:

- Changed optimize tables to run at 3am instead of 12am

- Changed all forums to show past 6 months of posts instead of all posts, as defaults

- Changed parent posts per page to 10

- Changed posts per page to 10

So far, the errors haven't come up since those changes, after errors every day for two weeks. We'll see if we get through today without incident.

I noticed that TheDieselStop runs on a server with less ram than ours (but dual processors), and has about double the active users. However, they are on a 768kb pipe, whereas ours is the seemingly unlimited RackSpace pipe.

Overall, the forum runs very fast, especially with zlib enabled, so that doesn't seem to be an issue.

Thanks!

Re: MaxConnection SQL errors #34277
05/20/03 12:48 PM
05/20/03 12:48 PM

A
Anonymous OP
Unregistered
Anonymous OP
Unregistered
A


Are you using persistant connections or not?
If so, try to turn them off.

You also might want to log the slow queries. It helps to determine the bottleneck.

Are you running a dedicated server or is it a shared one?

Re: MaxConnection SQL errors #34278
05/20/03 01:02 PM
05/20/03 01:02 PM

A
Anonymous OP
Unregistered
Anonymous OP
Unregistered
A


Yes, running persistent connections.

Already logging all slow queries... can't seem to locate any patterns so far.

Server is dedicated.

Re: MaxConnection SQL errors #34279
05/20/03 04:53 PM
05/20/03 04:53 PM

A
Anonymous OP
Unregistered
Anonymous OP
Unregistered
A


As i said, try to turn persistant connections of. That usually solves the problem.

Re: MaxConnection SQL errors #34280
05/20/03 09:53 PM
05/20/03 09:53 PM

A
Anonymous OP
Unregistered
Anonymous OP
Unregistered
A


Most definitely - turn persistent connections off so that those 150 can be recycled. Also - up the Max_Connections and consider having a professional tune that database so that your my.cnf file is properly setup for your specific installation.

Regards,

Brett Harris
Infopop Corporation

Re: MaxConnection SQL errors #34281
05/21/03 08:22 PM
05/21/03 08:22 PM

A
Anonymous OP
Unregistered
Anonymous OP
Unregistered
A


mmm, sorry Brett, upping the max connections doesn't fix it.

Muhammad, as you noted you have a slow query that's slowing things down and making things back up. It cascades. One gets slow and sucks memory/disc and slows everything behind it.

The way to fix it is to get these waiting clients in and out faster, not let more of them pile up which will only crash the server.

A few things to look at -

Do you need to optimize ALL of the tables every night? Are you doing very many message deletes that your posts table would become fragmented daily?

Changing forums to only show last 6 months was a big step in the right direction. That view/query is the biggy, the one that is stomping on you. Cut it down to even less time and you'll see even more difference. (I won't mention pruning here, ok?). I would bet that if you left it at all posts you're using the disc for sorting instead of RAM given that you have 512 megs of RAM and 265k records.

Make that 2 weeks and you'll see a fair difference. Remember, most people don't look at posts older than that and if they want to they can change their settings when they need to, but why have the default more than that?

Of course adding more memory and tinkering with my.cnf would always help because then you can sort in memory instead of on disc, but that's usually less expensive to say than it is to do.

You want to cut down the amount of data the servr is reading and sorting then returning if you can and you've identified the largest ways to do so. <img border="0" title="" alt="[Smile]" src="images/icons/smile.gif" />

<img src="http://navaho.infopop.cc/golf.gif" alt=" - " />

Re: MaxConnection SQL errors #34282
05/21/03 08:26 PM
05/21/03 08:26 PM

A
Anonymous OP
Unregistered
Anonymous OP
Unregistered
A


Turning off persistent connections:

If you have apache setup to allow more children than mysql allows conections yes, you'll run into a problem pretty quickly given your traffic. However if apache is setup, for example, to only have 100 children max, but mysql allows 150 connections then my math says you have 49 connections to spare - provided that apache is all that you have connecting to the database.

<img src="http://navaho.infopop.cc/golf.gif" alt=" - " />

Re: MaxConnection SQL errors #34283
05/27/03 11:22 PM
05/27/03 11:22 PM

A
Anonymous OP
Unregistered
Anonymous OP
Unregistered
A


Actually, we have 2 gigs in the MySQL server now and 1 gig in the Apache server and are now on a DS3. MySQL really likes RAM, the more the merrier.

David mentioned sync'ing your Apache MaxClients and MySQL max_connections parameters. If you don't get those two setting consistent, you'll never get rid of the errors. If you run apps besides Apache that need MySQL connections (local processing, ad servers, etc.), you'll need to take that into account. There's a little bit of magic involved in getting everything tuned correctly. There are no hard and fast rules that I could find, it was a matter of trial and error. Our magic numbers are 210 MaxClients for Apache and 250 max_connections for MySQL. Also, persistent connections should be ON for best performance. If you get these two settings right, each Apache process will keep a MySQL connection open all the time .. much faster!

There's a free program out there called "mytop". It works like the unix "top" command, except it shows you MySQL processes. That's what I used to find and optimize the queries that were slowing things down for us. You can leave it running in a window and set it to sort by processor time. When you start seeing errors, check that window and see which query is slowing things down. Figure out what the query does and see if you can eliminate or optimize it. In our case, we've turned off full post tracking and search all forums. Both are huge database hogs.

Bigger iron never hurts either! I think your server is marginal for running both Apache and MySQL on the same box.

TheDieselStop.Com

Complete source of information for all diesel-powered Ford vehicles.


Shout Box
Today's Birthdays
No Birthdays
Recent Topics
Active Links - reduce the length?
by peter gariepy. 01/22/18 12:53 AM
Upgrading from 7.6.0 to 7.6.1
by Baldeagle. 01/20/18 01:07 PM
Odd behavior with marking forums as read
by Baldeagle. 01/20/18 12:51 PM
Subscription Pending Auto Update to Active
by Geoff. 01/18/18 09:04 AM
7.6.1.1 Custom Tag Editor (YouTube)
by Mark S. 01/15/18 02:58 PM
Forum Statistics
Forums36
Topics35,050
Posts190,785
Members12,051
Most Online978
Jun 24th, 2007
Random Image
Powered by UBB.threads™ PHP Forum Software 7.6.1.1