[RESOLVED] Outgrowing a Dual Xeon server, what now?

[cadkins23]

I did netstat and got this:
procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
19 1 9556 141732 96352 1172700 0 0 43 22 38 7 53 12 33 2

Then I did nestat -t > 128.txt and got this: (128 people online when I did this)
www.potspace.com/128.txt

[cadkins23]

Sxooter wrote:

Zombie is a crashed / dead process, i.e. something went wrong. Sleeping is waiting to run, usually waiting on the db or I/O access.

Ok well I would see it disappear, and a minute later another zombie for a few seconds, etc.

[Sxooter]

Overall, this is a big improvement, since the server isn't crashing and you actually have some idle time on your CPUs showing up. Only 2% idle in vmstat and the 0.0% wait in top mean you're NOT I/O bound, which means you've still got a lot of room left for improvement.

How big is your database, in megabytes? If it's around 130 meg or so then mysql is fitting it all into memory and you don't need to give it more memory to play with. If it's noticeably bigger than that you might need to tune your db to use more memory.

Did you find anymore slow queries? I'm guessing that elimintating the super slow order by rand() limit 1 queries gave you quite a bit of breathing room.

The zombie processes bother me. They mean that for some reason the apache / php process they're running crashed. that normally shouldn't be happening.

[cadkins23]

Well I wasn't putting the log code in the right area, so after retrying for 2 hours I finally figured out where to put it lol and then the logging began. I had lowered it down to 1 second so now I raised it back to 5 seconds, cleared it and am waiting to see. We have 136 online logged in, wow.

[Sxooter]

Well, start planning on your second server now. If you keep growing like this, your first step will be to move the db to its own server. Or move the apache to another machine and dedicate this machine to db work, depending on what the new machine looks like.

Congrats on handling the load. How's the user experience from your end if you log in and use the site when it's under load?

[Sxooter]

As long as the zombies are short-lived (hahaha) you're ok. They're just waiting to be reaped. It's the ones that stick around for a long time you need to worry about as they're stuck waiting on something.

Are you showing any crashes in your apache error logs? Or the system /var/log/messages log?

[cadkins23]

# User@Host: youradul_dbadmin[youradul_dbadmin] @ localhost []
# Query_time: 8  Lock_time: 0  Rows_sent: 44936  Rows_examined: 89872
select *from members a where 1 = 1 order by a.member_id desc;

How come the rows examined is double the members I have? I know why 1 = 1 is used, because it's more or less a space filler if other filters are used in the search of my site.

[Sxooter]

cadkins23 wrote:

How come the rows examined is double the members I have? I know why 1 = 1 is used, because it's more or less a space filler if other filters are used in the search of my site.

I'm gonna guess that it means that the sort had to hit all the rows a second time. Why are you selecting ALL the users? I'm guessing a limit on that query would speed it up quite a bit.

[cadkins23]

Sxooter wrote:

Well, start planning on your second server now. If you keep growing like this, your first step will be to move the db to its own server. Or move the apache to another machine and dedicate this machine to db work, depending on what the new machine looks like.

Congrats on handling the load. How's the user experience from your end if you log in and use the site when it's under load?

Haha thanks. 😉 The user experience isn't too bad right now. I'll cehck the logs like you mentioned though. I know that the error logs have errors in them just from some erroneous php code. I wish I was a better coder person. 🙂

As for a second server, that's where I'm confused. I pay now $415 or something like that for this server, and I can't afford an additional $415 no way lol. 🙂 I wonder if I can get a small server for the database, or put the database on this one here, and use a smaller one for the php files, etc.

Could I just get two medium sized servers right now and pay the same amount? One reason I don't like Aplus is because they're setup fee happy. I wanted to get another server with them and they want like $300 in new setup fees when we just gave them like $300 in setup fees in January grrrr.

I've come across some other sites, but we use at least 10gb/day of traffic so we have to have room for growth, etc. Most hosts allow my content which is great because otherwise it would be tough looking for hosts.

Anyone have any ideas on a multi server configuration? haha 🙁 lol

[Sxooter]

We pay something like $3,000 a year for managed blades at Savvis. I can't recommend Savvis though, at least not yet. They seem to make way too many mistakes for my tastes.

I've always worked somewhere that the servers and provisioning were somebody else's problem. Sorry for not being a lot of help on that.

[cadkins23]

Just realized I had like 40 messages again from vbulletin saying:

mysql_connect() [<a 
href='function.mysql-connect'>function.mysql-connect</a>]: Can't connect to local MySQL server through socket 
'/var/lib/mysql/mysql.sock' (2)
/var/www/vhosts/potspace.com/httpdocs/forum/includes/class_core.php on 
line 271

[cadkins23]

I get the feeling that I'll end up getting another server and still top out at like 100+ members online. This stuff is a lot more tricky than I had originally thought lol. I used to think you just have to get the traffic and the rest is cake. Perhaps if my site was able to use ADSENSE then I could just hire a team lol.

[Sxooter]

Looks like you need to increase the maximum connections in mysql

[cadkins23]

Hmmmm.. Do I do that by changing my.cnf ?

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
long_query_time=5
log-slow-queries=/var/log/mysql-slow.log

[mysql.server]
user=mysql
basedir=/var/lib
max_connections=5000
[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Should I raise the 5000 to something larger?

[Sxooter]

Are you sure it's really set that high? That's REALLY high.

What does

show max_connections

from the mysql command prompt say? not sure how else to check it.

EDIT: Oh, and what's the max number of children that apache is set to?

[cadkins23]

When I asked my host about upgrading to MySQL 5.x they said they can do it but since we're using Plesk they have to use some program that will make it impossible to upgrade Plesk in the future or something like that. I don't see why not. I think my head's spinning after all this. 😉

[Sxooter]

According to this page: http://download1.swsoft.com/Plesk/Plesk8.1/Doc/plesk-8.1-unix-administrators-guide/index.htm Plesk 8.1 supports MySQL 5.0. So tell them to get off their asses and upgrade.

[cadkins23]

When I first launched the site, right away with barely any members on we topped out and needed a dedicated server. Our host shut us right down with no warning except for an email saying our site was taken down due to using up too much system resources. I upgraded but it took them the full 24 hours plus about 6 more hours to get us a server. After taking the time to transfer all the files and reconfigure it, etc. We quickly outgrew that one too. We were down for most of December because of one thing or another, including a hack/redirection attempt which ended up making us lose hundreds of members and reverting to a previous backup because once again I made the mistake of listening to my host. No matter which host I go with it's like they're the same imcompetent company. 😉 I'm just upset that I didn't need memory and now I'm going to be paying more with the same problem as before. Anyway we were down for most of December so when we came back up the traffic was almost gone from being down for almost a month. Basically when the script worked finally, I jumped on the site and my girlfriend tried and she got the too many connections problem. There were only 2 of us on and 1 of us was getting the error, and then I saw it. So we called the host and they increased it. I think they may have increased it on another occasion as well. I don't understand the whole concept so I don't know what the problem is. 🙁

Sxooter wrote:

Are you sure it's really set that high? That's REALLY high.

What does

show max_connections

from the mysql command prompt say? not sure how else to check it.

Actually I'm in pgpMyAdmin and that command doesn't work. All I know is that we had to raise it on one or two occasions because of the error messaging. I don't think raising it higher will help.

[cadkins23]

Sxooter wrote:

According to this page: http://download1.swsoft.com/Plesk/Plesk8.1/Doc/plesk-8.1-unix-administrators-guide/index.htm Plesk 8.1 supports MySQL 5.0. So tell them to get off their asses and upgrade.

Hmm, this is the email that they sent to me:

Aplus.net Level 2 Dedicated Support Engineer wrote:

We can do and upgrade but there are two things you want to consider. 1
is your code compliant with mysql 5.x 2.The fact that that the machine
has Plesk on it, to upgrade to Mysql 5 we have to do a non standard
upgrade using something called atomic rocket turtle this will make further
upgrades of Plesk impossible.

So I told them that as far as I am aware Plesk v8.1 is compatible with MySQL 5.x

[Sxooter]

I think your hosting company is lost. According to this page on 5.0:
http://dev.mysql.com/doc/refman/5.0/en/too-many-connections.html
the maximum number of connections that linux / solaris can reasonably handle is 500-1000, and the maximum compiled into static binaries is 4000. The page for 4.1 says the same thing.

OK, I had to hunt it down, because I'm not familiar at all with MySQL (and quickly becoming more familiar with it than I really want to) and as the mysql user you need to run this:

/usr/sbin/mysqld --verbose --help

to see all the settings it really has or would be using.

You should only need as many connections as you have people hitting your server, plus a few more. If you're using them all up with 2 users, then something's very wrong with your server and you need to find out where its leaking connections. the most common cause is a crashing apache / php child process, which is usually the result of a misconfigured system.

HOLD THE PRESSES:

Try "show variables;" to see all the vars in your system.

Wait there's more:

select @@max_connections;