Webhost complaining of MySQL overload -- need your advice

This is an archive of the phpBB 2.0.x support forum. Support for phpBB2 has now ended.
Forum rules
Following phpBB2's EoL, this forum is now archived for reference purposes only.
Please see the following announcement for more information: viewtopic.php?f=14&t=1385785
Locked
User avatar
gussie
Registered User
Posts: 92
Joined: Wed Nov 27, 2002 1:26 am

Webhost complaining of MySQL overload -- need your advice

Post by gussie »

Hello,

I have a heavily modded phpBB 2.0.3 running at a webhost who is using FreeBSD out of AboveNet

I first set up phpBB 2.0.1 here last August. We did not encounter the problem described below until 5 days ago. I suspected it was the problem outlined inthe Session Table Fix tutorial and implemented it. It hasn't helped.

In the past two months, I've added the Shop and Bank mods and they've been popular. Ours is specialized community (English Speaking people in Taiwan) but it is growing -- so the added attention (volume) should also play a role.

Here is the latest message from my webhost, 2 days after I implemented the Sessions Table Fix.
we had to turn off your scripts in [your forum] directory again

there was 100 mysql connections from your script, and the system load was at 93! (normally its 1 or less)

The problem doesnt appear to be solved.

Here is what I can recommend as a fix

1) dont run that software anymore

or

2) place at the top of the scripts a file locking mechanism to lock one of 5 lockfiles. If it succeeds - continue processing, and unlock after completion. If it fails then send back a page saying "system too busy - try again later". This forces your scripts to run AT MOST 5 copies.

2b) you may also want to limit the number of connections per IP address to keep one user from hogging all your resources. A scan of the outstanding requests on your site showed most of your connections was coming from a single IP address.

solution 2 requires some significant programming, if you are not prepared to undertake this then I recommend you not run the scripts.


Any ideas you have are deeply appreciated,

gus@segue.com.tw
User avatar
A_Jelly_Doughnut
Former Team Member
Posts: 34457
Joined: Sat Jan 18, 2003 1:26 am
Location: Where the Rivers Run
Contact:

Post by A_Jelly_Doughnut »

Some additional fixes would be to reduce the number of mysql queries by manually disabling some features (new posts here, last post by...). Also, the host could reduce the number of simultaneous connections in php.ini. I suggest finding a better host above all.
A Donut's Blog
"Bach's Prelude (Cello Suite No. 1) is driving Indiana country roads in Autumn" - Ann Kish
gatty
Registered User
Posts: 506
Joined: Fri May 03, 2002 12:27 am
Location: Australia
Contact:

Post by gatty »

when my forum was being DDoS'd my server load was 94 and 248 connections.....

It was running slow, but was still going :P

view your apache logs and see what's doin in there....
Gatty
www.gatty.net
Free Hosting With PHP/MySQL Support!

Image Search Before Asking!
User avatar
gussie
Registered User
Posts: 92
Joined: Wed Nov 27, 2002 1:26 am

Post by gussie »

jfyi, I ultimately switched to [spam] -- they are quite sophisticated in handling MySQL loads. They even came up with their own metric: "conuaries" -- which represents MYSQL connections and queries

My forum now averages about 15 million conuaries a month. I estimate that the host that I discussed above couldn't handle more than 5 to 10 million without affecting its other users.

I asked [spam] if I can have special pricing for my MySQL usage since I'm "up to" 15 million. They explained that for them, a heavy user is one that requires over 100 million conuaries. This actually made me feel better, since I won't worry about MySQL capacity anymore
Last edited by Techie-Micheal on Mon Jun 18, 2007 5:14 pm, edited 1 time in total.
Reason: Removed spam
Wert
Former Team Member
Posts: 3677
Joined: Tue Jul 03, 2001 8:33 pm
Location: Sacramento, CA
Name: Chris Aguilar

Post by Wert »

Heavily modded forums tend to load the server with a lot of unnecessary queries and the like.

Try running leaner (less or no mods) or wait for 2.2.
Chris Aguilar - AKA "Wert"
User avatar
JLA
Registered User
Posts: 580
Joined: Tue Nov 16, 2004 5:23 pm
Location: USA
Name: JLA FORUMS
Contact:

Re: Webhost complaining of MySQL overload -- need your advice

Post by JLA »

Is there a way to tell phpbb to limit the number of connections allowed by one IP address?

A sort of board wide flood control?
User avatar
camm15h
Former Team Member
Posts: 4981
Joined: Wed Jul 30, 2003 1:02 am
Location: Hull, UK
Name: Paul Cammish

Re: Webhost complaining of MySQL overload -- need your advice

Post by camm15h »

JLA wrote:Is there a way to tell phpbb to limit the number of connections allowed by one IP address?
This topic is referring to queries from requests on the website, not to restrict a users connection to one. The topic is also around four years old, (topics that are old shouldn't be bumped) therefore I advise you to start a new topic with the same question. :)

Regards,
camm15h
Locked

Return to “2.0.x Support Forum”