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.

Optimization for 2GB VPS CENTOS 6.2

Discussion in 'Workarounds and Optimization' started by SpaceCowboy, Feb 7, 2014.

  1. SpaceCowboy

    SpaceCowboy Active Member

    Joined:
    Jan 18, 2014
    Messages:
    28
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Hi guys i hope someone could advice me on what setting to use for this server. Since the suggestions i tried by lurking other folks thread didn't work so good for me.

    This is the actual my.cnf:
    Code:
    
    [mysqld]
    set-variable = max_connections=500
    log-slow-queries
    safe-show-database
    
    mysqltunner:
    Code:
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.1.72-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 9M (Tables: 78)
    [--] Data in InnoDB tables: 45M (Tables: 141)
    [!!] Total fragmented tables: 143
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 3d 14h 4m 49s (39M q [127.551 qps], 729K conn, TX: 551B, RX: 4B)
    [--] Reads / Writes: 96% / 4%
    [--] Total buffers: 34.0M global + 2.7M per thread (500 max threads)
    [OK] Maximum possible memory usage: 1.4G (70% of installed RAM)
    [OK] Slow queries: 0% (0/39M)
    [OK] Highest usage of available connections: 13% (69/500)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/16.6M
    [OK] Key buffer hit rate: 99.9% (133M cached / 76K reads)
    [!!] Query cache is disabled
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 7M sorts)
    [!!] Temporary tables created on disk: 39% (3M on disk / 9M total)
    [!!] Thread cache is disabled
    [!!] Table cache hit rate: 0% (64 open / 19K opened)
    [OK] Open file limit used: 0% (17/2K)
    [OK] Table locks acquired immediately: 99% (40M immediate / 40M locks)
    [!!] InnoDB data size / buffer pool: 45.7M/8.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
        Set thread_cache_size to 4 as a starting value
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
        query_cache_size (>= 8M)
        tmp_table_size (> 16M)
        max_heap_table_size (> 16M)
        thread_cache_size (start at 4)
        table_cache (> 64)
        innodb_buffer_pool_size (>= 45M)
    Bare in mind this is 1 website only with about 10,000 daily unique visitors using wordpress+small phpbb forums.
    I tried some tips on other threads but the site was unresponsive: pages started loading after a few seconds i clicked the links instead of instantly..site partially loading (missing sidebar), and general slowness, that's why i disabled cache, but maybe i was doing all wrong so what settings do you recommend for such a site? :)
     
  2. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    remove all the stuf you got in your current my.cnf
    and add

    [mysqld]
    skip-name-resolve

    myisam_use_mmap=1

    max_connections = 200
    max_user_connections = 75

    join_buffer_size=2M
    sort_buffer_size=256K

    table_open_cache = 1000
    table_definition_cache = 400
    max_allowed_packet = 32M

    thread_cache_size = 16

    query_cache_type = 1
    query_cache_size =30M
    query_cache_limit = 1M

    max_heap_table_size = 50M
    tmp_table_size = 50M

    key_buffer_size = 100M

    innodb_buffer_pool_size = 100M
    innodb_stats_on_metadata=0

    slow_query_log=1
    slow_query_log_file=mysql-slow.log
    long_query_time=0.1


    and restart, after several hours rerun mysqltuner.pl and post it here
    the difference should be huge, since your setup is very bad as default one
     
  3. SpaceCowboy

    SpaceCowboy Active Member

    Joined:
    Jan 18, 2014
    Messages:
    28
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Hi thinkbot! Thanks for helping me out, i am now using the config you suggested.
    Mysql service restarted with no problems the site seems to be running perfectly fine.
    I will post new stats tomorrow.

    Is there another files that might need tweaking?
    I'm thinking of php and apache config files, i haven't touch those at all.
    I don't know where they are either or if they are important to reduce server resources usage.
     
  4. SpaceCowboy

    SpaceCowboy Active Member

    Joined:
    Jan 18, 2014
    Messages:
    28
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Here the results after 24 hs uptime:

    Code:
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.1.72-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 9M (Tables: 78)
    [--] Data in InnoDB tables: 42M (Tables: 141)
    [!!] Total fragmented tables: 141
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 0h 2m 40s (9M q [111.018 qps], 162K conn, TX: 127B, RX: 1B)
    [--] Reads / Writes: 92% / 8%
    [--] Total buffers: 282.0M global + 2.9M per thread (200 max threads)
    [OK] Maximum possible memory usage: 857.0M (42% of installed RAM)
    [OK] Slow queries: 0% (331/9M)
    [OK] Highest usage of available connections: 22% (44/200)
    [OK] Key buffer size / total MyISAM indexes: 100.0M/13.4M
    [OK] Key buffer hit rate: 99.7% (8M cached / 22K reads)
    [OK] Query cache efficiency: 90.7% (6M cached / 7M selects)
    [!!] Query cache prunes per day: 80424
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 158K sorts)
    [OK] Temporary tables created on disk: 6% (24K on disk / 380K total)
    [OK] Thread cache hit rate: 99% (316 created / 162K connections)
    [!!] Table cache hit rate: 17% (262 open / 1K opened)
    [OK] Open file limit used: 9% (217/2K)
    [OK] Table locks acquired immediately: 99% (781K immediate / 781K locks)
    [OK] InnoDB data size / buffer pool: 42.8M/100.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
        query_cache_size (> 30M)
        table_cache (> 1000)
    
    Note that i used 2 methods to optimize database tables but none worked
    1)mysqlcheck -o --all-databases
    2)/http://www.justin.my/2010/09/optimize-only-fragmented-tables-in-mysql/
     
  5. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    those 2 methods don't change much

    Your current results are very good, you got only 6% of queries that create temporary tables on disk, so it's ok
    You have no problems to worry about now, it's very good

    you can increase query_cache_size a bit, to 50M or something like that
     
  6. cPanelPeter

    cPanelPeter Technical Analyst III
    Staff Member

    Joined:
    Sep 23, 2013
    Messages:
    569
    Likes Received:
    15
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    Twitter:
  7. SpaceCowboy

    SpaceCowboy Active Member

    Joined:
    Jan 18, 2014
    Messages:
    28
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Nice thanks a lot!

    I see, thanks for the advice.


    How many days is ok to let the server run before doing a reboot?
    And is there a way to automatize periodic restarts maybe with a cron job?
    I think it would be a good idea right?
     
  8. cPanelPeter

    cPanelPeter Technical Analyst III
    Staff Member

    Joined:
    Sep 23, 2013
    Messages:
    569
    Likes Received:
    15
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    Twitter:
    It's not necessary or recommended to restart the MySQL server on a regular basis. Why would you want to do that?
     
  9. SpaceCowboy

    SpaceCowboy Active Member

    Joined:
    Jan 18, 2014
    Messages:
    28
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    I was referring to the whole server, maybe to give it a break and start over clean i don't know...i thought it was needed, i feel stupid now.
     
  10. cPanelJared

    cPanelJared Technical Analyst
    Staff Member

    Joined:
    Feb 25, 2010
    Messages:
    1,842
    Likes Received:
    18
    Trophy Points:
    38
    Location:
    Houston, TX
    cPanel Access Level:
    Root Administrator
    The only normal reasons to reboot a Linux server are to upgrade the kernel, or if it is completely unresponsive. Other than that, Linux does not need to be rebooted periodically. It is different from Windows in that regard. Since Linux can stop and start processes easily, rebooting is not necessary to keep the server running well, and rebooting is also not a useful troubleshooting step.

    If there is a problem with a specific service on Linux, it is best to look at the logs for that service and troubleshoot it accordingly. Rebooting almost never helps and it can actually hurt the troubleshooting process, because after the reboot you cannot see what was running when the problem was happening.

    I hope this helps. Many new Linux users are accustomed to the Windows procedure of rebooting the server as a first troubleshooting step anytime a problem is encountered, or to try to keep the system running smoothly, but on Linux, rebooting is not helpful for troubleshooting and it is almost never needed for system maintenance.
     
  11. SpaceCowboy

    SpaceCowboy Active Member

    Joined:
    Jan 18, 2014
    Messages:
    28
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Oh, good to know i had no idea. Sometimes i'm forced to reboot it because i cannot fix the problem with loads going above "2.0" they can get up to 15 or even higher numbers then the site get's extremely slow. i don't know Linux so looking at the logs makes no sense to me. Is there any other config file on the server that could help stop this from happening? I would like track it down to fix it...but i don't know how to.
     
  12. cPanelJared

    cPanelJared Technical Analyst
    Staff Member

    Joined:
    Feb 25, 2010
    Messages:
    1,842
    Likes Received:
    18
    Trophy Points:
    38
    Location:
    Houston, TX
    cPanel Access Level:
    Root Administrator
    A basic working knowledge of the shell is important to successfully manage a server. Not everything is available in the WebHost Manager (many logs, for example). One of Linux's great strengths is that it logs almost everything, so understanding how to log into the shell and use basic commands to view and search through log files is very useful for running a Linux server.

    The following guide may help you to find the cause of the high load:

    http://forums.cpanel.net/f34/troubleshooting-high-server-loads-linux-servers-319352.html

    If you are not comfortable using the Linux shell, you may want to consider hiring a qualified administrator to help you. We have a list of companies that offer server management services here:

    All Services

    If you are interested in learning how to use the Linux shell to administer your server, there are many tutorials online. I did a Google search for "linux shell tutorial" and found the following:

    LinuxCommand.org: Learning the shell.
    The 5-Minute Essential Shell Tutorial - Linux Mint Community

    If you have never logged into a Linux system via SSH, there are also tutorials that explain how to do that online. I found the following using a Google search for "how to use ssh":

    http://www.wikihow.com/Use-SSH
     
  13. SpaceCowboy

    SpaceCowboy Active Member

    Joined:
    Jan 18, 2014
    Messages:
    28
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Thanks for all the links really appreciated. i'll try to learn as much as i can!
     
Loading...

Share This Page