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 Request my.cnf for 4 core + 3gb Ram of Vps

Discussion in 'Workarounds and Optimization' started by fancier, Feb 24, 2014.

  1. fancier

    fancier Member

    Joined:
    Oct 23, 2012
    Messages:
    13
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Guide my.cnf for 4 core + 3gb Ram of Vps

    I have SMF
    150 Tables
    550 mb Database of MYSQL
    Please see my.cnf and guide for optimization

    Code:
    [mysqld]
    wait_timeout=50
    interactive_timeout=100
    connect_timeout = 10
    key_buffer_size=512M
    query_cache_size=30M
    query_cache_limit=1M
    tmp_table_size=50M
    table_cache = 2048
    max_heap_table_size=50M
    thread_cache_size=64
    long_query_time = 5
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    user=mysql
    # Default to using old password format for compatibility with mysql 3.x
    # clients (those using the mysqlclient10 compatibility package).
    old_passwords=1
    
    # To allow mysqld to connect to a MySQL Cluster management daemon, uncomment
    # these lines and adjust the connectstring as needed.
    #ndbcluster
    #ndb-connectstring="nodeid=4;host=localhost:1186"
    
    [mysqld_safe]
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid
    
    [ndbd]
    # If you are running a MySQL Cluster storage daemon (ndbd) on this machine,
    # adjust its connection to the management daemon here.
    # Note: ndbd init script requires this to include nodeid!
    connect-string="nodeid=2;host=localhost:1186"
    
    [ndb_mgm]
    # connection string for MySQL Cluster management tool
    connect-string="host=localhost:1186"
    
    innodb_buffer_pool_size = 512M
    innodb_additional_mem_pool_size = 20M
    innodb_log_file_size = 10M
    innodb_log_buffer_size = 64M
    innodb_flush_log_at_trx_commit = 1
    innodb_lock_wait_timeout = 100
    
     
  2. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    run mysqltuner.pl and post results here
     
  3. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,682
    Likes Received:
    654
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
  4. fancier

    fancier Member

    Joined:
    Oct 23, 2012
    Messages:
    13
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    How to run mysqltuner.pl
     
  5. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
  6. fancier

    fancier Member

    Joined:
    Oct 23, 2012
    Messages:
    13
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Code:
    >>  MySQLTuner 1.3.0 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
     >>  Run with '--help' for additional options and output filtering
    Please enter your MySQL administrative login: realinfo
    Please enter your MySQL administrative password:
    [OK] Currently running supported MySQL version 5.0.96
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: ERROR 1109 (42S02) at line 1: Unknown table 'ENGINES' in information_schema
    Use of uninitialized value in concatenation (.) or string at ./mysqltuner.pl
            line 547, <> line 2 (#1)
        (W uninitialized) An undefined value was used as if it were already
        defined.  It was interpreted as a "" or a 0, but maybe it was a mistake.
        To suppress this warning assign a defined value to your variables.
    
        To help you figure out what was undefined, perl tells you what operation
        you used the undefined value in.  Note, however, that perl optimizes your
        program and the operation displayed in the warning may not necessarily
        appear literally in your program.  For example, "that $foo" is
        usually optimized into "that " . $foo, and the warning will refer to
        the concatenation (.) operator, even though there is no . in your
        program.
    
    
    [--] Data in MyISAM tables: 468M (Tables: 111)
    [--] Data in InnoDB tables: 30M (Tables: 34)
    [--] Data in MEMORY tables: 124K (Tables: 1)
    [!!] BDB is enabled but isn't being used
    [!!] Total fragmented tables: 5
    
    -------- Security Recommendations  -------------------------------------------
    ERROR 1142 (42000) at line 1: SELECT command denied to user 'realinfo'@'localhost' for table 'user'
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 3h 47m 56s (667K q [48.812 qps], 37K conn, TX: 3B, RX: 200M)
    [--] Reads / Writes: 60% / 40%
    [--] Total buffers: 852.0M global + 2.7M per thread (100 max threads)
    [OK] Maximum possible memory usage: 1.1G (37% of installed RAM)
    [OK] Slow queries: 0% (1K/667K)
    [!!] Highest connection usage: 100%  (101/100)
    [OK] Key buffer size / total MyISAM indexes: 712.0M/75.4M
    [OK] Key buffer hit rate: 100.0% (186M cached / 50K reads)
    [OK] Query cache efficiency: 48.0% (209K cached / 436K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 9% (1K temp sorts / 15K sorts)
    [OK] Temporary tables created on disk: 11% (581 on disk / 4K total)
    [OK] Thread cache hit rate: 99% (143 created / 37K connections)
    [!!] Table cache hit rate: 9% (1K open / 11K opened)
    [OK] Open file limit used: 13% (667/5K)
    [OK] Table locks acquired immediately: 99% (662K immediate / 662K locks)
    [!!] InnoDB  buffer pool / data size: 8.0M/30.2M
    [OK] InnoDB log waits: 0
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Add skip-bdb to MySQL configuration to disable BDB
        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
        Reduce or eliminate persistent connections to reduce connection usage
        Increase table_cache gradually to avoid file descriptor limits
        Read this before increasing table_cache over 64: http://bit.ly/1mi7c4C
    Variables to adjust:
        max_connections (> 100)
        wait_timeout (< 90)
        interactive_timeout (< 90)
        table_cache (> 2500)
        innodb_buffer_pool_size (>= 30M)
     
  7. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,682
    Likes Received:
    654
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
  8. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    in general its good, except InnoDB buffer,
    replace my.cnf with

    Code:
    [mysqld]
    # Default to using old password format for compatibility with mysql 3.x
    # clients (those using the mysqlclient10 compatibility package).
    old_passwords=1
    
    
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    user=mysql
    
    max_connections = 150
    max_user_connections = 75
    
    wait_timeout=50
    interactive_timeout=100
    connect_timeout = 10
    key_buffer_size=200M
    query_cache_type = 1
    query_cache_size = 30M
    query_cache_limit = 1M
    tmp_table_size=50M
    max_heap_table_size=50M
    table_cache = 1000
    thread_cache_size=64
    
    slow_query_log=1
    slow_query_log_file=mysql-slow.log
    long_query_time = 0.1
    
    innodb_buffer_pool_size = 75M
    
    [mysqld_safe]
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid
    
    
     
Loading...

Share This Page