High mysql loads, any ideas?

Hi folks,

I'm experiencing very high loads on my vbulletin forum with vbseo. Active users can rise to about 1000 during peak times. On average about 25,000 visits per day.

VPS Spec
Dual Processor Quad Core (8CPU) Intel(R) Xeon(R) CPU E5420 @ 2.50GHz
30 GB SA-SCSI Disk Space
768MB SLM RAM
centos-5-x86
Apache 2.2.9
MySQL 5.0.67
PHP 5.2.6

I've moved rewrite rules to httpd.conf.
I've installed XCache and using xcache settings on vbulletin and vbseo.

Thanks for any suggestions.

[root@server ~]# top
top - 12:20:54 up 21:18,  1 user,  load average: 9.67, 8.61, 8.50
Tasks: 200 total,   1 running, 198 sleeping,   0 stopped,   1 zombie
Cpu(s):  0.0% us,  2.9% sy, 10.4% ni, 84.1% id,  2.6% wa,  0.0% hi,  0.0% si
Mem:    786432k total,   462484k used,   323948k free,        0k buffers
Swap:        0k total,        0k used,        0k free,        0k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
23609 mysql     21   6  119m  84m 2792 S   18 11.0   5:27.47 mysqld
 5592 apache    19   4 92892  18m 9640 S    8  2.4   0:00.63 httpd
10060 apache    19   4 92804  17m 8504 S    8  2.3   0:00.78 httpd
 7448 apache    20   4 93080  19m  10m S    7  2.6   0:01.16 httpd
16059 apache    19   4 92860  18m 9320 S    7  2.4   0:01.27 httpd
 9770 apache    19   4 92804  17m 8580 S    5  2.3   0:01.12 httpd
19457 apache    19   4 92864  18m 9084 S    5  2.4   0:01.79 httpd
23622 mysql     22   6  119m  84m 2792 S    5 11.0   5:16.08 mysqld
 5327 apache    19   4 92632  20m  11m S    4  2.7   0:04.34 httpd
23643 mysql     22   6  119m  84m 2792 S    4 11.0   5:25.73 mysqld
26003 mysql     22   6  119m  84m 2792 S    4 11.0   5:17.99 mysqld
26586 mysql     22   6  119m  84m 2792 S    4 11.0   5:06.35 mysqld
 7311 apache    19   4 92796  17m 8652 S    4  2.3   0:01.23 httpd
17767 mysql     23   6  119m  84m 2792 S    3 11.0   5:00.07 mysqld
20047 apache    19   4 94004  18m 8532 S    3  2.4   0:00.61 httpd
 5603 apache    19   4 93912  18m 8480 S    3  2.4   0:00.60 httpd
28278 mysql     22   6  119m  84m 2792 S    2 11.0   4:57.86 mysqld
32349 mysql     22   6  119m  84m 2792 S    2 11.0   5:00.65 mysqld
 9757 apache    19   4 92940  19m 9860 S    1  2.5   0:02.43 httpd
 9833 apache    19   4 93004  18m 9152 S    1  2.4   0:02.05 httpd
29995 mysql     22   6  119m  84m 2792 S    1 11.0   4:50.91 mysqld
 6134 apache    20   4 91712  14m 6112 S    1  1.8   0:00.13 httpd
23617 mysql     22   6  119m  84m 2792 S    1 11.0   3:38.05 mysqld
26186 mysql     21   6  119m  84m 2792 S    1 11.0   5:01.51 mysqld
26222 mysql     22   6  119m  84m 2792 S    1 11.0   5:15.14 mysqld
 7983 apache    19   4 91640  13m 5936 S    1  1.8   0:00.10 httpd
30506 mysql     22   6  119m  84m 2792 S    0 11.0   5:03.45 mysqld
26507 mysql     22   6  119m  84m 2792 S    0 11.0   5:04.63 mysqld
30282 apache    20   4 92820  21m  12m S    0  2.8   0:10.04 httpd
 9779 apache    19   4 92948  18m 9176 S    0  2.4   0:02.59 httpd
 9609 apache    19   4 92660  20m  11m S    0  2.7   0:02.38 httpd
12242 apache    19   4 92932  18m 9208 S    0  2.4   0:02.05 httpd
15696 apache    19   4 92576  17m 8572 S    0  2.3   0:00.96 httpd
17865 apache    19   4 92792  17m 8656 S    0  2.3   0:00.71 httpd
15593 apache    19   4 92840  17m 8876 S    0  2.3   0:00.83 httpd
15810 apache    19   4 92084  17m 8952 S    0  2.3   0:00.68 httpd
15876 apache    19   4 92440  16m 7540 S    0  2.1   0:00.17 httpd
 3280 apache    19   4     0    0    0 Z    0  0.0   0:00.17 httpd <defunct>
[root@server ~]#

Code (markup):

 

If you are getting 1000 concurrent users, then it's obvious that little VPS can't handle it, since you are probably already using quite a bit of CPU resources on the node. You need your own dedicated server, with Apache/MySQL optimization, which will cost more.

 

Try to tweak my.cnf

 

I agree with royo, you need a dedi.

Have you tried installing MyTop ? That would help you in tweaking mysql to perform better.

Ishan

 

Like was said above it looks like your forum has outgrown the VPS.. I don't think you will be able to "tweak" mysql to get the load down. Search google for mysql primer and mysql tuner and you will find a few free tools to help you adjust your settings. If you post the contents of your my.cnf I can make a few suggestions.

 

Thanks for the suggestions.

I've now got a dedicated server so it's all new territory.

 

Forget Apache.

Nginx + Php-Fpm + XCache + Memcached

It's best to use Debian/Ubuntu/Free BSD.

 

you could go for a dedicated server, or you could go for another vps without a contorl panel or anything and dedicate that for mysql alone. you could manage mysql with some admin software locally from your system.

This way your existing vps will take the trouble of serving the sites and the new vps will serve only mysql queries for the site.

Several hard drive accesses could also slow down servers and increase load, splitting them would be a good idea.

 

You should check mysql to see if there are any particularly slow queries. You should tweak VBulletin and put time delays between posts, searches and reduce or remove the amount of different stats being displayed.

At the end of the day you need to host your MySQL server separately from the main webserver.
You may even want to consider a MySQL Cluster, as traffic grows your site will slow to a crawl or even crash. A MySQL cluster will keep things running reliably and smoothly.