MySQL Question

**mr_fern** · May 2nd, 2005, 2:02 AM

OK I run a community website that has anywhere from 50 to 400 members active at any given time, and membership is increasing. I've noticed that the real slow down and pressure on the server is the MySQL not being able to handle all the queries in the most efficient of time when there are more members online.

My question is, would purchasing another server and using it to solely host my database really be worh it? what would you estimate would be the type of performance increase? Or would it roughly provide the same kind of service?

My perception is that if I have a seperate server for the database then that server wouldn't have to worry about PHP Generation or serving pages via apache so it should be a great increase, but maybe I'm wrong. I'm still a bit new to server operation. I've been programming PHP for 2 years, this is my first database project, and I've only had my dedicated about 2 months.

Thanks ahead of time for any input.

H · May 2nd, 2005, 2:13 AM

Depending on how much RAM you have, a RAM upgrade may be all that is necessary.

Unfortunately I'm not the best at determining these things because of lack of experience. There are thers on this forum that will be able to offer much greater advice.

**mr_fern** · May 2nd, 2005, 9:31 AM

Well I currently have 2GB of RAM right now and it's a Xeon processor.

**Kickersny.com** · May 2nd, 2005, 10:08 AM

Wow, if you have 2GB and it's being that slow I'd recommend another server. But I'm also in the same boat as haugland in that I have little to no experience in the matter.

If you did get another server, I can't imagine it would have to be as good as your current one.

**mr_fern** · May 2nd, 2005, 10:13 AM

I noticed that a lot of queries get locked and when I was reading through the mysql manual they mentioned that a lot of updates along with a lot of selects will put pressure, so I set up one of the main update queries to process only every 2 minutes but I did that earlier in the morning when few people were on. So I will see just how effective it was later on today

abacus1001 · May 3rd, 2005, 11:00 AM

I like to know what happend or what is solution I have same problem

**Bigjohn** · May 3rd, 2005, 11:03 AM

what are you using on the site? Surmunity runs on a Xeon processor and handles lots of users with out much trouble at all...

**mr_fern** · May 5th, 2005, 6:14 PM

The site runs fine late night and early in the morning when there's only about 100-200 members active, but during the regular day it starts the suffer slow periods of movement.

Brief analysis makes me believe the slowdown is due to the MySQL trying to update the users list. Everytime you move around the site it updates your online time, which keeps track of when users were last active. The myISAM tables lock when updates or inserts are processed, so the simple select queries get backed up while waiting to complete the updates.

My feeling was that is I put seperate the MySQL database and put it on its own server, then those queries would run faster since that server would not be involved in all the other processes of the site (parsing php, accepting http requests, serving all the images and pages)

May 2nd, 2005, 2:02 AM	#1 (permalink)
mr_fern All Ur Base R Belong 2 Us Excelling Contributor Joined in Feb 2005 Lives in Vegas & New York 824 posts Gave thanks: 2 Thanked 6 times	MySQL Question OK I run a community website that has anywhere from 50 to 400 members active at any given time, and membership is increasing. I've noticed that the real slow down and pressure on the server is the MySQL not being able to handle all the queries in the most efficient of time when there are more members online. My question is, would purchasing another server and using it to solely host my database really be worh it? what would you estimate would be the type of performance increase? Or would it roughly provide the same kind of service? My perception is that if I have a seperate server for the database then that server wouldn't have to worry about PHP Generation or serving pages via apache so it should be a great increase, but maybe I'm wrong. I'm still a bit new to server operation. I've been programming PHP for 2 years, this is my first database project, and I've only had my dedicated about 2 months. Thanks ahead of time for any input. __________________ Nobody doing nothing

May 2nd, 2005, 9:31 AM	#3 (permalink)
mr_fern All Ur Base R Belong 2 Us Excelling Contributor Joined in Feb 2005 Lives in Vegas & New York 824 posts Gave thanks: 2 Thanked 6 times	Well I currently have 2GB of RAM right now and it's a Xeon processor. __________________ Nobody doing nothing

May 2nd, 2005, 10:08 AM	#4 (permalink)
Kickersny.com Surpass Fan Super #1 Joined in Aug 2004 Hosted on SH58 1,688 posts Gave thanks: 6 Thanked 7 times	Wow, if you have 2GB and it's being that slow I'd recommend another server. But I'm also in the same boat as haugland in that I have little to no experience in the matter. If you did get another server, I can't imagine it would have to be as good as your current one. __________________ - Evan Charlton \| [site] \| Server - SH58

May 2nd, 2005, 10:13 AM	#5 (permalink)
mr_fern All Ur Base R Belong 2 Us Excelling Contributor Joined in Feb 2005 Lives in Vegas & New York 824 posts Gave thanks: 2 Thanked 6 times	I noticed that a lot of queries get locked and when I was reading through the mysql manual they mentioned that a lot of updates along with a lot of selects will put pressure, so I set up one of the main update queries to process only every 2 minutes but I did that earlier in the morning when few people were on. So I will see just how effective it was later on today __________________ Nobody doing nothing

May 3rd, 2005, 11:00 AM	#6 (permalink)
abacus1001 Registered User Fresh Surpasser Joined in Apr 2004 6 posts Gave thanks: 0 Thanked 0 times	I like to know what happend or what is solution I have same problem __________________ www.MxMNetWork.com

May 2nd, 2005, 2:13 AM	#2 (permalink)
H after g, before i Resident. Joined in Jul 2004 Lives in N,BC,CA 8,079 posts Gave thanks: 48 Thanked 131 times	Depending on how much RAM you have, a RAM upgrade may be all that is necessary. Unfortunately I'm not the best at determining these things because of lack of experience. There are thers on this forum that will be able to offer much greater advice.

May 3rd, 2005, 11:03 AM	#7 (permalink)
Bigjohn minor deity Super #1 Joined in Apr 2004 Lives in Georgia Hosted on XEON 7,387 posts Gave thanks: 28 Thanked 94 times	what are you using on the site? Surmunity runs on a Xeon processor and handles lots of users with out much trouble at all... __________________ Proud to be a Surmunity Mod! XEON PASS60 PASS61 Make a fundamental difference! My Sites: Curious about Brewing Beer? Join the community! >>>>> Some Change is GOOD! Keep your paycheck! Support the Fair Tax Get into an Art museum Victorian London It's your brain -*ON WEB* - mybrainhost.com (under development) What SHOULD Government do? Much Less than it Does!

May 5th, 2005, 6:14 PM	#8 (permalink)
mr_fern All Ur Base R Belong 2 Us Excelling Contributor Joined in Feb 2005 Lives in Vegas & New York 824 posts Gave thanks: 2 Thanked 6 times	The site runs fine late night and early in the morning when there's only about 100-200 members active, but during the regular day it starts the suffer slow periods of movement. Brief analysis makes me believe the slowdown is due to the MySQL trying to update the users list. Everytime you move around the site it updates your online time, which keeps track of when users were last active. The myISAM tables lock when updates or inserts are processed, so the simple select queries get backed up while waiting to complete the updates. My feeling was that is I put seperate the MySQL database and put it on its own server, then those queries would run faster since that server would not be involved in all the other processes of the site (parsing php, accepting http requests, serving all the images and pages) __________________ Nobody doing nothing

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search