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.

Optimizing MySQL for 512Mb : (80% of installed RAM)

Discussion in 'Database Discussions' started by LeGastronome, Oct 23, 2010.

  1. LeGastronome

    LeGastronome Member

    Joined:
    Oct 21, 2010
    Messages:
    17
    Likes Received:
    0
    Trophy Points:
    1
    Hello,

    After several readings about MySQL optimizations, I have decide to optimize my server :

    After the first run of MySQLTuner :
    I had : Maximum possible memory usage: 2G (400% of installed RAM)

    On a 512MB server, it seems to be a little too huge :)

    so after many changes in the .conf I have this, Could you tell me if is it ok for you ?

    Code:
    >>  MySQLTuner 1.0.1 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
     >>  Run with '--help' for additional options and output filtering
    
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.0.91-community-log
    [OK] Operating on 32-bit architecture with less than 2GB RAM
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
    [--] Data in MyISAM tables: 60M (Tables: 243)
    [!!] InnoDB is enabled but isn't being used
    [!!] Total fragmented tables: 1
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 15h 25m 31s (327K q [5.899 qps], 4K conn, TX: 483M, RX: 52M)
    [--] Reads / Writes: 77% / 23%
    [--] Total buffers: 142.0M global + 2.7M per thread (100 max threads)
    [OK] Maximum possible memory usage: 410.7M (80% of installed RAM)
    [OK] Slow queries: 0% (2/327K)
    [OK] Highest usage of available connections: 6% (6/100)
    [OK] Key buffer size / total MyISAM indexes: 100.0M/36.4M
    [!!] Key buffer hit rate: 92.7% (909K cached / 66K reads)
    [OK] Query cache efficiency: 94.5% (285K cached / 302K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 8K sorts)
    [!!] Joins performed without indexes: 244
    [OK] Temporary tables created on disk: 18% (419 on disk / 2K total)
    [OK] Thread cache hit rate: 99% (6 created / 4K connections)
    [OK] Table cache hit rate: 42% (216 open / 511 opened)
    [OK] Open file limit used: 36% (373/1K)
    [OK] Table locks acquired immediately: 99% (47K immediate / 47K locks)
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Add skip-innodb to MySQL configuration to disable InnoDB
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        Adjust your join queries to always utilize indexes
    Variables to adjust:
        join_buffer_size (> 128.0K, or always use indexes with joins)
    And the .conf :

    Code:
    [client]
    #port            = 3306
    #socket          = /var/lib/mysql/mysql.sock
    
    # Disable Berkley DB functionality, saving memory
    #skip-bdb
    
    ###The MySQL server
    [mysqld]
    #port            = 3306
    #socket          = /var/lib/mysql/mysql.sock
    key_buffer = 100M
    max_connections = 100
    max_allowed_packet = 1M
    table_cache = 256
    #sort_buffer_size = 4M
    #read_buffer_size = 4M
    myisam_sort_buffer_size = 32M
    thread_cache_size = 8
    wait_timeout = 60
    connect_timeout = 30
    interactive-timeout = 50
    query-cache-type = 1
    query_cache_size = 16M
    #query_cache_limit = 1M
    # Try number of CPU's*2 for thread_concurrency
    #thread_concurrency = 8
    max_user_connections = 50
    log-error = /var/log/mysqld.log
    log-slow-queries = /var/log/mysqlslow.log
    old-passwords = 1
    
    [mysqldump]
    #quick
    max_allowed_packet = 2M
    
    [mysql]
    no-auto-rehash
    # Remove the next comment character if you are not familiar with SQL
    #safe-updates
    
    [isamchk]
    key_buffer = 64M
    sort_buffer_size = 64M
    read_buffer = 4M
    write_buffer = 4M
    
    [myisamchk]
    key_buffer = 64M
    sort_buffer_size = 64M
    read_buffer = 4M
    write_buffer = 4M
    
    [mysqlhotcopy]
    interactive-timeout
    
    Thanks
     
  2. 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
    Let's go ahead and remove the stuff commented out and fix things improperly listed (like key_buffer which is actually key_buffer_size):

    ###The MySQL server
    [mysqld]
    key_buffer_size = 100M
    max_connections = 100
    max_allowed_packet = 1M
    table_cache = 256
    myisam_sort_buffer_size = 32M
    thread_cache_size = 8
    wait_timeout = 60
    connect_timeout = 30
    interactive-timeout = 50
    query-cache-type = 1
    query_cache_size = 16M
    query_cache_limit = 1M
    max_user_connections = 50
    log-error = /var/log/mysqld.log
    log-slow-queries = /var/log/mysqlslow.log
    old-passwords = 1

    [mysqldump]
    max_allowed_packet = 2M

    [mysql]
    no-auto-rehash

    [isamchk]
    key_buffer_size = 64M
    sort_buffer_size = 64M
    read_buffer_size = 4M
    write_buffer_size = 4M

    [myisamchk]
    key_buffer_size = 64M
    sort_buffer_size = 64M
    read_buffer_size = 4M
    write_buffer_size = 4M

    [mysqlhotcopy]
    interactive-timeout

    If you aren't using InnoDB as the report appears to indicate, then you can skip it, so you could add:

    skip-innodb

    I did uncomment query_cache_limit since, even though you have it set to the default, it's easier to have the three that are associated together in the my.cnf (query_cache_type, query_cache_size and query_cache_limit).

    Next, you can try optimizing all databases to defragment tables:

    Code:
    mysqlcheck -u root -p --auto-repair --check --optimize --all-databases
    Finally, you've cut off the top of the report that tells your MySQL version. Which MySQL version are you using?

    Code:
    mysql --version
     
Loading...

Share This Page