Please help tune my web VPS and mysql VPS.

[TrickyD]

I could really use some help optimizing my two VPS. Both the mysql VPS and apache/php VPS 1024mb of ram.

My site has a Wordpress homepage, but the primary source of my traffic is the IPBoard forum.

I am experiencing really slow load times on my forum when navigating from the forum index into a category and also when running a 'view new posts' query.

My web VPS is consistently averaging 50-60% cpu usage and ram usage is running around 60-70% as well.

My database VPS seemingly isn't doing anything... The cpu average is around 0 and the ram usage is about 5-7%. I definitely feel like I have something or multiple somethings misconfigured.

Apache Config

mysql config

Advice very much appreciated!

[Volvospeed]

URL to your board?

[TrickyD]

The URL is http://ncaastrategies.com/utopia >> usually becomes especially apparent around 1PM EST, but always somewhat unresponsive.

Thanks

[Volvospeed]

From shell run

#crontab -e

Whats running at 01:00?

[.Nuno.]

can you show us "ps aux --sort -rss"?
Are you running apache-prefork?

[TrickyD]

I probably led you astray with the 1PM comment. My traffic spikes everyday around lunch and then again at night to a lesser degree. That mid afternoon spike in traffic is when the site is the slowest. I did check the crontab though and there after no scheduled tasks at 1.

Here is the pastebin on the webserver for ps aux --sort -rss
http://pastebin.com/tWVjjkJN

And here is the pastebin on the database server for ps aux --sort -rss
http://pastebin.com/KPftQe6x

I am running apache-prefork

[.Nuno.]

Hello,

I don't know if you are running more than wordpress and IPB but for that I would test 1 bigger vps instead of two smaller.
This is what I would consider:

• You should add an expire header to your images;
• Your MaxClient is a little high. Try 50;
• You can place nginx in front of apache to serve static files and just proxy php requests. With this you can lower apache requirements and turn off apache keepalive;
• You could add a subdomain (or a different domain) to serve static files;
• Sphinx is your friend too;
  

For you MySQL, you can run tuning-primer.sh and mysqltuner.pl to have a better view of your resources/configurations and follow the steps to have a better setup.

For wordpress you should install W3 Total Cache.

If you have time to play, you can test varnish cache carefully to see the results.

[AlexJ]

and turn off apache keepalive;

• You could add a subdomain (or a different domain) to serve static files;
  

Please provide steps in laymen terms to achieve above? I would like to do that also. I already have different domain but never used it because I don't know how to do it.

Also by turning off keep alive does it cause issue?

Thanks

[.Nuno.]

System Settings > System > General Configuration: "Upload URL" and "Images, CSS and JS URL"

i think you need to recache your posts after these changes, so test before you setup this in you live board.

[AlexJ]

System Settings > System > General Configuration: "Upload URL" and "Images, CSS and JS URL"

i think you need to recache your posts after these changes, so test before you setup this in you live board.

Isn't that URL is the path of your location based on forum link? Question would be how do I make it so that another domain point to that? Wouldn't that cause duplicate domain URL listing issue? Little confused here.

[TrickyD]

Thank you for the reply and suggestions .Nuno. -- tuning-primer.sh is new to me, I'll try this. I've been playing with mysqltuner.

I'd also love more information on on serving up static files in front of Apache.

I've been soliciting help from the users at my host's forum as well and maybe some of that info will be helpful here. http://forum.linode.com/viewtopic.php?p=44819#44819

The site is pretty much always slow at this point, but certainly more noticeable at some times than others. The very strange thing (at least to me) is that the site is very quick on the wordpress pages and when navigating from thread to thread on the forum but slower than molasses going from index to category and when using the new posts search.

Is there anything that would make sense as to why those times would be especially bad?

[.Nuno.]

You can always try nginx or vanish in front of apache

[Gary.]

It could not be your actual host overseling on the VPS..

The my.cnf, Change these in the Fine Tuning bit.

key_buffer = 64K

max_allowed_packet = 4M

table_cache = 16

sort_buffer = 256K

to

key_buffer = 64M

max_allowed_packet = 16M

table_cache = 512

sort_buffer _size = 1M

