BIG PROBLEM RAM USAGE

Discussion in 'General' started by Alex880, Jun 29, 2015.

  1. Alex880

    Alex880 New Member

    Hi, i have a wordpress site that get down costantly my vps. In my vps i have install perfect server Debian. I have 2 core and 2Gb ram, and with inly 1 site that have about 1200-1500 visit per day the server show that i use over 88%-99% ram. When the ram vps touch the 100% all my sites go down and i have to reboot the vps...

    in my.cnf i have set
    [mysqld]
    skip-innodb
    default-storage-engine = myisam


    Have any suggest or solution for me? i dont know really why my ram go over 90%.
     
  2. Alex880

    Alex880 New Member

    so i try to explain my problem a little better.
    I have a vps with 2 core and 2 gigabyte of ram.
    the i migrate my wordpress website the ram of the server up the 50% and when i transfer another site the ram go up the 90/98% and the mysql service go crash and i see the error "Error in establishing a database connection".
    I need to restart the server for solving. I dont know why of this error. Please can help me to solve this problems?
     
  3. till

    till Super Moderator Staff Member ISPConfig Developer

    First you should check which processes are using the ram e.g. is it the php process or mysql. If its mysql, then use the script "mysqltuner" to check if any limits are set too high:

    https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl

    Regarding php: Which php mode do you use? I recommend to swith to php-fpm and then go to options tab of the site and switch php-fpm to ondemand mode.
     
  4. Alex880

    Alex880 New Member

    Hi. How to switch in php-fpm? I using apache no ngix.
     
  5. till

    till Super Moderator Staff Member ISPConfig Developer

    Select "php-fpm" in the website settings of this site in ISPConfig.
     
  6. Alex880

    Alex880 New Member

    i need to restart the server?
     
  7. till

    till Super Moderator Staff Member ISPConfig Developer

    No.
     
  8. Alex880

    Alex880 New Member

    TNX my friend.
    I think the problem is mysql. Can tell me how to see what the services consuming this ram?
     
  9. till

    till Super Moderator Staff Member ISPConfig Developer

    Did you run mysqltuner already? Whats the output?
     
  10. AAPA

    AAPA New Member

    How to run MYSQLTUNER?
    I also have the same issue...
     
  11. Alex880

    Alex880 New Member

    Code:
    [email protected]:~/major-MySQLTuner-perl-7bd1cce# ./mysqltuner.pl
    >>  MySQLTuner 1.4.4 - Major Hayden <[email protected]>
    >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
    >>  Run with '--help' for additional options and output filtering
    [OK] Logged in using credentials from debian maintenance account.
    [OK] Currently running supported MySQL version 5.6.23-1~dotdeb.3
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
    [--] Data in MyISAM tables: 83M (Tables: 368)
    [--] Data in InnoDB tables: 4M (Tables: 2)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
    [!!] Total fragmented tables: 15
    
    -------- Security Recommendations  -------------------------------------------
    [OK] There is no anonymous account in all database users
    [OK] All database users have passwords assigned
    [!!] User ‘maynames@%’ hasn't specific host restriction.
    [!!] User ‘mynames@%’ hasn't specific host restriction.
    [--] There is 605 basic passwords in the list.
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 47m 23s (119K q [42.002 qps], 2K conn, TX: 3B, RX: 16M)
    [--] Reads / Writes: 99% / 1%
    [--] Total buffers: 192.0M global + 1.1M per thread (151 max threads)
    [OK] Maximum possible memory usage: 352.4M (17% of installed RAM)
    [OK] Slow queries: 0% (0/119K)
    [OK] Highest usage of available connections: 9% (14/151)
    [OK] Key buffer size / total MyISAM indexes: 16.0M/35.3M
    [OK] Key buffer hit rate: 99.7% (2M cached / 6K reads)
    [!!] Query cache is disabled
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 42K sorts)
    [!!] Temporary tables created on disk: 66% (6K on disk / 10K total)
    [OK] Thread cache hit rate: 99% (22 created / 2K connections)
    [OK] Table cache hit rate: 29% (431 open / 1K opened)
    [OK] Open file limit used: 71% (729/1K)
    [OK] Table locks acquired immediately: 99% (125K immediate / 125K locks)
    
    -------- InnoDB Metrics -----------------------------------------------------
    [--] InnoDB is enabled.
    [--] InnoDB BufferPool Size :128.0M
    [--] InnoDB BufferPool Inst :8
    [OK] InnoDB buffer pool / data size: 128.0M/4.0M
    [!!] InnoDB buffer pool <= 1G and innodb_buffer_pool_instances(=1).
    [OK] InnoDB log waits: 0
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Restrict Host for user@% to [email protected]
        MySQL started within last 24 hours - recommendations may be inaccurate
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
    Variables to adjust:
        query_cache_type (=1)
        tmp_table_size (> 16M)
        max_heap_table_size (> 16M)
        innodb_buffer_pool_instances (=1)
    You can follow this:
    Access via ssh on you server
    Code:
    # wget https://github.com/major/MySQLTuner-perl/zipball/master
    # unzip master
    # rm master
    # cd major-MySQLTuner-perl-7bd1cce
    major-MySQLTuner-perl-7bd1cce# chmod +x mysqltuner.pl
    major-MySQLTuner-perl-7bd1cce# ./mysqltuner.pl
    ;-)
     
  12. till

    till Super Moderator Staff Member ISPConfig Developer

    The mysqltuner output is ok. The relevant part for this issue is "
    Maximum possible memory usage: 352.4M (17% of installed RAM)" so mysql can not load more data into ram then the ram size.

    The next step is to find out which processes are actually using the memor. Please check with e.g. top or "ps -aux" which processes are usng all that ram.
     
  13. AAPA

    AAPA New Member

    Thanks for the reply ...
    I ran the commands as you suggested and give below the final output...

    =========================================
    [email protected]:~/major-MySQLTuner-perl-7bd1cce#
    =========================================

    Is the above correct and enough ...?
     
  14. Alex880

    Alex880 New Member

    when you are inside the dir major-MySQLTuner-perl-7bd1cce#, you have to run this 2 line commands:
    chmod +x mysqltuner.pl (with this you give the right permission for run the file)
    ./mysqltuner.pl (whit this you run the script)
     
  15. AAPA

    AAPA New Member

    I think I have run the 2 commands successfully now ...
    And still it shows 92% usage of my 512 mb RAM...
    I have hosted only 1 domain ...
     
  16. till

    till Super Moderator Staff Member ISPConfig Developer

    Your ram usage is absolutely normal.
     
  17. Alex880

    Alex880 New Member

    Till, can you tell me if mysql have any error log? and if yes, where i can found it?
     
  18. till

    till Super Moderator Staff Member ISPConfig Developer

    The mysql logs are in /var/log/mysql/
     
  19. Alex880

    Alex880 New Member

    ok, i have a log. if it possible, only if possible and if is not a problem, can you see my log? Really i dont know why my mysql service go down costantly.
     

    Attached Files:

  20. Alex880

    Alex880 New Member

    any idea?
     

Share This Page