I have a P3/850 with 2GB of RAM with 30GB 7200rpm HD. Though the CPU is dual capable, the memory is not upgradeable further (2GB max). I am hosting two forums (with VB) on that server with total users online about max at around 350. One of the two forums has a total users online of max around 300. The CPU load is not as low as I expected. Before I add a second forum, the max server load was was around 1.2 for the 15 minutes average (only for a while, when the forum got 250-300 concurrent users online) and mostly it's around 0.5. But today, it just got to around 3 (again 15 minutes average), although the web pages are still loaded reasonably fast.

Any apache/mysql configurations I need to change? FYI, apache is 1.3.20 and mysql is 3.23.32

Currently, this is my /etc/my.cnf:


[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
set-variable = max_connections=350
set-variable = key_buffer=384M
set-variable = max_allowed_packet=1M
set-variable = table_cache=1024
set-variable = sort_buffer=8M
set-variable = record_buffer=5M
set-variable = thread_cache=1024
set-variable = thread_cache_size=256
set-variable = thread_concurrency=4 # Try number of CPU's*2
set-variable = myisam_sort_buffer_size=64M
log-bin
server-id = 1



and my apache httpd.conf


MinSpareServers 2
MaxSpareServers 5
StartServers 2
MaxClients 300


I have tried to increase the values for MinspareServers and MaxSpareservers to 10 and 15 respectively, and it did not reduce the server load. I read somewhere telling that it might be better to reduce these values, especially for mysql driven sites, and hence I reduced them to 2 and 5 respectively.

Also, I wonder ... how much will the second CPU help in this case?

I just visited forums.anandtech.com ... The total users online was around 800! The site loads very fast! I wonder what kind of hardware specs they have on the server. Also, are they using VB?

your inputs/suggestions will be appreciated

cheers,
:beer:

Currently, 'ps-auxww | grep -c mysql' gives me 52 mysql processes running, and 'ps-auxww | grep -c http' gives me 62 httpd processes running. Are these numbers considered high/normal?

Total active users online on the two forums are about 275
CPU load:

4:27pm up 3:19, 1 user, load average: 3.04, 2.80, 2.83


'free' gives:


total used free shared buffers cached
Mem: 2088544 873568 1214976 0 111216 574268
-/+ buffers/cache: 188084 1900460
Swap: 2097136 0 2097136


Tons of memory left, yet my server load is still hanging around 3.0 (15 minutes averages). It has never gone up to more than 1.5 for the past 8 days (with about the same total numbers of visitors conccurently online).

cheers,
:beer:

Load average is calculated based on many factors, most important being CPU and I/O usage.

Apparently you do not need more memory, but an additional CPU will probably help tremendously.

You might also consider using persistant MySQL connections; make sure PHP has the option enabled, and make sure VBulletin uses mysql_pconnect() instead of mysql_connect() (and doesn't close the connection). I don't recall which VBulletin uses off hand...

With that said, there's only so much configuration changes can do; the end result is, if your system isn't fast enough to handle the amount of load, you will need to upgrade (either add the second CPU, or offload one of the sites to a second machine).

Hope this helps.

Anandtech uses FuseTalk 3 ( http://www.e-zonemedia.com/fusetalk ), a ColdFusion based forum. And their server configs can be found here >> http://www.anandtech.com/IT/showdoc.html?i=1456&p=14 . Something tells me you're looking to do something a little less drastic.. ;).

Hm ... 5 web servers and 1 dedicated mysql server to form a nice load balancing ... No wonder it's fast! :)

Something that is interesting ... The server load will be quite reasonable when the total number of users is around up to 175-200. Once it hits 200, the server load will increase by a factor of two, and once it hits 250, the server load will increase by a factor of 3 or more. This is just observation from my experience ...

cheers,
:beer:

Originally posted by dektong
Hm ... 5 web servers and 1 dedicated mysql server to form a nice load balancing ... No wonder it's fast! :)


Hehe - you missed one. According to that page JDF gave the link to - its 6 web servers and one database server. :)

Wow 6 server and 1 db server. I wonder how much they pay for bandwidth usage? Anybody?

Maybe your harddrive is too slow when there are more than 200 users on your boards. A change to SCSI may help because it reduces the CPU load (controller helps).
Put the database on a seperate and very fast hdd.
A second CPU will also help to reduce the load.

On the software side you can try Zend Optimizer (free), Zend Cache (Attentions this costs $2400) or a similar product.

- domi

I agree on the HD issue, IDE takes more CPU time than SCSI... so the switch might be useful... Also, worth a shot upgrade mysql which might help... try using the kernel webserver if you're using linux to serv any static content e.g. images which should help.

I don't know if vB supports it, but switch to using persistent connections with mysql...

Are you using mod_gzip with the vb?

SplashHost:
yes ... I am using mod_gzip. My clients insisted me to use mod_gzip since one of them can save about 20GB of bandwith with it, I am not sure about the other one since he just moved.

Voodoo Web:
My HD is 7200 rpm, which is better than 5400rpm but of course SCSI is still better (thus I am deciding whether to use SCSI for my next server). What I regretted though, is I did not go with two HDs (just one). With two HDs (one to server html pages, the other one for mysql), I think the server load will be somehow lower since the html/mysl requests (Write/read) can be handled at the same time on separate HDs.

JTY:
I am not sure what you mean by "using the kernel webserver ....". How do you do that? will you please be kind enough to explain? See my title? I am a complete newbie :D


For all:
So, my apache/mysql configurations are OK?

Thank you all!

cheers,
:beer:

For a site that gets so many hits, you should highly concider the use of SCSI drives. They use a lot less CPU and can handle I/O a lot better and faster than IDE drives.

sorry but you mysql settings are configured right and probably causing slow downs

free advice ;)

1. disabling mysql logging this can slow your server down and make it work harder than it needs, unless you have some kind of replication or backup system in place

2. key_buffers is WAY TOO high reduce 384M to around 16 - 32M - the way mysql uses memory once the key buffer is allocated it doesn't go down... - have a read

- http://www.mysql.com/doc/M/e/Memory_use.html
- http://i4net.tv/marticle/get.php?action=getarticle&articleid=3 from this link

MySQL memory used = key_buffer + max_connections * (join_buffer + record_buffer + sort_buffer + thread_stack + tmp_table_size)
probably what is happening is your key buffer is too high you're swapping to disk or that and a combination of mysql logging being turned on is making your cpu really work hard?


3. add a join_buffer setting

4. upgrade mysql to 3.23.43

5. what kernel are on 2.2.x or 2.4.x ?

here's what i'd put into your my.cnf file


[client]
port = 3306
socket = /var/lib/mysql/mysql.sock

[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
set-variable = max_connections=400
set-variable = key_buffer=24M
set-variable = join_buffer=6M
set-variable = record_buffer=6M
set-variable = sort_buffer=9M
set-variable = table_cache=1024
set-variable = myisam_sort_buffer_size=32M
set-variable = thread_cache_size=256

[myisamchk]
set-variable = key_buffer=256M
set-variable = sort_buffer=256M
set-variable = read_buffer=8M
set-variable = write_buffer=8M restart mysql

6. reduce MaxClients back to 150, you don't want to let more clients on than mysql will handle with the settings you set for it

mod_gzip shouldn't cause any problems at all.

Also check out Mysql.com's very own tips for optimal mysql performance on linux http://www.mysql.com/doc/L/i/Linux.html

Dektong, if i remember correctly in vbulletin there is a choice on hwo much compression is reduced between 1 and 9, it should be set at around 1-3 i think, the higher numbers dont reduce bandwidth much but take up much more processing power.

Originally posted by SplashHost.com
Dektong, if i remember correctly in vbulletin there is a choice on hwo much compression is reduced between 1 and 9, it should be set at around 1-3 i think, the higer numbers dont reduce bandwidth much but take up much more processing power. vB has inbuilt gzip support and the strongly recommended setting is 1 and no higher. Of course if you have mod_gzip installed you should turn off vB's inbuilt gzip support.

I knew it was something small, couldnt remember the exact setting

Eva, thanks for your reply :) Free advice is always welcome and most appreciated.

4. upgrade mysql to 3.23.43
Yes, I will want to do that. I want to make sure that it will work with CPanel/WHM ... I just realize that CPanel has a copy of my.cnf stored in /usr/local/cpanel/whostmgr/my.cnf that will autmatically rewrite my /etc/my.cnf ... No wonder I was confused how in the world my /etc/my.cnf got changed back to the original setting. Things like this prevented me to upgrade easily since I do not yet fully understand how CPanel/WHM works.


5. what kernel are on 2.2.x or 2.4.x ?


I am on RH 6.2 with 2.2.18pre11-va2.1 #1 kernel.
I am fully aware that mysql is having problems with 2.2.14 kernel. Before mysql puts this on their web, I got tons of mysql problems--table corrupted--and could not understand the reason why except guessing that this might have something to do with kernel (no problem when I tried it on 2.4.x kernel). But will 2.4.x kernel works better than 2.2.x for mysql (besides the problems with corrupted tables with 2.2.14 kernel)


6. reduce MaxClients back to 150, you don't want to let more clients on than mysql will handle with the settings you set for it

OK, done ... are my other settings OK? (MinSpareServer, MaxSpareServer, etc ... or does it really matter?)

Thanks for the tips ... I just had time to try what you suggested. In the past few days, I got about 100-120 mysql processes running and around 75-80 httpd processes running (ps -auxww | grep -c xxxx). Curretnly I got aroud 55 and 22 of httpd and mysql processes running respetively. 15 min load average is dropping to around 2.2 (still need lower :) ) with 'only' 235 people online on the two VB forums. I will see when it goes high at around 300-350 people online ...

