I'm running several applications on my VPS. These apps are built with PHP and used in a social network. Currently, I have some problems with the server's speed. My VPS has the following characteristics:
CPU 2x2000GHz
Memory: 3Gb
SAS 15K 100 Gb
CentOS 5.5
my.cnf (MyIsam engine):
max_connections=80
key_buffer = 16K
max_allowed_packet = 1M
table_cache = 128
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 64K
query_cache_type=1
query_cache_size=20M
server-id = 1
[mysqldump]
quick
max_allowed_packet = 10M
default-character-set = cp1251
[mysql]
no-auto-rehash
default-character-set = cp1251
[isamchk]
key_buffer = 8M
sort_buffer_size = 8M
[myisamchk]
key_buffer = 8M
sort_buffer_size = 8M
We also installed memcached:
PORT="11211"
USER="nobody"
MAXCONN="1024"
CACHESIZE="500"
OPTIONS="-l 127.0.0.1"
very small part of the mysql server processes list:
UPDATE 1
| 314041 | obwaga2_ob1 | localhost | obwaga2_ob1 | Query | 0 | Sending data | SELECT `uid` FROM `obschaga_users` WHERE `uid`>0 |
| 314045 | obwaga2_ob1 | localhost | obwaga2_ob1 | Query | 0 | Locked | UPDATE `obschaga_users` SET `online`=1306785866 WHERE `uid`=46217997 LIMIT 1 |
| 314046 | obwaga2_ob1 | localhost | obwaga2_ob1 | Query | 0 | Locked | UPDATE `obschaga_users` SET `online`=1306785866 WHERE `uid`=21704816 LIMIT 1
The problem consists in the 60-80% load when there are 200-280+ connects to the Apache web server. Could you please correct my mysql/memcached server settings to speed up the server? If you need more info regarding the server configuration - just let me know.
Thank you in advance.
UPDATE 2
Periodically I'm facing with the following issue:
-bash-3.2# uptime
-bash: fork: Cannot allocate memory
show status like 'Threads_connected';
command shows 93 threads. I suppose when the server does not respond to commands there are much more threads.
Sometimes I have the following error either:
ERROR 1040 (00000): Too many connections
Mysql processes are as below:
| 1630030 | obwaga2_obwaga | localhost | obwaga2_obwaga | Query | 0 | Opening tables | SELECT `sex` FROM `obschaga_users` WHERE `uid`<>134663653 and `club`=1 and `online`>=1306849507 |
| 1630031 | obwaga2_obwaga | localhost | obwaga2_obwaga | Query | 0 | Opening tables | SELECT `level`,`clan`,`silver`,`win`,`lost`,`val`,`otkaz`,`wgift` FROM `obschaga_users` WHERE `uid`= |
| 1630032 | obwaga2_obwaga | localhost | obwaga2_obwaga | Query | 0 | Opening tables | SELECT `uid`,`online` FROM `obschaga_users` WHERE `uid` IN (96113249, 88303183, 123525384, 37125913, |
| 1630033 | obwaga2_obwaga | localhost | obwaga2_obwaga | Query | 0 | Opening tables | SELECT `obsch`,`online` FROM `obschaga_users` WHERE `uid`=114941284 LIMIT 1 |
obschaga_users` WHERE `uid`= |
| 1630036 | obwaga2_obwaga | localhost | obwaga2_obwaga | Query | 0 | Opening tables | SELECT `uid1` FROM `obschaga_wait_friend` WHERE `uid0`=39448276 and `state`=10 LIMIT 1
The funny moment is that there are plenty of memory free -mto
shows:
total used free shared buffers cached
Mem: 3000 1111 1888 0 0 0
Swap: 0 0 0
Total: 3000 1111 1888
Should I show cat /proc/user_beancounters
or ulimit -a
?