The packet was so low I'm surrprised anyone can connect, The key_buffer was abnormally low.

[TrickyD]

Thanks Gary. I can definitely feel some difference in responsiveness since making the changes an hour or so ago. The new posts query is still abnormally long and navigating from index to a forum is noticeably quicker on average, but still painfully slow occasionally. I'll have to see how it holds up through the day tomorrow.

I also pulled the slow queries log today. Here are the results of that.

http://pastebin.com/dV77Z1BZ

My overall ram usage on both seems really low (less than 200mb used on the db server, less than 600mb used on the apache server) seems like I'm wasting an opportunity for improved performance there, but am I?

[Gary.]

Good stuff,

Change this also in your apache:

MaxKeepAliveRequests 500

to

MaxKeepAliveRequests 1000

Your my.cnf..

At the bottom locate:

[isamchk]

key_buffer = 16M

Remove and change to:

[isamchk]

key_buffer=64M

sort_buffer=64M

read_buffer=16M

write_buffer=16M

[myisamchk]

key_buffer=64M

sort_buffer=64M

read_buffer=16M

write_buffer=16M

save and restart your mysql, Also take note of the changes incase something goes wrong :smile:

[amw_drizz]

I am assuming this is a full blown production VPS right?

So do these things

• Turn down the Apache Error Logging from warn to error or crit. Less I/O if your site throws warnings occasionally.
• Change your PHP CONFIG!
  

No one here has said a thing about it. But if unchecked PHP will go nuts with any amount of ram you will let it have. If you do not already have fcgid or similar installed on your machine stop and do it now. It will take some tweaking to get configured for your setup but well worth it. Ones that is installed you will want to limit the amount of php threads allowed to run along with force close unused PHP threads. Otherwise they will hang around in memory and just kill it.

For your fcgid.conf file for apache use something like this

<IfModule mod_fcgid.c>

AddHandler fcgid-script .fcgi .php

FCGIWrapper /usr/bin/php5-cgi .php

  FcgidConnectTimeout 20

  DefaultMaxClassProcessCount 8

</IfModule>

The item you need to have is DefaultMaxClassProcessCount, otherwise a single connect to your site could use well over 30-40 php threads. No one thinks of it, but optimizing the PHP compiler along with some security tweaks along the way will make a huge difference in how responsive your site is. I've tailored my server to handle 10k+ hit spikes in traffic (ie a site got hit by reddit,digg,stumbleupon,etc) with out going off line or throwing error pages. And I am on less ram then you (only 512mb). Also looking at your apache configs some of your Values for maxrequestsperchild are insanely high, Change that to under 50 (mine is set to 0)

<IfModule mpm_worker_module>

    StartServers		  2

    MinSpareThreads	   2

    MaxSpareThreads	  20

    ThreadLimit		  20

    ThreadsPerChild	  20

    MaxClients		   40

    MaxRequestsPerChild   0

</IfModule>

# event MPM

# StartServers: initial number of server processes to start

# MaxClients: maximum number of simultaneous client connections

# MinSpareThreads: minimum number of worker threads which are kept spare

# MaxSpareThreads: maximum number of worker threads which are kept spare

# ThreadsPerChild: constant number of worker threads in each server process

# MaxRequestsPerChild: maximum number of requests a server process serves

<IfModule mpm_event_module>

    StartServers		  2

    MaxClients		   30

    MinSpareThreads	  25

    MaxSpareThreads	  75

    ThreadLimit		  64

    ThreadsPerChild	  25

    MaxRequestsPerChild   0

</IfModule>

That is my main config file for apache in the most questionable section. It serves me very well on my server.

Also Just Noticed that your on LiNode, if your in the Freemont, CA DataCenter Get out of there! Some bad karma in that DC; I use LiNode but I am on their Newark NJ Servers the 512mb Plan to be exact

Recap

• MySQL tunes based on above users are decent, still slightly high for some LiNode Machines
• Retune Apache, some values WAY too high and would cause lag when hit with alot of users since there isn't enough resources to cover them
• Configure FCGID for PHP to limit amount of PHP threads to something sane. (You don't need/want 500 php threads when 10 will do fine, etc)
  

Hope this helps in your web hosting venture, Jon