thanks everybody for the tips ...

cheers,
:beer:

If you run a "top", you will see a line that looks something like this:

CPU states: 99.8% idle, 0.0% user, 0.2% kernel, 0.0% iowait, 0.0% swap

My example is from a very bored server that has nothing to do (99.8% idle), but the numbers I'm interested in for your situation are % iowait and % swap.

Since you are on a single IDE HD, you may be hitting an I/O bottleneck. Your HD may be big and fast, but it can still only do one operation at a time, and if it is constantly seeking back and forth between web pages and database files, you will take a significant performance hit due to the seek delay. As I saw mentioned earlier, adding a second drive and moving either the DB files or the web files to the second drive can help a *lot*, if this is indeed where you bottleneck is.

Several people have suggested SCSI and they have a good point, but throwing in a second IDE drive may be "good enough" for now and will cost less and be easier to do that converting your entire setup to SCSI. If you can, put your second IDE drive on your secondary IDE controller, instead of making it a slave to your first drive on the primary controller.

Hopefully your % swap is 0 or near 0. If not, you are using swap memory (which adds even more disk activity) and you need to either reduce your memory usage or buy more memory. It doesn't sound like this is really your problem though. I'd focus more on the I/O situation.

Good luck
Doug

Originally posted by JTY
I don't know if vB supports it, but switch to using persistent connections with mysql...

In the vbulletin forums they advise to wsitch this off to lower the server load :eek: