Critique my "my.cnf" for 2Gb System

[WebDork]

I am having performance problems with my server where the load goes through the roof and I eventually run out of memory and the system reboots. I have read every post I can find on my.cnf and they are all somewhat contradictory - Im hoping that someone can check out my config and let me know what I have messed up if anything

System info:

- CPU: AMD Athlon(tm) 64 Processor 3400+
- 2Gb Ram
- 2.4.21-40.EL kernel
- centOS 3.6
- PLESK 7.5.4

Server info: http://www.angusserver1.com/

MySQL 4.1.15

PHP 4.4.2 (cgi) (built: Feb 19 2006 19:45:50)
Copyright 1997-2006 The PHP Group
Zend Engine v1.3.0, Copyright 1998-2004 Zend Technologies with the ionCube PHP Loader v2.5, Copyright 2002-2004, by ionCube Ltd.

my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
#skip-locking
#skip-innodb
#log-bin
old_passwords=1

max_allowed_packet = 16M

max_connections = 500
max_connect_errors = 10

wait_timeout=100
connect_timeout = 10
interactive_timeout=100

query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1

table_cache = 1024
thread_concurrency=2

key_buffer=150M
join_buffer_size = 1M
record_buffer=1M
read_buffer_size = 1M
sort_buffer_size = 1M
read_rnd_buffer_size = 1M
myisam_sort_buffer_size=64M

#thread_cache_size = 256
thread_cache_size = 128

[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M

TOP

23:12:04 up 1:38, 3 users, load average: 6.43, 4.59, 4.14
213 processes: 205 sleeping, 8 running, 0 zombie, 0 stopped
CPU states: cpu user nice system irq softirq iowait idle
total 36.1% 0.0% 23.5% 5.0% 3.3% 31.9% 0.0%
Mem: 1993952k av, 1983328k used, 10624k free, 0k shrd, 50788k buff
1428844k actv, 270796k in_d, 29612k in_c
Swap: 4096532k av, 10760k used, 4085772k free 1268160k cached

TOP a bit later

23:26:16 up 1:53, 3 users, load average: 30.01, 20.26, 14.66
367 processes: 361 sleeping, 5 running, 1 zombie, 0 stopped
CPU states: cpu user nice system irq softirq iowait idle
total 49.6% 0.0% 15.3% 3.1% 1.7% 30.0% 0.0%
Mem: 1993952k av, 1978008k used, 15944k free, 0k shrd, 39352k buff
1529860k actv, 290660k in_d, 27496k in_c
Swap: 4096532k av, 70748k used, 4025784k free 1129056k cached


ps aux | grep -c httpd
132

mysqladmin status

Uptime: 1918 Threads: 125 Questions: 137530 Slow queries: 7 Opens: 398 Flush tables: 1 Open tables: 392 Queries per second avg: 71.705

mytop

MySQL on localhost (4.1.15) up 0+00:33:48 [23:23:51]
Queries: 140.4k qps: 71 Slow: 8.0 Se/In/Up/De(%): 85/00/06/01
qps now: 59 Slow qps: 0.0 Threads: 137 ( 118/ 4) 80/01/07/01
Cache Hits: 61.7k Hits/s: 31.1 Hits now: 27.5 Ratio: 51.8% Ratio now: 58.5%
Key Efficiency: 99.9% Bps in/out: 8.6k/229.8k Now in/out: 7.1k/219.6k


Anything else worth posting let me know

Ill post similar on WHT and then post whatever the outcome is here so hopefully others can benefit.

 

[WebDork]

mysqladmin processlist

+------+------------+-----------+--------------------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+------------+-----------+--------------------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| 7024 | netsexiest | localhost | netsexiest | Sleep | 336 | | |
| 7397 | netsexiest | localhost | netsexiest | Sleep | 230 | | |
| 7429 | netsexiest | localhost | netsexiest | Sleep | 219 | | |
| 7441 | netsexiest | localhost | netsexiest | Sleep | 214 | | |
| 7708 | netsexiest | localhost | netsexiest | Sleep | 140 | | |
| 7937 | netsexiest | localhost | netsexiest | Sleep | 83 | | |
| 7983 | netsexiest | localhost | netsexiest | Sleep | 71 | | |
| 8028 | forums | localhost | aussiecelebs_phpbb | Query | 57 | Copying to tmp table | SELECT f.forum_name, t.topic_title, u.user_id, u.username, u.user_sig, u.user_sig_bbcode_uid, p.post |
| 8027 | forums | localhost | aussiecelebs_phpbb | Query | 57 | Copying to tmp table | SELECT f.forum_name, t.topic_title, u.user_id, u.username, u.user_sig, u.user_sig_bbcode_uid, p.post |
| 8029 | forums | localhost | aussiecelebs_phpbb | Query | 57 | Locked | UPDATE phpbb_users SET user_session_time = 1144193679, user_session_page = 0, user_lastvisit = 1 |
| 8044 | forums | localhost | aussiecelebs_phpbb | Query | 52 | Locked | SELECT u.*, s.* FROM phpbb_sessions s, phpbb_users u WHERE s.session_id = '8886b89eb0525edd15f |
| 8047 | forums | localhost | aussiecelebs_phpbb | Query | 52 | Locked | SELECT u.*, s.* FROM phpbb_sessions s, phpbb_users u WHERE s.session_id = '31765e0ee528257f3ba |
| 8065 | netsexiest | localhost | netsexiest | Sleep | 45 | | |
| 8071 | forums | localhost | aussiecelebs_phpbb | Query | 44 | Locked | SELECT u.*, s.* FROM phpbb_sessions s, phpbb_users u WHERE s.session_id = '860bb9365cd3fdab3c1 |
| 8075 | forums | localhost | aussiecelebs_phpbb | Query | 41 | Locked | SELECT u.*, s.* FROM phpbb_sessions s, phpbb_users u WHERE s.session_id = '88d281abb98b9c25153 |
| 8094 | forums | localhost | aussiecelebs_phpbb | Query | 37 | Locked | SELECT u.*, s.* FROM phpbb_sessions s, phpbb_users u WHERE s.session_id = '725dd9f786302a59fd5 |
| 8101 | forums | localhost | aussiecelebs_phpbb | Query | 35 | Locked | SELECT u.*, s.* FROM phpbb_sessions s, phpbb_users u WHERE s.session_id = '8886b89eb0525edd15f |
| 8108 | forums | localhost | aussiecelebs_phpbb | Query | 34 | Locked | SELECT u.*, s.* FROM phpbb_sessions s, phpbb_users u WHERE s.session_id = 'f5c2d46c417e067f0de |
| 8128 | forums | localhost | aussiecelebs_phpbb | Query | 29 | Locked | SELECT * FROM phpbb_users WHERE user_id = 43903 |
| 8129 | netsexiest | localhost | netsexiest | Sleep | 28 | | |
| 8139 | forums | localhost | aussiecelebs_phpbb | Query | 26 | Locked | SELECT u.*, s.* FROM phpbb_sessions s, phpbb_users u WHERE s.session_id = '3d8076a681842bd7808 |
| 8142 | netsexiest | localhost | netsexiest | Sleep | 25 | | |
| 8148 | netsexiest | localhost | netsexiest | Sleep | 24 | | |
| 8155 | netsexiest | localhost | netsexiest | Sleep | 21 | | |
| 8159 | netsexiest | localhost | netsexiest | Sleep | 20 | | |
| 8160 | netsexiest | localhost | netsexiest | Sleep | 20 | | |
| 8164 | netsexiest | localhost | netsexiest | Sleep | 18 | | |
| 8165 | forums | localhost | aussiecelebs_phpbb | Query | 18 | Locked | SELECT u.*, s.* FROM phpbb_sessions s, phpbb_users u WHERE s.session_id = 'f5c2d46c417e067f0de |
| 8170 | netsexiest | localhost | netsexiest | Sleep | 16 | | |
| 8181 | netsexiest | localhost | netsexiest | Sleep | 11 | | |
| 8183 | netsexiest | localhost | netsexiest | Sleep | 11 | | |
| 8186 | forums | localhost | aussiecelebs_phpbb | Query | 10 | Locked | SELECT * FROM phpbb_users WHERE user_id = 15659 |
| 8188 | netsexiest | localhost | netsexiest | Sleep | 9 | | |
| 8189 | netsexiest | localhost | netsexiest | Sleep | 9 | | |
| 8198 | netsexiest | localhost | netsexiest | Sleep | 6 | | |
| 8206 | forums | localhost | aussiecelebs_phpbb | Query | 4 | Locked | SELECT u.*, s.* FROM phpbb_sessions s, phpbb_users u WHERE s.session_id = 'f5c2d46c417e067f0de |
| 8212 | forums | localhost | aussiecelebs_phpbb | Query | 2 | Locked | SELECT u.*, s.* FROM phpbb_sessions s, phpbb_users u WHERE s.session_id = 'd1c58426ddfcaf442d5 |
| 8217 | netsexiest | localhost | netsexiest | Sleep | 1 | | |
| 8218 | netsexiest | localhost | netsexiest | Sleep | 1 | | |
| 8219 | netsexiest | localhost | netsexiest | Sleep | 1 | | |
| 8220 | netsexiest | localhost | netsexiest | Sleep | 1 | | |
| 8221 | netsexiest | localhost | netsexiest | Sleep | 0 | | |
| 8222 | admin | localhost | | Query | 0 | | show processlist |
+------+------------+-----------+--------------------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+

 

[wagnerch]

Most of this stuff is documented on MySQL's website, very few people offer a silver bullet solution. It is more or less baselining using a tool like mysqlreport (see http://www.hackmysql.com/) and then tuning the parameters based on MySQL documentation and an understanding of what the parameters are.

http://dev.mysql.com/doc/refman/4.1/en/server-system-variables.html

The thing to be careful of here is you have 500 max connections, it is easily possible to exhaust memory on your server by doing that. If all connections were ever active they would consume about 1.5GB (on the low end, on the high end it would be more than 2GB). Keep in mind this does NOT include any other process on the server!! Apache/PHP is probably taking a fair amount of memory itself!

I would start by a) baselining your server, b) enable slow query logging, c) identify slow queries and resolve them and then rebaseline to see if it paid off. MySQL is supposed be a very "light" database, and the trade-off (due to the design goals of being a light database) is that it is not a highly concurrent database. There is a considerably high amount of lock contention on the database tables, the only way to eliminate this contention is to improve how quickly it can return a result for a query. Reducing the contention is the key, because it will reduce your need to have a high number of client connections. Since you are running mysql 4.1, you may want to slide a bit more memory to your query cache -- considering your database is 85% reads it should have some sort of pay off. 32M sounds a bit small for a query cache.

There is no doubt that your server is running a fairly high volume of traffic here, 75 queries per second is pretty high. I have a 100 domains and they are only pegging about 9 qps.

It may be worthwile to dump a mysqladmin variables. I know with mysql 3.23.58 you have to use "set-variable = thread_cache_size=..." in the /etc/my.cnf file. Mytop is indicating your thread_cache_size is actually 4, wondering if actually did take effect.