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.

help me optimize my vps

Discussion in 'Workarounds and Optimization' started by roseadens, Dec 22, 2012.

  1. roseadens

    roseadens Registered

    Joined:
    Dec 22, 2012
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    my server spec are given below

    /http://serverbear.com/benchmark/2012/12/20/brDSe7wi2TFMrbb9


    Code:
    root@india [~]#  /usr/local/cpanel/3rdparty/mysqltuner/mysqltuner.pl
    
     >>  MySQLTuner 1.2.0_1 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at [url]http://mysqltuner.com/[/url]
     >>  Run with '--help' for additional options and output filtering
    
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.1.66-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 213M (Tables: 655)
    [--] Data in InnoDB tables: 208K (Tables: 13)
    [--] Data in MEMORY tables: 0B (Tables: 2)
    [!!] Total fragmented tables: 15
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 12h 5m 30s (567K q [13.031 qps], 6K conn, TX: 1B, RX: 73M)
    [--] Reads / Writes: 96% / 4%
    [--] Total buffers: 34.0M global + 2.7M per thread (151 max threads)
    [OK] Maximum possible memory usage: 449.2M (22% of installed RAM)
    [OK] Slow queries: 0% (41/567K)
    [OK] Highest usage of available connections: 18% (28/151)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/35.6M
    [OK] Key buffer hit rate: 100.0% (23M cached / 5K reads)
    [!!] Query cache is disabled
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 59K sorts)
    [!!] Joins performed without indexes: 2665
    [!!] Temporary tables created on disk: 38% (14K on disk / 37K total)
    [!!] Thread cache is disabled
    [!!] Table cache hit rate: 13% (64 open / 492 opened)
    [OK] Open file limit used: 12% (123/1K)
    [OK] Table locks acquired immediately: 99% (599K immediate / 600K locks)
    [OK] InnoDB data size / buffer pool: 208.0K/8.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        Enable the slow query log to troubleshoot bad queries
        Adjust your join queries to always utilize indexes
        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)
        join_buffer_size (> 128.0K, or always use indexes with joins)
        tmp_table_size (> 16M)
        max_heap_table_size (> 16M)
        thread_cache_size (start at 4)
        table_cache (> 64)
    _______________________________________________________________________________
    root@india [~/vivek]# nano /etc/my.cnf
    ****
    [mysqld]
    innodb_file_per_table=1
    
    _______________________________________________________________________________
    CentOS release 5.8 (Final) 64bit
    
    whm vps optimized
    Can u help me
     
    #1 roseadens, Dec 22, 2012
    Last edited: Dec 22, 2012
  2. Astral God

    Astral God Well-Known Member

    Joined:
    Sep 27, 2010
    Messages:
    180
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    127.0.0.1
    cPanel Access Level:
    Root Administrator
    You may follow MySQLTuner recommendations, restart your sql server, let the server run at least 48h and repost the results here, but also the content of your my.cnf.
     
  3. srpurdy

    srpurdy Well-Known Member

    Joined:
    Jun 1, 2011
    Messages:
    101
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    make:
    Code:
    key_buffer_size=50M
    join_buffer_size=256K #dont increase this optimize the database by adding indexes for best performance
    query_cache_size=32M
    query_cache_limit=30M
    query_cache_type=1
    tmp_table_size=32M #most likely you wont need to increase this
    max_heap_table_size=32M #most likely you wont need to increase this
    thread_cache_size=16K
    
    and like Astral said let it run for 48hours and post another report. :)
     
  4. roseadens

    roseadens Registered

    Joined:
    Dec 22, 2012
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Code:
    root@india [~]#  /usr/local/cpanel/3rdparty/mysqltuner/mysqltuner.pl
    
     >>  MySQLTuner 1.2.0_1 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at [url]http://mysqltuner.com/[/url]
     >>  Run with '--help' for additional options and output filtering
    
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.1.66-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 211M (Tables: 619)
    [--] Data in MEMORY tables: 0B (Tables: 2)
    [!!] Total fragmented tables: 6
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 3d 18h 8m 53s (3M q [11.323 qps], 27K conn, TX: 14B, RX: 411M)
    [--] Reads / Writes: 98% / 2%
    [--] Total buffers: 40.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/3M)
    [OK] Highest usage of available connections: 1% (9/500)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/35.3M
    [OK] Key buffer hit rate: 100.0% (173M cached / 15K reads)
    [!!] Query cache is disabled
    [OK] Sorts requiring temporary tables: 0% (10 temp sorts / 399K sorts)
    [!!] Joins performed without indexes: 20499
    [!!] Temporary tables created on disk: 37% (105K on disk / 281K total)
    [OK] Thread cache hit rate: 99% (9 created / 27K connections)
    [OK] Table cache hit rate: 33% (85 open / 255 opened)
    [OK] Open file limit used: 0% (156/65K)
    [OK] Table locks acquired immediately: 99% (4M immediate / 4M locks)
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Enable the slow query log to troubleshoot bad queries
        Adjust your join queries to always utilize indexes
        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_size (>= 8M)
        join_buffer_size (> 128.0K, or always use indexes with joins)
        tmp_table_size (> 32M)
        max_heap_table_size (> 32M)
    Code:
    root@india [~]# nano /etc/my.cnf
    [mysqld]
    # innodb_file_per_table=1
    # INNODB #
    innodb_flush_method            = O_DIRECT
    innodb_log_files_in_group      = 2
    innodb_log_file_size           = 32M
    innodb_flush_log_at_trx_commit = 1
    innodb_file_per_table          = 1
    innodb_buffer_pool_size        = 8M
    
    # CACHES AND LIMITS #
    tmp_table_size                 = 32M
    max_heap_table_size            = 32M
    query_cache_type               = 0
    query_cache_size               = 0
    max_connections                = 500
    thread_cache_size              = 50
    open_files_limit               = 65535
    table_definition_cache         = 4096
    table_open_cache               = 4096
    
    myisam-recover=backup,force
    skip_name_resolve
     
Loading...

Share This Page