1. Please take a little time for this simple survey! Thank you for participating!
    Dismiss Notice
  2. Dear Pleskians, please read this carefully! New attachments and other rules Thank you!
    Dismiss Notice
  3. Dear Pleskians, I really hope that you will share your opinion in this Special topic for chatter about Plesk in the Clouds. Thank you!
    Dismiss Notice

Critique my "my.cnf" for 2Gb System

Discussion in 'Plesk for Linux - 8.x and Older' started by WebDork, Apr 4, 2006.

  1. WebDork

    WebDork Guest

    0
     
    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

    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.
     
  2. WebDork

    WebDork Guest

    0
     
    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 |
    +------+------------+-----------+--------------------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
     
  3. wagnerch

    wagnerch Guest

    0
     
    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.
     
Loading...