The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Memory usage steadily rising?

Discussion in 'Workarounds and Optimization' started by กำนัน, Jan 12, 2012.

  1. กำนัน

    Joined:
    Jan 12, 2012
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    I've been watching the following green line inexorably and steadily rising (it's from munin plugin). I need to know how I can diagnose this, or even if it is a problem. The dark blue section also seems unnaturally large. Can someone please comment?... Where do I even begin to diagnose this?...

    memory-month.png
     
  2. กำนัน

    Joined:
    Jan 12, 2012
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    ... and a little more info... restarting apache appears to reduce things down a little... and swapoff then swapon puts things back into normal territory, APART from the dark blue section...

    memory-day.png
     
  3. กำนัน

    Joined:
    Jan 12, 2012
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    As I prefer to try and help myself before opening a ticket, I'd be really grateful if someone could spend a few minutes explaining to me what's going on here.
     
  4. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    cPanel ticket support only supports issues related to cPanel. If you believe your server or sites are having higher usage due to either increased traffic or some account on the machine, then that would be outside our support boundaries in the ticket system anyway. We'd simply send you back to the forums for help in optimization.

    Now, you might consider hiring someone to troubleshoot this for you:

    Dev & Sys Admin Services « Application Catalog

    Otherwise, you might consider using top in command line and WHM's Daily Process Logs to see if you notice any unusual activity.

    You would want to check for any site or Apache errors in /usr/local/apache/logs/error_log location.

    You could also try checking MySQL optimization recommendations by running this script in command line:

    /usr/local/cpanel/3rdparty/mysqltuner/mysqltuner.pl

    Other areas to check are sar output, ps aux output, etc. There are a high number of threads about Apache and MySQL optimization on this forum that might be of use.
     
  5. กำนัน

    Joined:
    Jan 12, 2012
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Thanks. I think you may have pointed me in the right direction. Looks like my sql conf is all out of whack for a start...



    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.1.56-log
    [OK] Operating on 64-bit architecture

    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 1G (Tables: 489)
    [--] Data in InnoDB tables: 160K (Tables: 10)
    [!!] Total fragmented tables: 90

    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 9h 53m 17s (1M q [14.619 qps], 111K conn, TX: 7B, RX: 465M)
    [--] Reads / Writes: 52% / 48%
    [--] Total buffers: 186.0M global + 12.4M per thread (500 max threads)
    [!!] Maximum possible memory usage: 6.2G (317% of installed RAM)
    [OK] Slow queries: 0% (3/1M)
    [OK] Highest usage of available connections: 3% (18/500)
    [OK] Key buffer size / total MyISAM indexes: 128.0M/324.9M
    [OK] Key buffer hit rate: 99.9% (34M cached / 45K reads)
    [OK] Query cache efficiency: 53.9% (556K cached / 1M selects)
    [!!] Query cache prunes per day: 3828
    [OK] Sorts requiring temporary tables: 0% (17 temp sorts / 17K sorts)
    [!!] Temporary tables created on disk: 27% (20K on disk / 74K total)
    [OK] Thread cache hit rate: 99% (18 created / 111K connections)
    [OK] Table cache hit rate: 59% (949 open / 1K opened)
    [OK] Open file limit used: 8% (1K/16K)
    [OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
    [OK] InnoDB data size / buffer pool: 160.0K/8.0M

    -------- Recommendations -----------------------------------------------------
    General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Reduce your overall MySQL memory footprint for system stability
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Variables to adjust:
    *** MySQL's maximum memory usage is dangerously high ***
    *** Add RAM before increasing MySQL buffer variables ***
    query_cache_size (> 32M)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
     
  6. กำนัน

    Joined:
    Jan 12, 2012
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    DId I just post? I think I did. Maybe a moderator placed it on preview?
     
  7. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    Could you post the content of your /etc/my.cnf file? It appears that whatever settings are in that file are going over the memory usage you would want to allow for MySQL.
     
  8. กำนัน

    Joined:
    Jan 12, 2012
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Sure, thanks... although I already made some adjustments in order to try and correct this myself. Perhaps you could comment on my current settings...


    [mysqld]
    collation_server=utf8_unicode_ci
    character_set_server=utf8
    skip-character-set-client-handshake
    safe-show-database
    skip-locking
    key_buffer = 400M
    max_allowed_packet = 1M
    table_cache = 8192
    sort_buffer_size = 2M
    read_buffer_size = 2M
    read_rnd_buffer_size = 8M
    myisam_sort_buffer_size = 64M
    thread_cache_size = 80
    query_cache_type=1
    query_cache_limit=1M
    query_cache_size= 64M
    tmp_table_size=256M
    max_heap_table_size=256M
    # Try number of CPU's*2 for thread_concurrency
    thread_concurrency = 4
    set-variable = max_connections=80
    # skip-innodb
    local-infile=0
    log-slow-queries

    [mysqldump]
    quick
    max_allowed_packet = 16M

    [mysql]
    no-auto-rehash
    # Remove the next comment character if you are not familiar with SQL
    #safe-updates

    [isamchk]
    key_buffer = 128M
    sort_buffer_size = 128M
    read_buffer = 2M
    write_buffer = 2M

    [myisamchk]
    key_buffer = 128M
    sort_buffer_size = 128M
    read_buffer = 2M
    write_buffer = 2M

    [mysqlhotcopy]
    interactive-timeout​
     
    #8 กำนัน, Jan 17, 2012
    Last edited: Jan 17, 2012
  9. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    Since I have no idea what was edited, it's difficult to comment entirely on the prior MySQL tuner results. After 24 hours after the change, please post the results from that script again with the current /etc/my.cnf contents

    I did the following:

    - removed safe-show-databases as it is default already
    - removed skip-locking as it is now called skip-external-locking and is default already
    - removed thread_concurrency as it only applies to Solaris system and does not exist on Linux or FreeBSD
    - removed any comments from the file as commented out lines aren't being used as variables, place such comments into a backup and keep /etc/my.cnf clean with only variables being used
    - removed quick under [mysqldump] as it is the default already
    - changed key_buffer entries to key_buffer_size
    - changed table_cache to table_open_cache as it was renamed in MySQL 5.1, which you are using
    - changed read_buffer entries to read_buffer_size
    - changed write_buffer entries to write_buffer_size
     
  10. กำนัน

    Joined:
    Jan 12, 2012
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Thank you for your kind assistance. Here is the result you requested...



    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.1.56-log
    [OK] Operating on 64-bit architecture

    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 1G (Tables: 489)
    [--] Data in InnoDB tables: 160K (Tables: 10)
    [!!] Total fragmented tables: 34

    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 6h 30m 42s (1M q [15.902 qps], 105K conn, TX: 5B, RX: 418M)
    [--] Reads / Writes: 53% / 47%
    [--] Total buffers: 730.0M global + 12.4M per thread (80 max threads)
    [OK] Maximum possible memory usage: 1.7G (85% of installed RAM)
    [OK] Slow queries: 0% (4/1M)
    [OK] Highest usage of available connections: 16% (13/80)
    [OK] Key buffer size / total MyISAM indexes: 400.0M/282.9M
    [OK] Key buffer hit rate: 99.9% (31M cached / 36K reads)
    [OK] Query cache efficiency: 64.7% (725K cached / 1M selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (14 temp sorts / 18K sorts)
    [!!] Temporary tables created on disk: 29% (21K on disk / 72K total)
    [OK] Thread cache hit rate: 99% (13 created / 105K connections)
    [OK] Table cache hit rate: 61% (942 open / 1K opened)
    [OK] Open file limit used: 8% (1K/16K)
    [OK] Table locks acquired immediately: 99% (891K immediate / 891K locks)
    [OK] InnoDB data size / buffer pool: 160.0K/8.0M

    -------- Recommendations -----------------------------------------------------
    General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses​


    There are less errors than before, but I can't figure how to fix the Temp tables issue. Also, the fragmented tables can only be reduced to 12. I've tried optimizing every database on the server but it never goes below 12, and is again rising with more fragmented tables. Is this normal? I've no idea how to address this if 'Optimize' doesn't fix it.

    Also of concern, I've noticed this in Process Manager. It's been there all day, and is using around 10 percent memory alegedly. Is this significant?...


    /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --log-error=/var/lib/mysql/example.example.co.uk.err --pid-file=/var/lib/mysql/example.example.co.uk.pid
     
  11. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    The process you mention is the MySQL process itself. It contains all the other user processes within it. It isn't concerning because it needs to run for MySQL to function.

    For the optimize question for fragmented tables, what command are you running to optimize them?

    Finally, for the information on "Temporary table size is already large - reduce result set size" warning, here is a discussion about this on MySQL mailing list:

    MySQL Lists: mysql: Temporary tables created on disk: 99%
    MySQL Lists: mysql: Re: Temporary tables created on disk: 99%
     
  12. กำนัน

    Joined:
    Jan 12, 2012
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    I'm trying several methods. First, I tried phpMyAdmin to Optimize all databases tables. Secondly, I used the various application's (e.g. Invision Power Board) own internal method for optimizing. Finally, I tried various terminal command lines to optimize all databses. None could get the result to below 12 fragmented tables, and the number just keeps going back up again. I fear a this may be indicative of a more serious problem in my databases.
     
  13. alphawolf50

    alphawolf50 Well-Known Member

    Joined:
    Apr 28, 2011
    Messages:
    186
    Likes Received:
    2
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    Fragmented tables are not a big deal. InnoDB tables (of which you have 10) often report being fragmented when they are not. You also have a fairly write-heavy access pattern, which tends to fragment MyISAM tables. If you want to see which tables mysqltuner thinks are fragmented, run this in phpMyAdmin:
    Code:
    SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema','mysql') AND Data_free > 0 AND NOT ENGINE='MEMORY'
    Sorry to butt in on the tuning here... but unless MySQL is the only thing you're running on that server, it is still using far too much RAM. You need to reduce your per-session buffers (sort_buffer_size, read_buffer_size, read_rnd_buffer_size). 256K is sufficient for most people for these settings, and they should only be increased above this size if you have actual evidence that it will increase performance with your specific load. Here's more info on why 256K is usually the right size: Planet MySQL - Archives - More on understanding sort_buffer_size

    Also table_open_cache should be closer to 1024 on your machine. Having this number too large can actually increase the time it takes to open and close tables.

    Consider setting table_definition_cache=512.
     
  14. กำนัน

    Joined:
    Jan 12, 2012
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Thank you alphawolf50. I will implement your recommendations, run for a few days and see what happens.
    Now that I've been tuning, the munin graph from the last month now looks like this... I notice the aformentiond committed line still rises after each restart. According to munins own notes, 'The amount of memory allocated to programs. Overcommitting is normal, but may indicate memory leaks'... Does this look normal?...

    memory-month.png
     
  15. alphawolf50

    alphawolf50 Well-Known Member

    Joined:
    Apr 28, 2011
    Messages:
    186
    Likes Received:
    2
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    In the range that it's been in for the last 2 weeks it's not a problem at all. If it keeps rising steadily well above the 2GB mark (like it used to), then it is indicative of a memory leak that needs to be addressed. When the red area (swap) increases, it is in issue that needs immediate attention, or performance will suffer. Keep an eye on it, and let us know if that happens. If it does, we'll need to know which version of Apache and PHP you're using, and what your settings are. When Apache is configured properly it will kill its own processes/threads from time to time to prevent memory leaks from affecting the system.
     
  16. กำนัน

    Joined:
    Jan 12, 2012
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hi,

    Things appear to be OK with MySQL (although the temp tables warning is still there)...

    What's concerning me is this potential memory leak, and after waiting a while, it appears to be once again going over the available memory. I've attached the latest munin graph...
    I'm running Apache 2.2.19 and PHP 5.3.6.
    Which settings do you specifically require? I'd sure appreciate any advice you can give me...
     

    Attached Files:

Loading...

Share This Page