1

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 ?

9
  • 60-80% load doesn't necessarily mean there is a problem. You just have a busy server. Why do you say it's a problem? You do mean a load of 0.60 - 0.80, correct?
    – HTTP500
    May 30, 2011 at 19:32
  • @Jason No, I mean that uptime command shows 80 not 0.8. When there is a such load on the server, the apps start working much more slower. That is, I thought there is a problem.
    – Andrew
    May 30, 2011 at 19:47
  • How did you realize the problem was MySQL and not Apache?
    – ghm1014
    May 30, 2011 at 19:55
  • @ghm1014 Actually, I am not sure in my statement completely. I know that our application generates a lot of queries to the mysql server. There are number of them, please see the processes list in my updated original post. So, I decided that the mysql server is the problem and caching should be set up on the server. One time the server has gone down due to the memory outage issue.
    – Andrew
    May 30, 2011 at 20:08
  • For your processlist I see that some queries get locked. In my experience Web Server is more likely to be the bottleneck than the database. Do you have any test environment to run some stress tests?
    – ghm1014
    May 30, 2011 at 20:24

3 Answers 3

2

Chances are your configuration is OK. It is much more likely that there are algorithmic improvements to be made within your code.You didn't mention if the load was generated by Apache, MySQL, or something else. This is the first thing to check. If it is Apache, start by profiling your PHP code. xhprof is excellent for this.

If MySQL is likely the culprit, look into the slow query log. Also spend time using the EXPLAIN statement.

This SQL query is also suspect:

SELECT `uid` FROM `obschaga_users` WHERE `uid`>0

This looks like it is going to return every uid in an entire table (I doubt many uids are negative). If this query is run in your application it is likely a huge performance problem. At the very least, ensure that there is an index on the uid column. However, really this sort of query should never be used. There has to be a way to have some sort of extra search conditions to reduce the amount of data returned.

2
  • Ok @spectre256 I will pass your tip to my developer. Thank you for checking.
    – Andrew
    May 31, 2011 at 7:33
  • Our server is almost down. Please look on the updated post. When I managed to type top -c and then sort processes using Shift+m, I saw huge number of the mysqld processes. I suggest that the mysql server took all memory
    – Andrew
    May 31, 2011 at 13:41
2

Changing my comment to an answer

  1. Make sure you're not exhausting your maximum connections of 60. Run show status like 'Threads_connected'; during peak times to see if your hitting the maximum.

  2. Your buffer sizes for MySQL are very modest. Providing Apache isn't using all your memory, increase them to something like:

    bulk_insert_buffer_size=32M 
    join_buffer_size=4M 
    key_buffer_size=128M 
    max_allowed_packet=32M 
    query_cache_limit=4M 
    read_buffer_size=1M 
    read_rnd_buffer_size=2M 
    sort_buffer_size=8M 
    table_cache=128 
    tmp_table_size=32M
    
3
  • Should I add some connection_timeout directives? My server is almost down now. Please see my original post UPDATE #2
    – Andrew
    May 31, 2011 at 13:39
  • I don't think connection timeout is your problem. Just make sure you close connections in your code when you're done. I also see that you have no swap memory - is this intentional?
    – sreimer
    May 31, 2011 at 14:50
  • Our host does not allow to use swap!
    – Andrew
    May 31, 2011 at 16:27
1

You only show 3 processes in your list, 2 of which are writes and 1 read. MyISAM does table locks for writes.

You can fiddle all you want but you probably should look at getting another VPS, and putting MySQL alone on it. i.e. Have a web/app tier and a DB tier. You should probably also look seriously at migrating to InnoDB which has greater concurrency / row-level locking.

Cheers

1
  • Most of the queries are getting data but another time all of them have locked status. I will take your advice and try convert my database to InnoDB. Thank you.
    – Andrew
    May 31, 2011 at 7:22

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .